Application Development Challenges with Postgres (PGConf.dev 2025)

But it isn't all roses. Persistence is difficult in itself, and application development in particular -- automating open-ended interactions with stored state -- brings with it some unique challenges for developers and DBAs alike. Some of Postgres' properties and features help dev teams overcome these issues. Others exacerbate them. In this talk, we'll discuss the rise of the "accidental DBA" and the implications of vernacular schema architecture. We'll explore how development teams work with Postgres across all phases of the software development lifecycle. ... {ts:282} promises we make to our users, to our stakeholders are not about the database. They're about the whole system. The database is a core component. But it is not what users see. It's not what people think of themselves as interacting with. So compatibility and interface stability requirements also are at the level of the system, not the database. … And mistakes here can be very very difficult to take back. In particular, it can be really difficult for us to recognize that what we're doing demands us to be really active stewards of the data and to approach the requirements and {ts:458} complications or nuances that we encounter with a really truly critical eye. Finally, the team can be very unfamiliar with just how expressive data models can be or not realize how important certain elements are and that's how you end up with databases that don't have a single {ts:477} foreign key constraint. So, we'll tend to lean on the familiar the things that we know about. We'll prioritize discoverability and we'll avoid magic like triggers even when a trigger is the best you available solution for the specific problem. um a lot of the time we'll use lowest … But because we think from code, not at first from a position that prioritizes where the data are going to be most of the time, we don't make good decisions here. Uh we're not really set up for success when we start working with persistence. {ts:686} There are many problems that are not unique. probably most of them uh for instance if you're doing something geospatial you probably picked postgress because you know about postpostgis similarly PG vector is you know sort of a killer app for the um ML and vector embedding uh use case but there are a … Um this is all difficult and there are further difficulties. SQL injection is you know {ts:859} the perennial top contender for the most financially damaging application security vulnerability where you're just like interpolating user input directly into a statement and you don't escape it and somebody escapes it for you and then does a few more things on top. … {ts:1398} this is this works it's high effort it's high redundancy and you have a problem of minute variations that make a big difference um also if you you know once you start evolving the schema making some changes to how uh the application uh works with data uh those changes {ts:1417} either break your tests or they don't which is worse because your tests no longer guarantee what you think they do. … Uh we do also only get the first error. This record has many more problems. Um so we're going to have to iterate to solve you know this thing and get this record into the database for real. It is very good to fail fast. Uh we do feel it {ts:1533} a little bit more on the application side because we have long workflows and a lot of setup. … Could be the database, could be something in the code. We have to go on a bit of a journey to rule out the other things and then start figuring out what uh behavior we've encoded in the database is incorrect. And the more complicated the database side of the of the equation is, the more difficult this {ts:1571} gets. … They're very noisy. You have to restart the entire server to adjust them. So hope you got got it right in production the first time. Um, sometimes if especially if you're in a platform as a service context and you're in an {ts:1605} ephemeral environment and haven't wired up PG Badger, it's a pain to get to them.