Jul 12, 2026 · 17 min · 11 segments
In this episode of Scaling Postgres, we discuss excluding partitions without using a partition key, a pgBackRest interview, Postgres backup without an incremental chain and a replication deadlock bug…
Creston JamisonHost
Our first piece of content is how to achieve pruning when querying by non-partitioned columns in PostgreSQL.

This is from hackybenita.com. And the first part of this blog post goes over partitioning in general, how to set it up.

And he's using the example of you have an events table, so you're collecting all sorts of events or user actions.

And they did partition by the timestamp, so if you query with a particular timestamp range, it will scan only one table that that timestamp is valid for.

Whereas if you query, say, by the session ID, you're going to get every partition.

And if you do index it, it will give you an index on that local table, so it will make this look up faster, but it still must scan every single index.

But he goes into a technique on how to actually prune on non-partition key columns.

So what you can do is you can find the min and max session ID for a given year, whatever the partition is.

because there's strong correlation between the session ID and the timestamp because they're sequential, of course.

And the trick is you put these session IDs as a check constraint on each partition.

So in the 2025 partition he has here, he puts the constraint between 1 and 4,320 and then in the 2026 partition he puts 4,320 up to 10,000 because that's what session IDs are set in that partition.

Now when you query by a session ID it is going to automatically exclude the non-relevant partition.

So it gives you partition exclusion even when the partition key was not included in the query.

Our first piece of content is how to achieve pruning when querying by non-partitioned columns in PostgreSQL.

This is from hackybenita.com. And the first part of this blog post goes over partitioning in general, how to set it up.

And he's using the example of you have an events table, so you're collecting all sorts of events or user actions.

And they did partition by the timestamp, so if you query with a particular timestamp range, it will scan only one table that that timestamp is valid for.

Whereas if you query, say, by the session ID, you're going to get every partition.

And if you do index it, it will give you an index on that local table, so it will make this look up faster, but it still must scan every single index.

But he goes into a technique on how to actually prune on non-partition key columns.

So what you can do is you can find the min and max session ID for a given year, whatever the partition is.

because there's strong correlation between the session ID and the timestamp because they're sequential, of course.

And the trick is you put these session IDs as a check constraint on each partition.

So in the 2025 partition he has here, he puts the constraint between 1 and 4,320 and then in the 2026 partition he puts 4,320 up to 10,000 because that's what session IDs are set in that partition.

Now when you query by a session ID it is going to automatically exclude the non-relevant partition.

So it gives you partition exclusion even when the partition key was not included in the query.
The rest of this transcript — segmented and speaker-labeled, so you land on the exact moment something was said
Search every transcript — by keyword, by phrase, or by meaning, across every show Radar indexes
Trends — what is surging across podcasts, measured against its own baseline
Alerts — when a name you follow appears in a newly indexed episode
No account is needed to search Radar.