PostgreSQL Concepts

Plain-language definitions for 40 core PostgreSQL concepts — the terminology you need to understand query performance, storage, and internals.

B

B-tree index

The default index type in PostgreSQL. Organizes data in a balanced tree structure for fast equality and range lookups. Best for columns used in WHERE clauses, JOIN conditions, and ORDER BY.

Bloat

Wasted space in tables and indexes caused by PostgreSQL's MVCC model. Dead rows from updates and deletes remain until VACUUM reclaims the space. Excessive bloat slows sequential scans and inflates disk usage.

Buffer cache

PostgreSQL's shared memory area (shared_buffers) that caches frequently accessed data pages. A cache hit avoids a disk read. Low hit ratios indicate memory pressure or inefficient queries scanning too much data.

C

Checkpoint

A periodic operation where PostgreSQL flushes dirty pages from the buffer cache to disk. Ensures crash recovery can start from a known-good state. Frequent checkpoints increase I/O; infrequent ones increase recovery time.

Cold cache

When the buffer cache has no data relevant to a query, forcing PostgreSQL to read from disk. First queries after a restart or on new data hit a cold cache. Benchmarks on a cold cache measure worst-case latency.

Connection pooling

Reusing a pool of database connections across application requests instead of opening a new one each time. Tools like PgBouncer and application-level pools reduce connection overhead and prevent hitting max_connections.

Covering index

An index that includes all columns a query needs, allowing PostgreSQL to answer it entirely from the index without touching the table (an index-only scan). Created with INCLUDE to add non-key columns.

CTE (Common Table Expression)

A named temporary result set defined with WITH. In PostgreSQL 12+, simple CTEs are inlined into the main query and optimized normally. Use MATERIALIZED to force a CTE to execute separately if needed.

D

E

F

G

H

I

J

L

M

Materialized view

A view whose results are stored on disk and must be explicitly refreshed. Trades storage and freshness for speed — ideal for expensive aggregations or joins that don't need real-time results. Gold Lapel creates these automatically.

MVCC (Multi-Version Concurrency Control)

PostgreSQL's approach to concurrency. Each transaction sees a snapshot of the database, and writes create new row versions rather than overwriting. Enables readers to never block writers, but creates dead tuples that need vacuuming.

N

P

Partial index

An index with a WHERE clause that only indexes rows matching a condition. Smaller and faster than a full index when queries consistently filter on the same condition (e.g., WHERE status = 'active').

Planner (query planner)

The component that decides how to execute a query — which indexes to use, join order, scan methods. Makes decisions based on table statistics. Stale statistics (skipping ANALYZE) lead to bad plans.

Prepared statement

A pre-parsed query template with parameter placeholders. Avoids repeated parsing and planning overhead. Most drivers use them automatically. Watch out for plan caching with skewed data distributions.

Q

R

REFRESH MATERIALIZED VIEW CONCURRENTLY

Refreshes a materialized view without locking out readers. Requires a unique index on the view. Slower than a non-concurrent refresh because it diffs old and new data, but avoids downtime.

REINDEX

Rebuilds an index from scratch. Fixes index bloat and corruption. REINDEX CONCURRENTLY (PostgreSQL 12+) rebuilds without locking writes, though it takes longer and uses more disk space temporarily.

Row estimation

The planner's prediction of how many rows a query step will return. Based on table statistics from ANALYZE. When estimates are wildly off, the planner may choose a slow plan (e.g., sequential scan instead of index scan).

S

Sequential scan (Seq Scan)

Reading every row in a table from start to finish. Efficient for small tables or queries that need most rows. Problematic for large tables with selective WHERE clauses — usually means a missing index.

Shared buffers

PostgreSQL's main memory cache for table and index data (shared_buffers setting). Typically set to 25% of system RAM. Too small causes excessive disk I/O; too large can compete with the OS page cache.

Slow query log

Queries that exceed log_min_duration_statement are logged to the PostgreSQL log. The primary tool for finding queries that need optimization. Common thresholds: 100ms–1000ms depending on workload.

T

V

W

WAL (Write-Ahead Log)

A sequential log of all changes written before they're applied to data files. Guarantees crash recovery — PostgreSQL can replay the WAL to restore any committed transaction. Also used for replication.

Work_mem

Memory allocated per-operation for sorts, hash joins, and hash aggregations. Low values cause these operations to spill to disk (slow). Set per-session for complex queries rather than globally high, to avoid exhausting RAM.

Write amplification

When a single logical write causes multiple physical writes. In PostgreSQL, an UPDATE writes a new heap tuple, updates every index on the table, and writes WAL entries for each. HOT updates and fewer indexes reduce it.