Big Bad World of Postgres Dev Environments with Elizabeth Garrett Christensen

Speaker 1: I would I would like to get to know my audience just briefly. Um who here is using Postgres in production at some kind of level? And who awesome, everybody Um and who is kind of Postgres curious and and doesn't know that much? Nobody. Okay. That's my worst nightmare. No, I'm just kidding. Um cool. Yeah, so um I'm Elizabeth. I've spoken here a few times. I now work at Snowflake after having gone through an acquisition earlier this year. I I wish upon all of the um startups that you may have the same wonderful experience that I have had. But yeah, so I wanted to talk about development environments. Because I get so much attention when I come to DjangoCon.

Speaker 1: I have a little table and people love to talk about Postgres and I love to talk about Postgres, but there's always this sad moment when about half of the people are like I'm not allowed to touch production. Like I just do development. I am sorry. So um and then there's sort of another kind of side to that coin, which is I've given a couple talks about performance. Hetty gave a really nice talk yesterday about query tuning. And so this is kind of the other side of that coin, right? How do we how do we get access to the data and how can we sort of make our development environment work for us? So that's kind of what I wanted to bring to you today. I planned not a long talk and a little bit of time for discussion if people are up for sharing like what's working for them or maybe what's not working for them.

Speaker 1: I know a lot of you guys are doing great things and have figured out lots of tools. I'm gonna tell you about the things I've seen the crunchy data and the snowflake customers do that have worked really well that we've kind of helped them implement. Um but development environments are a big world, right? There's There's hundreds of ways you can do this stuff and and you know lots of different options. So development environments, you know, for your database, right, is that you're you're writing tests, you're doing all your Django stuff, like that that's all great, right? But you can't use the same database that your production app is using to do development on because the scale is different, the settings are different, everything is slightly different, right? It's not the same, it's the same database Um but you you want something that kind of looks like the database and feels like the database, but really isn't the database and doesn't have

Speaker 1: sensitive data in it and and you don't want to spend a bunch of time you know, kind of wasting everybody's time doing lots of different crazy stuff. Um one thing that I've noticed, like I go to PyCon pretty often and I come here and I I try to kind of talk to developers about um databases and lots of developers know a little bit about Postgres, but they don't sort of feel that they want to get in the database, right? They don't they don't know what the schema looks like. They don't get in it and they don't get their hands on it and I'm sort of a big proponent of like just get in your database. Like get in it look at it. Like The more that you know it, the more you can kind of build, you know, build backwards and and when you're doing things like you guys are doing where you're you know you're doing a big rewrite or you're doing a big schema change and and kind of from that application level, the more that you know about your database, the better off you are.

Speaker 1: And then it's also helpful to work on all this debugging and kind of performance stuff I'm not going to talk a ton about performance in Postgres, but there's sort of two main things that are, I think, kind of table stakes for performance, especially for query planning, and that's PG statements. Like how how many people are using PGSTAT statements today to look at their like query? Oh very few of you, but yet most of you are in using Postgres. So PG stat statements is just how you're gonna look at how long queries take, what queries are slow. You know it kind of keeps track of all of that for you in your production database, but you can easily export that data somewhere else and do whatever you want. And then the other kind of tool that you use with explain

Speaker 1: or with PG Stat statements is the explain plan And the explain plans will basically show you how Postgres is doing things inside of it and how it's deciding, you know, to use an index or to use a sequential scan and how it's planning things out. So development environments, big picture. I'm going to talk you through the three things I've seen people do. I'm sure most of you are doing local development or container development, and I'm also going to talk a little bit about some things I've seen people do with cloud development environments. So for local Postgres, you can get Postgres like 200 different ways, right? You can get it from the community. If you're on a Mac, you can brew install. There's, you know, you can get it from the Git repo, the Postgres repo, the mirror.

Speaker 1: You can also use an app called Postgres. app When I am just working with people who are just for the first time writing their very first app and using Postgres, I tell them to use Postgres. app. You download it onto a Mac. It um will basically just runs as an application. You just connect it right to your little, you know, in your settings. py file, you'll connect it. And then I have a little video here if this plays. So you'll just literally get in it, open it up, you double-click on the database, like you don't have to do any crazy connection string stuff. And you're just write MPSQL and you can see the models and all of the tables that your Django code built you in Postgres.

