Table Partitioning for Self-Hosted Supabase: A Complete pg

As your self-hosted Supabase database grows into millions of rows, you'll notice query performance degrading, VACUUM operations taking forever, and index maintenance becoming painfully slow. Table partitioning solves these problems by splitting large tables into smaller, more manageable chunks.

If you're running a self-hosted Supabase instance, you have full control over PostgreSQL extensions like pg_partman that automate partition management. This guide walks you through implementing partitioning from scratch.

Why Partition Tables in Self-Hosted Supabase?

Table partitioning divides a large table into smaller pieces called partitions. Each partition contains a subset of data based on criteria you define—typically date ranges or specific values. PostgreSQL's query planner then targets only relevant partitions instead of scanning the entire table.

Benefits You'll Actually Notice

Faster queries: 5-20x improvement by scanning only relevant partitions
Quicker maintenance: VACUUM and REINDEX run on smaller chunks
Easier data lifecycle: Drop old partitions instead of deleting rows
Better scalability: Add partitions as data grows without restructuring

When Partitioning Makes Sense

There's no magic threshold, but consider partitioning when:

Tables exceed 10-50GB or contain millions of rows
You're storing time-series data (logs, events, analytics)
Queries frequently filter on specific columns (dates, regions, tenant IDs)
VACUUM operations are taking too long

Don't partition just because you can. Partitioning adds complexity, and complexity should be avoided until it's actually needed. If your queries are fast enough and maintenance isn't a problem, hold off.

Understanding Partition Types

PostgreSQL supports three partitioning methods:

Range Partitioning

Best for time-series data. Each partition holds rows within a specific range.

