Sql

It’s been almost a year since I last posted an update on eugene, the CLI tool I’m building to help people write safer SQL migration scripts for postgres. I announced this tool in Careful with That Lock, Eugene: Part 2. At the time, eugene would execute a single SQL script, recording all the locks acquired and warn about possible downtime due to migrations. It could produce JSON suitable for automated tooling and Markdown suitable for human reading and using in CI comments/checks. That version was already good enough for me to start using in real projects — but it’s improved a lot since then, it’s now easy to run with almost no setup. ...

Last week I was made aware that we had some foreign keys not backed by indexes in the system we’re developing at work. Foreign keys in postgres must be backed by an index only on the side they refer to, not necessarily the side they refer from. Here’s an example: create table author( id bigint generated always as identity primary key, name text not null ); create table book( id bigint generated always as identity primary key, author bigint not null references author(id), title text not null ); In this example, there’s a foreign key from the book table to the author table. Since author refers to a primary key in the author table, inserts into book can validate very quickly. There’s no index on the author column in the book table though. The consequence of this is that delete on author must check every single row in book to check if it’s safe to actually delete. The really annoying part of this is that the scan does not show up in query plans: ...

The past few weeks I’ve been experimenting with DuckDB, and as a consequence I’ve ended up talking about it a lot as well. I’m not going to lie, I really like it! However, experienced programmers will rightly be skeptical to add new technology that overlaps with something that already works great. So why not just use postgres? Well, I really like postgres too, and I think you should consider just using it! But despite both of these technologies being all about tabular data, they’re not really for the same kinds of problems. I think DuckDB is primarily an analysis or ELT tool, and it really excels in this space. postgres can do a lot of the things that DuckDB can do, but not nearly as fast or easily. I wouldn’t want to use DuckDB for a transactional workload, so it’s not going to replace postgres for anything that I use it for. ...

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. ...