Postgres FM - How to become a DBA

Episode Date: August 26, 2022

And few things we mentioned: Topic request on Reddit — thanks HerbyHoover!Haki Benita's blog PostgreSQL documentation (table of contents) Planet PostgreSQL (blog aggregator) MVCC Unmasked (by Bruce Momjian) The Internals of PostgreSQL (by Hironobu SUZUKI)PostgreSQL 14 Internals — parts I and II (by Egor Rogov)Cybertec blogmodern-sql.com (by Markus Winand)use-the-index-luke.com (by Markus Winand)The Art of PostgreSQL (by Dimitri Fontaine) explain.depesz.comexplain.dalibo.com ------------------------What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov and @michristofidesIf you would like to share this episode, here's a good link (and thank you!)Postgres FM is brought to you by:Nikolay Samokhvalov, founder of Postgres.aiMichael Christofides, founder of pgMustardWith special thanks to:Jessie Draws for the amazing artwork  

Hello and welcome to PostgresFM, a weekly show about all things PostgresQL. I am Michael, founder of PD Mustard, and this is my co-host Nikolai, founder of Postgres AI. Hey Nikolai, what are we talking about today? Hi Michael, I guess today we again are going to react to feedback or requests. We saw on Reddit a very good request asking us to discuss how to become DBA if you're currently in a different area and you want to shift to this field. So let's discuss this. Yeah, absolutely. It was really nicely worded, wasn't it? It was around both what are the expectations maybe for a junior DBA and also

what would their responsibilities typically be. And I think this is going to vary quite a lot depending on the type of organization, and we can go into that for sure. But equally, there doesn't seem to be a ton of good materials out there on this. So I'm looking forward to hearing your thoughts on it. So yeah, should we start with what maybe the... What is DBA? Yeah, what do we mean by DBA? Database administrator. I guess last decade, this term changed its meaning a lot, right? And it's many, many factors here. Originally, DBA was like sysadmin style person

who should be invisible. If work is done well, it should be like, nobody knows DBA is there because it's like people learn about, exist only in case of some incidents, troubles and so on. But in cloud era some of work almost disappeared because if you use the managed version of Postgres as Cloud SQL on GCP or RDS on AWS, of course you don't need to be an expert in backups and replication at all. Moreover, yesterday, I remember on Twitter, we discussed once again that dumps are not backups. And I have belief that RDS plays a big role in other managed Postgres services. They play a big role in teaching bad things because people don't realize what backups are. I mean, junior people, junior engineers.

And they also want at the same time to sometimes to have backups in hand. But you cannot take backup from RDS. So you still use a dump. But probably this is a different topic, right? Dumps versus normal backups. But anyway, like some functions of DBA vanished because they were replaced by a managed service. Others appeared, like knowledge about how to manage cloud resources and so on. Somehow reacting to this, some organizations started to introduce new terminology like DBR-RE.

Also reacting to SRE concepts? So we're talking about site reliability engineers, database reliability engineers, which is a term, the latter of those is a term I've only started hearing more recently. But I think that that factors into where these tasks are done in an organization and not necessarily what title they have. So in the past, well, actually, even nowadays,

in a small organization, there's a really good chance you don't have any person with DBA in their name, you might not even have an SRE by title, if you've only got a team of four, probably the founder handles it, or one of the engineers takes the lead on that. So I guess if we start with tasks, you've already mentioned backups, but I think there's a there's like a few more that used to typically live within the DBA role and still exist, but maybe get split in different roles these days. But when I was at Redgate, we always had different tools kind of dedicated to different roles. And we always thought of our monitoring tool as predominantly a DBA tool. We had backup tools and they were predominantly a DBA tool.

And we had a few others as well. Yeah, well, yeah. So replication and major upgrades I'd also consider were typically part of the DBA role. Maybe not so much anymore, but again, with managed services handling some of that.

But monitoring, I know we've already discussed it in depth just recently, but that does... Yeah, we should say that those who just joined us a week ago, we discussed Postgres monitoring, and I think it was quite a successful episode, so please check it out. But 100%, monitoring is still on the plate, and it's a very relevant skill that BAs should have.

