1---
2title: Partition Large Tables for Better Performance
3impact: MEDIUM-HIGH
4impactDescription: 5-20x faster queries and maintenance on large tables
5tags: partitioning, large-tables, time-series, performance
6---
7 
8## Partition Large Tables for Better Performance
9 
10Partitioning splits a large table into smaller pieces, improving query performance and maintenance operations.
11 
12**Incorrect (single large table):**
13 
14```sql
15create table events (
16  id bigint generated always as identity,
17  created_at timestamptz,
18  data jsonb
19);
20 
21-- 500M rows, queries scan everything
22select * from events where created_at > '2024-01-01';  -- Slow
23vacuum events;  -- Takes hours, locks table
24```
25 
26**Correct (partitioned by time range):**
27 
28```sql
29create table events (
30  id bigint generated always as identity,
31  created_at timestamptz not null,
32  data jsonb
33) partition by range (created_at);
34 
35-- Create partitions for each month
36create table events_2024_01 partition of events
37  for values from ('2024-01-01') to ('2024-02-01');
38 
39create table events_2024_02 partition of events
40  for values from ('2024-02-01') to ('2024-03-01');
41 
42-- Queries only scan relevant partitions
43select * from events where created_at > '2024-01-15';  -- Only scans events_2024_01+
44 
45-- Drop old data instantly
46drop table events_2023_01;  -- Instant vs DELETE taking hours
47```
48 
49When to partition:
50 
51- Tables > 100M rows
52- Time-series data with date-based queries
53- Need to efficiently drop old data
54 
55Reference: [Table Partitioning](https://www.postgresql.org/docs/current/ddl-partitioning.html)
56