PostgreSQL mechanics I want to remember in production

PostgreSQL is an ocean. I do not expect to remember every setting, lock mode or planner detail.

But most production problems are not completely new problems. A slow endpoint, blocked worker, growing table, stale replica or risky migration usually connects to a small set of database mechanics. If I can reason about those mechanics, I can ask better questions and handle a large part of the incident without guessing.

These are the eight areas I want to keep clear in my head.

My map

1. Indexes decide how PostgreSQL can find rows.
2. Query plans show the path PostgreSQL chose.
3. Isolation decides what concurrent transactions can observe.
4. Locks coordinate operations that cannot safely overlap.
5. MVCC and vacuum explain row versions, cleanup and bloat.
6. Replication moves committed changes to standby servers.
7. Migrations change a live database without surprising the application.
8. Configuration should describe the real workload and hardware, not hide a bad query.

The shortest production mental model is:

Understand the workload → find the rows → inspect the chosen path → understand concurrent access → check waiting → check cleanup → check replicas → change and tune carefully.

An index is another data structure maintained beside the table. It can make a suitable read much faster, but every relevant INSERT, UPDATE and DELETE must also maintain that index.

The useful question is not “Should this column have an index?” It is:

Which real query should become cheaper, and what write and storage cost am I accepting for it?

B-tree: the normal starting point

B-tree is PostgreSQL's default index type. It keeps keys ordered, so it works well for equality, ranges and ordering.

CREATE INDEX orders_customer_created_idx	ON orders (customer_id, created_at DESC);

This index is a natural fit for:

SELECT *FROM ordersWHERE customer_id = 42  AND created_at >= now() - interval '30 days'ORDER BY created_at DESC;

Mental model: a dictionary sorted by word. PostgreSQL can navigate near the required key instead of reading every page.

GIN: values containing many searchable elements

GIN is an inverted index. It is useful when one row contains several searchable values, such as a jsonb document, an array or full-text tokens.

CREATE INDEX events_payload_gin_idx	ON events USING gin (payload); SELECT *FROM eventsWHERE payload @> '{"network":"mainnet"}';

GIN can make containment queries fast, but it is larger and usually more expensive to update than a simple B-tree.

Mental model: the index at the back of a textbook. A term points to every page containing it.

BRIN: tiny summaries for naturally ordered large tables

BRIN stores summaries for ranges of physical table pages instead of one entry per row. It is useful when the indexed value is strongly correlated with physical row order, as created_at often is in an append-only event table.

CREATE INDEX events_created_brin_idx	ON events USING brin (created_at);

BRIN may tell PostgreSQL which groups of pages could contain the time range. PostgreSQL still checks the rows inside those pages. It is less precise than B-tree, but dramatically smaller.

Mental model: warehouse aisles labelled “January to March” and “April to June.” The label gets me to the right area, not the exact box.

Composite indexes: column order changes the search

For a B-tree index on (a, b), entries are ordered first by a, then by b inside each value of a.

• WHERE a = 1 AND b = 2 is a strong match.
• WHERE a = 1 can use the leading column.
• WHERE b = 2 may still use the index, including a skip scan when it is profitable, but it is usually less efficient because b is not globally ordered by itself.

The old statement that a trailing column cannot use a composite index is too absolute. The planner can scan the index or use skip scan. The practical rule is that leading-column constraints normally make a multicolumn B-tree most efficient.

Column order is not simply “put the most selective column first.” I start from the workload:

1. Columns commonly tested with equality usually come first.
2. A range or ordering column usually follows those equality columns.
3. I verify the exact query with EXPLAIN, because selectivity, ordering and index-only access can change the answer.

Workload shape: the index must fit real traffic

An index can look correct on paper and still be a poor production trade. Before adding one, I ask four questions.

1. How much of the table does the query return?

Cardinality is the number of distinct values in a column. Selectivity is how narrowly a particular predicate reduces the result.

An email column usually has high cardinality, so WHERE email = ... is often selective. A status column may have only a few values, but that does not make an index automatically useless. The distribution matters:

orders.status completed: 9,900,000 rowsfailed:       100,000 rows

An index is unlikely to help a query that reads almost every completed row. It may help a query for the much smaller failed set. If the application repeatedly searches only that minority, a partial index can express the real workload:

CREATE INDEX orders_failed_created_idx	ON orders (created_at)	WHERE status = 'failed';

