Module 4: Native Partitioning in PostgreSQL

Welcome to the module

In module 3 you left your search engine working: Spanish FTS over posts with a GIN index, ranking by ts_rank_cd, snippets with ts_headline, and a fuzzy fallback with pg_trgm. The posts table with 100k rows responds in 13 ms — congratulations, that's production-ready.

But the Data Layer sub-track doesn't end with fast search engines. You have another table in your Blog API that grows differently: events records every post view, every like, every comment, every user action. After 6 months you have 50 million rows. After a year, 100 million. And the query "show me the events from the last week" takes 3.2 seconds. Your autovacuum takes hours. Deleting events older than a year with DELETE FROM events WHERE created_at < NOW() - INTERVAL '1 year' holds the lock for 40 minutes and leaves the table with massive bloat.

This module teaches you the tool PostgreSQL has for that class of table: native partitioning. You're going to learn to make the "do I partition this table?" decision with concrete criteria, to apply the 3 types (range, list, hash) depending on the case, to verify partition pruning with EXPLAIN, to automate the maintenance with pg_partman, and to run a zero-downtime migration on a table in production.

By the end of the module you'll be able to receive the ticket "events is slow and the disk is filling up" and solve it with architectural confidence, not with patches.

Where are we? Where are we going?

What you already know:

Guide #8 (PostgreSQL & SQLAlchemy): schema, B-tree indexes, basic EXPLAIN, transactions, basic locking, the SQLAlchemy ORM, Alembic migrations.
Guide #12 (Database Performance): deep EXPLAIN ANALYZE, advanced indexing (composite, covering, partial), N+1, pg_stat_statements, autovacuum tuning, anti-patterns (large OFFSET, COUNT(*) on massive tables).
Guide #13 (SQL Patterns for Production): cursor pagination, soft deletes, audit logs, multitenancy with RLS, zero-downtime migrations — this is the technique you're going to reuse at the end of the module.
Modules 1-3 of this guide: deep JSONB, multilingual FTS, fuzzy search with pg_trgm. You solved the problem "what happens when a column stores dynamic schemas?" and "what happens when I need search?".

What you'll build this week:

The next architectural problem: what happens when a table grows beyond what the standard engine handles well?

The PostgreSQL "table + B-tree + autovacuum" combo works excellently up to a point. Past ~50-100M rows with range queries, that engine starts to suffer. The causes are concrete: giant B-tree indexes that don't fit in RAM, autovacuum that takes hours and blocks, range queries that scan millions of unnecessary tuples, dropping old data with a massive DELETE that holds locks and leaves bloat. Partitioning solves all four by dividing the table into smaller pieces that are managed independently.

Where we're going:

Module 5 — Materialized Views: you'll take partitioning as the base. Typical dashboards query recent partitions and aggregate data. MVs avoid recomputing every time.
Module 6 — Advisory Locks: when you coordinate the job that creates new partitions or the one that drops the old ones, an advisory lock prevents concurrent runs.
Module 8 — Final project: you'll apply real partitioning to the Blog API refactor (a comments table partitioned by month with a zero-downtime migration).

Professional objective

By the end of this module you'll be able to:

Decide whether a table should be partitioned using quantitative criteria (volume, query pattern, retention), avoiding premature partitioning.
Choose between the 3 types (range, list, hash) based on the use case with a memorable decision tree.
Create partitioned tables with PostgreSQL 16's declarative syntax, including a default partition and index propagation to the children.
Verify that partition pruning works by reading EXPLAIN ANALYZE and recognizing which partitions get scanned.
Automate the maintenance (creating future partitions, dropping old ones) with pg_partman.
Apply the "RLS for isolation + partitioning for scale" pattern in multi-tenant SaaS.
Migrate an existing non-partitioned table to a partitioned one without downtime, reusing the technique from guide #13.
Work with partitioned tables from async SQLAlchemy 2.0 without changing the ORM code.

Why does this module matter?