Speaker 1: So the upsides for local development right are that it's super simple, super fast. Your PSQL is right there if you're familiar with the command line stuff. The downsides are that once you get into compiling extensions, different Postgres versions. Maybe your production database has a lot of fancy stuff on it that doesn't come with Postgres. app. You're gonna be kind of managing all of these different things. There's configurations that in Postgres that you may need in your local environment. And so sometimes those are simple and you can just add those manually, but you can get a little more complexity there and that makes it difficult. Lots and lots of folks use Docker. Um, I guess right if you're using Docker for your environment, I guess raise your hand. That would be good for me to know.

Speaker 1: The vast majority of you. Okay, cool. That and that's kind of what I assumed. I think Especially since the Django app world plays so well with like the Docker Compose, you're probably just putting all of your app pieces in a Docker Compose. And that works great. You know, you can get a basic Postgres image from the main Docker repo. You can get, like if you want Postgres with PG vector, you can get that. You can get PostGIS. If you're trying to put a bunch of stuff in Docker , you can tell Docker to I think I had another slide. Well, I don't. Um, you can kind of tell Docker how to add things all together. Um, so when you're using this kind of Postgres

Speaker 1: extension world, right, which is huge. There's basic extensions, the contrib extensions that come with Postgres. And so like those are things like PG statements. some of the buffer cache stuff, like basic the cryptography piece, the foreign data wrapper, you can just do create extension in Postgres. Any version of Postgres will let you make that extension. And then there's this huge giant world of third-party extensions, right, that have to be compiled and have to run on the right Postgres version and have to have like all the right pieces. You know, some of those like PostGIS could be like a big part of your application, some of them could be small, right? Like maybe you're gonna try out table partitioning and you wanna try the Partman extension.

Speaker 1: Docker will let you add extensions. So as you kind of build out your little Docker file, you can tell Docker like, get me an image, and then add like this in this sample, like you can add like PG cron, which is the little cron. um that'll run like little tasks and little jobs at certain times for you. But you know that can at some point like You're kind of you're now like in some kind of custom Docker image world, right? Where it's getting complicated. You've got like 20 things you're adding to these Docker files. And so what we've seen a bunch of people do that have these really kind of more custom Postgres setups is that they're sort of taking the cloud environment that they have. And then they're kind of using all of that stuff that they have in that cloud environment to build their development environment.

Speaker 1: So your cloud environment has, you know, your schema in it, it has all the configs, it has all of your indexes it has the same exact extensions, it has all of the packaging work has been done to kind of make that all work And then it also, you can so you can use tools like you can fork a database in the cloud, you can use a replica, and then you can, you know. Make your own database. So the downside to these cloud environments is the cost, right? So you're paying somebody to do that, and then you you need an internet connection, which can be annoying. And if you're using a cloud environment and you didn't install Postgres, you you need to install at least the little connector. So if you're on a Mac, that's the libpq.

Speaker 1: And if you're on Linux, I think it's called Postgres client or something like that. that. So one thing that's really cool that you can do either in your kind of Docker setup or we've seen a lot of people use with this cloud setup is template databases. So you just create a template and that has everything that your database has, all of the settings, all the same schema, and then you can just build another database off of that template. So it's it's a little kind of Postgres thing that comes with it. So if you're not familiar with Postgres enough to have ever used You may have gotten your Postgres and it comes with a Postgres database inside it. Postgres can have hundreds of databases inside a single instance. And we have lots of clients that we work with that have like a hundred or two

Speaker 1: like they have all their developers have their own database that's kind of a little copy of production and that's kind of how they do it. You can create a template using some of the Postgres tools. Like if you don't want data, you can just do a schema-only dump and create a template that way. And then you would basically restore your production schema and all of your indexes and things to that new template database. So if we've got, you know, we figured out some kind of development environment, right? We've we've got our Docker, we've got some kind of cloud environment. But but I also want you to have some kind of data. So you know your fixtures and all of your other things that you guys have that are built into Django are great for testing and they're great for just like your kind of table stakes

Speaker 1: um unit testing, but in terms of like query planning and getting like beyond that, you need um you need more data, right? So you can dump Postgres data out with a PG dump. You can dump single tables if you just want to work on a couple things and you don't want to work on the whole database. Jingo has a little dump data tool. PG dump is different than that. So if you've never used the PG dump tool, it's Postgres specific. It's a little bit faster. The Django dump tool is kind of meant to go between databases and data platforms, so it doesn't have like Postgres specific things in it, like schemas and indexes. If you're super into like really deep Postgres stuff, um

