Chris Travers: How TimescaleDB Solves Common PostgreSQL Problems in Database Operations With Data Retention Management
In my career, I have frequently worked for companies with large amounts of time-partitioned data, where I was a software engineer focusing on our PostgreSQL databases. So it was my job to deal with outages in the database. One source of repeated outages has been partition management to handle data retention. We’d already grown past the point where deleting data row-by-row was no longer practical, so we needed to use PostgreSQL partitioning to manage data retention. In brief, dropping a whole partition allows PostgreSQL to remove the entire file from disk for a subset of your data rather than going through each row and removing them individually. So it’s much faster. But you do have to add new partitions where you’re ingesting new data and drop the old ones. We had reasonably well-tested scripts for adding and removing partitions. Unfortunately, the interactions around the scripts were less well-tested, and new, frequent and long-running queries prevented the partition management scripts from getting the locks required and creating new partitions. We didn’t see the problem at first because we’d created partitions a few days in advance, but then we ran out of time, and with no new partitions, we couldn’t insert, and whoops, down goes the app. These types of problems are particularly hard to debug and disentangle because they are often caused by totally unrelated pieces of code, in combination with changes in load. PostgreSQL has begun to address this with newer approaches attaching partitions concurrently, but they’re quite complex. This is why the lock minimization strategies of TimescaleDB are so exciting to me; it’s one of the things I was most excited about when I joined. I’ve seen the outages caused by partitions failing to create, or disk filling up because they can’t be dropped, or the pauses in other, normal queries by partition management code. I know how difficult these problems can be. This post discusses how TimescaleDB addresses these challenges and why I think our approach offers a[...]
