Database table partitioning implementation

Database Table Partitioning Implementation

Partitioning is splitting one logical table into separate physical pieces (partitions) by key value. Query SELECT * FROM events WHERE created_at > '2025-01-01' on unpartitioned 500 million row table scans all. On monthly partitioned — only one with ~40 million rows. Plus each partition can independently archive, move to cheap storage, or delete.

PostgreSQL: Declarative Partitioning

Since PostgreSQL 10 partitioning is built-in and doesn't require triggers.

Range Partitioning by Date

-- Create parent table
CREATE TABLE events (
    id          BIGSERIAL,
    user_id     INTEGER NOT NULL,
    event_type  VARCHAR(64) NOT NULL,
    payload     JSONB,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
) PARTITION BY RANGE (created_at);

-- Create partitions — one month each
CREATE TABLE events_2025_01 PARTITION OF events
    FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

CREATE TABLE events_2025_02 PARTITION OF events
    FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');

-- Each partition has its own indexes
CREATE INDEX ON events_2025_01 (user_id, created_at DESC);
CREATE INDEX ON events_2025_02 (user_id, created_at DESC);

-- Index on parent table automatically creates on all partitions (PG 11+)
CREATE INDEX ON events (user_id, created_at DESC);

Hash Partitioning (Even Distribution)

CREATE TABLE user_sessions (
    id          UUID DEFAULT gen_random_uuid(),
    user_id     INTEGER NOT NULL,
    data        JSONB,
    created_at  TIMESTAMPTZ DEFAULT now()
) PARTITION BY HASH (user_id);

-- 8 partitions for even distribution
DO $$
BEGIN
    FOR i IN 0..7 LOOP
        EXECUTE format(
            'CREATE TABLE user_sessions_%s PARTITION OF user_sessions
             FOR VALUES WITH (MODULUS 8, REMAINDER %s)',
            i, i
        );
    END LOOP;
END $$;

List Partitioning by Value

CREATE TABLE orders (
    id          BIGSERIAL,
    country     CHAR(2) NOT NULL,
    status      VARCHAR(32),
    total       NUMERIC(12,2),
    created_at  TIMESTAMPTZ DEFAULT now()
) PARTITION BY LIST (country);

CREATE TABLE orders_us PARTITION OF orders FOR VALUES IN ('US');
CREATE TABLE orders_ca PARTITION OF orders FOR VALUES IN ('CA');
CREATE TABLE orders_mx PARTITION OF orders FOR VALUES IN ('MX');
CREATE TABLE orders_other PARTITION OF orders DEFAULT; -- for all others

Automatic Partition Creation

Creating partitions manually each month is unreliable. Automate via pg_partman:

# Install extension
apt-get install postgresql-14-partman

# In PostgreSQL
CREATE EXTENSION pg_partman SCHEMA partman;

-- Configure automatic management
SELECT partman.create_parent(
    p_parent_table   => 'public.events',
    p_control        => 'created_at',
    p_type           => 'native',
    p_interval       => 'monthly',
    p_premake        => 3  -- create 3 partitions ahead
);

-- Update configuration
UPDATE partman.part_config
SET retention            = '12 months',  -- delete partitions older than 12 months
    retention_keep_table = false,         -- delete physically, not just detach
    infinite_time_partitions = true
WHERE parent_table = 'public.events';

Run maintenance (add new + delete old partitions):

# cron: every day at 2:00 AM
0 2 * * * psql -d mydb -c "SELECT partman.run_maintenance_proc();"

MySQL: Partitioning

MySQL supports partitioning but with limitations: all unique keys must include partitioning key.

CREATE TABLE orders (
    id         BIGINT NOT NULL AUTO_INCREMENT,
    user_id    INT NOT NULL,
    status     VARCHAR(32),
    total      DECIMAL(12,2),
    created_at DATETIME NOT NULL,
    PRIMARY KEY (id, created_at),  -- created_at required in PK for partition by range
    KEY idx_user_id (user_id)
)
PARTITION BY RANGE (YEAR(created_at) * 100 + MONTH(created_at)) (
    PARTITION p202501 VALUES LESS THAN (202502),
    PARTITION p202502 VALUES LESS THAN (202503),
    PARTITION p202503 VALUES LESS THAN (202504),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

Add partition before previous expires:

-- Before adding new partition — remove MAXVALUE
ALTER TABLE orders REORGANIZE PARTITION p_future INTO (
    PARTITION p202504 VALUES LESS THAN (202505),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

Partition Pruning: Verify It Works

-- PostgreSQL: verify partition pruning is used
EXPLAIN SELECT * FROM events
WHERE created_at BETWEEN '2025-01-01' AND '2025-01-31';

-- Should show: "Seq Scan on events_2025_01"
-- (not "Seq Scan on events" which means full table scan)