The rule to remember is not “low cardinality is bad.” It is:

An index is most useful when it lets PostgreSQL avoid enough table work for the query that actually runs.

2. Is the table read-heavy or write-heavy?

Every index occupies storage and adds work to relevant writes. A read-heavy reporting table may justify several carefully chosen indexes or a materialized view. An append-heavy audit table may need only the indexes required by retention and common investigations—perhaps a small BRIN index on a naturally ordered timestamp.

I do not use a fixed formula such as “ten indexes means eleven writes.” The real cost includes index-page changes, WAL, cache pressure, vacuum work and possible page splits. PostgreSQL can also use a HOT update when no indexed column changes and the new tuple version fits on the same heap page; that avoids adding new index entries. The direction is still clear: unnecessary indexes increase write amplification.

3. How frequently does the query run?

An index saving 200 ms on a query executed once a week may be less valuable than one saving 5 ms on a request executed thousands of times per second. I compare the read benefit with write frequency, storage, cache use and operational importance.

4. Will the choice remain sensible as data grows?

The planner estimates how many rows a predicate will return and chooses the cheapest path for the current statistics and cost model. As the table and value distribution change, a sequential scan can legitimately replace an index scan—or the reverse.

I use pg_stat_user_indexes to look for usage patterns, not as an automatic deletion list:

SELECT relname,	indexrelname,	idx_scan,	idx_tup_read,	idx_tup_fetchFROM pg_stat_user_indexesORDER BY idx_scan ASC;

idx_scan = 0 needs context. Statistics may have been reset recently, the index may protect a constraint, a rare incident query may depend on it, or a standby may serve the reads. I combine these counters with query history, index size and a representative observation window before dropping anything.

Index mistakes I check first

• An index exists, but its leading columns do not match the query.
• A function changes the indexed expression, such as lower(email), without a matching expression index.
• A query returns so much of the table that a sequential scan is cheaper.
• Too many indexes make writes and vacuum more expensive.
• A large unused index consumes cache and disk.
• Statistics are stale, so the planner estimates the wrong number of rows.

EXPLAIN shows the planner's chosen path. EXPLAIN ANALYZE executes the query and records what really happened.

For a read query, my normal starting point is:

EXPLAIN (ANALYZE, BUFFERS)SELECT *FROM ordersWHERE customer_id = 42;

ANALYZE really runs the statement. For an UPDATE, DELETE or INSERT, I use it only with care—often inside a transaction that I deliberately roll back.

Estimated rows versus actual rows

The estimated cost is not elapsed time. It is the planner's internal unit for comparing candidate plans. I do not ignore estimates; I compare them with reality.

estimated rows: 100actual rows:    500,000

A large difference is often the most important clue. PostgreSQL may choose a nested loop or index scan because it expected a small result. The real result is huge, so that choice becomes expensive.

Common causes include stale statistics, correlated columns and predicates the planner cannot estimate well.

Scan types

• Sequential scan: read table pages and test rows. Good when much of the table is needed or the table is small.
• Index scan: find matching index entries, then fetch the table rows.
• Index-only scan: answer from the index when it contains the needed values and the visibility map says heap checks can be avoided.
• Bitmap scan: collect matching row locations, group them by page, then read the pages. Often useful between a highly selective index scan and a full sequential scan.

A sequential scan is not automatically a bug. It may be the cheapest correct plan.

Join types

Nested loop reads an outer input and looks up the inner input for every outer row. It is excellent when the outer result is small and the inner lookup is cheap—often indexed. It becomes painful when the outer side is unexpectedly large.

Hash join builds an in-memory hash table from one input and probes it with the other. It is often effective for larger equality joins. If the hash exceeds memory, it can spill into batches on disk.

Merge join consumes two suitably ordered inputs and advances through them together. It can be strong when both sides are already ordered or indexes provide the order.

Buffers and filters

Buffers: shared hit=12000 read=4000Rows Removed by Filter: 900000

• shared hit means PostgreSQL found blocks in its shared buffer cache.
• shared read means blocks had to be read into that cache. It does not by itself prove physical disk I/O because the operating system may cache them.
• A high “Rows Removed by Filter” count shows work performed and then discarded at that plan node.

High reads suggest an I/O path worth investigating. High hits with a slow query can indicate CPU work, excessive row processing, contention or another wait. Buffers are evidence, not a complete diagnosis. I also inspect timing, row counts, loops, temp reads/writes, wait events and system metrics.