Speaker 1: Postgres has a bunch of things inside of it in its own table statistics that sort of know how big the database is, what the like cardinality of certain columns are, and you can actually dump table statistics. and restore those into a database that doesn't even have the same data. This is a new thing coming out in Postgres 18. I have a little flyer at my table about other Postgres 18 features if you're curious. And yeah, so one thing that we've sort of seen a lot of uptick with lately is the PostgreSQL anonymizer. If you haven't seen this, it's Sort of in its second iteration as a project. There's an older version one that was a little bit more simple. This is built by a French Postgres

Speaker 1: company called Dollybo. They're wonderful people. I know several of them. They're super solid. It's a pretty trustworthy tool. And they have all this fancy stuff that you can do. to basically mask your data. You can create fake data, you can create seed data. But they have s one of the things that's just so incredible is their dynamic masking. So what you can do with the PostgreSQL anonymizer is literally column by column with single SQL statements is just say, okay, for my first name column. I just want, you know, make me a dummy first name. Don't make it a real first name. Okay, and for the phone number column, just like obfuscate with these numbers, right? I don't want real data. So

Speaker 1: you could do something like this where you know on the left side here I've got a live database. This is the actual data. Um and then I have an anonymized user. So I've made my development user only sees mask data. The same query side by side. They see something that looks like the same data, right? It looks like first names and last names, but is different names. And then the phone number is completely different. So you can do dynamic masking with all of the things like birthdays, any kind of personal identifying information, which I'm sure lots of us have in our apps. And what's cool about this is it's tied to a user. So you can kind of create a little workflow for yourself if you want to. You know, do your PG dump, but you do your PG dump as a development user, and that way when you do a dump, all the data is masked.

Speaker 1: Um, and that's pretty awesome. So, you know, this is kind of a a diagram of what we've what I've seen several people do kind of using some of the cloud tools that we have available is they're sort of doing these schema-only dumps, creating this template database. That's sort of accessible to the developers where the production database is not. And then that masked and anonymized data is going into the template. And then any developer can just build their own development database straight off of that template. And you know, with a connection string, they're kind of off and running with a development environment on a pretty complicated Postgres setup. Yeah, so um that's kind of it.

Speaker 1: Um I just you know I have a little summary slide up here, but I want to hear like if people are open to asking questions or kind of sharing what's worked. I'm oh we have great. I like it.

Speaker 2: So actually when you started your talk, which was great, thank you, my first question was in my head, and anonymizing the data. because that has been a huge pain over all over the years. But you mentioned that already. But is there a way to also incorporate um Uh not translations, but things that are not necessarily English words or English phone numbers or something that is more local. Specific.

Speaker 1: Okay, so I think the way that you do that with anonymizer is not with the like dummy data you use like the little SQL tools that just like you would keep like the country codes and then you would mix those up. Like I think there's a way to do that with SQL and not like the dummy data. But there's other names that I don't know That's a great question. So she's asking about like names that like, you know, using st non-standard names. I have not gone that far into anonymizer. That's a great question. I I can't answer that though, but but if you find out, let me know. Uh

Speaker 3: when I saw database anonymization in your Description. That's what brought me here too. So I have a question about that too. Um we have like a really old scrambler that scrambles um databases, but it can't handle JSON fields. Is that something that this plugin can handle?

Speaker 1: Oh, that's a great question too. I have not tested that. I have not tested it with JSON because it works on column by column data, I would assume you could, but like I would, you're probably gonna get into some tricky sequel. But um Yeah. Like some something should be addressed, but like will it become an address? Have you messed around at at all with like generated columns with um your JSON data? Like You could sort of like get around this with some of the like Postgres does some weird stuff, especially for like these JSON people where you can Basically, you can generate your own column, right? And Postgres now, as of Postgres 18, has both stored

Speaker 1: generated columns and generated columns on the fly. And so I think these are gonna, I haven't seen everything, but I think what's gonna happen is that the generated columns are gonna be like how you solve some of these column by column requests if your data is JSON. So I've seen that too. Yeah.

Speaker 4: Hi, so two quick questions. Um the first one is uh this uh setup of security labels, right? Um where you have to effectively go to the columns that are it sensible and have them be anonymized. Can this be set up as uh the Django migrations easily? So it's like defined in code and you don't need to set it up um manually in production. And

Speaker 1: That sounds like a great idea for a patch into the Django ORM. Are you like right did you start writing it already?

Speaker 4: No, no.

