Partitioning Strategies I Wish I'd Known Earlier

There is no shortage of articles about DuckDB. Most of them cover the happy path. This one is about what happened after the happy path ended.

Most of the complexity was accidental. Over the years we'd layered three different approaches to partitioning on top of each other, and each one made sense at the time.

Some background

We had good intentions. We even had a document describing how partitioning should work. The trouble was that the document and the code had quietly gone their separate ways.

When we finally measured, the numbers were worse than anyone guessed. Around 42% of the time spent on a typical change went into working around partitioning rather than building anything.

What we changed

We started by writing down the one rule we actually cared about, then deleted every bit of code that didn't serve it. It's not glamorous, but it works.

Here's a trimmed-down version of what we ended up with:

SELECT
  user_id,
  event_at,
  LAG(event_at) OVER (PARTITION BY user_id ORDER BY event_at) AS previous_event_at
FROM events
WHERE event_at >= CURRENT_DATE - INTERVAL '30 days';

It's tempting to generalize this into a library. Resist that urge until you've needed it in at least three places.

A few things that helped along the way:

Agree on what "done" means before the first pull request.
Delete dead code aggressively; it's the cheapest refactor there is.
Pair on the first few changes so the knowledge doesn't live in one head.

Trade-offs

The biggest cost was social, not technical. Changing how people think about partitioning takes much longer than changing the code.

There are cases where this falls apart. If your team is very small or your traffic is very spiky, the extra moving parts may not pay for themselves.

What's next

If you take one thing away from this, let it be this: partitioning is a design decision, not a cleanup task. Make it early and make it on purpose.