Partitioning gets sold as a performance switch. You flip it on a big table, and the slow queries get fast. Most of the time, that is exactly what happens. But there is a version of this where you do all the work, split a huge table into clean partitions, and the slow queries stay exactly as slow as they were. The table is partitioned. Nothing got better.

When that happens, the partition key is almost always the reason. Partitioning mechanics are the easy part, and PostgreSQL handles them well. Choosing the column to partition on is the decision that carries the weight, and it is the one you mostly cannot take back once the table is large. This post is about how to make an informed decision for that choice.

If you want the ground-level mechanics first – how range, list, and hash partitioning work, how pruning skips irrelevant partitions, how maintenance gets easier – that all lives in my earlier post on improving PostgreSQL performance with partitioning. This one picks up where that leaves off and focuses on the single choice that decides whether any of it pays off.

The partition key is the whole game

Here is the whole idea in one sentence. Partition pruning, the thing that makes partitioning fast, only kicks in when your queries filter on the partition key. Get the key right, and PostgreSQL reads one small partition instead of the whole table. Get it wrong, and PostgreSQL still reads everything, except now it is reading everything spread across a pile of sub-tables, which can actually run slower than the single big table you started with.

So the real question is never “should I partition this table.” It is “what do my important queries filter on, and can I partition on that?” The key has to match the workload. Get that one thing right and the rest tends to fall into place. Get it wrong, and nothing else you do saves it.

A quick way to check yourself before you commit: pull your ten most frequent and most expensive queries against the table. Look at the WHERE clauses. If almost all of them filter on the same column or the same small set of columns, you have found your candidate key. If they filter on ten different things, partitioning will help some of those queries and do nothing for the rest, and you need to know that going in.

Why hash on a skewed column backfires

Hash partitioning is where good intentions go sideways, so it is worth walking through carefully.

The pitch for hash partitioning is even distribution. You take a column with lots of distinct values, hash it, and PostgreSQL spreads the rows across a fixed number of partitions. On paper, a column with a hundred-plus distinct values sounds perfect for this. Plenty of values, spread across, say, ten partitions. Even split.

The catch is that the number of distinct values isn’t the only thing that matters. What matters is how the rows are distributed across those values. Take a multi-tenant SaaS table with a tenant or workspace column. You might have a few hundred distinct tenants, which looks like great spread. But real customer bases are lopsided. A handful of your biggest tenants generate most of the rows, and the long tail of small tenants barely registers. Hash partitioning does not know or care about that. It distributes by value, not by volume. So it hashes your few whale tenants into two or three partitions, piles most of the data there, and leaves the rest nearly empty.

Now you have ten partitions, 80 percent of the data sitting in two of them, and queries against your largest tenants scanning those bloated partitions. You did the work of partitioning and kept the exact hotspot you were trying to break up.

Picture dividing a warehouse into aisles by the first letter of each product name. Looks perfectly organized on the floor plan. Then you notice most of your catalog starts with the same few letters, and everyone is still crowding into the same corner while half the building sits empty. The dividers are up. The traffic never moved.

Hash partitioning is a genuinely good tool when the key is naturally uniform: a UUID primary key, a surrogate ID with no business meaning, a customer ID that really is evenly spread. On a skewed column, it distributes the label without distributing the load. I went deeper on exactly when hash beats range, and when it does not, in my post on when hash partitioning works better than range.

Match the key to how the data actually behaves

The fix for a skewed key is not a cleverer hash. It is choosing a key that lines up with how the table is actually queried and how its data actually grows. In practice, different tables in the same database want different keys, and that is fine. Partition each one for its own workload rather than forcing a single scheme across all of them.

Two patterns cover a large share of real cases.

Even distribution with no natural ordering: hash on a uniform key. When a table is keyed on something like a UUID or a surrogate customer ID, and the values genuinely spread on their own, hash partitioning does what it promises. The rows land evenly, no single partition becomes a hotspot, and new customers slot into a partition automatically based on their ID with no manual work from you. This is the right home for hash.

Queried by recency, grows over time: range on a timestamp. When a table is almost always queried by time – recent orders, this month’s events, the last quarter of activity – range partitioning on the timestamp is the natural fit. Queries that filter on a date range prune straight to the partitions they need. Adding next month is trivial; PostgreSQL just routes new rows to the new partition. And aging out old data becomes a cheap partition drop instead of a giant, I/O-heavy DELETE. That last benefit alone justifies the choice for a lot of time-series and event workloads.

If a table is naturally segmented along two dimensions at once, time and tenant, for example, PostgreSQL supports composite partitioning to handle both. It is powerful, and it adds real administrative overhead, so it earns its place only on genuinely high-scale tables where a single key cannot do the job.

