- What is Partitioning?
- Why Partition?
- Types of Partitioning
- How Hash Partitioning Works
- Our Implementation
- Step-by-Step Migration Explained
- How PostgreSQL Routes Queries
- Performance Benefits
- Best Practices
- Monitoring Partitions
- Common Pitfalls
- Hands-On Exercises
Partitioning is a database technique that divides a large table into smaller, more manageable pieces called partitions. Each partition is a separate physical table, but PostgreSQL presents them as a single logical table to your application.
┌─────────────────────────────────────────────────────────────────────────┐
│ BEFORE PARTITIONING │
├─────────────────────────────────────────────────────────────────────────┤
│ │
│ ┌─────────────────────────────────────────────────────────────────┐ │
│ │ users table │ │
│ │ │ │
│ │ ┌─────┬─────┬─────┬─────┬─────┬─────┬─────┬─────┬─────┬─────┐│ │
│ │ │row 1│row 2│row 3│row 4│row 5│row 6│row 7│row 8│ ... │row N││ │
│ │ └─────┴─────┴─────┴─────┴─────┴─────┴─────┴─────┴─────┴─────┘│ │
│ │ │ │
│ │ 7 MILLION ROWS IN ONE FILE │ │
│ │ (1+ GB on disk) │ │
│ └─────────────────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────────────────────┐
│ AFTER PARTITIONING (16 partitions) │
├─────────────────────────────────────────────────────────────────────────┤
│ │
│ ┌─────────────────────────────────────────────────────────────────┐ │
│ │ users (parent/logical table) │ │
│ │ - No data stored here! │ │
│ │ - Just defines the structure │ │
│ └───────────────────────────┬─────────────────────────────────────┘ │
│ │ │
│ ┌─────────────────────┼─────────────────────┐ │
│ │ │ │ │
│ ▼ ▼ ▼ │
│ ┌──────────┐ ┌──────────┐ ┌──────────┐ │
│ │users_p00 │ │users_p01 │ ... │users_p15 │ │
│ │ ~437K │ │ ~437K │ │ ~437K │ │
│ │ rows │ │ rows │ │ rows │ │
│ │ (~65MB) │ │ (~65MB) │ │ (~65MB) │ │
│ └──────────┘ └──────────┘ └──────────┘ │
│ │
│ 16 SEPARATE FILES, EACH ~1/16th OF TOTAL │
│ │
└─────────────────────────────────────────────────────────────────────────┘
| Aspect | Logical Table | Physical Partitions |
|---|---|---|
| Name | users |
users_p00, users_p01, ... |
| Visibility | Application sees this | Hidden from application |
| Data | None (just a view) | Actual data stored here |
| Queries | Query this table | PostgreSQL routes internally |
Your application code doesn't change at all! PostgreSQL handles all the routing automatically.
When a table grows to millions of rows, several problems emerge:
┌─────────────────────────────────────────────────────────────────────────┐
│ PROBLEMS WITH LARGE TABLES │
├─────────────────────────────────────────────────────────────────────────┤
│ │
│ 1. SLOW INDEX OPERATIONS │
│ ┌────────────────────────────────────────────────────────────────┐ │
│ │ B-Tree Index with 7M entries │ │
│ │ │ │
│ │ [Root] │ │
│ │ / \ │ │
│ │ [L1] [L1] ← More levels = slower │ │
│ │ / | \ / | \ │ │
│ │ [L2][L2][L2][L2][L2][L2] │ │
│ │ / | \ ... many more ... │ │
│ │ [Leaf nodes with actual row pointers] │ │
│ │ │ │
│ │ Tree Depth: log₁₆(7,000,000) ≈ 5-6 levels │ │
│ │ Each lookup = 5-6 disk reads (without cache) │ │
│ └────────────────────────────────────────────────────────────────┘ │
│ │
│ 2. VACUUM TAKES FOREVER │
│ ┌────────────────────────────────────────────────────────────────┐ │
│ │ VACUUM must scan entire 1GB+ table to find dead tuples │ │
│ │ Can take minutes, blocks autovacuum from other tables │ │
│ └────────────────────────────────────────────────────────────────┘ │
│ │
│ 3. NO PARALLEL PROCESSING │
│ ┌────────────────────────────────────────────────────────────────┐ │
│ │ Sequential scan of 7M rows = single CPU core doing all work │ │
│ │ Can't utilize multi-core processors efficiently │ │
│ └────────────────────────────────────────────────────────────────┘ │
│ │
│ 4. BACKUP/RESTORE NIGHTMARE │
│ ┌────────────────────────────────────────────────────────────────┐ │
│ │ Can't backup or restore individual portions of data │ │
│ │ All-or-nothing approach for massive tables │ │
│ └────────────────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────────────────┘
| Problem | Solution with Partitioning |
|---|---|
| Slow indexes | Smaller indexes per partition (16x smaller!) |
| Long VACUUM | VACUUM runs on small partitions independently |
| No parallelism | PostgreSQL can scan multiple partitions in parallel |
| Backup issues | Can backup/restore individual partitions |
| Lock contention | Locks are per-partition, less blocking |
PostgreSQL supports three types of partitioning:
Divides data based on a range of values (usually dates).
┌─────────────────────────────────────────────────────────────────────────┐
│ RANGE PARTITIONING │
├─────────────────────────────────────────────────────────────────────────┤
│ │
│ PARTITION BY RANGE (created_at) │
│ │
│ ┌────────────┐ ┌────────────┐ ┌────────────┐ ┌────────────┐ │
│ │ Jan 2024 │ │ Feb 2024 │ │ Mar 2024 │ │ Apr 2024 │ ... │
│ │ │ │ │ │ │ │ │ │
│ │ users_2024 │ │ users_2024 │ │ users_2024 │ │ users_2024 │ │
│ │ _01 │ │ _02 │ │ _03 │ │ _04 │ │
│ └────────────┘ └────────────┘ └────────────┘ └────────────┘ │
│ │ │ │ │ │
│ ▼ ▼ ▼ ▼ │
│ ┌────────────────────────────────────────────────────────────────┐ │
│ │ Timeline │ │
│ │ ──────────────────────────────────────────────────────────► │ │
│ │ Jan 1 Feb 1 Mar 1 Apr 1 May 1 ... │ │
│ └────────────────────────────────────────────────────────────────┘ │
│ │
│ USE CASE: Time-series data, logs, events │
│ BENEFIT: Easy to drop old partitions (instant delete!) │
│ │
└─────────────────────────────────────────────────────────────────────────┘
SQL Example:
CREATE TABLE events (
id UUID PRIMARY KEY,
created_at TIMESTAMPTZ NOT NULL,
event_type TEXT
) PARTITION BY RANGE (created_at);
CREATE TABLE events_2024_01 PARTITION OF events
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE events_2024_02 PARTITION OF events
FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
-- etc.Divides data based on explicit list of values.
┌─────────────────────────────────────────────────────────────────────────┐
│ LIST PARTITIONING │
├─────────────────────────────────────────────────────────────────────────┤
│ │
│ PARTITION BY LIST (region) │
│ │
│ ┌────────────┐ ┌────────────┐ ┌────────────┐ ┌────────────┐ │
│ │ EMEA │ │ APAC │ │ AMER │ │ DEFAULT │ │
│ │ │ │ │ │ │ │ │ │
│ │ 'UK','DE', │ │ 'JP','AU', │ │ 'US','BR', │ │ Everything │ │
│ │ 'FR','IT' │ │ 'SG','IN' │ │ 'CA','MX' │ │ else │ │
│ └────────────┘ └────────────┘ └────────────┘ └────────────┘ │
│ │
│ USE CASE: Multi-tenant, regional data, categories │
│ BENEFIT: Queries for specific region only scan that partition │
│ │
└─────────────────────────────────────────────────────────────────────────┘
SQL Example:
CREATE TABLE customers (
id UUID PRIMARY KEY,
name TEXT,
region TEXT NOT NULL
) PARTITION BY LIST (region);
CREATE TABLE customers_emea PARTITION OF customers
FOR VALUES IN ('UK', 'DE', 'FR', 'IT', 'ES');
CREATE TABLE customers_apac PARTITION OF customers
FOR VALUES IN ('JP', 'AU', 'SG', 'IN', 'CN');Divides data based on a hash function applied to a column.
┌─────────────────────────────────────────────────────────────────────────┐
│ HASH PARTITIONING │
├─────────────────────────────────────────────────────────────────────────┤
│ │
│ PARTITION BY HASH (id) │
│ │
│ ┌─────────────┐ │
│ │ User ID │ │
│ │ (UUID) │ │
│ └──────┬──────┘ │
│ │ │
│ ▼ │
│ ┌────────────────────┐ │
│ │ hash(UUID) % 16 │ │
│ │ = 0, 1, 2, ... 15│ │
│ └────────────────────┘ │
│ │ │
│ ┌──────┬──────┬────┴────┬──────┬──────┐ │
│ │ │ │ │ │ │ │
│ ▼ ▼ ▼ ▼ ▼ ▼ │
│ ┌────┐ ┌────┐ ┌────┐ ┌────┐ ┌────┐ ┌────┐ │
│ │ p0 │ │ p1 │ │ p2 │ ...│p13 │ │p14 │ │p15 │ │
│ └────┘ └────┘ └────┘ └────┘ └────┘ └────┘ │
│ │
│ USE CASE: Even distribution when no natural partition key │
│ BENEFIT: Guaranteed even data distribution across partitions │
│ │
└─────────────────────────────────────────────────────────────────────────┘
Why Hash for Users Table?
- No natural range: Users don't have a natural date-based access pattern
- No natural list: Users don't belong to predefined categories
- Even distribution: We want equal-sized partitions for balanced load
- ID-based lookups: Most queries are by user ID anyway
PostgreSQL uses an internal hash function to convert any value into an integer:
┌─────────────────────────────────────────────────────────────────────────┐
│ HASH FUNCTION INTERNALS │
├─────────────────────────────────────────────────────────────────────────┤
│ │
│ Input: UUID = '550e8400-e29b-41d4-a716-446655440000' │
│ │
│ Step 1: PostgreSQL's hash function │
│ ┌─────────────────────────────────────────────────────────────────┐ │
│ │ hashfunc(UUID) → 2847593821 (some large integer) │ │
│ └─────────────────────────────────────────────────────────────────┘ │
│ │
│ Step 2: Modulo operation │
│ ┌─────────────────────────────────────────────────────────────────┐ │
│ │ 2847593821 % 16 = 13 │ │
│ └─────────────────────────────────────────────────────────────────┘ │
│ │
│ Result: This UUID goes to partition users_p13 │
│ │
│ ────────────────────────────────────────────────────────────────── │
│ │
│ PROPERTIES OF GOOD HASH FUNCTION: │
│ │
│ 1. DETERMINISTIC: Same input always produces same output │
│ hash('abc') = 12345 (always) │
│ │
│ 2. UNIFORM: Outputs are evenly distributed │
│ ~6.25% of UUIDs hash to each of 16 partitions │
│ │
│ 3. AVALANCHE: Small input change = completely different output │
│ hash('abc') = 12345 │
│ hash('abd') = 98765 (completely different!) │
│ │
└─────────────────────────────────────────────────────────────────────────┘
partition_number = hash(partition_key) % MODULUS
Where:
- partition_key = the column value (our user's UUID)
- MODULUS = number of partitions (16)
- Result: 0 to 15 (determining which partition gets the row)
┌─────────────────────────────────────────────────────────────────────────┐
│ INSERTING 4 USERS INTO PARTITIONS │
├─────────────────────────────────────────────────────────────────────────┤
│ │
│ INSERT INTO users (id, first_name, last_name) │
│ VALUES │
│ ('uuid-1', 'Alice', 'Smith'), -- hash('uuid-1') % 16 = 3 │
│ ('uuid-2', 'Bob', 'Jones'), -- hash('uuid-2') % 16 = 7 │
│ ('uuid-3', 'Carol', 'White'), -- hash('uuid-3') % 16 = 3 │
│ ('uuid-4', 'David', 'Brown'); -- hash('uuid-4') % 16 = 12 │
│ │
│ │
│ RESULT: │
│ │
│ users_p00 │ users_p03 │ users_p07 │ users_p12 │ ... │
│ ──────────┼─────────────────┼──────────────┼────────────────┼───── │
│ (empty) │ Alice, Smith │ Bob, Jones │ David, Brown │ │
│ │ Carol, White │ │ │ │
│ │
│ Note: Alice and Carol ended up in same partition (p03) by chance! │
│ This is normal - hash functions don't guarantee unique buckets. │
│ │
└─────────────────────────────────────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────────────────────┐
│ CURRENT STATE │
├─────────────────────────────────────────────────────────────────────────┤
│ │
│ Table: users │
│ ├── Columns: id, created_at, updated_at, first_name, last_name │
│ ├── Rows: ~7,000,000 │
│ ├── Size: ~1 GB │
│ ├── Indexes: │
│ │ ├── users_pkey (PRIMARY KEY on id) │
│ │ ├── idx_user_name (first_name, last_name) │
│ │ └── idx_users_created_at (created_at DESC) │
│ └── Referenced by: users_accounts.user_id (FOREIGN KEY) │
│ │
└─────────────────────────────────────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────────────────────┐
│ TARGET STATE │
├─────────────────────────────────────────────────────────────────────────┤
│ │
│ Table: users (PARTITIONED) │
│ ├── Partition Method: HASH (id) │
│ ├── Number of Partitions: 16 │
│ ├── Columns: id, created_at, updated_at, first_name, last_name │
│ ├── Total Rows: ~7,000,000 │
│ ├── Rows per Partition: ~437,500 │
│ ├── Total Size: ~1 GB (same) │
│ ├── Size per Partition: ~65 MB │
│ │ │
│ ├── Partitions: │
│ │ ├── users_p00 (MODULUS 16, REMAINDER 0) │
│ │ ├── users_p01 (MODULUS 16, REMAINDER 1) │
│ │ ├── users_p02 (MODULUS 16, REMAINDER 2) │
│ │ │ ... (12 more partitions) ... │
│ │ └── users_p15 (MODULUS 16, REMAINDER 15) │
│ │ │
│ └── Indexes (on each partition): │
│ ├── users_pXX_pkey (PRIMARY KEY on id) │
│ ├── idx_users_part_name (first_name, last_name) │
│ └── idx_users_part_created_at (created_at DESC) │
│ │
└─────────────────────────────────────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────────────────────┐
│ MIGRATION WORKFLOW │
├─────────────────────────────────────────────────────────────────────────┤
│ │
│ ┌─────────────────┐ │
│ │ STEP 1 │ Create empty partitioned table structure │
│ │ Create New │ users_partitioned with 16 partitions │
│ └────────┬────────┘ │
│ │ │
│ ▼ │
│ ┌─────────────────┐ │
│ │ STEP 2 │ INSERT INTO users_partitioned │
│ │ Copy Data │ SELECT * FROM users │
│ └────────┬────────┘ (PostgreSQL auto-routes to partitions!) │
│ │ │
│ ▼ │
│ ┌─────────────────┐ │
│ │ STEP 3 │ ALTER TABLE users_accounts │
│ │ Drop FK │ DROP CONSTRAINT fk_users_accounts_user │
│ └────────┬────────┘ │
│ │ │
│ ▼ │
│ ┌─────────────────────────────────────────────────────────────────┐ │
│ │ STEP 4: ATOMIC SWAP (Critical Section!) │ │
│ │ │ │
│ │ BEGIN TRANSACTION; │ │
│ │ ALTER TABLE users RENAME TO users_old; │ │
│ │ ALTER TABLE users_partitioned RENAME TO users; │ │
│ │ COMMIT; │ │
│ │ │ │
│ │ ⚠️ For a brief moment, both tables exist! │ │
│ │ But the RENAME is atomic - no queries will fail. │ │
│ └────────┬────────────────────────────────────────────────────────┘ │
│ │ │
│ ▼ │
│ ┌─────────────────┐ │
│ │ STEP 5 │ ALTER TABLE users_accounts │
│ │ Restore FK │ ADD CONSTRAINT fk_users_accounts_user │
│ └────────┬────────┘ FOREIGN KEY (user_id) REFERENCES users(id) │
│ │ │
│ ▼ │
│ ┌─────────────────┐ │
│ │ STEP 6 │ DROP TABLE users_old CASCADE │
│ │ Cleanup │ (Remove the old non-partitioned table) │
│ └────────┬────────┘ │
│ │ │
│ ▼ │
│ ┌─────────────────┐ │
│ │ STEP 7 │ ANALYZE users │
│ │ Statistics │ (Update query planner statistics) │
│ └─────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────────────────┘
CREATE TABLE users_partitioned (
id UUID NOT NULL DEFAULT uuidv7(),
created_at TIMESTAMPTZ DEFAULT NOW(),
updated_at TIMESTAMPTZ,
first_name VARCHAR(100) NOT NULL,
last_name VARCHAR(100) NOT NULL,
PRIMARY KEY (id)
) PARTITION BY HASH (id);What happens:
- Creates a parent table with no actual data storage
PARTITION BY HASH (id)tells PostgreSQL how to route data- The parent table is just a "dispatcher" - all data lives in partitions
CREATE TABLE users_p00 PARTITION OF users_partitioned
FOR VALUES WITH (MODULUS 16, REMAINDER 0);What this means:
MODULUS 16: We're dividing into 16 bucketsREMAINDER 0: This partition gets rows wherehash(id) % 16 = 0- Repeat for REMAINDER 1 through 15
Why 16 partitions?
┌─────────────────────────────────────────────────────────────────────────┐
│ PARTITION COUNT TRADE-OFFS │
├─────────────────────────────────────────────────────────────────────────┤
│ │
│ TOO FEW PARTITIONS (e.g., 4): │
│ ├── Each partition still very large (1.75M rows each) │
│ ├── Limited parallelism benefits │
│ └── Maintenance still slow │
│ │
│ TOO MANY PARTITIONS (e.g., 1000): │
│ ├── Too many small files (overhead) │
│ ├── Query planning becomes slow │
│ └── File descriptor exhaustion risk │
│ │
│ JUST RIGHT (16): │
│ ├── ~437K rows per partition (manageable) │
│ ├── 16 parallel workers possible │
│ ├── Easy to scale (can split later if needed) │
│ └── Powers of 2 are efficient for hash functions │
│ │
│ RULE OF THUMB: │
│ - Aim for partitions between 100K and 10M rows │
│ - 8-64 partitions for most OLTP workloads │
│ │
└─────────────────────────────────────────────────────────────────────────┘
CREATE INDEX idx_users_part_name ON users_partitioned (first_name, last_name);Magic of partitioned indexes:
┌─────────────────────────────────────────────────────────────────────────┐
│ PARTITION-WISE INDEXING │
├─────────────────────────────────────────────────────────────────────────┤
│ │
│ When you create an index on a partitioned table: │
│ │
│ CREATE INDEX idx_users_part_name ON users_partitioned (first_name); │
│ │
│ PostgreSQL AUTOMATICALLY creates 16 indexes: │
│ │
│ ┌────────────────────────────────────────────────────────────────┐ │
│ │ users_partitioned ← Parent index (metadata only) │ │
│ │ └── idx_users_part_name │ │
│ │ ├── users_p00_first_name_idx ← Actual index on p00 │ │
│ │ ├── users_p01_first_name_idx ← Actual index on p01 │ │
│ │ ├── users_p02_first_name_idx ← Actual index on p02 │ │
│ │ │ ... │ │
│ │ └── users_p15_first_name_idx ← Actual index on p15 │ │
│ └────────────────────────────────────────────────────────────────┘ │
│ │
│ BENEFIT: Each partition index is 16x smaller = 16x faster to search │
│ │
└─────────────────────────────────────────────────────────────────────────┘
INSERT INTO users_partitioned
SELECT * FROM users;What happens during INSERT:
┌─────────────────────────────────────────────────────────────────────────┐
│ DATA ROUTING DURING INSERT │
├─────────────────────────────────────────────────────────────────────────┤
│ │
│ Each row from original table: │
│ │
│ ┌─────────────────────────────────────────────────────────────────┐ │
│ │ Row: id='abc-123', first_name='John', last_name='Doe' │ │
│ └─────────────────────────────────────────────────────────────────┘ │
│ │ │
│ ▼ │
│ ┌─────────────────────────────────────────────────────────────────┐ │
│ │ PostgreSQL computes: hash('abc-123') % 16 = 7 │ │
│ └─────────────────────────────────────────────────────────────────┘ │
│ │ │
│ ▼ │
│ ┌─────────────────────────────────────────────────────────────────┐ │
│ │ Row is physically inserted into: users_p07 │ │
│ └─────────────────────────────────────────────────────────────────┘ │
│ │
│ This happens for all 7 million rows! │
│ ~437,500 rows end up in each of 16 partitions (evenly distributed) │
│ │
└─────────────────────────────────────────────────────────────────────────┘
ALTER TABLE users RENAME TO users_old;
ALTER TABLE users_partitioned RENAME TO users;Why this is safe:
ALTER TABLE RENAMEis an atomic operation- Takes only a brief AccessExclusiveLock (metadata update)
- No data copying needed - just catalog update
- Your application sees either old name or new name, never inconsistent state
┌─────────────────────────────────────────────────────────────────────────┐
│ SELECT QUERY ROUTING │
├─────────────────────────────────────────────────────────────────────────┤
│ │
│ SCENARIO 1: Query by partition key (id) │
│ ────────────────────────────────────────── │
│ │
│ SELECT * FROM users WHERE id = 'abc-123'; │
│ │
│ ┌────────────────────────────────────────────────────────────────┐ │
│ │ Query Planner: │ │
│ │ 1. Sees WHERE id = 'abc-123' │ │
│ │ 2. Computes hash('abc-123') % 16 = 7 │ │
│ │ 3. ONLY scans users_p07 (partition pruning!) │ │
│ └────────────────────────────────────────────────────────────────┘ │
│ │
│ QUERY PLAN: │
│ Index Scan using users_p07_pkey on users_p07 │
│ Index Cond: (id = 'abc-123') │
│ │
│ ═══════════════════════════════════════════════════════════════════ │
│ │
│ SCENARIO 2: Query without partition key │
│ ──────────────────────────────────────── │
│ │
│ SELECT * FROM users WHERE first_name = 'John'; │
│ │
│ ┌────────────────────────────────────────────────────────────────┐ │
│ │ Query Planner: │ │
│ │ 1. Cannot determine partition from first_name │ │
│ │ 2. Must scan ALL 16 partitions │ │
│ │ 3. But can do so in PARALLEL! │ │
│ └────────────────────────────────────────────────────────────────┘ │
│ │
│ QUERY PLAN: │
│ Append │
│ -> Parallel Index Scan on users_p00 │
│ -> Parallel Index Scan on users_p01 │
│ -> ... (all 16 partitions) │
│ │
└─────────────────────────────────────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────────────────────┐
│ PARTITION PRUNING VISUALIZATION │
├─────────────────────────────────────────────────────────────────────────┤
│ │
│ WITHOUT PARTITIONING: │
│ SELECT * FROM users WHERE id = 'xyz'; │
│ │
│ ┌──────────────────────────────────────────────────────────────┐ │
│ │ ████████████████████████████████████████████████████████████ │ │
│ │ ████████████████████ SCAN ALL 7M ROWS ██████████████████████ │ │
│ │ ████████████████████████████████████████████████████████████ │ │
│ └──────────────────────────────────────────────────────────────┘ │
│ Time: Index scan through 7M-row index │
│ │
│ ═══════════════════════════════════════════════════════════════════ │
│ │
│ WITH PARTITIONING (partition pruning): │
│ SELECT * FROM users WHERE id = 'xyz'; │
│ │
│ hash('xyz') % 16 = 5 → Only scan partition 5! │
│ │
│ ┌────┬────┬────┬────┬────┬────┬────┬────┬────┬────┬────┬────┬────┐ │
│ │ p0 │ p1 │ p2 │ p3 │ p4 │ p5 │ p6 │ p7 │ p8 │...│p14 │p15 │ │ │
│ │skip│skip│skip│skip│skip│████│skip│skip│skip│...│skip│skip│ │ │
│ │ │ │ │ │ │SCAN│ │ │ │ │ │ │ │ │
│ └────┴────┴────┴────┴────┴────┴────┴────┴────┴────┴────┴────┴────┘ │
│ │
│ Time: Index scan through 437K-row index (16x smaller!) │
│ │
└─────────────────────────────────────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────────────────────┐
│ PERFORMANCE COMPARISON │
├─────────────────────────────────────────────────────────────────────────┤
│ │
│ Operation │ Before (No Partition) │ After (16 Partitions)│
│ ───────────────────────┼───────────────────────┼──────────────────────│
│ Index size (total) │ 600 MB (1 big index) │ 600 MB (16 × 37.5MB) │
│ Index scan by ID │ 5-6 levels deep │ 4-5 levels deep │
│ Sequential scan │ Single thread │ Up to 16 threads │
│ VACUUM duration │ ~10 minutes │ ~40 seconds each │
│ Index rebuild │ ~5 minutes │ ~20 seconds each │
│ DELETE old data │ Hours (for millions) │ Instant (DROP part.) │
│ Lock contention │ Table-wide locks │ Partition-level locks│
│ │
└─────────────────────────────────────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────────────────────┐
│ PARALLEL QUERY EXECUTION │
├─────────────────────────────────────────────────────────────────────────┤
│ │
│ Query: SELECT COUNT(*) FROM users WHERE first_name LIKE 'A%' │
│ │
│ WITHOUT PARTITIONING: │
│ ┌────────────────────────────────────────────────────────────────┐ │
│ │ CPU Core 1: Scanning all 7M rows... ████████████████████████ │ │
│ │ CPU Core 2: (idle) │ │
│ │ CPU Core 3: (idle) │ │
│ │ CPU Core 4: (idle) │ │
│ │ │ │
│ │ Total time: 10 seconds │ │
│ └────────────────────────────────────────────────────────────────┘ │
│ │
│ WITH PARTITIONING (parallel_workers = 4): │
│ ┌────────────────────────────────────────────────────────────────┐ │
│ │ Worker 1: Scanning p00, p04, p08, p12 ██████ │ │
│ │ Worker 2: Scanning p01, p05, p09, p13 ██████ │ │
│ │ Worker 3: Scanning p02, p06, p10, p14 ██████ │ │
│ │ Worker 4: Scanning p03, p07, p11, p15 ██████ │ │
│ │ │ │
│ │ Total time: ~2.5 seconds (4x faster!) │ │
│ └────────────────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────────────────────┐
│ BEST PRACTICES │
├─────────────────────────────────────────────────────────────────────────┤
│ │
│ ✅ DO: Choose partition key based on query patterns │
│ - If most queries are by ID → Hash partition by ID │
│ - If most queries are by date → Range partition by date │
│ │
│ ✅ DO: Include partition key in WHERE clauses when possible │
│ - Enables partition pruning │
│ - Query: WHERE id = 'xyz' → Only 1 partition scanned │
│ │
│ ✅ DO: Create indexes on the parent table │
│ - PostgreSQL propagates to all partitions automatically │
│ │
│ ✅ DO: Monitor partition sizes periodically │
│ - Ensure even distribution │
│ - Detect skew early │
│ │
│ ✅ DO: Use power-of-2 partition counts for hash │
│ - 8, 16, 32, 64 work best │
│ - More efficient hash distribution │
│ │
│ ✅ DO: Plan for growth │
│ - Adding partitions later requires rehashing ALL data │
│ - Start with more partitions than you think you need │
│ │
└─────────────────────────────────────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────────────────────┐
│ ANTI-PATTERNS │
├─────────────────────────────────────────────────────────────────────────┤
│ │
│ ❌ DON'T: Partition small tables (< 1M rows) │
│ - Overhead exceeds benefits │
│ - Regular indexes are sufficient │
│ │
│ ❌ DON'T: Use too many partitions (> 100 for hash) │
│ - Query planning becomes slow │
│ - File descriptor exhaustion │
│ │
│ ❌ DON'T: Forget to include partition key in JOINs │
│ - SELECT * FROM users u │
│ JOIN orders o ON u.id = o.user_id -- Good! │
│ WHERE u.id = 'xyz' -- Partition pruning works │
│ │
│ ❌ DON'T: Create partitions with different column definitions │
│ - All partitions must match parent exactly │
│ │
│ ❌ DON'T: Rely on partitioning for security/multi-tenancy │
│ - Partitions are internal organization, not access control │
│ - Use Row Level Security (RLS) for that │
│ │
│ ❌ DON'T: Update the partition key column │
│ - UPDATE users SET id = 'new-id' WHERE id = 'old-id' │
│ - This may move row between partitions (expensive!) │
│ │
└─────────────────────────────────────────────────────────────────────────┘
-- 1. Check partition sizes and row counts
SELECT
c.relname AS partition_name,
pg_size_pretty(pg_relation_size(c.oid)) AS size,
pg_stat_get_live_tuples(c.oid) AS row_count
FROM pg_inherits i
JOIN pg_class c ON c.oid = i.inhrelid
WHERE i.inhparent = 'users'::regclass
ORDER BY c.relname;
-- 2. Check if partitions are evenly distributed
SELECT
c.relname AS partition,
pg_stat_get_live_tuples(c.oid) AS rows,
ROUND(100.0 * pg_stat_get_live_tuples(c.oid) /
SUM(pg_stat_get_live_tuples(c.oid)) OVER(), 2) AS percentage
FROM pg_inherits i
JOIN pg_class c ON c.oid = i.inhrelid
WHERE i.inhparent = 'users'::regclass
ORDER BY c.relname;
-- 3. Check partition pruning in query plan
EXPLAIN (ANALYZE, COSTS OFF)
SELECT * FROM users WHERE id = 'some-uuid-here';
-- Should show "Partitions removed: 15" or similar
-- 4. Check index sizes per partition
SELECT
schemaname,
tablename,
indexname,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE tablename LIKE 'users_p%'
ORDER BY tablename, indexname;┌─────────────────────────────────────────────────────────────────────────┐
│ FOREIGN KEY CONSIDERATIONS │
├─────────────────────────────────────────────────────────────────────────┤
│ │
│ PostgreSQL 11+ supports FKs referencing partitioned tables │
│ But there are restrictions: │
│ │
│ ✅ WORKS: FK pointing TO partitioned table │
│ CREATE TABLE orders ( │
│ user_id UUID REFERENCES users(id) -- OK! │
│ ); │
│ │
│ ⚠️ CAREFUL: FK FROM partitioned table │
│ The referenced table must also handle the partition key │
│ │
│ ❌ LIMITATION: Unique constraints must include partition key │
│ CREATE TABLE users ( │
│ id UUID, │
│ email TEXT UNIQUE -- ERROR! Must include id │
│ ) PARTITION BY HASH (id); │
│ │
│ ✅ WORKS: │
│ CREATE TABLE users ( │
│ id UUID, │
│ email TEXT, │
│ UNIQUE (id, email) -- OK: includes partition key │
│ ) PARTITION BY HASH (id); │
│ │
└─────────────────────────────────────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────────────────────┐
│ CROSS-PARTITION QUERY PERFORMANCE │
├─────────────────────────────────────────────────────────────────────────┤
│ │
│ EXPENSIVE OPERATION: │
│ SELECT * FROM users ORDER BY created_at LIMIT 10; │
│ │
│ WHY? PostgreSQL must: │
│ 1. Scan all 16 partitions │
│ 2. Get top 10 from EACH partition │
│ 3. Merge and re-sort all 160 rows │
│ 4. Return final top 10 │
│ │
│ SOLUTION: If this query is frequent, consider: │
│ - Range partitioning by created_at instead │
│ - Maintaining a separate "recent_users" cache table │
│ - Using a covering index │
│ │
└─────────────────────────────────────────────────────────────────────────┘
After running the migration, try these exercises to understand partitioning:
┌─────────────────────────────────────────────────────────────────────────┐
│ PARTITIONING RESULTS - 6,870,006 USERS │
├─────────────────────────────────────────────────────────────────────────┤
│ │
│ Migration Time: 38.6 seconds (data copy) + 0.5s (ANALYZE) │
│ │
│ PARTITION DISTRIBUTION: │
│ ┌──────────┬──────────┬────────────┬─────────────┬────────────┐ │
│ │ Partition│ Rows │ Data │ Index │ Total │ │
│ ├──────────┼──────────┼────────────┼─────────────┼────────────┤ │
│ │ users_p00│ 429,491 │ 28 MB │ 32 MB │ 60 MB │ │
│ │ users_p01│ 428,638 │ 28 MB │ 31 MB │ 59 MB │ │
│ │ users_p02│ 429,198 │ 28 MB │ 32 MB │ 60 MB │ │
│ │ users_p03│ 429,214 │ 28 MB │ 31 MB │ 59 MB │ │
│ │ users_p04│ 429,173 │ 28 MB │ 32 MB │ 60 MB │ │
│ │ users_p05│ 429,347 │ 28 MB │ 32 MB │ 60 MB │ │
│ │ users_p06│ 431,359 │ 28 MB │ 32 MB │ 60 MB │ │
│ │ users_p07│ 429,476 │ 28 MB │ 32 MB │ 60 MB │ │
│ │ users_p08│ 428,035 │ 28 MB │ 32 MB │ 60 MB │ │
│ │ users_p09│ 428,721 │ 28 MB │ 32 MB │ 60 MB │ │
│ │ users_p10│ 428,656 │ 28 MB │ 32 MB │ 60 MB │ │
│ │ users_p11│ 428,887 │ 28 MB │ 32 MB │ 60 MB │ │
│ │ users_p12│ 429,404 │ 28 MB │ 31 MB │ 59 MB │ │
│ │ users_p13│ 429,722 │ 28 MB │ 32 MB │ 60 MB │ │
│ │ users_p14│ 429,441 │ 28 MB │ 32 MB │ 60 MB │ │
│ │ users_p15│ 431,244 │ 28 MB │ 32 MB │ 60 MB │ │
│ ├──────────┼──────────┼────────────┼─────────────┼────────────┤ │
│ │ TOTAL │6,870,006 │ 448 MB │ 512 MB │ 960 MB │ │
│ └──────────┴──────────┴────────────┴─────────────┴────────────┘ │
│ │
│ DISTRIBUTION: Each partition = 6.23% - 6.28% (nearly perfect!) │
│ │
│ PARTITION PRUNING TEST: │
│ SELECT * FROM users WHERE id = 'uuid-here'; │
│ → Only users_p00 scanned (not all 16!) │
│ → Execution Time: 0.082 ms │
│ │
└─────────────────────────────────────────────────────────────────────────┘
-- Run this to see how evenly data is distributed
SELECT
tableoid::regclass AS partition,
COUNT(*) AS rows,
ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER(), 2) AS pct
FROM users
GROUP BY tableoid
ORDER BY partition;
-- Expected: Each partition should have ~6.25% (100%/16)-- First, get a real user ID
SELECT id FROM users LIMIT 1;
-- Then explain the query
EXPLAIN (ANALYZE, COSTS OFF)
SELECT * FROM users WHERE id = '<paste-id-here>';
-- Look for: "Partitions removed: 15"
-- This means only 1 partition was scanned!-- Query WITH partition key (fast - single partition)
EXPLAIN ANALYZE
SELECT * FROM users WHERE id = '<some-id>';
-- Query WITHOUT partition key (slower - all partitions)
EXPLAIN ANALYZE
SELECT * FROM users WHERE first_name = 'John';
-- Compare the execution times and notice:
-- - First query scans 1 partition
-- - Second query scans all 16 (but can be parallel!)-- Enable parallel query
SET max_parallel_workers_per_gather = 4;
-- Run aggregate query
EXPLAIN ANALYZE
SELECT COUNT(*) FROM users WHERE first_name LIKE 'A%';
-- Look for "Workers Planned: 4" in the output
-- This means 4 CPU cores working simultaneously!You've learned:
- What partitioning is: Splitting a table into smaller physical pieces
- Why we partition: Better performance, parallelism, maintenance
- Types of partitioning: Range, List, Hash
- Hash partitioning: Uses hash(id) % N to route rows
- Migration process: Create → Copy → Swap → Cleanup
- Query routing: PostgreSQL automatically finds the right partition
- Benefits: Smaller indexes, parallel scans, faster maintenance
- Best practices: Choose right key, power-of-2 counts, monitor distribution
Next steps:
- Run the migration
- Verify partition distribution
- Run the exercises
- Monitor performance improvements!