Speaker 1: I I cannot imagine that this exists now in the ORM because this piece of it is so new. Um I mean I could be wrong, obviously. I don't there's not I don't know everything, but um yeah, I I think that probably it's part of the extension.

Speaker 4: Okay. Maybe maybe I'll write it. And just another quick question is um I'm not very familiar with template uh database When you make another database based on another one, is that copy on write? So it it doesn't copy the entire data again? Or is that um

Speaker 1: I'm pretty sure it copies the whole database again. But I I don't know. I don't know. I would have to confirm that before I told you a hundred percent.

Speaker 4: That's fine. I appreciate it. Thank you.

Speaker 5: I can answer your first question.

Speaker 1: Oh good.

Speaker 5: Um right now you need to use a there's a migration called RunSQL, which lets you write and run arbitrary SQL and it would be pretty trivial to do um in a migration. And that's how probably how I would do it if I was using it. It is also possible to find custom migration like types yourself. And so if you were doing a lot of them, I'd probably write a like anonymizer migration that had was a little easier to use than writing writing raw SQL. And that's the type of thing that could potentially become like I I don't know that it would necessarily be the best candidate for like a core feature because this is not a core feature feature of Postgres, right?

Speaker 1: Right.

Speaker 5: But as like a but as like a package you could install, you know, Django PG Anonymizer or whatever. Whatever that would be pretty cool and not I don't think particularly hard to develop.

Speaker 4: Thank you. Hi, uh

Speaker 6: thank you for the uh great tips. Um I was wondering if you have experience with uh truncating a database. Sometimes the database is huge even if you anonymize the database. You don't want to copy the entire database. Do you have the experience we used to do?

Speaker 1: So there's a lot of different ways that people do that. There's some basic table sample. There's a table sample in SQL so that you can just like pull out a sample of what the data is. Um in terms of like how people you can definitely do it table at a time, right? Like if you want to just work on some stuff, you can work on um the sample data. I'm trying to think of other things I've seen people do to like truncate size of data. I'm sure somebody in here has done this, like so I'm sure somebody has a solution that I haven't thought of

Speaker 7: Thank you for the talk. I give a talk yesterday about generated columns. So I was thinking about anonymization. I think um it's it will be easy to generate a new JS field uh if you have one but my question is uh do you think that this function for the anonymizing are immutable because otherwise it's possible to use for generated column. I

Speaker 1: say the last part.

Speaker 7: Do you think that the the this function for creating dummy names are immutable.

Speaker 1: Immutable.

Speaker 7: Immutable, sorry.

Speaker 1: Okay. And immutable meaning say tell me more about what you mean when you say that.

Speaker 7: Because otherwise you can't use uh a function that is not immutable in a generated column.

Speaker 1: Oh right. Okay. I don't know.

Speaker 7: Okay.

Speaker 1: That's a good question.

Speaker 4: All right. This talk is from online from Doreen asking Uh you mentioned database copies in Postgres. Uh can we use this feature to achieve replication in Postgres?

Speaker 1: You can't really use PG dump and PG restore for replication, there's better tools for that. I don't think anybody's doing like anonymized replication. You like you could there's two kinds of Postgres replication, right? There's like the synchronous replicate everything, replicate the full database to create a replica. or you can use logical replication in Postgres, which will just replicate the things you want. So you know if you want to take just some of your tables and put them in a different place, you can do that with logical replication. I don't see a ton of people using replication for development environments though.

Speaker 8: Okay, I have time for one more question.

Speaker 1: Oh man, it's from Tim. It's gonna be hard.

Speaker 6: Thank you, Elizabeth. It was fantastic as always.

Speaker 5: Let's come on Natalia. I I've always liked your speaking style, your presentation style. Um what features of Postgres 18 are you excited about that you think

Speaker 1: There's not a ton in Postgres 18 that isn't supported like the big the like the big sort of headline feature of Postgres 18 is the asynchronous IO um and that's a um like that's a system setting um Which is, I don't know how many of you guys are following like the inner workings of Postgres, but the asynchronous I. O. is exciting because it's like a lot faster reads if you turn it on. But that is sort of the first building block to what will be multi-threaded Postgres. So it you know in a couple years we'll have like, you know, we can run Postgres on these big GPU machines that you guys are using for machine learning. and Postgres would be really fast.

Speaker 8: Thank you. Thank you. Well, that's all the time we have. Lots of conversation. Elizabeth has a

Speaker 1: I have a little Postgres table out there with some Postgres 18 stuff or find me and come talk to me about Postgres stuff.