The part that makes this a commitment, not a setting

Here is what sets the partition key apart from an ordinary tuning knob. Some parts of a partitioning scheme are easy to change later, and one part is very close to permanent. It really helps to know which is which before you start.

Adding a new range partition is trivial. Next month rolls around, you create next month’s partition, and PostgreSQL routes to it. You can create a rule or schedule for them with pg_partman and forget about it.

Changing the number of hash partitions is the opposite. The modulus, the count of hash buckets, is baked into how every existing row was placed. Change it, and every row has to be re-sorted into the new set of buckets, which means copying the entire table, usually with downtime or an elaborate online-migration dance to avoid it. On a table that is already large enough to need partitioning, that is an expensive, high-risk event. You get to pour that particular foundation about once.

Even changing the partitioning column entirely, going from hash on tenant to range on time because you finally looked at the query patterns, is effectively a full rebuild of the table. Cheap while the table is small. Painful once it holds billions of rows, which is exactly the point where you most need partitioning to be right.

This is why the honest framing is that partitioning is a schema commitment, not a performance setting. The cost of getting the key wrong is not “it runs a little slower.” The cost is a migration you have to schedule, staff, test, and take a maintenance window for. Choose the key before the table gets too big to move, because “too big to move” is a real threshold.

A short checklist before you commit

Before you run the CREATE TABLE, walk this list. It takes an afternoon, and it saves the migration.

• Pull your top ten queries against the table and confirm they filter on the column you plan to partition on. No filter on the key means no pruning, which means no benefit.
• Check the actual data distribution across the candidate key, not just the count of distinct values. If a few values hold most of the rows, a hash on that column will build hotspots.
• Match the strategy to the access pattern: hash for uniform keys with no natural order, range for time-based and recency-driven access, list for a small fixed set of categories.
• Test on a production-like copy with representative data volume and a realistic query mix. Partitioning behavior at ten thousand rows tells you almost nothing about behavior at a billion.
• Size your partition count sensibly. A few dozen to a few hundred partitions is a healthy range. Thousands of tiny partitions create their own planner and maintenance overhead, which we covered under over-partitioning in the previous post.

Where this pays off

For teams running PostgreSQL under real growth, a SaaS platform adding data every month, a financial system where the transaction table never stops filling, the partition key is one of the highest-leverage schema decisions on the table. Get it right, and you buy years of headroom: fast queries, cheap archival, maintenance you can run one partition at a time, and a database that stays lean while the data keeps climbing. Get it wrong, and you buy a migration.

The good news is that this is a knowable decision, not a guess. The workload tells you the key. The data distribution tells you the strategy. A test on real volume tells you whether you got it right, while the table is still small enough that the answer is cheap.

This is the kind of call our team makes with clients week in and week out, usually on tables that are already large and already business-critical, where the margin for a wrong key is thin. If you are staring at a table that is getting too big to move and you want a second set of eyes on the key before you commit, that is exactly what our performance optimization and scaling work is for.

Frequently asked questions

Q. What is the most important factor when choosing a PostgreSQL partition key?

It comes down to your query patterns. Pruning only skips the partitions you do not need when your queries filter on the partition key, so the key has to be the column your important queries actually filter on. 

Q. Why does hash partitioning sometimes fail to improve performance?

Because hash spreads rows by the value of the key, not by how many rows sit behind each value. If a few values hold most of your rows, hash drops those heavy values into a couple of partitions and builds hotspots right where you were trying to remove them. Hash pays off when the key is naturally uniform, like a UUID or an evenly spread surrogate ID.

Q. Can I change a partition key after the table is in production?

You can, but be ready for it to hurt. Adding a new range partition is easy. Changing the number of hash partitions or the partitioning column means re-sorting every existing row, which is really copying the whole table. On a large table, that is a migration with downtime, and that is exactly why you want to get the key right before the table grows.

Q. Should every large table use the same partition key?

No, and it is fine that they differ. Every table gets queried its own way, so partition each one for its own workload. A table queried by recency wants range on a timestamp. A table keyed on a uniform UUID wants hash. Force one scheme onto all of them, and some tables end up partitioned in a way that has nothing to do with how they are actually queried.

Q. How do I know if my partition key is skewed before I commit?

Run a quick count of rows grouped by your candidate key and look at the spread. If the top few values own a big share of the rows, the key is skewed, and hash will build hotspots. Do this on a production-like copy with real data rather than trusting the number of distinct values, because that is the check that catches the problem while it is still cheap to fix.