Home/Examples/Postgres Table Partitioning: Monthly Range Partitions for 500M Row Time Series
● Calculations run locally in your browser. Some site features, such as usage analytics or shared results, may use network requests.
Example — PostgreSQL Partition Strategy Calculator
Postgres Table Partitioning: Monthly Range Partitions for 500M Row Time Series
Example of setting up monthly range partitioning for a 500M row Postgres time series table. Free, browser-only database tool.
Worked example
Input
Table: events (500M rows, growing at 5M/month)
Query pattern: always filtered by event_date (last 30-90 days)
Retention: 24 months then archive to S3
Output
Partitioning strategy: RANGE on event_date, monthly partitions
CREATE TABLE events (
id BIGSERIAL,
event_date DATE NOT NULL,
user_id BIGINT,
type TEXT,
payload JSONB
) PARTITION BY RANGE (event_date);
-- Monthly partition
CREATE TABLE events_2024_01 PARTITION OF events
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
Benefits:
Query on last 30 days: scans 1-2 partitions (vs full 500M)
Partition pruning: WHERE event_date >= '2024-01-01'
DETACH + DROP partition: archive month in milliseconds
Vacuum per partition: smaller, faster
Index per partition:
CREATE INDEX ON events_2024_01 (user_id, event_date);
Monthly range partitioning converts a 500M-row query into a 20M-row query (one month's data) through partition pruning — the planner only scans partitions whose range overlaps the WHERE clause. DETACH PARTITION moves a partition out of the table without locking or copying data — archive old months instantly. Each partition has its own indexes, VACUUM, and statistics, making maintenance O(partition_size) not O(table_size).
What to do next
Automate partition creation with pg_partman. Create next month's partition 1 week in advance. Set up a monthly cron job to DETACH and archive partitions older than 24 months.
Use the PostgreSQL Partition Strategy Calculator to run this on your own input.
Does Postgres partition pruning work with prepared statements?
With generic query plans (default after 5 executions with the same plan), partition pruning may not work at execute time — the plan is generated once without knowing the parameter values. Use EXECUTE with literal values, or set plan_cache_mode = force_custom_plan to force re-planning per execution (at the cost of planning overhead).
Can I add partitioning to an existing 500M row table without downtime?
Yes, using pg_partman with a background migration. The process: create a new partitioned table, attach the old table as a default partition, migrate rows in batches during off-peak hours, then swap. This takes days to weeks for 500M rows but requires no downtime. Alternatively, rename the old table and create the partitioned table with the original name.