Overview
PostgreSQL 18 introduced new functions that let developers copy database statistics from production to development environments. This enables replicating production query plans without copying the actual data, solving a longstanding problem where query optimization behaves differently across environments.
The Breakdown
- PostgreSQL’s pg_restore_relation_stats() and pg_restore_attribute_stats() functions let you export and import database statistics that the query planner uses to optimize queries
- The query planner uses these statistics to decide execution strategies - with production stats, it can choose different approaches like index scans vs full table scans based on real data distributions
- Statistics files are extremely compact - hundreds of GB of production data compress to under 1MB of statistics, making them practical to share across environments
- SQLite already supports similar functionality through writable sqlite_stat1 and sqlite_stat4 tables that allow manual control of query planning decisions
- The .fullschema command in SQLite CLI exports both schema and statistics together, enabling reproduction of query performance issues without loading massive database files