Summary

Radim Marek discusses the introduction of new functions in PostgreSQL 18 that allow developers to replicate production query plans in development environments without needing production data. This innovation enhances the ability to simulate production workloads accurately.

Key Points

• New functions introduced in PostgreSQL 18: `pg_restore_relation_stats()` and `pg_restore_attribute_stats()`.
• Released in September 2025, these functions enable the copying of internal statistics from production to development.
• The PostgreSQL query planner utilizes these statistics to optimize query execution, which often differ between environments.
• Example provided: Simulating statistics for a status column in a test_orders table to influence query planning decisions.
• Statistics dumps are notably small, often under 1MB, compared to the hundreds of GB of production data.
• SQLite also offers a similar feature through its `sqlite_stat1` and `sqlite_stat4` tables, allowing for alternative statistics injection.
• This capability aids in reproducing query problems for testing without loading large database files.

Analysis

The introduction of these functions in PostgreSQL represents a significant advancement in database management, allowing developers to create more efficient and accurate testing environments. By enabling the simulation of production query plans, organizations can enhance their development processes and reduce the risks associated with deploying untested changes.

Conclusion

IT professionals should leverage these new PostgreSQL features to improve their development workflows. Utilizing production statistics in development can lead to better performance tuning and more reliable applications.