Measuring I/O wait with track_io_timing

BUFFERS tells me how many blocks were found in shared buffers and how many were read into them. It does not tell me how long those reads waited on I/O unless track_io_timing is enabled.

For one privileged debugging session:

SET track_io_timing = on; EXPLAIN (ANALYZE, BUFFERS)SELECT *FROM ordersWHERE customer_id = 42;

The plan can then include a separate line similar to:

Buffers: shared hit=100 read=5I/O Timings: shared read=45.200

That is much stronger evidence than saying “five reads must be the bottleneck.” In this example the executor accumulated about 45 ms of measured shared-read wait. I still compare it with the node and total execution timings rather than labelling all remaining time “CPU”; lock waits, scheduling and other work may also contribute.

To make collection the server default:

ALTER SYSTEM SET track_io_timing = on;SELECT pg_reload_conf();

This setting can be changed without restarting PostgreSQL, but only a superuser or a role granted permission to set it may do so. It repeatedly asks the operating system for the current time, so its overhead depends on the platform. I measure that overhead—pg_test_timing is provided for this—before enabling it broadly on a high-throughput system. For a focused investigation, session-level use keeps the scope clear.

My plan-reading order

1. Confirm total execution time and whether the result is actually slow.
2. Read from the deepest nodes upward: data flows from children into parents.
3. Find the node consuming the most time or repeating many loops.
4. Compare estimated and actual rows at that node.
5. Check filters, heap fetches, buffers and spills.
6. Fix the cause—query shape, statistics, index or memory—not merely the visible scan name.

Isolation controls which committed versions a transaction can see while other transactions are changing the same data.

PostgreSQL implements READ COMMITTED, REPEATABLE READ and SERIALIZABLE. Asking for READ UNCOMMITTED behaves like READ COMMITTED.

Read Committed: a new snapshot for each statement

This is the default. Each statement sees rows committed before that statement began.

Two SELECT statements in one transaction can therefore see different committed values.

The classic lost-update risk appears with application-side read, modify and write:

Session A reads balance = 100Session B reads balance = 100Session A writes 110Session B writes 110

The final value is 110, not 120.

That is different from an atomic SQL update:

UPDATE accountsSET balance = balance + 10WHERE id = 7;

Concurrent executions lock and update the current row version, so this particular increment is not the same unsafe pattern. When logic must first inspect the value, I use an atomic conditional statement, an appropriate row lock or a stronger isolation design.

Repeatable Read: one stable transaction snapshot

A PostgreSQL REPEATABLE READ transaction keeps the snapshot established by its first query or data-modifying statement. Later statements do not see changes committed after that snapshot.

If it tries to modify a row changed by another transaction after its snapshot, PostgreSQL can abort it with:

could not serialize access due to concurrent update

The application must retry the whole transaction.

Repeatable Read still permits serialization anomalies such as write skew:

Rule: at least one doctor must remain on call Transaction A sees two doctors and removes AliceTransaction B sees two doctors and removes BobBoth commitResult: zero doctors remain

The transactions changed different rows, so a simple row-level write conflict did not protect the cross-row rule.

Serializable: behave like some one-at-a-time order

PostgreSQL Serializable uses Serializable Snapshot Isolation. It tracks dangerous read/write dependency patterns. If concurrent work could not be explained by any serial order, PostgreSQL aborts one transaction.

This protects rules spanning several rows or predicates, but the application still needs a bounded retry loop for serialization failures—and sometimes deadlocks.

Mental model:

• Read Committed: take a fresh photograph for every statement.
• Repeatable Read: keep one photograph for the transaction.
• Serializable: keep a photograph and reject a combination of edits that could not have happened one transaction at a time.

The isolation level is not a substitute for understanding the invariant. I first state the business rule, then decide whether an atomic statement, constraint, row lock or Serializable transaction protects it.

MVCC lets readers and writers overlap, but operations still need locks when the same logical resource cannot safely change at the same time.

Row-level locks

UPDATE and DELETE acquire row-level locks. Explicit locking clauses include:

SELECT *FROM accountsWHERE id = 7FOR UPDATE;

The lock does not normally block a plain reader, but it conflicts with operations that need an incompatible lock on the same row. PostgreSQL also takes table-level locks for many commands; row and table locks solve different conflicts.

