PostgreSQL Tutorial: Performance optimization
This chapter explains how to optimize PostgreSQL performance and provides examples.
PostgreSQL Tutorial: Tuning Internal Statistics (pg_stats)
Summary: In this tutorial, you’ll learn how PostgreSQL internal statistics (pg_stats) work and some optimization guides.
PostgreSQL Tutorial: Tuning random_page_cost setting
Summary: in this tutorial, you will learn how to tune random_page_cost setting in PostgreSQL.
PostgreSQL Tutorial: Maintaining and Tuning Large Tables
As applications grow, PostgreSQL tables inevitably scale from thousands of records to hundreds of millions or even billions. Performance degradation for large tables is rarely sudden; instead, it happens through a process of silent degradation over time.
PostgreSQL Tutorial: Tuning Database Connections Under Linux
Scaling Postgres’s high-concurrency handling capability can never rely on simply throwing more hardware at the problem. Each database connection incurs a hidden system tax, including Linux-level memory and CPU overhead, as well as Postgres-level MVCC (Multi-Version Concurrency Control) and locking constraints. To handle high-performance workloads, we need to stop guessing and start precisely tuning our systems by deeply understanding these underlying limitations.
PostgreSQL Tutorial: Enable Atomic Writes at Storage Layer
Summary: In this tutorial, you will learn how to enable atomic writes at the storage layer and disable full_page_writes to boost OLTP processing performance of PostgreSQL.
PostgreSQL Tutorial: Enabling Linux Huge Pages
Summary: Using Huge Pages reduces the memory page table size of PostgreSQL processes and cuts down CPU overhead spent on memory management, thereby improving overall database performance.
PostgreSQL Tutorial: Tuning I/O Performance with pg_stat_io
Summary: In this tutorial, you will learn about pg_stat_io view, including byte-level I/O statistics, WAL tracking, and comprehensive real-world use cases for better database performance tuning.
PostgreSQL Tutorial: Partitioned table statistics
Summary: in this tutorial, you will learn how PostgreSQL collects partitioned table statistics and how they affect PostgreSQL’s estimates.
PostgreSQL Tutorial: Row count estimates in EXPLAIN
Summary: in this tutorial, you will learn what’s ’estimated rows’ in EXPLAIN output all about.