Production query plans without production data

2026-03-09 · Source: Simon Willison's Weblog · Field: Technology & Digital — Software Development & Engineering, Database Systems · Depth: Intermediate, quick

Summary

PostgreSQL 18, released in September 2025, introduces new functions, `pg_restore_relation_stats()` and `pg_restore_attribute_stats()`, that enable developers to replicate production query plans in development environments without needing to copy large datasets. These functions allow users to manually inject or restore database statistics, which are crucial for the query planner's decision-making. For instance, one can simulate a column's data distribution, like a `status` column being 95% `delivered`, to observe how PostgreSQL might choose between an index scan or a full table scan. Statistics dumps are notably small, typically under 1MB for databases with hundreds of tables, contrasting sharply with potentially hundreds of gigabytes of production data. SQLite offers a similar capability through its writable `sqlite_stat1` and `sqlite_stat4` tables, allowing manual control over query planner statistics.

Key takeaway

For database administrators and developers troubleshooting query performance issues, these new PostgreSQL 18 functions and existing SQLite capabilities are critical. You can now accurately predict production query plans in a development environment by importing only tiny statistical metadata, saving significant time and resources compared to copying entire production databases. This allows for more precise optimization and debugging of complex queries.

Key insights

Replicating production query plans in development is possible by transferring small statistical metadata, not full datasets.

Principles

• Query planners rely on data statistics.
• Statistics are compact representations of data.

Method

Use `pg_restore_attribute_stats()` to manually define column statistics for PostgreSQL query plan simulation.

In practice

• Simulate specific data distributions for query plan testing.
• Copy `sqlite_stat1` table content for SQLite query plan reproduction.

Topics

• PostgreSQL 18
• Query Planner
• Database Statistics
• Development Environments
• SQLite

Best for: Software Engineer, Data Engineer

Related on AIssential

• 10 GitHub Repositories for Modern Database Systems and Tools — Modern database ecosystems extend beyond storage, offering specialized open-source tools for diverse application needs from analytics to AI agent memory.
• Predictive Query Language (PQL) Explained — PQL offers a SQL-like syntax to define predictive targets and entities for business forecasting.
• Creating Synthetic Data In Snowflake Part II — Snowflake functions like `Seq`, `Uniform`, `Randstr`, `Normal`, and `Get` enable robust synthetic data generation.
• Analytics Patterns Every Data Scientist Should Master — Mastering seven core SQL patterns enables efficient, reusable solutions for common data analysis problems.
• PostgreSQL 19 Beta Introduces SQL Graph Queries and Concurrent Table Repacking — PostgreSQL 19 Beta integrates graph queries and concurrent table repacking, significantly enhancing performance and operational efficiency.
• Postgres vs MySQL vs SQLite: Comparing SQL Performance Across Engines — Database choice significantly impacts application performance, with each SQL engine excelling in different workloads.

Open in AIssential →

Editorial summary, takeaway, and curation by AIssential. Original article published by Simon Willison's Weblog.