Key takeaways The default values in the server configuration file have small memory settings. You should carefully modify primary memory configurations, shared_buffers and work_mem, in… Read More »Server Tuning Guideline – PostgreSQL 12 High Performance Guide (Part 10/12)
Key Takeaways First question the semantic correctness of a statement before attacking the performance problem We should avoid SELECT *, ORDER BY and DISTINCT unless… Read More »Query Optimizations Tips – PostgreSQL 12 High Performance Guide (Part 9/12)
Key takeaways: Queries are executed as a series of nodes that each do a small task, such as fetching data aggregation or sorting. Sequential scans… Read More »Spotting Query Problems – PostgreSQL 12 High Performance Guide (Part 8/12)
Key takeaways: The database statistics are exposed using views. The fastest way to spot performance issues is by using the pg_stat_statements view Particularly valuable statistics… Read More »Making Use of Statistics – PostgreSQL 12 High Performance Guide (Part 7/12)
Key takeaways Adding an index increases overhead every time you add or change rows in a table. Each index needs to satisfy enough queries to… Read More »Index Optimization Techniques – PostgreSQL 12 High Performance Guide (Part 6/12)
Key takeaways: Indexes should reduce the amount of read data blocks necessary to answer a query Execution plan of a query depends on the data… Read More »Index efficiency and maintenance – PostgreSQL 12 High Performance Guide (Part 5/12)
Key takeaways: Any time you UPDATE or DELETE, you will leave a dead row behind (and potentially a dead index entry) that needs to be… Read More »Configuring Vacuum for Performance – PostgreSQL 12 High Performance Guide (Part 4/12)
Key takeaways Opening a new PostgreSQL connection is a fairly expensive operation in terms of CPU used. Connection poolers keep some of those connections open… Read More »Connection pooling – PostgreSQL High Performance Guide (Part 3/12)
Key takeaways PostgreSQL allocation for the default shared_buffers is extremely low and we need to increase it to allow proper shared memory size for the… Read More »Optimizing PostgreSQL shared_buffers – PostgreSQL High Performance Guide (Part 2/12)
Key takeaways PostgreSQL is often shipped with a configuration that isn’t suitable for production. Due to this, you should at least configure the PostgreSQL buffer… Read More »PostgreSQL Architecture – PostgreSQL High Performance Guide (Part 1/12)