When I Grow Up I Want to be a Database Administrator

Hi everybody, so I'm Karen Jex. I'm a senior solutions architect at Crunchy Data, and I'm going to try and remember to stay behind the podium so you can hear me. Thank you so much for letting me be here today to talk to you about my auto. Favourite topic, databases. And it is such a pleasure to be here. I'm very, very happy. So who am I? This is me in my happy place all splattered in mud. I live in a Small village in the French Alps. So when I'm not playing with databases or talking about databases, you'll usually find me out on one of my bikes, road, gravel, or mountain bike, depending on how I'm feeling. As I said, I'm currently a senior solution.

Architect at Crunchy Data, and I came to that from a long background as a database administrator, DBA. I'm on the PostgreSQL Europe Board of Directors. I'm leading the very, very recently established Postgres Grow Europe Diversity Task Force. And I write and present talks about databases at Postgres events and at developer events. So this is my third time being at DjangoCon Europe and And thank you so much for inviting me back. It really is a pleasure. I absolutely love spending time with the Django community. It's probably, you know, right up there on my list of favourite things to do. And I'm really glad I

I'm really glad I managed to get here just in time for yesterday's boat trip, which I think was awesome, so I hope you all enjoyed that as much as I did. So, when asked what do you want to be when you grow up, I doubt any kid ever said a database administrator. So I thought I'd try a show of hands. Who in this room When they were a kid, wanted to be a database administrator when they grew up. I I can wait. I'm shocked. Not a single hand went up. Can't believe it. I've spent my whole career working with databases, as you can see by this diagram

of my not very diverse career so far. So I've had such wide-ranging job titles as junior database administrator, database administrator. Senior Database Administrator, Database Ext, Database Consultant. In fact, my current job title, Senior Solutions Architect, is the only one I've ever had that doesn't have the word. Database in it, and I still work exclusively with databases. And even I didn't say I wanted to be a DBA when I grew up. Although I think at one point I did want to be a dinosaur, so I possibly didn't know all that much about. career options. Anyway, I'm a data person, so I thought

just in case I was wrong, and kids do actually say that they want to be database administrators when they grow up, I should do some actual research. research and look at some data. Fortunately lots of people had already done the hard work for me, so they've been out and spoken to kids and asked them what they want to be when they grow up and they've spoken to adults and asked them what they wanted to be when they were growing up. Tal And you'll actually be surprised to hear that database administrator, can I have a drum roll here? Yeah, yeah, it didn't feature in any of the lists. Bet or doctor was a popular choice, as was professional footballer, astronaut, obviously.

even engineer featured in most of the lists. But not database administrator. And in fact I've even had to inflict my drawing skills on you to be able to show you a picture of a DBA because it's not even easy to find useful images. So I have to concede that maybe, just maybe, not everyone is as passionate about databases as I am. In fact, most of the developers that I speak to just want the database to Quietly do its thing in the background so they can concentrate on developing their applications. Which even I have to admit doesn't seem like an unreasonable request. But the world of databases is changing. Traditional DBA role is becoming less and less common, and developers are often expected to manage their own databases.

And even if you don't have to actually look after your own databases, the database is likely the back. Bone of your application. So even in that case, you need to know how to interact with it. So I thought it would be fun, well, fun for a database administrator, to talk to you and think about how, as a developer, you can navigate this uncomfortable reality. And what you actually need to know about databases. So obviously, because I love databases so much, I think everyone should want to know everything about databases. If you don't Want to talk constantly about voice cod normal form, why not? If your party trick isn't to recite the acid properties of transactions and the different transaction isolation modes available in each of the database management systems.

And discuss how your chosen database management system either conforms to or differs from the uh SQL standard. Are you even really living? See me afterwards if you want more ideas for sparkling conversation starters. your next dinner party. I was in the audience for Danieli's The Attentive Programmer keynote at PyCon Italia a couple of weeks ago and I'm really glad that you all got to hear it yesterday and I hope you enjoyed it. as much as I did. And in that Danielli uh talked about the idea of writing a love program. And I really enjoyed the ode to his favourite camera written in Python form. If you didn't manage to catch the talk, I'd

Do you highly recommend watching that on the recording? But it got me thinking about how I would express love in database terms. So my chosen love language. Would have to be the entity relationship diagram. I think it's a beautiful way to represent almost any real-world object in terms of their attributes and the relationships between them. I love the fact That there are rules, there's syntax, there are conventions. I like the simplicity of the Crow's foot notation and the fact that so much information can be v can be conveyed by just a few little lines. So just from this tiny snippet here.