But you're missing a big chunk of, or maybe just not mentioned it yet, tuning, performance tuning and query analysis, query optimization, right? Yes. So sometimes that, so once an organization is large enough, sometimes that gets put into a different team than the engineering team. And that is often called the database team or the DBA team or something like that. But I've also seen that work taken on 100% by a development team. So I have seen some of that. I guess they're doing DBA tasks at that point. Is that what you're saying? Well, when we discuss these things, we see that some things already went to managed services, if you use them. But others, actually all of them, when I just named them,

I immediately distinguished two areas, infrastructure DBA and application DBA. I like it a lot. I like this classification. It's very good. I've never seen it in a job advert, but the first time I ever heard the phrase application DBA was reading a Haki Benita blog post.

I don't know if that's where. Yeah. So either. Yeah. Well, kudos to him. Great blogs. If you haven't checked them out, I'll link them up in the show notes. But yeah, that feels like a really good distinction.

And maybe what we're saying is that in the world of having managed services, there is less need for some of those infrastructure DBA tasks, but just as much need for the application DBA tasks. but just as much need for the application DBA tasks. Right, exactly. And I guess if we talk about junior DBA and how to start, the first question is which database system and which of these two areas you think most about, like infrastructure area, people, teams still manage Postgres themselves a lot. Like RDS is not like 100% of cases, right?

So infrastructure DBA or application DBA? This is the main question to start because different skill sets are needed. Yeah, that's a good point. So you're thinking of going... There is overlapping, of course, right? There is overlapping. Yeah. Well, and at smaller organizations, you might need to do both, even in that kind of juniors

type role i've definitely seen some people fall into the role like they've one of our early customers described themselves as a pretend dba they were you know they were just the person in there they didn't even step forward for the role when it was asked they just were the one person that didn't step back when they were picking the person that was gonna to look after it. But I guess if we talk about larger organizations, it's a bit easier because you can have these specialist teams or areas. And maybe if you can find a role in one of those where you can learn from the other application DBAs or the other infrastructure DBAs, then you've got a smaller set of responsibilities

that you need to learn about. and you can maybe get skilled at before either learning the others or specializing deeper. Right. Well, in larger organizations, I think the need in application DBS is usually bigger than in infrastructure DBS. And also thinking about structure, infrastructure team probably is like some big department and if they exist and they can work only with managed services or they can manage Postgres for themselves. But it's some kind of monolithic entity. But if we think about engineering in bigger organizations, usually it's split to if they follow microservices, for example, it's a lot of teams, smaller teams. Even if there is no microservices approach. Still, there are many teams. And I strongly believe that it's good when organization management found a way to have growing knowledge of databases inside each team.

So there are probably a few people who are experts, but inside each team, it's good to have junior DBS and mid-level DBS, application DBS. They don't need to understand details about backups and replication. Monitoring, probably, yes, some part of it, which is related to optimization of queries and some other basics like transaction locking and so on. But I saw people who can optimize SQL, but they don't understand backups at all. And vice versa, I saw very good infrastructure DBS who even cannot write complex SQL at all. Like only very basic. So it's like there is a split here. And I think application DBAs is like overall, if you count numbers,

I believe industry needs more application DBAs than infrastructure DBAs in general. Yeah, that makes sense. And it makes sense even if we're not talking solely about job titles. So a lot of those SREs, DBREs, a lot of the developers that have to take on DBA responsibilities, the majority of their DBA type work is probably still on the application side, worrying about correct indexing or looking at some dodgy queries that areM is spitting out, that kind of thing. And maybe advising team members on pull requests. If they have people in their team that don't know as much about databases, maybe giving them feedback or explaining to them a better way of doing something in the database. Right. So let's start probably with how to be a junior infrastructure DBA, right? And then we will discuss how to be junior application DBA. Where to start, what to do,

like what knowledge should be definitely in skill set or knowledge set and how to develop skills further. If you start any DBA, but if you start, my first recommendation is just to read documentation. If you print it, it's 3,000 pages, if I'm not mistaken. Really? secretary said of so podcast ai and she said oh i just printed 3 000 pages this is this is you i don't know i just use it and suggest people how to use it better that's it so that's how i i know 3 000 pages at a4 of letter size format it's very well structured of course and i suggest reading it from very beginning and just read it and course, skip some non-relevant parts,