1. It's the moment where PostgreSQL enters "quasi-DBA" territory for the senior backend dev. Partitioning is an architectural decision that crosses development and operations. The backend dev makes it when they read the next support ticket "the events API takes more than 5 seconds at peak hours", or when they see the autovacuum metric taking longer every week, or when the infra team asks "can you delete old data without taking down the site?". Knowing how to solve it sets you apart.

2. The typical alternative is adding services to the stack — and it's almost always premature. The common reflex on seeing "a very big table" is "let's migrate it to Cassandra" or "let's put in a data warehouse." Both options are very expensive in operations, money, and latency, and they're almost always unnecessary. A 100M-row table partitioned by date in PostgreSQL answers range queries for the last week in under 50 ms. The right decision 80% of the time is "partition first, consider another option if that isn't enough afterwards."

3. Multi-tenant SaaS needs it explicitly. If you build a multi-tenant app (which is practically every modern B2B app), the question "RLS or partitioning?" shows up. The right answer is "both, they're complementary". RLS isolates for security. Partitioning scales for performance. Without partitioning, RLS over a 100M-row table with 1000 tenants leaves you with queries that scan the whole table anyway.

4. Deleting old data without taking down production is a rentable skill. In any system with audit logs, events, metrics, or sessions, the moment comes to delete the old stuff (compliance, costs, performance). Doing it with DELETE FROM ... WHERE created_at < ... on a massive table is a bomb — locks, bloat, app queries that time out. With partitioning, DROP PARTITION is instantaneous and frees disk immediately. That difference is weeks vs minutes in operations.

A scenario that illustrates the module

Imagine you get to the office on Monday. The platform squad assigns you this ticket:

"The events table has 50M rows and grows 2M/month. The analytics dashboards take 4 seconds to load. The compliance team is asking us to delete events older than 18 months. When we try a DELETE by range, the API gets slow for all users. We need to solve it without breaking anything."

— Lead Backend, Monday 9:15 AM

Without this module, the options that occur to you are:

"Let's migrate it to a data warehouse" (weeks of work, $$$, new latency).
"Let's delete in small batches and cross our fingers" (a palliative, it doesn't solve the slowness).
"Let's add more indexes" (doesn't help with range queries).

With this module, your proposal on Tuesday is:

Partition events by created_at monthly (capsules 02 and 03).
Configure pg_partman so it creates the next 12 partitions automatically and drops the ones older than 18 months (capsule 07).
A zero-downtime migration using the technique from guide #13 (referenced in capsule 08, not re-explained).
Validate with EXPLAIN ANALYZE that the dashboard's queries only scan the partitions of the requested range (capsule 06).
Demonstrate to the lead with before/after benchmarks: 4s → 40ms (capsule 03 shows how to measure).

On Wednesday you have the PR approved. On Thursday it's in production. On Friday the dashboard responds in 40 ms and the cron's monthly DROP PARTITION takes 200 ms instead of 40 minutes. The compliance team is happy. Your lead is happier.

That's what this module equips you for.

Module map

Capsule	Topic	What you'll learn
01	Module introduction	Why partitioning, what you'll build (this capsule)
02	When to partition and when not to	A decision matrix with quantitative criteria to avoid premature partitioning
03	Range partitioning by date	The 80% case: partitioning `events`/logs/time-series by `created_at` monthly with declarative SQL
04	List partitioning by category/tenant	Multi-tenant SaaS: partitioning by `tenant_id` and combining it with RLS
05	Hash partitioning for uniform distribution	When there's no natural criterion: hashing over `user_id` to spread rows evenly
06	Partition pruning and constraint exclusion	How the planner decides which partitions to scan and how to verify it with EXPLAIN
07	`pg_partman` and automated maintenance	Creating future partitions and dropping old ones without touching anything manually
08	Project: partitioning `events` with 50M rows	Applying everything to a real case with a zero-downtime migration and before/after benchmarks

Narrative flow:

Capsules 02-03: you establish the criteria for when to partition and you learn the most common case (range by date). With that you solve 80% of real problems.
Capsules 04-05: you complete the repertoire with the other two types. You now handle all 3 use cases (time-series, multi-tenant, uniform distribution).
Capsule 06: ensures you understand the query-performance benefit — partition pruning with EXPLAIN. Without this, partitioning may not help.
Capsule 07: automation with pg_partman. Without this, partitioning gets abandoned because "it's too much maintenance."
Capsule 08: an integrative project where you apply everything to a real table with a zero-downtime migration.

Connection with the integrative project

The guide's final project (module 8) is the "Advanced Blog API": a refactor of the Blog API from guide #8 incorporating all the advanced features. A key piece is partitioning the comments table by month.

This module's mini-project (capsule 08) replicates that technique on an events table. When you get to the integrative project, the mechanics of the zero-downtime migration, configuring pg_partman, and validating with EXPLAIN will already be internalized — you'll just change the context (from events to comments).

The architectural decisions you'll make in the integrative project (partition by month or by quarter? retention of how many months? what to do with the default partition?) are the ones you'll learn here.

Connection with guide #13 (SQL Patterns for Production)

Guide #13 covered zero-downtime migrations in general: the "new table → backfill → atomic swap → cleanup" pattern for any schema change without taking down production. This module doesn't re-explain that technique. It assumes it and applies it to the specific case of "converting a non-partitioned table into a partitioned one."

Guide #13 also covered partitioning for audit logs briefly as one of the techniques for keeping the audit_logs table fast (module 7 of #13). This module goes deeper into that technique: the patterns you saw in #13 (range partitioning by date, dropping old partitions) are exactly the ones you'll learn here in detail. If you want to reapply what you learn to audit_logs, it all transfers directly.

And guide #13 covered multitenancy with RLS (module 5 of #13). Here you complement it with partitioning by tenant_id. Capsule 04 of this module makes the complementarity explicit: "RLS for security isolation + partitioning for performance scale — they're orthogonal, they combine."

What is NOT covered in this module

❌ JSONB and FTS — covered in modules 1-3 of this guide. If your partitioned table has JSONB or FTS columns, everything you learned applies the same over partitions (the indexes propagate).
❌ Materialized views — covered in module 5. MVs over partitioned tables are a common pattern, but the mechanics of MVs are separate.
❌ Multi-DB sharding / physical replication — that belongs to DBA guides or to distributed-database operations guides. Native partitioning is a single DB with the table divided internally, not multiple DBs.
❌ Citus, Hyperscale, TimescaleDB — these are extensions that build on top of native partitioning. The guide mentions some as a reference, but it doesn't go into their specific APIs.
❌ Re-explaining zero-downtime migrations — the technique is in guide #13. Here we only apply it.
❌ Partitioning with the old "table inheritance" — that was the pre-PostgreSQL 10 method. It's deprecated. This guide only covers declarative partitioning (PG 10+, mature in 14+).

Traps to avoid while taking the module

1. "Partitioning is always good, let's do it from the start of the project." It's the most common mistake. Partitioning small tables (<10M rows) adds complexity without benefit: queries that don't use the partition key are slower, foreign keys have limitations, schemas are more complex to maintain. Capsule 02 teaches you the quantitative criteria for avoiding this mistake. Read it carefully — it's the module's most important decision.

2. "Partition pruning is automatic, no need to think about it." Partition pruning depends on your query including the partition key in the WHERE. If you have events partitioned by created_at but you run SELECT ... WHERE user_id = 42 (without a date filter), PostgreSQL scans all the partitions. Capsule 06 teaches you to read EXPLAIN to confirm pruning before declaring victory.

3. "pg_partman is optional, I'll add it later." It isn't optional in production. Without pg_partman, someone has to create new partitions every month manually and drop the old ones. That person goes on vacation, forgets, the inserts start failing (or land in the default partition, which grows out of control). Capsule 07 treats it as a mandatory piece.

4. "RLS or partitioning for multi-tenant — one replaces the other." A recurring confusion. RLS guarantees that tenant A doesn't read tenant B's data (security). Partitioning makes tenant A's queries only scan their partition (performance). They do different things. They combine. Capsule 04 makes it explicit with code.

5. "Skipping capsule 06 because it sounds obvious." Partition pruning isn't obvious when you read it for the first time in a real EXPLAIN. There are cases where it doesn't work the way you expect (queries with OR, queries with IN, queries with the partition key in a subquery) and the plan scans all the partitions even though it "should" use pruning. That capsule trains your eye for reading EXPLAIN on partitioned tables.

6. "The zero-downtime migration is trivial — after all, I already read #13." The general technique is in #13. Applying it to partitioning has specific gotchas (how to ensure new inserts go to the partitioned table while you backfill the old one, how to handle foreign keys, how to do the atomic swap). Capsule 08 covers it concretely.

Self-assessment question

Before starting this module, make sure you can confidently answer:

About PostgreSQL and guide #8:

Can you read an EXPLAIN ANALYZE and tell a Seq Scan from an Index Scan from a Bitmap Heap Scan?
Have you written an Alembic migration and do you know the difference between op.execute() and op.create_table()?
Do you understand what a B-tree index is, when it's used, and when it isn't?

About guide #12:

Do you know what autovacuum is and why it matters for tables with lots of UPDATE/DELETE?
Have you used pg_stat_statements or auto_explain?
Do you recognize the large-OFFSET anti-pattern and why cursor pagination solves it?

About guide #13:

Do you know what Row-Level Security (RLS) is and how it's configured for multi-tenant?
Have you read about the zero-downtime migrations technique (new table → backfill → swap)?
Do you recognize the general pattern of "deleting old stuff without touching the live app"?

About modules 1-3 of this guide:

Do you know how to declare Mapped[dict] for JSONB in SQLAlchemy 2.0?
Have you added a GIN index for FTS in a migration?

If you hesitate on more than 2-3 questions, it's worth reviewing the previous material before moving on. If you hesitate on one or two specific ones, keep going and come back when the concept shows up.

Evidence of success

By the end of the module you'll know you succeeded if:

✅ You can receive the ticket "table X has 80M rows and range queries take seconds" and propose whether to partition or not, with quantitative justification.
✅ You can write the SQL to create a range-partitioned table with a default partition and indexes propagated to the children.
✅ You can read an EXPLAIN ANALYZE of a partitioned table and say how many partitions it scanned and why.
✅ You can configure pg_partman to maintain future partitions and drop old ones according to retention.
✅ You can explain to a colleague why "RLS or partitioning for multi-tenant" is a false dichotomy.
✅ You can run the "non-partitioned → partitioned" zero-downtime migration on a table with real traffic.
✅ You can produce before/after benchmarks that demonstrate the improvement with numbers (latency, throughput, DROP time).

We start in the next capsule

Capsule 02 — When to partition and when not to — establishes the criteria that govern the whole module. Before learning the syntax, you learn the decision: given a concrete case (volume, query pattern, retention, schema), do you partition or not?

That capsule presents a decision matrix with quantitative criteria, counterexamples of "tables that should NOT be partitioned," and the explicit trade-offs (what you gain vs what you pay). When you come out of it, you're going to be able to defend the decision "let's not partition this table yet" with data, not with hunches.

Before moving on, make sure you have PostgreSQL 14+ running locally (16+ recommended) and that your Blog API from module 1 is still accessible — you're going to use the events table as the working case in capsules 03 and 08.

Resources for the module

PostgreSQL 16 — Table Partitioning (Chapter 5.11) — the complete official reference. Read it once cover to cover for the overview; you're going to come back to specific sections during the module.
pg_partman — GitHub — the extension that automates the maintenance. Skim the README now; capsule 07 goes into detail.
Crunchy Data — Partitioning in PostgreSQL — a pragmatic overview with real examples. A good complement to the official docs.
AWS RDS — Best Practices for PostgreSQL Partitioning — an operational perspective from a mainstream cloud provider.
depesz blog — Waiting for PostgreSQL 14: Add option for DETACH PARTITION CONCURRENTLY — the history and caveats of the concurrent detach feature. Useful for understanding why PG 14+ is the target.

Module 4 — Advanced PostgreSQL for Backend Guide

Next capsule: When to partition and when not to — the decision matrix that avoids premature partitioning.

← PreviousModule project: the blog's Spanish-language search Next →When to partition and when not to: the decision matrix before the syntax