Here. We know that one department can have one or more employees, and that an employee belongs to one and only one department. I like the way that the colouring and the spacing and the organization of an Entity relationship diagram can convey the information about that and can help even a non-technical person to understand the different concepts and how the different pieces of data fit together. I've been known to lose myself rearranging entity relationship diagrams, making them look pretty and organized, making sure there are no crossing lines, making sure that it fits nicely on one sheet of paper, albeit sometimes a very large sheet of paper that I had to use a plotter to print out.

But that's probably just me. So what better thing to use my love language for than to describe one of my favourite things, a Postgres cluster? So the process of Creating this data model always seems quite simple to start with. So I uh defined my I modeled my Postgres cluster. Uh I showed that I can have one or more databases in my Postgres cluster, I can define one or more roles. Or users for my cluster. I can create one or more table spaces. Each of my databases can create one or can contain one or more schemas. Each of my tables will belong to one of those schemas and will reside in one of those table spaces. I can create an index on one or more columns, but equally a column can belong to one or more indices.

I could then start to model the permissions granted to the roles. I could model the fact that certain databases have access to certain table spaces. I need to think about how changes will be modelled over time. Do I want to store the history? I could add in other object types like views, functions, stored procedures, extensions. And I've not even started to add in the column. And the primary key columns, the foreign keys. And I had to force myself to stop there. Because the goal of this talk really isn't to come up with a realistic model of my Postgres cluster, but to show how I could lovingly draw. Draw my databases in this way. So thank you to Danieli for that inspiration. You can all thank him later as well.

Okay, so to go back to the original actual subject of the talk. What even is a DBA? And please don't answer that unless you can think of something polite and constructive to say. So, according to various different definitions that I took from careers sites, from database management systems, Vendors, um various different places, there seems to be a general consensus that a DBA is someone who uses specialist software to manage and secure computer systems that store data. Everyone's agreed, which tells us pretty much nothing at all about what a DBA actually does all day. Okay, so what does a DBA actually do?

I compile the list of responsibilities based on um some of those same definitions came with a list of responsibilities. A lot of the uh job sites have got um job adverts that have lists of um lists of responsibilities. And it turns out to be a pretty Long list. So apparently a DBA is expected to do some or all of the following tasks: design, implement, and maintain backup and recovery procedures. Design and implement security procedures and manage database access. Monitor the database's availability, performance, security, space, and more. Logical and physical data modelling. We're all right there. I like that bit as you could see. 24/7 support and troubleshooting.

Plan performance. And test database software install and upgrades. And it goes on. Provide database expertise, advice and support to other teams. Fix performance problems. Generally improve database performance. Plan for future database. database size and resource needs, create databases, design and implement database maintenance procedures, and make sure data protection GDPR rules are obeyed. And then there's the list of skills that a DBA is expected to have. So I created this chart last year based on data from um for the UK from IT Jobswatch. So it ranks the required skills and capabilities for DBA job roles.

based on how often they appeared in job adverts with DBA or database administrator in them. It's not very easy to read, but it boils down to SQL skills, procedural languages, data integration and ORMs. Knowledge of one or more database management systems, so this lists SQL Server, Oracle, and Postgres, for example. Knowledge of various operating systems, including Linux, Windows, and various cloud environments. Performance tuning. Database migration, disaster recovery, social skills. What are they trying to say about database administrators here? And when I did the quick check last week to make sure that this list was still up to date, social skills ranked even higher in the list.

Moving on, high availability, replication, clustering, DevOps, and automation methodologies and tools. That is a lot to know and a lot to do. Oh, and to make things even less clear, there are also different flavours or different types of DBA. So a traditional split is between the production DBA and the development DBA. So the development DBA would work closely with developers, focus Focusing on building and maintaining a database environment that supports the application development, and then usually throws things over the fence to the production DBA, who makes sure that the databases in your production environment are kept up and running, focusing on things. Like availability, performance, and security.

So it might even be that you are the development DBA in your organization just without the job title. In some organizations, the split is between application DBA. So the person concerned with the logical application-related aspects of the database, and the system DBA, so the person responsible for the underlying software and physical infrastructure. And in this situation, you'd probably be the application DBA. But as well as production DBAs and development DBAs and application DBAs and system DBAs, you'll also hear of data warehouse DBAs, cloud DBAs, database architects, and even replication DBAs or backup and recovery DBAs who just look after one aspect of database administration.

And these different types of DBA will differ from one organization to another, they'll differ sometimes between one team and the next. The division of responsibilities isn't always clear-cut, and the role Often overlap. Fortunately, you don't need to worry about all of that. You don't need to be an expert in all of that. I really enjoyed Katie McLaughlin's Keynote, what should you have to worry about from Django Con Europe 22 in Porto? I don't know who else saw that talk, but Katie pointed out that although, yes, you absolutely can run Django by creating a server, installing Django. Django and Postgres and Nginx and then making it available to all to the world, there are then all sorts of things to worry about, including server and network availability, operating system, Django and Nginx and Postgres