but very beginning and a lot of interesting stuff. So documentation is great. It's missing a lot of things like use cases, but it's great. It's actually split quite well for this topic. I'm just looking at the table of contents right now. And there's obviously a really good introduction at the beginning. And then it does a whole load on SQL language, which I'd probably consider a bit more on the application DBA side of things.

Yeah, as I've said, there are infrastructure DBAs who don't know SQL almost. So it's possible. But then the next whole big section is on server administration. So that feels like a really good place to start for the infrastructure DBA side of things. Exactly, yeah. And slight aside, but the documentation is famously good in Postgres. So I know not all systems have good documentation,

but I found it really approachable when I was first getting to know Postgres. And the other thing that I found surprisingly accessible to me when I was new to Postgres was the source code as well. Not the source code itself, but the comments in the source code as well. Not the source code itself, but the comments in the source code. So if you're ever interested in why something's a certain way or why a certain default is a certain number, that kind of thing, often there's a really good comment

in the source code that will tell you why. So don't be scared of that. Definitely, absolutely. And I always say the same. But it's already kind of deeper than junior, maybe. But also, if you go to source deeper, sometimes important concepts are missing in documentation. It happens. For example, heap-only tuples, tuples. Heap-only tuples, right? Currently, hackers, I saw the discussion of hackers, and it's about to be added to documentation or just recently added, and it will be in Postgres 15. So it's still missing, and I always used link to source code to explain what it is.

So source code, it augments documentation. Yeah. By the way, I looked it up and both tuples and tuples are very much acceptable. So it's good news. Problem solved. We discussed it three weeks ago or two. Right. But don't expect a good how tos in documentation or in source code. This is what is missing there. And like that. But don't expect good how-tos in documentation or in source code. This is what is missing there. And for that, you need books, video courses, maybe, or articles, blog posts. There are many, many very good materials around. Planet Postgres or org will show some of them. How-tos usually are there. Unfortunately, Postgres documentation doesn't have how-tos. almost, like step-by-step

and so on. Otherwise, it'd be 10,000 pages. Exactly. But yeah, the other thing I'd say is, I think it's acceptable and okay as a junior DBA to only know one way of doing something. So make sure you know the basics and how the default is in Postgres and a way of solving a problem. And maybe don't worry about there's 20 different backup tools. Make sure you know and can use one of the better ones or you understand the basics of one of them. I wouldn't worry too much about this. There's so much depth in each of these topic areas that you could go into. I'd recommend more getting a breadth of the tasks

and being able to solve each one a little bit instead of... Or how would you say? Well, it depends on personality. Some people, like they read documentation. It's like a book, which educates you, explains the concepts, shows some examples and so on. But some people just need, like modern time, we live in a stack overflow approach is very popular. And sometimes people just need step-by-step recipes. And this is probably bad because if you skip reading books, it's bad. But if you already read the book and just need a few examples how to do something step-by-step, it's a good thing.

I'm not saying that documentation doesn't have examples. But it's structured in a way to explain things, to teach, not to show how to perform basic things or more advanced things. So topics that infrastructure DBA should know, of course, MVCC, and I can recommend materials from Bruce Momgen, of course, right? We will provide some links.

Bruce always has perfect presentations, especially explaining basics. Also backups, replication, monitoring, transaction processing. What else? Upgrades, minor upgrades, major upgrades. Upgrades. Extensions. Maybe awareness of operating system and how the operating system and the database interact.

Well, in general, a good DBA, a infrastructure DBA, should be a good expert in Linux, definitely, and file systems. In clouds, if it's a cloud environment, most of the cases currently are cloud environments. So, of course, these areas should be covered with certain depth. And actually, also, we discussed before our episode that sometimes people manage multiple database systems. I feel very rare, like successful stories like that, it's quite rare. Database system is a very, very advanced system. It's very deep, advanced.