CREATE TABLE events (
    id UUID DEFAULT gen_random_uuid(),
    created_at TIMESTAMPTZ NOT NULL,
    event_type TEXT NOT NULL,
    payload JSONB,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_01 PARTITION OF events
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
    
CREATE TABLE events_2026_02 PARTITION OF events
    FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');

List Partitioning

Best for categorical data like regions or tenant IDs.

CREATE TABLE orders (
    id UUID DEFAULT gen_random_uuid(),
    region TEXT NOT NULL,
    total_amount NUMERIC,
    PRIMARY KEY (id, region)
) PARTITION BY LIST (region);

CREATE TABLE orders_us PARTITION OF orders FOR VALUES IN ('us-east', 'us-west');
CREATE TABLE orders_eu PARTITION OF orders FOR VALUES IN ('eu-west', 'eu-central');

Hash Partitioning

Distributes data evenly across partitions. Useful for load balancing but doesn't support value-based queries.

CREATE TABLE sessions (
    id UUID DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL,
    data JSONB,
    PRIMARY KEY (id, user_id)
) PARTITION BY HASH (user_id);

CREATE TABLE sessions_0 PARTITION OF sessions FOR VALUES WITH (modulus 4, remainder 0);
CREATE TABLE sessions_1 PARTITION OF sessions FOR VALUES WITH (modulus 4, remainder 1);

Setting Up pg_partman in Self-Hosted Supabase

Manual partition management is tedious. pg_partman automates partition creation, maintenance, and cleanup. Here's how to set it up in your self-hosted instance.

Step 1: Enable the Extension

Connect to your Supabase database and create the pg_partman schema:

-- Create a dedicated schema for pg_partman
CREATE SCHEMA IF NOT EXISTS partman;

-- Enable the extension
CREATE EXTENSION IF NOT EXISTS pg_partman WITH SCHEMA partman;

If you're managing your database through environment variables and configuration, you'll also need pg_cron for scheduling:

CREATE EXTENSION IF NOT EXISTS pg_cron;

Step 2: Configure the Background Worker

For automatic partition maintenance, add pg_partman's background worker to your PostgreSQL configuration. In your postgresql.conf or Docker environment:

shared_preload_libraries = 'pg_partman_bgw'
pg_partman_bgw.interval = 3600
pg_partman_bgw.role = 'postgres'
pg_partman_bgw.dbname = 'postgres'

Restart PostgreSQL for changes to take effect.

Step 3: Create a Partitioned Table

Let's create an events table with monthly partitions:

-- Create the parent table
CREATE TABLE public.events (
    id UUID DEFAULT gen_random_uuid(),
    account_id UUID NOT NULL,
    event_type TEXT NOT NULL,
    payload JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

-- Create an index for common queries
CREATE INDEX idx_events_account ON public.events (account_id);
CREATE INDEX idx_events_type ON public.events (event_type);

Note the composite primary key. The partition key column must be part of the primary key—this is a PostgreSQL requirement.

Step 4: Register with pg_partman

Now register the table with pg_partman to automate partition management:

SELECT partman.create_parent(
    p_parent_table => 'public.events',
    p_control => 'created_at',
    p_type => 'range',
    p_interval => '1 month',
    p_premake => 4  -- Create 4 future partitions
);

This creates partitions for the current month, 4 months ahead, and any historical data. pg_partman handles the rest automatically.

Step 5: Schedule Maintenance

Set up a cron job to run partition maintenance regularly:

SELECT cron.schedule(
    'partition-maintenance',
    '0 * * * *',  -- Every hour
    $$CALL partman.run_maintenance_proc()$$
);

This ensures new partitions are created before they're needed and old ones are managed according to your retention policy.

Configuring Retention Policies

One major benefit of partitioning is simplified data lifecycle management. Instead of deleting millions of rows, you drop entire partitions.

Set Up Automatic Retention

UPDATE partman.part_config 
SET 
    retention = '12 months',
    retention_keep_table = false,
    retention_keep_index = false
WHERE parent_table = 'public.events';

This configuration:

Keeps 12 months of data
Automatically drops older partitions
Removes associated indexes when dropping partitions

Manual Partition Cleanup

For one-off cleanup or testing:

-- Drop a specific partition
DROP TABLE public.events_p2025_01;

-- Or use pg_partman's cleanup
SELECT partman.drop_partition_id(
    p_parent_table => 'public.events',
    p_keep_table => false,
    p_retention => '6 months'
);

Migrating Existing Tables to Partitioned Tables

Already have a large table? Here's how to convert it:

Step 1: Create the New Partitioned Table

-- Create partitioned version with same structure
CREATE TABLE public.events_new (
    LIKE public.events INCLUDING ALL
) PARTITION BY RANGE (created_at);

-- Add the partition key to primary key
ALTER TABLE public.events_new 
DROP CONSTRAINT events_new_pkey,
ADD PRIMARY KEY (id, created_at);

Step 2: Create Initial Partitions

-- Create partitions for existing data range
DO $$
DECLARE
    start_date DATE := '2025-01-01';
    end_date DATE := '2026-04-01';
    curr_date DATE := start_date;
BEGIN
    WHILE curr_date < end_date LOOP
        EXECUTE format(
            'CREATE TABLE public.events_p%s PARTITION OF public.events_new 
             FOR VALUES FROM (%L) TO (%L)',
            to_char(curr_date, 'YYYY_MM'),
            curr_date,
            curr_date + INTERVAL '1 month'
        );
        curr_date := curr_date + INTERVAL '1 month';
    END LOOP;
END $$;

Step 3: Migrate Data

-- Insert data from old table (partition routing happens automatically)
INSERT INTO public.events_new 
SELECT * FROM public.events;

-- Verify row counts match
SELECT 
    (SELECT COUNT(*) FROM public.events) AS old_count,
    (SELECT COUNT(*) FROM public.events_new) AS new_count;

Step 4: Swap Tables

BEGIN;
    ALTER TABLE public.events RENAME TO events_old;
    ALTER TABLE public.events_new RENAME TO events;
COMMIT;

-- After verification, drop the old table
DROP TABLE public.events_old;

Step 5: Register with pg_partman

SELECT partman.create_parent(
    p_parent_table => 'public.events',
    p_control => 'created_at',
    p_type => 'range',
    p_interval => '1 month',
    p_premake => 4
);

Performance Optimization Tips

Partitioning alone isn't enough. Follow these practices to maximize performance:

Ensure Partition Pruning Works

PostgreSQL skips irrelevant partitions only when queries include the partition key. Always filter by your partition column:

-- Good: Uses partition pruning
SELECT * FROM events 
WHERE created_at >= '2026-03-01' 
  AND created_at < '2026-04-01'
  AND event_type = 'user_signup';

-- Bad: Scans all partitions
SELECT * FROM events 
WHERE event_type = 'user_signup';

Check if pruning is working with EXPLAIN:

EXPLAIN (COSTS OFF) 
SELECT * FROM events WHERE created_at = '2026-03-15';

You should see only relevant partitions in the plan.

Index Each Partition

Create indexes on the parent table—they'll apply to all partitions:

CREATE INDEX idx_events_lookup 
ON public.events (account_id, event_type, created_at DESC);

Avoid Default Partition Bloat

If you use a default partition, monitor its size. An overgrown default partition slows down everything:

-- Check default partition size
SELECT pg_size_pretty(pg_total_relation_size('events_default'));

If it's growing unexpectedly, you likely have data outside your partition ranges.

Monitor Partition Health

Regular monitoring helps catch issues early. Set up queries in your monitoring stack:

-- Partition sizes
SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS size
FROM pg_tables 
WHERE tablename LIKE 'events%'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC;

Integration with Row Level Security

Partitioned tables work with Row Level Security (RLS), but there's a performance consideration. RLS policies are evaluated for each row, so ensure your partition key queries run first:

-- Enable RLS
ALTER TABLE public.events ENABLE ROW LEVEL SECURITY;

-- Create policy that benefits from partition pruning
CREATE POLICY events_account_access ON public.events
    FOR ALL
    USING (
        account_id = auth.uid()
        -- Partition key in WHERE clause of your queries helps here
    );

Common Issues and Solutions

"Cannot create partition for table"

Usually means the partition key isn't in the primary key:

ALTER TABLE events 
DROP CONSTRAINT events_pkey,
ADD PRIMARY KEY (id, created_at);

Data Going to Default Partition

Check your partition ranges match incoming data:

SELECT * FROM partman.part_config WHERE parent_table = 'public.events';

pg_partman Not Creating Partitions

Verify the background worker is running:

SELECT * FROM pg_stat_activity WHERE backend_type = 'pg_partman maintenance';

If not running, check your shared_preload_libraries configuration.

Simplifying Management with Supascale

Setting up partitioning is one thing—keeping it running smoothly is another. For self-hosted Supabase, you're responsible for monitoring partition growth, ensuring maintenance jobs run, and backing up partitioned tables correctly.

Supascale simplifies self-hosted Supabase management with automated S3 backups that handle partitioned tables correctly, monitoring to track database growth, and one-click restore capabilities. At a one-time cost of $39.99, it removes much of the operational burden while keeping you in full control.

Table Partitioning for Self-Hosted Supabase: A Complete pg_partman Guide

Learn how to implement table partitioning in self-hosted Supabase using pg_partman for faster queries and easier maintenance.