Capacity planning estimates how much data and workload a PostgreSQL system will need to absorb before growth turns a workable design into an unmanageably large table or insufficient infrastructure. Start with ingestion rate in rows and bytes, expected business growth, and retention obligations. Treat the result as a revisable estimate rather than a one-time forecast. section 4.2 section 7.5
Build and maintain the estimate with four inputs:
- Measure current ingestion by row count and data size, then extrapolate over the period data must remain available. section 4.2
- Include projected growth outside the database, such as expansion from 25 to 200 retail locations. section 4.2
- Track performance trends, usage patterns, and workload metrics over time; current-state monitoring alone cannot show the longer-term shape of growth. section 7.5
- Recheck projections regularly and revise partitioning, architecture, or infrastructure plans when observed growth diverges from estimates. section 4.2 section 7.5
Sizing time partitions from sensor traffic
A network of 1,000 devices producing 1,440 measurements per device per day provides a basis for annual ingestion estimates. If the resulting table size makes monthly chunks meaningful, partition by RANGE on a timestamp column and create monthly partitions. This choice also makes retention operations cheap: an expired month can be removed by dropping its partition instead of deleting individual rows. section 4.2
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM erp.payments_p
WHERE tstamp='2022-03-09 22:58:20.431946+00';
QUERY PLAN
----------------------------------------------------------------------------
Seq Scan on payments_p_2022_3 payments_p (cost=0.00..111450.00 rows=1
width=32) (actual time=1780.649..2535.625 rows=1 loops=1)
Filter: (tstamp = '2022-03-09 22:58:20.431946+00'::timestamp with time
zone)
Rows Removed by Filter: 5349599
Buffers: shared read=44580
Planning:
Buffers: shared hit=20 dirtied=2
Planning Time: 0.406 ms
Execution Time: 2535.658 ms
(8 rows)
The plan shows the practical payoff of sizing partitions around expected data volume: a timestamp lookup scans only the March 2022 partition through partition pruning, reading 44,580 buffers and finishing in about 2.5 seconds rather than scanning the entire 150-million-row table. section 4.2
Long-term statistics make capacity decisions more defensible. Preserve snapshots so growth can be compared with changing performance, rather than inferred from a dashboard’s current view. Storage and cost for retained monitoring data are themselves planning constraints. Lightweight snapshots of PostgreSQL cumulative and dynamic statistics can support later analysis and visualizations without a server restart. section 7.5