And I saw ideas, like we have a bunch of experts in Oracle, for example. Let's teach them all in Postgres. Our organization has them both. So they will be experts in both. It's very difficult to follow both areas. I don't have time to catch up. For example, when I'm in America, I wake up a lot of European guys posted so many great materials about Postgres. I want to read them all, but I don't have time, right?

And if you put Oracle on SQL Server, you just don't have time, right? And if you put Oracle on Play or SQL Server, you just don't have time to develop yourself. So my point is that you should be a good expert in just one system. Of course, it's possible to be polyglot. It's possible. But you won't be very deep eventually. And I guess the important point for juniors is that it's totally okay to only know one. And Postgres is a really good choice right yeah i agree but it shouldn't worry you if you're considering a junior role and you only have experience in one database management system it's okay to learn one there will be good jobs out there for you right right but you should be not expert but you should know some languages

additionally because sometimes you need to code something. And also if your colleagues use some languages, you will need to understand how some libraries or RMs, various middleware things work, right? This feels like a really good time to switch over. I feel like we've talked about infrastructure a little bit. So yeah, let's switch over to applications. Before we do that, just let's mention quite good books, both available online. If you want to go deeper, of course, you need to understand internals. Because internals is how things work from inside.

If you understand them, you can explain many things which are impossible to explain if you don't go inside. And the first is internals online from Suzuki, right? Sorry, I don't remember the name. We'll get it. I'll get it in the link. Suzuki was definitely... Sorry about the name. But the second one was recently published from Igor Rokov,

and it was translated from Russian. It's a very good book. It's missing many parts, of course, but the parts it has covered very well. For example, indexes, how indexes work, various types of indexes. So I recommend both. Yeah, you were right, by the way, Hironobu Suzuki. Sorry, I forgot the first name.

Apologies. Right. These two books will give you a very, very far horizon for development to be more advanced DBA. Actually, infrastructure first, but also application, because if you want to understand how various parts are working, you need to go into internals if you're an application dev. Yeah, 100%. Both of them are excellent and go quite deep, but they're also written simply, and I found it able to follow topics that I didn't know about at all and learn about them.

So I think they're both excellently written. Yeah, it looks like they are very different, but I enjoy reading them both and I do it all the time. I return to specific topics. I use them as a reference to understand specific, like to recall something or to understand something

which I didn't understand before and so on, like all the time. This is for more advanced DBA. Two must have resources this time, in addition to documentation and source code, of course. Yeah. Oh, and finally, for simple things, how-tos. I criticize documentation in terms of how-tos.

There are many blog posts, but of course, let's mention one of them. This is CyberTech. And their posts are always brief, problem-oriented, and I enjoy reading them as well. They're like, let's take this problem and attack it. And also they explain things, but they show us how to do something, right? Yeah, true. I like them as well. I think they include some comics as well at the start, especially I think it's Lawrence who does.

But the other thing I was going to say is there's a blog aggregation site called Planet Postgres that not everybody seems to be aware of. And that's great resource the cybertech blog gets syndicated to it as do maybe 20 30 40 others that get released regularly so that's a great place to if you want to start seeing what people are writing about at the moment but of course if you want to know a bit more about a topic you can just search it and often one of those good blog posts will be surfaced right near the top. Okay, application DBA, where to start? Right, so what would be expected, let's say you've started as a developer or data analyst or something like that, and you've been taking on more and more database type tasks, you've become maybe a little bit of the expert in your team when it comes to database things but if you look at what a dba is expected to do maybe that's quite intimidating and

you feel like you've got a lot to learn where should you start or can i apply for a junior dba application dba role today and then learn those things as i as i work or what should i be doing in advance i think the number one step is to learn SQL and learn modern SQL. It can be not very advanced, but it should be modern. Not to be like from SQL 92 standard. And a good place to learn it, for example, it's modernSQL.com from Marcus Winan, right? Yeah. So also use the index look.

These two websites are very, like they are great, very great places to, of course, basics can be learned from anywhere, like from Postgres documentation, from books, courses. It's not a big deal, but these two resources will give you some good momentum towards modern SQL

