PostgreSQL Performance Glossary

Complete dictionary of PostgreSQL performance terms, metrics, and concepts with practical examples and detailed explanations.

A B C D E F G H I J K L M N O P Q R S T U V W X Y Z

A

ACID

A set of properties that guarantee database transactions are processed reliably: Atomicity, Consistency, Isolation, and Durability.

Example: In a banking transaction, ACID ensures that when transferring money between accounts, either both debit and credit operations succeed completely, or neither happens at all.

Autovacuum

PostgreSQL's automatic maintenance process that reclaims storage and updates statistics by removing dead tuples and updating the visibility map.

Configuration: autovacuum = on
Related: autovacuum_naptime, autovacuum_vacuum_threshold

B

Buffer Pool

In-memory cache that stores frequently accessed data pages to reduce disk I/O operations. Controlled by the shared_buffers parameter.

Recommendation: Set to 25% of total RAM for dedicated database servers.
Monitoring: Check pg_stat_bgwriter for buffer hit ratio.

Btree Index

The default index type in PostgreSQL, optimized for equality and range queries. Maintains sorted order and supports various data types.

Usage: CREATE INDEX idx_name ON table (column);
Best for: WHERE clauses, ORDER BY, and range queries.

C

Checkpoint

A process that ensures all dirty buffers are written to disk and creates a recovery point. Critical for crash recovery and performance tuning.

Key Parameters:
checkpoint_timeout - Maximum time between checkpoints
checkpoint_completion_target - Spread checkpoint I/O over time

Connection Pooling

Technique to manage database connections efficiently by reusing existing connections rather than creating new ones for each request.

Popular Tools: PgBouncer, pgpool-II
Modes: Session, transaction, statement pooling

P

pgbench

PostgreSQL's built-in benchmarking tool for running performance tests. Simulates TPC-B workload by default but supports custom scripts.

Basic Usage: pgbench -c 10 -t 1000 dbname
Metrics: TPS, latency, connection times

Planner

PostgreSQL's query optimizer that determines the most efficient execution plan for SQL queries based on statistics and cost estimates.

View Plans: EXPLAIN (ANALYZE, BUFFERS) SELECT ...
Key Concepts: Cost estimation, join algorithms, index selection

Q

Query Plan

The execution strategy chosen by the planner for a SQL query, showing the sequence of operations and their estimated costs.

Analysis: Look for sequential scans, nested loops, and sort operations
Optimization: Add indexes, rewrite queries, update statistics

T

TPS (Transactions Per Second)

A key performance metric measuring the number of database transactions completed per second. Higher TPS indicates better performance.

Measurement: pgbench reports including and excluding connection time
Factors: Hardware, configuration, query complexity, concurrency

Tuple

PostgreSQL's term for a row in a table. Due to MVCC, multiple versions of the same logical row may exist as different tuples.

Dead Tuples: Old versions after UPDATE/DELETE operations
Cleanup: VACUUM removes dead tuples and reclaims space

V

VACUUM

Maintenance operation that removes dead tuples, updates statistics, and reclaims storage space. Essential for PostgreSQL performance.

Types: VACUUM, VACUUM FULL, VACUUM ANALYZE
Automation: autovacuum handles routine maintenance

W

WAL (Write-Ahead Logging)

PostgreSQL's crash recovery mechanism that ensures data durability by writing changes to a log before modifying data pages.

Benefits: ACID compliance, point-in-time recovery, replication
Configuration: wal_level, max_wal_size

Work_mem

Memory allocated for internal sort operations and hash tables before writing to temporary disk files. Critical for query performance.

Impact: Affects ORDER BY, DISTINCT, joins, and aggregations
Tuning: Start with 4MB, monitor for disk-based operations