I have been running production PostgreSQL systems for over sixteen years, and if there is one lesson that never gets old, it is this: a table that behaves beautifully at ten million rows can turn into a nightmare at five hundred million. Indexes stop helping as much as they used to, vacuum runs longer, and every maintenance window gets scarier. When I see queries slowing down despite solid indexing, partitioning is usually the next tool I reach for. Let me walk you through how I actually implement it.

What Partitioning Really Is

Partitioning takes one logical table and splits it into several physical sub-tables called partitions. From the application side, nothing changes. You still insert into and query one table name. Behind the scenes, PostgreSQL decides which partition each row belongs to and routes reads and writes there automatically.

I like to compare it to a filing cabinet with labeled drawers. If everything is dumped into one drawer, finding last year's invoices means digging through everything ever filed. Label the drawers by year, and you go straight to "2024" and search only there. That is the entire value proposition, in one image.

Why I Bother With It

  • Query performance. PostgreSQL can skip whole partitions entirely through partition pruning, so it only scans the data that could possibly match.
  • Faster maintenance. Running VACUUM, ANALYZE, or REINDEX on a smaller partition is a lot less painful than running it on one giant table.
  • Simple data lifecycle management. Getting rid of old data becomes DROP TABLE partition_name, which is nearly instant. Compare that to a DELETE that has to touch millions of rows and generates a mountain of dead tuples.
  • Better I/O control. I can put hot, frequently accessed partitions on fast storage and let cold partitions sit on cheaper disks.

The Three Strategies I Choose Between

PostgreSQL, from version 10 onward, gives me three built-in partitioning strategies. I pick based on the shape of the data.

Range partitioning splits rows by a range of values, most often dates or numeric IDs. This is my default choice for time-series data, things like orders, logs, or events that accumulate by day or month.

List partitioning splits rows by a discrete set of values, like country codes or status flags. I reach for this when the data naturally falls into named buckets rather than a continuum.

Hash partitioning spreads rows evenly across a fixed number of partitions by hashing the partition key. I use this when there is no natural range or list to key off of, but I still want the data spread out for performance and manageability.

Setting Up Range Partitioning

Here is how I would partition an orders table that is bringing in millions of rows a year. I will partition it by created_at, monthly.

Step 1: Create the Partitioned Parent Table

CREATE TABLE orders ( id BIGSERIAL, customer_id BIGINT NOT NULL, amount NUMERIC(10,2) NOT NULL, status TEXT NOT NULL, created_at TIMESTAMPTZ NOT NULL ) PARTITION BY RANGE (created_at); 

Something worth keeping in mind here: the parent table never actually stores any rows. It is a purely logical container that PostgreSQL uses to route data to the right child partition.

Step 2: Create the Partitions

CREATE TABLE orders_2024_01 PARTITION OF orders FOR VALUES FROM ('2024-01-01') TO ('2024-02-01'); CREATE TABLE orders_2024_02 PARTITION OF orders FOR VALUES FROM ('2024-02-01') TO ('2024-03-01'); CREATE TABLE orders_2024_03 PARTITION OF orders FOR VALUES FROM ('2024-03-01') TO ('2024-04-01'); 

One detail I always double check with new team members: the range bounds are inclusive at the bottom and exclusive at the top. A row dated exactly 2024-02-01 lands in orders_2024_02, not orders_2024_01.

Step 3: Add Indexes

I create indexes on the parent table, and as of PostgreSQL 11 they propagate down to every partition automatically. That saves me from having to remember to index each child individually.

CREATE INDEX idx_orders_customer_id ON orders (customer_id); CREATE INDEX idx_orders_created_at ON orders (created_at); 

Step 4: Insert Data as Normal

INSERT INTO orders (customer_id, amount, status, created_at) VALUES (42, 199.99, 'completed', '2024-01-15 10:30:00+00'); 

I do not have to tell PostgreSQL which partition this belongs in. It reads the created_at value and routes it to orders_2024_01on its own.

Step 5: Confirm Partition Pruning Is Actually Happening

This step is not optional in my process. I always verify pruning with EXPLAIN before I trust a partitioning setup in production.

EXPLAIN SELECT * FROM orders WHERE created_at >= '2024-02-01' AND created_at < '2024-03-01'; 

If the setup is correct, the plan should only touch orders_2024_02. If I see the planner scanning every partition, something in my query or my partition key is off, and I go fix it before moving on.

When the Key Is Categorical: List Partitioning

If I am partitioning something like a customers table by region rather than by time, list partitioning is the right tool.

CREATE TABLE customers ( id BIGSERIAL, name TEXT NOT NULL, region TEXT NOT NULL ) PARTITION BY LIST (region); CREATE TABLE customers_us PARTITION OF customers FOR VALUES IN ('US', 'CA'); CREATE TABLE customers_eu PARTITION OF customers FOR VALUES IN ('DE', 'FR', 'GB', 'NL'); CREATE TABLE customers_apac PARTITION OF customers FOR VALUES IN ('AU', 'JP', 'SG', 'IN'); 

Each region code is explicitly assigned to a bucket, which makes this strategy easy to reason about and easy to explain to the rest of the team.

