Batch operations using composite keys in postgres over jdbc
Throughout a career as a software developer, there are a lot of patterns that you see just barely often enough to remember that they exist, and have to look up every time you need them. I’ve found that one way that I can more easily remember things is to write them down, and this particular pattern is one that is very useful to know about in my current project. So the time has come to write it down so I hopefully can commit it to memory properly this time. While this post is postgres-specific, I’m sure other databases have the features they need to achieve the same thing efficiently too.
In my current project, we have a lot of database tables with composite primary keys. Or in plain english, we have a bunch of tables where the primary key actually consists of more than one column. This is because a design principle for the database was to prefer natural keys over synthetic keys. While this is a good design principle, it does come with some mechanically annoying consequences. The topic for this post is the case where you have a bunch of keys in memory and need to instruct the database to delete all the corresponding rows. This is a fairly common kind of use case in web development, just think of all the list views you’ve seen with check boxes for deleting. With a primary key that is a single column, we can just fire off a prepared statement like this:
Then we just make sure to pass ids as a JDBC array type, and we get efficient bulk deletes. But this doesn’t work when the primary key is composite. What we would like to do, is to express something a lot like the following (illegal) SQL:
