Learn SQL with PostgreSQL - Table Partitioning for Large-Scale Data

#Introduction

Welcome to episode 18 of the Learn SQL with PostgreSQL series! Imagine an events table recording all application logs — every day it grows by hundreds of thousands of rows, and within a year it reaches hundreds of millions. Indexes are already created, but everything starts to feel slow: backups take longer, maintenance gets heavier, and queries carry an ever-growing proportion of old data. This is when table partitioning is needed.

Partitioning splits one large logical table into several smaller physical partitions, united by a parent table. Logically, the application still SELECTs from a single table — but physically, PostgreSQL can discard irrelevant partitions and handle old data more efficiently. This is one of the techniques you must master to build a database that survives at scale.

In this episode, we'll cover when partitioning is truly needed, the three declarative partitioning strategies (Range, List, Hash), and partition pruning that makes queries touch only the relevant partitions.

#When Is Table Partitioning Needed?

Partitioning isn't the solution for every table — small tables actually become more cumbersome when partitioned. When is partitioning beneficial?

• Very large tables (tens to hundreds of millions of rows) that start slowing down maintenance such as VACUUM and indexes.
• Data with a clear lifecycle — e.g. logs or events where "new" data is actively used and "old" data is only for archive.
• Operational needs such as deleting old data as fast as possible (DROP PARTITION is far faster than DELETE ... WHERE over billions of rows).
• Queries that almost always filter on the partition column (e.g. date ranges).

1. Tabel berisi ratusan juta baris
2. Backup dan VACUUM semakin lambat
3. Query time-series sering menyaring rentang tanggal
4. Data lama perlu diarsip / dihapus rutin

#Declarative Table Partitioning

PostgreSQL supports declarative partitioning: the parent table is declared with PARTITION BY, then the partitions are created explicitly. There are no triggers to maintain — routing is handled automatically by the engine.

#Partition by Range: Value Ranges

The most common strategy for time-series: partition by date ranges. Each partition handles one period (day, week, or month).

CREATE TABLE events (
    id BIGINT GENERATED ALWAYS AS IDENTITY,
    event_type TEXT NOT NULL,
    payload JSONB NOT NULL,
    created_at TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (created_at);
 
CREATE TABLE events_2026_07 PARTITION OF events
FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');
 
CREATE TABLE events_2026_08 PARTITION OF events
FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');

Now every INSERT whose created_at falls in July is automatically routed to events_2026_07, and August's to events_2026_08. The application doesn't need to know which partition is used.

#Partition by List: Categories and Regions

PARTITION BY LIST partitions by enumerated values — categories, regions, or statuses. It fits data that's naturally grouped:

CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    region TEXT NOT NULL,
    total NUMERIC(12,2) NOT NULL
) PARTITION BY LIST (region);
 
CREATE TABLE orders_jakarta PARTITION OF orders
FOR VALUES IN ('jakarta');
 
CREATE TABLE orders_bandung PARTITION OF orders
FOR VALUES IN ('bandung');
 
CREATE TABLE orders_lainnya PARTITION OF orders
DEFAULT;

Notice the DEFAULT partition that holds every value that doesn't match — a safety net so inserts with a new region don't fail. But use it carefully: without a default, inserting a value outside the partitions errors — which can actually be a signal to add a new partition.

#Partition by Hash: Even Distribution

PARTITION BY HASH distributes rows evenly across a number of partitions based on the hash of a key column. There's no business meaning to each partition — the sole purpose is load distribution:

CREATE TABLE user_sessions (
    id UUID NOT NULL,
    user_id UUID NOT NULL,
    session_data JSONB NOT NULL
) PARTITION BY HASH (user_id);
 
CREATE TABLE user_sessions_0 PARTITION OF user_sessions
FOR VALUES WITH (MODULUS 4, REMAINDER 0);
 
CREATE TABLE user_sessions_1 PARTITION OF user_sessions
FOR VALUES WITH (MODULUS 4, REMAINDER 1);
 
CREATE TABLE user_sessions_2 PARTITION OF user_sessions
FOR VALUES WITH (MODULUS 4, REMAINDER 2);
 
CREATE TABLE user_sessions_3 PARTITION OF user_sessions
FOR VALUES WITH (MODULUS 4, REMAINDER 3);

Four partitions split the data by hashing user_id with modulus 4. Each partition holds roughly a quarter of the data — suitable for even read/write load when there's no column with a natural grouping.

#Partition Pruning

Partition pruning is the magic that makes partitioning feel like a cheat: PostgreSQL examines the WHERE condition, then cuts execution down to only the relevant partitions. Other partitions aren't touched at all.

SELECT COUNT(*)
FROM events
WHERE created_at >= '2026-08-01'
  AND created_at < '2026-08-15';

Because the partition column created_at is filtered with a range, PostgreSQL knows the rows can only be in events_2026_08. Other partitions aren't scanned — the query runs as if touching a small table.

Compare with a query without a filter on the partition column:

SELECT COUNT(*) FROM events;

This query must touch all partitions — of course. This is why partitioning must align with the query pattern: the partition column must always be included in the WHERE, or pruning doesn't happen.

#Verifying Pruning with EXPLAIN

EXPLAIN
SELECT COUNT(*)
FROM events
WHERE created_at >= '2026-08-01'
  AND created_at < '2026-08-15';

The output will show a section like:

Append
  Subplans Removed: 1
  ->  Seq Scan on events_2026_08 events_1

The Subplans Removed line shows how many partitions were skipped. If it's 0, there's no pruning — go back and check the form of the WHERE expression.

#Partition Management: Creating and Dropping

The operational advantage of partitioning shows when adding and dropping data periods:

CREATE TABLE events_2026_09 PARTITION OF events
FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');

DROP TABLE events_2026_05;

#Closing

Key takeaways:

• Partitioning splits one large table into small physical partitions managed as a single logical table.
• Range for time, List for categories, Hash for even load.
• Partition pruning makes queries touch only relevant partitions — as long as WHERE uses the partition column.
• DROP PARTITION is far faster than DELETE for old data.
• Align the partition strategy with the query pattern so pruning actually happens.

In the next episode, episode 19, we prepare for production readiness: Backup, Replication & PgBouncer — from logical backup with pg_dump and pg_restore, physical backup with WAL for Point-In-Time Recovery, physical streaming and logical replication, to connection pooling with PgBouncer.