and a little bit more advanced SQL. And you understand how much you can do and of course application DBA should know SQL very well yeah and I think that's a really good point even if you flick through modern SQL and just make sure you don't have any blind spots maybe you've already got really advanced on SQL through your through your development role or different role you have but maybe you just haven't had a need for window functions yet or so there's like maybe there's like one or two topics that you haven't had a need for window functions yet. Or so there's like, maybe there's like one or two topics

that you haven't had to learn yet. It's just not been necessary, but brushing up on those or learning about those and then maybe trying it out a little bit, that could be really helpful. I was going to mention one more book, which is The Art of Postgres.

I think that'd be a really good application. And since we started to talk about application, you already mentioned Haki Benita and he has beautiful posts, of course. And some of posts are structured in a way like items, one, two, three, four, and so on. It's very good. I found them very, very, they also will educate you and let you know something new all the time. And of let's let's do some buzzwords you mentioned window functions ct so with recursive ct lateral what else if you're there are things which are standard these things are standard but there are things which postgres has probably for many many years which also worth learning for example how to work with arrays arrays are there all the time. Or we mentioned in some of previous episodes,

row type or record type, row type basically. So how to wrap and unwrap these things. Many, many functions Postgres has, regular expressions and so on. JSONB? There's so many cool... Language inside language. Because JSONB is already in standard. Not JSONB in standard. JSON support is in modern SQL standard. So it's like language inside language. They are interconnected, of course. And, of course, this is a huge area of learning.

But even to get the basics, even to have a look at where in the documentation can I look up which functions there are? What could it be used for? I think there was, you mentioned CyberTech already. I think they did a really good blog post on when is it appropriate? Why do we have JSONB support? When is it a perfect use case? When is it a terrible use case? That's the kind of thing an application DBA should be helping their team with in terms of if they decide to just have an ID column and then a JSON B column,

they want to throw the entire schema in there. Maybe it's the application DBA that should be pushing back and saying, that's not the best idea. Or maybe if they do the opposite and they want a super wide table with loads of columns that are going to be mostly nulls, maybe that could be... Knowing when these things are appropriate feels like the job of the application DBA, even a junior one, maybe. Yeah, this is a good point.

So to start to do something, I always use Google. And it's so good that recently it was fixed and we see the latest major version in the search result page. But since you say how to choose something, here we already start to discuss a very important skill to make experiments. Yeah. It's so important to learn from the very beginning. something here we already start to discuss very important skill to make experiments yeah like it's so important to learn from very beginning instead of asking how to do something all or why it's not working it's good if people ask not like show me how to do like but show me how to reproduce something and compare and choose instead of asking what is better, JSON or pure relational approach, normalize to voice code

normal form, BCNF, right? Or like third normal form at least and so on. Instead of that, you just need to ask people, help me to conduct proper experiment so I will

be able in future to compare myself. And you know my approach, like for infrastructure DBA, we need multi-session experiments. We should be alone on the machine. But for application debates, it's totally fine to share one machine among multiple people and focus on I.O IO operations, mostly not on timing when you optimize queries. And you can conduct experiments and see results even if you have one connection. You don't need to pitch a bench. You don't need benchmarking approaches at all.

You just need to design two schemas, to write queries, to compare plans, to compare how many IO operations in both. And this is a basic recipe people always use and I'm always using. That's a really good point. Actually, I would include a lot of performance stuff in application DBA world. So in terms of knowing, maybe you want to learn, even if it's a junior, maybe you want to learn, of course, about B-tree indexes, but maybe you also want to learn about the

two or three most common types and when they're appropriate, as well as performance optimization work in general, being able to help people when and why their query might be slow. Right. Well, since I work in this area, in the area of scaling the process of how teams approach SQL optimization, I see how you can grow and you can grow your team. I think everyone should be an expert in SQL performance, but I see that 90% of people don't care. They just want to ship their features and to be good enough, right? So not to fail at release time. But since like if application DBAs, of course, should be performance experts, not huge experts, but some kind of experts and they should care about performance and they should protect the application from poor performance they should care about it and it's good if you like know how

