Step 1 · The pattern
One table, every month
Events are appended all day and almost every query asks about recent ones. Keeping everything in one table is the simplest design, and for a long time it works.
- query
- answered
In short
Time partitioning: how it works and how it breaks
Event tables, audit logs and ledgers are append-only: rows are added constantly and almost every query asks about recent ones. Kept as one table, the data only gets heavier. Indexes cover years of rows nobody reads, and removing old data means deleting it row by row, which is real work that competes with live traffic and leaves dead space behind. Time partitioning splits the table into child tables by date range, for example one per month. Queries with a time filter touch only the partitions that can contain matching rows; the planner skips the rest. Only the newest partition is written to, so older ones behave like sealed blocks. Archiving a month becomes detaching or dropping one partition, a quick catalogue change instead of millions of deletes. The costs: queries without a time filter visit every partition, and new partitions must exist before their rows arrive.
How this comes up in interviews
"An append-only events table keeps growing, and the job that deletes old rows is slowing down live queries. How would you redesign it?"
Then they ask
- Which queries get faster after partitioning by month, and which get no benefit?
- What happens if a row arrives for a month that has no partition yet?
- How does removing a month of data differ before and after the change?
A strong answer covers
- Row-by-row deletes are real work that competes with live traffic and leaves dead space behind.
- Split the table into child tables by date range, for example one per month.
- Queries with a time filter touch only the partitions that can hold matching rows.
- Archiving a month becomes detaching or dropping one partition, a quick catalogue change.
- Queries without a time filter visit every partition, and new partitions must exist before their rows arrive.
Points to cover, not a script. Say them in your own words and tie them to a system you have worked on.
On one page: the track cheat sheet.
Sources: Revolut Tech: Event Streaming, the Revolut way ↗ · Revolut Tech: Recording more events… But where will we store them? ↗ · PostgreSQL documentation: table partitioning ↗ · Report a problem
Diagrams are our own simulations: the timings, rates and counts in them are illustrative, not measurements. Captions are original wording.