updates. Database migrations, database administration, backups, and more. And if you're the one worrying about all of those things, Katie wondered: are you a Django developer or are you a combination Django developer? And database administrator and systems administrator and network administrator and full stack engineer and grossly, grossly underpaid. But you absolutely don't need To know everything about databases. It pains me to say that, but you don't. I certainly don't know everything about application development, and even that statement makes it sound as though I know much more than I do. I know just enough.

To help developers out with their database-related questions. And likewise, you need to know just enough about databases to help you out with your application development. This is the agenda from a talk I gave called How to Keep Your Database Happy at PyCon UK last September. So, in that talk, I gave my top five tips for things that you can put in place without too much effort to make sure you Have a robust performant database environment. First, I recommended checking a few key configuration parameters, make sure that they're set correctly for your environment and your for your workload. Second, make sure your

Taking regular backups of your database and that you're testing your recovery process. Thirdly, put a high availability architecture in place so that if you lose your primary database, you've got something there to take over. Fourth, make Make sure that the right users and applications can connect with uh connect to and interact with your database. And finally put monitoring and um alerting in place. Make sure you know what's going on in your database, make sure that you know if something goes wrong, so that you can react quickly and fix things. I then gave about a three minute overview of how to do each of those things. Funnily enough, that wasn't enough

Time to go into the details of everything that you need to know to look after your database, i. e. everything that I've spent the last 25 years learning. And that was just the things that relate to the production system side of things. We didn't even Start to think about the application side of things, the database design, the query design, uh query performance, uh connection management, stored procedures, transaction management. But as I keep saying, you don't need to know all of these things in detail. For me the most important thing really is to know that these things exist and to know where to look and who to ask if you need more information.

So when I do talks I always try and include links to the relevant parts of the documentation, links to other resources, because I know that I can't cover everything in a 30-minute presentation. And even if I did manage to cover everything in a in a 30 minute presentation, nobody's going to remember all of the things I said. If when you come up against an issue, when something comes up, you think, oh, I vaguely remember Karen saying something about That and then you can go and read up on it. I feel as though I've done something useful. If you don't have the time, the skills, the inclination, or the infrastructure of

To do all of the things that you need to do to look after a database, there are various options, including managed services that will do all of that for you. The big cloud providers all provide managed database services. The company I work for, Crunchy Data, has a managed Postgres service called Crunchy Bridge. And there are plenty of others out there. Even if you do have the skills and the inclination, it might just be a better use of your time. Um To outsource some of these tasks and then you can use your expertise and your time as an application developer on things that are more important to you. So going back To that list of DBA responsibilities.

Which of those do you actually have to know about or be able to do yourself? Well, it's definitely useful to have some knowledge of how database backup and recovery works. You might Need to do it yourself for your dev environments, for example. You might need to make sure that sensible backup processes are in place for your chosen managed database services. And you definitely want to test out recovering a database in case something goes. Very wrong. Security. You might not be the one that designs and enforces database security practices, policies, but it's helpful to have them in the back of your mind so that when you're designing your application. Application, when you're coding, you've got an idea of some of the best practices.

So the principle of least privilege, for example, separation of duties , which users should connect to the database and how. So having an idea of what some of the metrics are that are shown on your database dashboard can be really valuable to help you with any debugging. Logical and physical data modelling. So that is a must from my point of view, and as you Saw that's one of you know one of my favorite things. Um having that vision of what the data in your system looks like and how it fits together can be really helpful when you're looking at the best ways to write code that accesses and updates. That data. Hopefully, you won't have to do 24

by 7 database support, but being able to do some basic database troubleshooting could save you a lot of time. So if you need to do things like debugging. connection errors, figuring out why a database went down, understanding why performance of a query suddenly dropped off a cliff. Hopefully you won't have to worry yourself about database software install and Upgrades that can hopefully be left to someone else, especially if you're using a managed service. Although database expertise is unlikely to be part of your actual remit, it will probably give you a boost if you manage to understand some database. Stuff and explain it to the rest of your team. It's definitely important to be able to work out why a particular query or part of your application is performing badly

and be able to make improvements to performance. Even better if you know. how to write code that will access the database in a performant way in the first place. Capacity planning might not be in your remit, but it is good to be aware of the resources that are being used for the database and how they're expected to grow over time. So that you can design your application to be able to cope with that change. As far as database creation is concerned, a lot of organizations are putting in place automated processes to create database clusters and individual. or databases. But even if that's the case or you're using a managed service where there's an API for that, it's helpful to know about some of the configuration parameters so that you can make sure that that database is being set up in a way that's useful.