to conduct experiments and if it's also good if you start using experiments in some kind of workflow constantly in the process. And it's good when also you ask others to conduct experiments, even if they cannot understand all the details, but you have some workflow in your team and the artifacts are collected. So you can, being application DBA, you can analyze them and suggest if it's good or not good, or how to optimize right and there are many many resources here i think we like out of time to cover all of them but of course explain tools are great examples here explain depish explain the libo pg mustard of course and these tools will of course you should have them and use them to see to read plans and so on yeah exactly

find one you like and get good at using it to solve problems. Actually, I remembered one last thing on the infrastructure side that I forgot that I don't think we mentioned was the maintenance side of things. Maybe that's both of these roles. Maintenance, you mean fighting with bloat, rebuilding indexes? And rebuilding indexes, yeah. I guess, are they the only maintenance tasks?

Well, minor upgrades, major upgrades, it's already a huge task still. But minor upgrades can be considered as a maintenance task. Yeah. Some switchovers when we move to some newer hardware, for example, and so on. Yeah. Awesome. We can add a lot. I feel we didn't cover well application DBA because performance topic is huge. For example, I mentioned regular expressions, but if you use them blindly, when you grow to a billion rows, you won't be some ways to improve this area. But it's huge. You need to

deal with many extensions. You need to understand partitioning and so on and so on and so on. Well, but let's say for a junior person coming from a development background type thing, I think they'll have a lot of those things, the basics of them already. So it might be a wider topic if you were to go from, let's say, straight out of school or straight out of college into that role. There would be a lot to learn. But if you're coming from a different profession, I think a lot of those skills, you might already have some experience with them. And maybe it's an easier role to transition into from a different one. And then the other point I wanted to make around that before we finish was that the role could be quite different at different stages of company. And depending on how you learn best, it might be that you'd be much better off in one of these than the others. But it's definitely worth considering, you know,

if you love being chucked in the deep end, and you love dealing with problems by facing them head on, maybe a startup where they're scaling very quickly is a great place to learn and maybe getting a developer role and putting your hand up when there are database problems that's a great way of learning if that's where you thrive but if that is terrifying to you and i would totally empathize then maybe you're going to be better off joining a much bigger company or much more like maybe steady stable company that's smaller where you can learn from somebody who's already been doing it. And you've got time to experiment and you've got time to learn these skills when it's not an emergency and maybe have some help when there is an emergency. And there's a few of you to be able to jump on the problem together, that kind of thing. The other third category that I hadn't really thought much about,

but is really common in the Postgres world, is the consultancy space. there's a lot of people hire dbas or outsource some of this work so there are lots of consultancies that are hiring for dba roles and i i imagine that would be a great place maybe i'm not sure they hire many juniors but maybe once you've got those basic skills or maybe some of them do hire juniors and you can those those are the places where you can learn from real like deep experts and that might be where you thrive as well yeah and there are experts in specific topics of course so i agree and it's a good idea to to depending on the company size it's a good idea to hire consultants for short term or maybe for long term if you need to grow expertise internally constantly of course consultants will help them to

grow all the time it's a good good approach but maybe last recommendation from from me don't like we discussed this split between infrastructure and application dba but if you chose one side don't underestimate the other side for example if you came from the data analytics area to application DBA, and if you don't care about infrastructure at all, you just want to learn SQL, to understand explain, to understand some indexes, of course, about how replication works. You will be very, very surprised when you see that each time you update, not changing anything, new row version, new tuple is created. Or, for example, a long-running query on a standby node somehow affects the primary node and the performance degrades. This will be a big surprise to you and only understanding internals and the other side of this big area which we call dba will help you to know what to do next how to deal with it yeah

absolutely wonderful well thanks so much nicole i think that's a good hopefully that's a good overview thanks again for the request to cover this i'll i'll make sure to post this up as a reply. With all the links. Yeah, exactly. So yeah, I feel like we're giving people long reading lists, but it's worth it. So yeah, thanks everyone for joining us again. Keep the requests and suggestions coming. Subscribe, like, and share in your social networks and groups. Yeah, we really appreciate everybody that's done that so far.

It helps us a lot. It helps us get more and better suggestions as well. So appreciate it feedback drives us thank you so much yeah take care bye