NOWAIT and SKIP LOCKED

NOWAIT fails immediately instead of waiting for a conflicting row lock.

SKIP LOCKED skips rows that another worker has already locked. It is useful for queue-like tables where an inconsistent view is acceptable because each worker only needs an available job.

WITH claimed AS (	SELECT id	FROM jobs	WHERE status = 'ready'	ORDER BY created_at	FOR UPDATE SKIP LOCKED	LIMIT 1)UPDATE jobsSET status = 'running',	started_at = now()FROM claimedWHERE jobs.id = claimed.idRETURNING jobs.*;

The important correction is that a queue does not avoid SELECT FOR UPDATE; it commonly uses FOR UPDATE SKIP LOCKED so workers do not wait behind one another for the same candidate row.

Advisory locks

Advisory locks protect application-defined resources that may not correspond to a particular table row. PostgreSQL stores the lock, but the database does not enforce what the key means. Every cooperating caller must follow the same convention.

SELECT pg_try_advisory_xact_lock(9001);

Typical uses are leader election, one schema task per tenant, or ensuring only one scheduler runs a particular job.

• Transaction-level advisory locks are released automatically when the transaction ends.
• Session-level advisory locks remain until explicitly released or the session ends, even if a transaction rolls back.
• try variants return immediately instead of waiting.

I prefer transaction-level locks for short database work because their lifetime is easier to reason about. Advisory locks are not a replacement for unique constraints, foreign keys or row locks that protect actual stored data.

Hot rows and deadlocks

If many workers update one counter or account row, the lock serializes those writes. Adding more workers can increase the queue without increasing throughput.

Possible fixes include reducing transaction duration, partitioning the logical counter, recording append-only deltas and aggregating later, or redesigning ownership so fewer workers write the same row.

A deadlock is different from ordinary waiting:

Transaction A holds row 1 and waits for row 2Transaction B holds row 2 and waits for row 1

PostgreSQL detects the cycle and aborts one transaction. Acquiring resources in a consistent order reduces this risk.

When production work appears frozen, I check pg_stat_activity for wait events and pg_locks for blockers before blaming the query plan.

PostgreSQL uses Multi-Version Concurrency Control. Readers can keep seeing an older committed row version while another transaction creates a newer version.

An UPDATE generally creates a new tuple version instead of overwriting the old version in place. Tuple metadata such as xmin and xmax helps PostgreSQL decide which version is visible to a snapshot.

When no active snapshot can see an old version, it is dead. But it still occupies space until vacuum makes that space reusable.

What ordinary vacuum does

Routine vacuum:

• identifies tuple versions no longer needed by any transaction;
• makes their space reusable;
• cleans related index entries when appropriate;
• updates visibility information that can help index-only scans;
• freezes sufficiently old transaction information to prevent transaction-ID wraparound.

Ordinary VACUUM usually does not return table space to the operating system. It makes space reusable inside the relation. VACUUM FULL rewrites the table and takes a much stronger lock, so it is an exceptional repair tool rather than routine maintenance.

Why long-running transactions are dangerous

Vacuum cannot remove a row version that might still be visible to the oldest relevant snapshot. A long-running or idle in transaction session can therefore hold the cleanup horizon back and allow dead versions to accumulate.

The precise statement is not “vacuum cannot clean anything created after the transaction began.” It can still clean versions that are certainly invisible to every snapshot. The problem is that an old snapshot can keep many otherwise-dead versions potentially visible.

Other cleanup blockers include old prepared transactions and replication slots that retain WAL or catalog history longer than expected.

What I monitor

• pg_stat_activity: old transactions, idle in transaction, wait events.
• pg_stat_user_tables: live/dead tuple estimates and autovacuum timestamps.
• pg_stat_progress_vacuum: active vacuum progress.
• table and index sizes over time.
• transaction-ID age and old prepared transactions.
• replication slots retaining excessive WAL.

Mental model: MVCC is a document history. Updates create a new revision; vacuum removes revisions that no possible reader still needs. A very old reader forces the history to remain available.

PostgreSQL records changes in the Write-Ahead Log before the corresponding data pages need to be written. Physical streaming replication sends WAL records from the primary to a standby, where they are replayed.

The data moves through distinct stages:

primary writes WAL	→ standby receives WAL	→ standby flushes WAL durably	→ standby replays WAL	→ change becomes visible to standby queries