for your um for your particular use case. Hopefully that elusive someone else will deal with general database maintenance tasks for you, especially if you're using a managed service. But again it's helpful to be familiar with some of the basics of things like autovacuum, rebuilding indexes, refreshing materialized views, uh keeping database statistics up to date, etcetera, just so that you're aware of the impact of um those Process is not running or you're aware of the impact that those processes could have on your application as they run. And then the final one on here, it can be a real pain, but obviously data protection, GDPR is everyone's responsibility.

So that one isn't a database administration-specific task, but it is tightly related to the database. And then going back to the list of DBA skills and capabilities, which are those? Do you actually need to have? So I know SQL SQL is the first on there. I know everyone uses ORMs, I know. But if you want to understand what the ORM is doing and potentially why a query Seems to be misbehaving, there's no better way than to understand and to be able to write your own SQL queries. You don't need to know about lots of different database technologies, just the database management system. That you're actually using is enough.

Obviously, I'm going to recommend using Postgres for pretty much any situation because that's the database I know and love. But you might have your reasons for using something else. I already touched on performance tuning and disaster. To recovery. Social skills. Well, having spent time with the Django community, I'd say you're generally fine on the social skills front and definitely several steps above the general database administrator. I think you're okay there. And you probably already know at least as much about DevOps and automation um as the average DBA. So what do I want you to know about databases? Well I said in my ID ideal world everybody would want to know everything there is about databases

but I recognise that's not the world we live in. So you can get an idea of some of the things that I think it's actually useful for you to know by looking at the titles of some of the talks that I've written for developer Events. So I gave a talk called How to Keep Your Database Happy that we've already spoken about, where I gave those my top five tips for things that you can put in place to make sure that your database kind of ticks along nicely in the background. I gave I gave one called How to Tune PostgreSQL to work even better, where I spoke about the most useful Postgres configuration parameters, the ones that you might need to think about and to look at to make sure that your database is configured specifically. For your use case. I gave one recently called Tuning Your Database for Analytics, which talked about

the fact that a lot of the time you might have analytics queries running in your normal Application database, so the impact that that could have on performance and how you could do things to mitigate that. I gave one called everything you wanted to know about databases, but were too afraid to ask your DBA. uh where I explained um what's a database cluster, um how what's a database, what's a schema, a table space, all the different kinds of objects that you can create in your database, how do they fit together? How do you connect to your database? What kind of information do you need? Obviously not absolutely everything that you need to know about databases, but hopefully a good start and again with lots of links to the documentation.

I did one called database troubleshooting for developers. So I went through some of the most common errors that you might come across and issues and what you would do to fix those and where you could again look for more information about those. And then there's one that I did which wasn't written for developers but was for um well it wasn't for application developers it was for the actual developers of Postgres to talk to them about how people are actually using Postgres out in the real world. And the goal of that was very much to say these are the things people are doing. We're often very quick to judge and say people are doing things wrong, but actually, these are some of the reasons that they might be doing. those.

Maybe we haven't got the right documentation. Maybe we haven't pointed people to the right training resources. Maybe we haven't thought about specific use cases. So I've tried really hard to um to share some of that information there. And as I've said, when I do my technical presentations, I always try really hard to include links to documentation, to resources, etcetera. So you can go and look in the Postgres documents for um information about pretty much anything there. Uh there are talk recordings so most of the Postgres conferences are now being recorded and you can find the replays of those online. So some of those can be really useful and accessible.

There are many, many Postgres conferences, and I know from speaking developers that you don't necessarily always think that those events are aimed at you. We would very much like to change that. We would love more application development. To come to Postgres conferences because you really are the users of the database. So knowing what you want to know about what we can help with is really useful, and we would love to welcome you more into that. community. So feel free to talk to me about some of those events. Tell me what you'd like to see from those, what we can do to help you. There are many, many different books available on Postgres if you still like actually reading books

uh and there are various tutorials around. So um Crunchy Data for example has the um Postgres playground which is Postgres running in your browser and then a series of tutorials that you can work through. So feel free to go and have a look at that if you want to. And then hopefully you'll be able to proudly talk about how I learned to stop worrying and love the database. So I have deliberately left some time here. I've um put a thank you slide, so and there's also a link to the slides for this talk. So I've left some time. here so that you can ask me questions about databases

because as I said in my bio I was once described as quite personable for a DBA. So um I I hope I am approachable and I would love to um to answer any questions. If not, what I've done is I've included some snippets from that talk that I gave at the Postgres Developers Conference so that you can see some of the things that I was talking to them about.