Postgres

It’s always an exciting day for me when I get access to the source code for an entirely new application I need to work on. How does it look inside, how does it work? Sometimes, there’s some design documentation along with it, or operational procedures, or maybe some developer handbook in a wiki. I do check all of those, but I don’t expect any of those things to accurately describe how the code works, because they tend to change less frequently....

In my post about batch operations, I used the where id = any(:ids) pattern, with ids bound to a JDBC array. I’ve gotten questions about that afterwards, asking why I do it like that, instead of using in (:id1, :id2, ...). Many libraries can take care of the dynamic SQL generation for you, so often you can just write in (:ids), just like the array example. I would still prefer to use the = any(:ids) pattern, and I decided to write down my reasoning here....

Throughout a career as a software developer, you encounter many patterns. Some appear just often enough to remember that they exist, but you still need to look them up every time. I’ve discovered that writing things down helps me remember them more easily. This particular pattern is very useful for my current project. So, it’s time to write it down and hopefully commit it to memory properly this time. Although this post is specific to PostgreSQL, I’m sure other databases have the necessary features to achieve the same results efficiently....

Database servers are usually long-lived, and important parts of the infrastructure that we build on. We rarely set them up from scratch, because we have to take such good care of them over time. I think this causes a lot of people to think that setting up a database server is some mysteriously difficult ordeal. To be clear, that’s actually true, if you need high availability and a solid recovery point objective....

I have been working quite a bit on picking up dangerous migration patterns in migration scripts over at the eugene repository lately. A major feature I’ve added is syntax tree analysis, so that we can pick up some patterns without having to run the SQL scripts. This isn’t quite as precise as running the scripts, but it’s a lot faster and can catch quite a few common mistakes. So let’s take a look at how it works!...

A while back, I wrote Careful with That Lock, Eugene about an idea for how to check if a database migration is likely to disturb production. That post came about after having an inspiring chat with a colleague about the advantages of transactional migration scripts and the ability to check the postgres system catalog views before committing a transaction. Over the past few weeks, I’ve been experimenting with this idea to test if I can use it to build valuable safety checks for DDL migrations....

It is rewarding to work on software that people care about and use all around the clock. This constant usage means we can’t simply take the system offline for maintenance without upsetting users. Therefore, techniques that allow us to update the software seamlessly without downtime or compromising service quality are incredibly valuable. Most projects I’ve worked on use a relational database for persistence, and have some sort of migration tool like flyway or liquibase to make changes to the database schema....

If you’re developing a transactional application backed by postgres, there’s a pretty cool trick you can use to check if you’re missing indexes that could potentially cause serious performance issues or even outages. In particular, I mean foreign keys where the referencing side of the constraint does not have an index. The idea is very simple, we can select all of the columns that take part in a foreign key, then remove the ones that take part in a complete index, and the remainder should be the empty set, or possibly match a known allowlist....

For tests that need to touch the database, it is generally a really good idea to roll back transactions. That way, you can run lots of tests in parallell or in any arbitrary order and the tests won’t interfere with each other. But sometimes, that just isn’t possible. One reason for this could be that the code base handles transactions in a way that makes it really hard to get a handle on them in the right place, or it could be a legacy code base where everything is running with auto-commit or some other explanation....

There are 2 configuration options that every OLTP application that uses postgres should set, in order to protect the database from high load: statement_timeout idle_in_transaction_session_timeout These can both be set by client configuration and require no special permissions to set, and are easily overridden locally for transactions that have different requirements. They can be a bit scary to retrofit to existing applications, but we can activate two postgres extensions to help us measure our queries to find safe values to set:...