Those stages matter because “the replica has the commit” can mean received, flushed or replayed.

Asynchronous replication

Streaming replication is asynchronous by default. The primary can acknowledge a commit before the standby has durably received it.

Benefits:

• lower commit latency;
• the primary does not wait for a remote server.

Trade-offs:

• failover can lose commits that were acknowledged only by the old primary;
• replica reads can be stale;
• lag can grow when the network or standby cannot keep up.

Synchronous replication

With a synchronous standby configured, commits can wait for a chosen remote durability point.

• synchronous_commit = on normally waits for the synchronous standby to flush the commit WAL durably.
• remote_write waits for the standby operating system to receive the write, a weaker durability guarantee.
• remote_apply waits until the standby replays the commit and it becomes visible there.

Synchronous replication can protect acknowledged commits if the topology and settings are correct, but it adds at least network round-trip latency and can reduce availability when the required standby is unavailable.

I describe the requirement with:

• RPO: how much acknowledged data can be lost after failover?
• RTO: how long can recovery and traffic switching take?

“Zero RPO” is not achieved by merely having a replica. It depends on which standby is synchronous, what acknowledgement level is required and whether the failover system promotes the correct node.

Lag and promotion

Useful positions distinguish WAL sent, written, flushed and replayed. A standby can have received data that is not yet visible because replay is behind.

On promotion, the standby restores WAL already available from its archive or local WAL and exits recovery. It cannot recover asynchronous WAL it never received. Before routing writes, the failover process must also prevent the old primary from returning as a second writer; that fencing problem is outside PostgreSQL's replay mechanism but essential to avoiding split brain.

Mental model: the primary writes a durable change log; the standby follows that log. Synchronous versus asynchronous replication decides how far the follower must progress before the client hears “committed.”

A migration is not only a schema definition. It is a production workload that takes locks, reads pages, writes WAL, creates row versions and interacts with old application code.

Constraints belong in the database

Application validation gives a good error message, but it cannot protect data written by another service, script or concurrent request. I use database constraints for invariants PostgreSQL can enforce:

• NOT NULL for required values;
• CHECK for row-level rules;
• UNIQUE for uniqueness under concurrency;
• FOREIGN KEY for references between tables.

Application validation complements constraints; it does not replace them.

Adding a column

Modern PostgreSQL can add a column with a non-volatile default without rewriting every existing row. It records the value in table metadata and supplies it for old rows until they are rewritten later.

ALTER TABLE ordersADD COLUMN retry_count integer NOT NULL DEFAULT 0;

The statement still needs a table lock, so lock acquisition can wait behind long transactions. A volatile default such as clock_timestamp(), a stored generated column and some other column forms can still force a table rewrite. I check the exact PostgreSQL version and expression instead of treating every default as free.

Adding a large foreign key safely

Adding a foreign key normally checks old rows. On a large table, that scan extends the time locks are held.

The safer two-step pattern is:

ALTER TABLE ordersADD CONSTRAINT orders_customer_fkFOREIGN KEY (customer_id)REFERENCES customers(id)NOT VALID;

New and changed rows must obey the constraint immediately. Existing rows have not yet been checked.

Later:

ALTER TABLE ordersVALIDATE CONSTRAINT orders_customer_fk;

Validation scans old rows using a less disruptive lock than adding and validating the constraint in one operation. It still consumes I/O and should be observed like any other large scan.

Backfilling without one giant transaction

This is risky on a large table:

UPDATE ordersSET new_status = old_status;

One large transaction can hold many row locks, generate substantial WAL, create dead tuples, delay replica replay and make cancellation or rollback expensive.

I backfill bounded batches with a stable key:

WITH batch AS (	SELECT id	FROM orders	WHERE new_status IS NULL	ORDER BY id	LIMIT 1000)UPDATE ordersSET new_status = old_statusFROM batchWHERE orders.id = batch.id;

The migration worker commits each batch, observes database pressure and then optionally pauses between transactions. Sleeping inside one uncommitted transaction keeps its locks and old snapshot open, defeating much of the purpose.

The batch size is not always 1000; I choose it from row width, latency, WAL volume and replica lag.

My migration checklist

1. Identify the lock mode and set a short lock_timeout so the migration fails instead of unexpectedly blocking production.
2. Check whether the change scans or rewrites the table.
3. Make old and new application versions compatible during deployment.
4. Separate schema addition, backfill and constraint enforcement when needed.
5. Commit backfills in small batches and make them resumable.
6. Watch WAL volume, replica lag, dead tuples and request latency.
7. Have a rollback or roll-forward plan before starting.