When There Is No Natural Key: Hash Partitioning

Sometimes there is genuinely no range or category to split on, and I just need the data spread evenly for performance and manageability. That is when I use hash partitioning. Here I am splitting an events table into four partitions by user_id.

CREATE TABLE events ( id BIGSERIAL, user_id BIGINT NOT NULL, event_type TEXT NOT NULL, payload JSONB, occurred_at TIMESTAMPTZ NOT NULL ) PARTITION BY HASH (user_id); CREATE TABLE events_p0 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 0); CREATE TABLE events_p1 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 1); CREATE TABLE events_p2 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 2); CREATE TABLE events_p3 PARTITION OF events FOR VALUES WITH (MODULUS 4, REMAINDER 3); 

Catching the Rows You Forgot About: Default Partitions

Especially during development, or when I am not one hundred percent sure every possible value has a home, I set up a default partition to catch anything that does not match an existing rule.

CREATE TABLE orders_default PARTITION OF orders DEFAULT; 

This has saved me more than once from a failed insert at 2 a.m. because someone introduced a new status value nobody told me about.

Automating Partition Creation

I never want to be the person manually creating next month's partition by hand, and neither should you. In production, I write a function like this and schedule it.

CREATE OR REPLACE FUNCTION create_monthly_partition(target_date DATE) RETURNS VOID AS $$ DECLARE partition_name TEXT; start_date DATE; end_date DATE; BEGIN start_date := DATE_TRUNC('month', target_date); end_date := start_date + INTERVAL '1 month'; partition_name := 'orders_' || TO_CHAR(start_date, 'YYYY_MM'); EXECUTE FORMAT( 'CREATE TABLE IF NOT EXISTS %I PARTITION OF orders FOR VALUES FROM (%L) TO (%L)', partition_name, start_date, end_date ); END; $$ LANGUAGE plpgsql; -- Create partitions for the next 3 months SELECT create_monthly_partition(DATE_TRUNC('month', NOW()) + (n || ' month')::INTERVAL) FROM generate_series(0, 2) AS n; 

I schedule this with pg_cron when it is available, or with an external scheduler like a cron job or Airflow when it is not. Either way, I make sure I always have a few months of partitions created ahead of time, so an inserting application never hits a missing partition.

Dropping Old Partitions

This is where partitioning earns its keep for data lifecycle management. Instead of running a slow, lock-heavy DELETE that chews through WAL and leaves bloat behind, I just drop the partition.

-- Instantly drop a year's worth of data DROP TABLE orders_2022_01; DROP TABLE orders_2022_02; -- ... etc 

If I need to keep the data around for archival purposes rather than destroying it, I detach it instead:

ALTER TABLE orders DETACH PARTITION orders_2022_01; -- Partition now exists as a standalone table, archive or export it 

Gotchas I Watch Out For

A few things trip people up every single time, so let me save you the pain.

Your primary key has to include the partition key. PostgreSQL cannot enforce uniqueness across partitions unless the partition key is part of the key.

-- This will fail: ALTER TABLE orders ADD PRIMARY KEY (id); -- This works: ALTER TABLE orders ADD PRIMARY KEY (id, created_at); 

Foreign keys referencing a partitioned table are not supported. Foreign keys going out from a partitioned table are fine, but nothing else can point at it with a foreign key constraint.

Partition pruning only works if the partition key shows up in your WHERE clause. Leave it out, and PostgreSQL has no choice but to scan every partition.

Do not go overboard with granularity. Hundreds of tiny partitions will hurt planning time more than they help. For time-series data, I have found monthly or quarterly partitions to be the sweet spot in most systems I have run.

Checking Your Partition Setup

When I want a quick sanity check on what partitions exist and how big they have gotten, I run this:

-- List all partitions of a table SELECT inhrelid::regclass AS partition_name, pg_get_expr(c.relpartbound, inhrelid) AS partition_bound, pg_size_pretty(pg_relation_size(inhrelid)) AS size FROM pg_inherits JOIN pg_class c ON c.oid = inhrelid WHERE inhparent = 'orders'::regclass ORDER BY partition_name; 

When I Choose Not to Partition

Partitioning is not free. It adds real operational complexity, and I skip it when any of the following apply:

  • The table is under roughly ten million rows. Indexing alone usually handles that fine.
  • There is no natural partition key to work with.
  • Most of the queries against the table are aggregations across the whole dataset anyway, in which case partitioning will not move the needle much.
  • The team maintaining the system is not ready to take on the added operational complexity.

Wrapping Up

PostgreSQL's declarative partitioning is mature and, honestly, not that hard to implement once you understand the shape of your data. Here is what I keep coming back to:

  • Use range partitioning for time-series and sequential data.
  • Use list partitioning for categorical or enum-style columns.
  • Use hash partitioning when you need even distribution and have no natural key.
  • Always include the partition key in your primary key.
  • Automate partition creation, and drop old partitions instead of running expensive deletes.
  • Always verify partition pruning with EXPLAIN so you know your queries are actually benefiting.

My advice after years of doing this: pick one table that is currently causing you pain, instrument it properly, implement partitioning, and measure the before and after. Once you see the difference for yourself, you will not need convincing on the next one.