Configuration is the last lever in this map, not the first. If estimates are wrong, I refresh or improve statistics. If the query asks for unnecessary work, I fix the query. If an index does not match the workload, I fix the index. Only then do I ask whether PostgreSQL's cost assumptions misrepresent the actual system.

random_page_cost is an estimate, not an SSD switch

seq_page_cost and random_page_cost help the planner compare sequential and non-sequential page access. Their values are relative cost units, not measured milliseconds.

Lowering random_page_cost relative to seq_page_cost makes index scans look more attractive. That can be reasonable when random access is relatively cheap or most of the working set is cached. It is not a rule that every SSD database should copy the same lower value.

SHOW seq_page_cost;SHOW random_page_cost;SHOW effective_cache_size;

effective_cache_size is also an estimate used during planning. It does not allocate memory. It describes how much cache may be available to a query across PostgreSQL shared buffers and the operating-system cache, while accounting for competing queries.

I change planner cost settings only after collecting representative plans and system measurements. These values describe an average workload; tuning them to force one preferred plan can make many other plans worse.

A small tuning loop I can repeat

1. Capture the query, parameters, frequency and latency distribution.
2. Run EXPLAIN (ANALYZE, BUFFERS) safely on representative data.
3. With I/O timing enabled, separate measured I/O wait from other executor time.
4. Check estimates, statistics, query shape and indexes before global settings.
5. Test a proposed setting locally or at session level.
6. Compare several important workloads, not only the query that started the investigation.
7. Roll out gradually and watch latency, throughput, WAL, cache behaviour and replicas.

Mental model: planner settings are the map scale. Changing the scale can improve PostgreSQL's route choice when the old assumptions are wrong, but it does not repair a missing road.

A production triage order I can reuse

When a database-backed service is slow or stuck, I work from the symptom instead of immediately adding an index.

A query is slow

1. Capture the exact query and parameters.
2. Inspect EXPLAIN (ANALYZE, BUFFERS) safely; enable I/O timing when measured read/write wait is needed.
3. Compare estimated and actual rows.
4. Find the expensive node, loops, filters, reads and spills.
5. Check the workload shape: selectivity, frequency, read/write ratio and expected growth.
6. Decide whether the fix is the query, statistics, index, schema, configuration or amount of work requested.

Requests are waiting

1. Check pg_stat_activity wait events.
2. Find the blocking session and the lock it holds.
3. Check transaction age and whether it is idle.
4. Resolve the application behavior that kept the transaction open.

Storage is growing

1. Separate live data growth from dead tuples and unused indexes.
2. Check autovacuum history and thresholds for high-churn tables.
3. Look for old snapshots, prepared transactions and retained replication slots.
4. Avoid jumping directly to VACUUM FULL; understand what prevents normal reuse first.

A replica is stale

1. Compare sent, received, flushed and replayed WAL positions.
2. Check network delay, replay capacity and conflicting long-running standby queries.
3. Decide whether the observed lag violates the application's freshness or failover requirement.

What I want to remember

• Indexes: design from selectivity, frequency and read/write shape; every index has a write and storage cost.
• Plans: compare estimates with actuals and find where rows, time, buffers and measured I/O wait accumulate.
• Isolation: protect the business invariant, and retry transactions that PostgreSQL deliberately aborts.
• Locks: ordinary waiting, hot rows and deadlocks are different problems; inspect blockers.
• MVCC: updates create versions; vacuum can remove only versions no snapshot may still need.
• Replication: received, flushed and replayed are different durability and visibility points.
• Migrations: lock time, scan time, WAL and mixed application versions matter as much as the DDL syntax.
• Configuration: measure first; planner cost values describe workload assumptions, not universal hardware truths.

I do not need the whole ocean in my head. I need a dependable map for deciding where to look next.

References

• PostgreSQL index types
• PostgreSQL multicolumn indexes
• Heap-only tuple (HOT) updates
• Using EXPLAIN
• Run-time statistics and track_io_timing
• Planner cost constants
• Index usage statistics
• Transaction isolation
• Explicit locking and advisory locks
• Routine vacuuming
• Streaming and synchronous replication
• ALTER TABLE and constraint validation