Database Monitoring, Troubleshooting, and Diagnostics Questions

Direct answer

Fsync is the operating-system call that forces written data, here the write-ahead log, out of buffers and onto durable storage before Postgres reports a transaction committed, so "fsync is the bottleneck" means the time Postgres spends waiting for that flush, not query planning or CPU, is what's showing up as write latency. Investigate across three layers, database configuration, the operating system, and hardware, roughly in that order since each layer's fix gets progressively more disruptive, and validate every change against the database's own WAL sync timing rather than eyeballing overall latency, since that instrumentation is off by default and will silently read zero even under real fsync pressure until it's turned on.

Structured elaboration

Glossary

WAL (write-ahead log) is the durability mechanism: every change is written to a sequential log before the corresponding data page is modified, so that after a crash the database can replay the log to recover. Fsync is the OS call that flushes buffered writes to the physical device, so a subsequent power loss can't lose data the database already told a client was committed. synchronous_commit controls whether COMMIT waits for that flush (the default) or returns early with a small, bounded durability risk window.

Root causes to investigate, database to hardware

• Database configuration: synchronous_commit = on (the default) means every commit waits for its own fsync, so a high-throughput, small-transaction workload turns fsync latency directly into commit latency. Check whether wal_buffers is large enough that WAL isn't being flushed early simply because a small buffer filled, and whether concurrent commits are actually benefiting from group commit, Postgres automatically batches commits that arrive within a short window into fewer physical fsyncs, but that only helps if there's real concurrency; a single-threaded write path gets zero benefit from group commit no matter how it's tuned.
• Operating system: confirm the I/O scheduler and mount options aren't adding unnecessary overhead (noatime avoids an extra metadata write per read), and check disk queue depth and await time specifically on the WAL device during the latency spikes, isolated from the data-file device if they're on separate volumes.
• Hardware: spinning disks, or network-attached storage without a battery-backed or power-loss-protected write cache, have to perform a genuinely slow physical flush on every fsync. An SSD or NVMe device with power-loss protection can acknowledge a flush far faster, since it doesn't need to wait on a mechanical seek or an unprotected volatile cache. On virtualized or cloud storage, confirm which performance tier is actually backing the volume and whether it's been silently throttled.

Prioritized mitigations, safest and cheapest first

1. Confirm wal_buffers is reasonably sized; a config change with no durability trade-off.
2. For a high-concurrency workload, verify group commit is actually batching, per-transaction commit latency should fall as concurrency rises if grouping is working, and stay flat or rise if something is preventing it.
3. OS and mount tuning is low-risk and worth ruling out before touching any durability setting.
4. Only as an explicit, communicated trade-off: synchronous_commit = off (or local) removes the per-commit wait for the flush, trading a small, bounded window of possibly-lost-but-reported-committed transactions (bounded by wal_writer_delay, typically sub-second) for much lower commit latency. This changes the durability contract and has to be a product decision, not a quiet configuration tweak; Postgres allows it to be set per-transaction, so scope it to genuinely tolerant workloads rather than flipping it globally.
5. A hardware or infrastructure change (moving WAL to a device with power-loss protection, or a higher-tier cloud volume) is the most effective fix for a genuinely fsync-bound device, and also the most disruptive and costly. Reserve it for once the cheaper levers are confirmed insufficient.

Measuring the impact of each change safely

Turn on track_wal_io_timing (and track_io_timing) first, since both default to off, and compare wal_sync_time (from pg_stat_wal) before and after each change on a canary slice of traffic or in a maintenance window, not from a single eyeballed "it looks better" glance. Roll each layer's change out independently so the improvement can be attributed to the specific lever, not a bundle of simultaneous changes.

Worked example

Real settings and statistics read from a live Postgres 16.14 instance:

SHOW synchronous_commit;   -- on
SHOW wal_sync_method;      -- fdatasync
SHOW wal_buffers;          -- 4MB
SHOW full_page_writes;     -- on
SHOW checkpoint_timeout;   -- 5min
SHOW max_wal_size;         -- 1GB

The genuinely useful finding, discovered by actually querying the instrumentation rather than assuming it was already on:

SHOW track_wal_io_timing;  -- off
SHOW track_io_timing;      -- off

SELECT wal_sync, wal_write_time, wal_sync_time FROM pg_stat_wal;
--  wal_sync | wal_write_time | wal_sync_time
-- ----------+----------------+---------------
--        52 |              0 |             0

52 real WAL sync operations had already happened on this instance, yet wal_write_time and wal_sync_time both read exactly zero, not because no time was spent, but because both timing trackers default to off, so pg_stat_wal silently reports zero under real fsync activity until they're explicitly enabled. This is the concrete instrumentation gap the measurement step above exists to catch: without turning these on first, a before-and-after comparison of a fsync-related change would be comparing two zeros and drawing a false conclusion either way.

Trade-offs and pitfalls

• Enabling track_wal_io_timing adds a small overhead of its own, a timing syscall on every fsync, usually negligible relative to the fsync cost itself, but worth being deliberate about turning on in production rather than assuming it's already there, which is exactly why Postgres ships it off by default.
• synchronous_commit = off is frequently reached for because it visibly works, latency drops, but it's a durability trade-off dressed as a performance tweak. Always name the exact bounded data-loss window it introduces when proposing it, and prefer scoping it to specific low-stakes transactions over a global setting.
• Blaming fsync or hardware before ruling out that the workload itself issues too many small, unbatched commits (an ORM committing once per row in a loop, for example) is a common trap; sometimes the fix is application-side batching, not storage tuning, and no infrastructure upgrade fixes a per-row-commit anti-pattern.

Direct answer

Work down a fixed checklist in order of cheapest to check: is the ETL job actually blocking writers with locks, competing for the same I/O or CPU budget, crowding out connections, or forcing a full scan for lack of an index. Each cause maps to a specific immediate mitigation, and every mitigation on this list shares the same underlying principle: get the ETL job off the primary's critical write path right now, rather than try to make the primary absorb it better.

Structured elaboration

Lock waits

Query pg_locks joined to pg_stat_activity for any backend blocked longer than a short threshold (a second or so) during the ETL window, and identify what's holding the lock. A reporting job using a stronger lock than it needs, or holding a long transaction open across many statements (which also blocks routine table maintenance), is the classic culprit. Immediate mitigation: cancel or terminate the blocking backend if it belongs to the ETL job, or, better, point the ETL job's reads at a replica so they never take a lock the primary's writers can even see.

I/O pressure

Check disk queue depth and throughput during the window versus outside it. A large scan issued by the ETL job competes for the same disk queue as the primary's write fsyncs. Immediate mitigation: throttle the job's I/O, or move the read to a replica so the primary's write I/O isn't sharing a device queue with it at all.

CPU and connection pressure

Check connection count and CPU-bound query time during the window. An ETL job opening many parallel connections or running heavy aggregation can starve the connection pool (the limited set of pre-opened database connections the application draws from instead of opening a new one per request) or CPU run queue for OLTP (online transaction processing, the live, latency-sensitive production traffic the application itself generates) requests even without any locking involved. Immediate mitigation: cap the job's connection count and parallelism, or route it through a separate pool or role with its own bounded connection share so it structurally cannot starve OLTP's pool.

Missing indexes forcing a full scan

Run EXPLAIN (ANALYZE, BUFFERS) on the ETL job's actual query. A sequential scan (reading every row in the table in order, rather than jumping straight to matching rows through an index) over a large table, where an index would let it filter early, can dominate the I/O budget for the whole window by itself. This one usually can't be fixed instantly under incident pressure (building an index mid-incident on a live table has its own write-amplification cost), so the immediate move is still to throttle or move the job, with the index build scheduled as a safe follow-up once it can be built online.

The durable fix, named but out of scope here

Once the immediate incident is stable, the real long-term answer for almost every version of this pattern is the same: nightly batch reads against a live OLTP primary are close to always the wrong architecture. Pointing the job at a read replica or a change-data-capture-fed copy, permanently, is the actual destination. That's replication and architecture territory, out of scope for this diagnostic checklist, but worth naming so the incident's fix doesn't quietly become the permanent state.

Worked example

A real, live-captured blocking chain from Postgres 16. One session represents the ETL job taking a table-level share lock (a stand-in for a bulk-read-style operation) inside an open transaction:

BEGIN;
LOCK TABLE wal_churn IN SHARE MODE;
SELECT pg_sleep(2);
COMMIT;

A concurrent OLTP write against the same table, started half a second later:

UPDATE wal_churn SET payload = 'blocked-by-etl' WHERE id = 1;

The standard pg_locks/pg_stat_activity join, run while both sessions were actually in flight:

SELECT blocked.pid AS blocked_pid, blocking.pid AS blocking_pid,
       now() - blocked.query_start AS blocked_duration
FROM pg_stat_activity blocked
JOIN pg_locks bl ON bl.pid = blocked.pid AND NOT bl.granted
JOIN pg_locks kl ON kl.locktype = bl.locktype
                 AND kl.relation IS NOT DISTINCT FROM bl.relation
                 AND kl.granted AND kl.pid <> bl.pid
JOIN pg_stat_activity blocking ON blocking.pid = kl.pid
WHERE blocked.pid <> pg_backend_pid();

Real, captured output from that live snapshot:

 blocked_pid | blocking_pid | blocked_duration
-------------+--------------+-------------------
         339 |          332 | 00:00:00.820206

The query correctly identified session 339 (the OLTP update) as blocked by session 332 (the ETL-style share lock), sampled about 0.82 seconds into the block. The exact duration is a live snapshot, not a fixed benchmark, it will vary with when you happen to sample it, but the mechanism reproduces reliably: run the two sessions staggered like this and the join query will always surface exactly this blocking relationship.

Trade-offs and pitfalls

• Killing the ETL job mid-run without checking whether it's resumable or idempotent can leave its target (a warehouse table, a report) in a worse, half-updated state than simply being late. Know the job's resumability before reaching for a hard cancel under pressure.
• Moving ETL reads to a replica doesn't eliminate contention, it relocates it: the replica's apply process now competes with the ETL job's reads, which can surface as replication lag (the delay between when a write commits on the primary and when it becomes visible on the replica) instead of primary write latency. A real fix for this specific incident, but it needs its own monitoring, not a free lunch.
• Adding a missing index reactively, under incident pressure, without checking whether the ETL job's actual filter is selective enough for the planner to use it, can burn effort on a fix that doesn't move the needle. Verify with EXPLAIN against the real query before committing to the build.

Direct answer

A storm of PAGELATCH_UP waits on tempdb almost never means rows are locked. It means many sessions are fighting over a small set of "traffic control" pages that tempdb uses to hand out space (or, on a different pattern, over the system catalog pages that track temporary-object metadata), and a latch (an in-memory, short-duration protection on a page's physical consistency) is a different animal from a lock (a transactional guarantee on a row or table that can be held for the life of a transaction), and a different animal again from a deadlock (two transactions each waiting on a lock the other one is holding, so neither can proceed until the engine detects the cycle and kills one of them), which is a lock-level phenomenon, not the latch queue described here. Under a parallel build-and-insert workload, this usually happens because many concurrent workers are each creating and dropping temporary tables, table variables, or spill work tables (for sorts, hashes, or cursors) fast enough that the pages coordinating space allocation, or the catalog rows describing those objects, become a single point of serialization. The fix path is: identify which page type is hot with the wait DMVs (dynamic management views, the built-in system views that expose live server state), then apply the mitigation that matches that specific cause, because the tempdb-file-count fix and the metadata fix solve two different problems that both surface as the same wait type.

Structured elaboration

Two distinct root causes behind the same wait type

tempdb is the shared scratch database SQL Server uses for temporary tables, table variables, sort/hash spill space, and version-store data for snapshot isolation. Two unrelated mechanisms inside it can both produce PAGELATCH_UP waits:

PFS tracks free space on roughly every 8,000-page range of a data file; GAM and SGAM each track roughly 4 GB of space per page (GAM records which extents are already allocated, SGAM which mixed extents, extents whose 8 pages are shared across several different small objects rather than dedicated to just one, still have a free page). Because SQL Server's allocation scan always starts at a predictable page, when many sessions allocate at once they queue up on the same PFS/GAM/SGAM page even though they're allocating different, unrelated objects. That is allocation-page contention: real work is blocked on a bookkeeping page, not on each other's data.

Metadata contention is a separate mechanism: every temp table create/drop touches rows in tempdb's own system catalog, and those catalog pages can themselves become a latch hotspot under very high create/drop rates, independent of how much data space is available.

Diagnosing which one you have

First confirm the wait itself and who is waiting:

SELECT r.session_id, r.wait_type, r.wait_time, r.wait_resource,
       t.text AS running_query
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.wait_type LIKE 'PAGELATCH%';

wait_resource (or resource_description in sys.dm_os_waiting_tasks) is formatted database_id:file_id:page_id, for example 2:1:88 (database_id 2 is always tempdb). To classify the page:

• If page_id is evenly divisible by 8088, it is a PFS page.
• If it is a low, fixed page number that recurs across many different waiting sessions on the same file (classically page 2 or page 3 of a file), it is a GAM or SGAM page.
• If neither pattern holds, or the page belongs to a system object, it is likely metadata contention rather than allocation contention.

To directly test for metadata contention, resolve the page to the object it belongs to:

SELECT OBJECT_NAME(dpi.object_id, dpi.database_id) AS system_table_name,
       COUNT(DISTINCT r.session_id) AS session_count
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.fn_PageResCracker(r.page_resource) AS prc
CROSS APPLY sys.dm_db_page_info(prc.db_id, prc.file_id, prc.page_id, 'LIMITED') AS dpi
WHERE dpi.database_id = 2
  AND dpi.object_id IN (3, 9, 34, 40, 41, 54, 55, 60, 74, 75)
  AND UPPER(r.wait_type) LIKE N'PAGELATCH[_]%'
GROUP BY dpi.object_id, dpi.database_id;

Those ten numbers in the object_id IN (...) list are SQL Server's fixed, built-in object IDs for its own system base tables that back the temp-object catalog (tables like sysschobjs and syscolpars); they're the same on every SQL Server instance, which is why they can be hardcoded here instead of looked up by name. If this query returns rows, sessions are contending on tempdb's own catalog tables (metadata contention). If instead you see the PFS/GAM/SGAM page pattern from the previous step and this query returns nothing, you have allocation-page contention. A key diagnostic tell either way: this is a latch queue, not a blocking lock chain, so it will not show up in a sys.dm_exec_requests blocking-session-id chain the way row-lock blocking does; if you go looking for blocking there and find nothing, that itself is a clue you're looking at the wrong mechanism.

Mitigations, by layer

Worked example

The PFS-page test and the tempdb-file-count recommendation are both concrete, checkable arithmetic. Running this reproduces both:

def is_pfs_page(page_id, interval=8088):
    return page_id % interval == 0

print("905856 %% 8088 =", 905856 % 8088, "-> PFS page:", is_pfs_page(905856))

def recommended_tempdb_files(logical_cpus):
    if logical_cpus <= 8:
        return logical_cpus
    return 8  # start at 8; add in groups of 4 if contention persists

for cpus in [4, 8, 16, 32]:
    print(f"{cpus} logical CPUs -> start with {recommended_tempdb_files(cpus)} tempdb data files")

total_gb, n_files = 32, recommended_tempdb_files(16)
print(f"{total_gb} GB tempdb split across {n_files} equally sized files -> {total_gb/n_files} GB each")

Output (executed):

905856 % 8088 = 0 -> PFS page: True
4 logical CPUs -> start with 4 tempdb data files
8 logical CPUs -> start with 8 tempdb data files
16 logical CPUs -> start with 8 tempdb data files
32 logical CPUs -> start with 8 tempdb data files
32 GB tempdb split across 8 equally sized files -> 4.0 GB each

So on a 16-core box with a single 32 GB tempdb data file, the concrete first move is 8 files of 4 GB each, not one 32 GB file: each file gets its own GAM/SGAM page pair, so the allocator's round-robin scan across files spreads what used to be one hot page into eight independently-scanned ones.

For the DMV query shape itself, here is illustrative (not executed, since this workload runs on SQL Server and this sandbox has no SQL Server instance) output showing what the classification query would return if you were hitting metadata contention on the temp-object catalog:

system_table_name   session_count
------------------  -------------
sysschobjs           14
syscolpars           9

A result like that, with zero hits for the PFS/GAM/SGAM page pattern, tells you memory-optimized tempdb metadata (or reducing create/drop rate) is the right lever, not adding more data files.

Trade-offs & pitfalls

• Do not reach for trace flags 1118/1117 on SQL Server 2016 or later: their behavior is the default already, so setting them is harmless but signals you're diagnosing from stale knowledge, and on genuinely old instances (2014 and earlier) 1118 has a real cost, wasting up to 56 KB per tiny object that never grows past one page.
• Memory-optimized tempdb metadata is not a default-on fix: it requires a restart, and its MEMORYCLERK_XTP memory consumer (SQL Server's internal name for the pool of memory the in-memory OLTP / memory-optimized engine uses) can grow unbounded under heavy load, so bind it to a resource governor pool with an explicit memory cap and only turn it on once the catalog-contention diagnostic query above has actually shown session buildup on system tables. Turning it on speculatively is treating a possible symptom as a confirmed diagnosis.
• Adding tempdb files is not free either: more files past what CPU concurrency and I/O bandwidth justify just adds round-robin overhead without reducing real contention, and unequal file sizes defeat the fix outright because the proportional-fill allocator favors the file with more free space, funneling load right back onto one file.
• The highest-leverage fix is frequently on the application side, not the infrastructure side: cutting how many temp objects a parallel workload creates and drops per second (by batching, reusing tables, or lowering the degree of parallelism) is usually cheaper and more effective than any tempdb reconfiguration, because it removes the contention at its source instead of spreading it thinner.

Direct answer

The orders table as given only ever holds the current row per order_id, so it cannot show that two writers raced: you need an append-only audit trail (a trigger-based history table, or change-data-capture off the write-ahead log) to see every write that happened, not just the last one that survived. Once that history exists, a LAG() window function over each order's writes, ordered by updated_at, flags any pair of successive writes to the same order closer together than your chosen threshold.

Structured elaboration

Assumptions this query depends on

• updated_at must be assigned by the database server (or a synchronized server clock), not by the client, and at least millisecond resolution. Second-granularity or client-supplied timestamps can make two genuinely different writes 400 milliseconds apart look simultaneous, or sort in the wrong order entirely under clock skew.
• Some form of auditing or logging exists (or can be added). Without one, a lost update leaves literally no trace in orders itself: the second writer's row simply replaces the first's, and the query below has nothing to query. This is the assumption the question is explicitly asking to state.
• Not every close pair of writes is a bug. A legitimate automated pipeline can move an order through several states in well under a second. The strongest additional signal that a close pair is actually a lost update rather than a fast, correct pipeline is that the two writes came from different actors or sessions, which needs an updated_by or session/transaction identifier column in the audit trail to check.

Query design

• Partition the audit history by order_id, order by updated_at with the audit table's own surrogate key as a tiebreaker for exact ties.
• Use LAG() to pull each row's previous write time and previous status within that partition.
• Filter for gaps under the chosen threshold (the question's example: under 1 second).

Worked example

Schema and seed data (executed against Postgres 16):

CREATE TABLE orders (
  order_id    bigint PRIMARY KEY,
  user_id     bigint NOT NULL,
  status      text NOT NULL,
  updated_at  timestamptz NOT NULL
);

-- Audit table: one row per write to `orders`, populated by an AFTER UPDATE trigger
-- (or, in production, by logical decoding, Postgres reading committed changes straight out of the
-- write-ahead log itself, or CDC, change data capture, streaming every insert, update, and delete
-- elsewhere as it happens, off the WAL instead of a trigger).
-- This is the "available auditing" the question's assumption is about, because
-- `orders` itself only ever holds the current row.
CREATE TABLE orders_audit (
  audit_id    bigserial PRIMARY KEY,
  order_id    bigint NOT NULL,
  status      text NOT NULL,
  updated_at  timestamptz NOT NULL
);

CREATE OR REPLACE FUNCTION orders_audit_fn() RETURNS trigger AS $$
BEGIN
  INSERT INTO orders_audit(order_id, status, updated_at)
  VALUES (NEW.order_id, NEW.status, NEW.updated_at);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_orders_audit
AFTER INSERT OR UPDATE ON orders
FOR EACH ROW EXECUTE FUNCTION orders_audit_fn();

Seeded three orders: order 1 gets a paid -> shipped -> cancelled sequence where the second and third writes land 350ms apart (simulating two racing workers), order 2 gets a single ordinary update 1.2 seconds later (no race), order 3 repeats the racing pattern with a 480ms gap. The detection query:

WITH ordered AS (
  SELECT
    order_id,
    status,
    updated_at,
    LAG(status)     OVER w AS prev_status,
    LAG(updated_at) OVER w AS prev_updated_at
  FROM orders_audit
  WINDOW w AS (PARTITION BY order_id ORDER BY updated_at, audit_id)
)
SELECT
  order_id,
  prev_status,
  status AS next_status,
  ROUND(EXTRACT(EPOCH FROM (updated_at - prev_updated_at))::numeric, 3) AS gap_seconds
FROM ordered
WHERE prev_updated_at IS NOT NULL
  AND updated_at - prev_updated_at < interval '1 second'
ORDER BY order_id, updated_at;

Real output:

 order_id | prev_status | next_status | gap_seconds
----------+-------------+-------------+-------------
        1 | paid        | shipped     |       0.002
        1 | shipped     | cancelled   |       0.365
        3 | paid        | refunded    |       0.002
        3 | refunded    | shipped     |       0.483

Order 2's single, well-spaced update never appears, exactly as expected: the query correctly isolates the two orders with suspiciously close successive writes and leaves the normal one alone.

Trade-offs and pitfalls

• This query surfaces the symptom (near-simultaneous writes), not proof of an actual lost update. Two legitimate rapid writes from the same idempotent retry look identical here; correlating against an actor or session identifier cuts the false-positive rate substantially.
• An audit table retrofitted today only protects you going forward. Past lost updates that happened before the trigger existed left no trace; if you need historical evidence, you're limited to whatever the write-ahead log's retention or a CDC stream already captured.
• A synchronous trigger adds write overhead to every UPDATE (roughly one extra small insert per write). On very high-throughput tables, a CDC stream off the WAL avoids adding synchronous work inside the OLTP transaction at all.
• Detection is not prevention. The actual fix for lost updates is optimistic concurrency (a version column, or an UPDATE ... WHERE updated_at = :expected check on rows-affected) or pessimistic locking (SELECT ... FOR UPDATE) at the point of the read-modify-write cycle. This query is a forensic and monitoring tool, not a substitute for that guard.

Direct answer

I confirm bloat (wasted space: dead row or index-entry versions that still occupy disk pages but no longer hold anything live) with direct measurement rather than inference from symptoms alone, using PostgreSQL's pgstattuple extension to see exactly how full the index's pages actually are, since "performance got worse" has several other possible causes and I don't want to rebuild the wrong thing. Once confirmed, REINDEX CONCURRENTLY is the default remediation, because it rebuilds the index without holding the exclusive lock a plain REINDEX or DROP/CREATE would, so normal reads and writes against the table keep working the whole time.

Structured elaboration

Why indexes bloat in the first place. PostgreSQL uses multi-version concurrency control (MVCC): an UPDATE doesn't modify a row in place, it writes a new row version and marks the old one dead, and the same happens to that row's index entries, a new one gets added, the old one becomes dead weight. VACUUM (run automatically by autovacuum, Postgres's built-in background process that watches tables and runs VACUUM on them for you on a schedule, or run manually) reclaims that dead space for reuse within the same table or index, but it doesn't shrink the file back down on disk in the common case; the freed space stays allocated to that index, available for future rows, but not returned to the filesystem. A workload with heavy UPDATE or DELETE traffic and infrequent or falling-behind autovacuum activity accumulates dead space faster than it's reclaimed, which is what bloat actually is: not corruption, just historical dead versions and freed-but-unreturned space diluting the live, useful data in the index's pages.

Confirming bloat, not just assuming it. pgstattuple (a PostgreSQL extension: CREATE EXTENSION pgstattuple;) gives direct, page-level measurement instead of relying on plan cost estimates or a hunch:

SELECT * FROM pgstattuple('idx_sessions_user_id');   -- physical stats: free space, dead tuples
SELECT * FROM pgstatindex('idx_sessions_user_id');   -- btree-specific: avg_leaf_density, leaf_fragmentation

pgstatindex's avg_leaf_density is the most direct bloat signal for a B-tree index (the tree-shaped structure Postgres uses for a normal index; a "leaf page" is one of the disk pages at the very bottom of that tree, the ones that actually hold the index entries pointing back at table rows, as opposed to the upper pages that just route a search down to the right leaf): it's the percentage of each leaf page actually occupied by live index entries, versus free space. A healthy, freshly-built index typically sits around 90%; a meaningfully bloated one drops well below that. leaf_fragmentation (how out-of-physical-order the leaf pages have become relative to their logical scan order) is a second, independent signal: a high value means even a full index scan is doing more random I/O than it should, on top of whatever the density number already shows.

Worked example

I reproduced real index bloat on a seeded PostgreSQL 16 table (200,000 rows, one index on a user_id column), then measured and remediated it, rather than describing the mechanism abstractly.

Baseline, right after building the index and running a single VACUUM:

avg_leaf_density: 90.73   leaf_fragmentation: 0   index_size: 1568 kB

I then disabled autovacuum on the table (ALTER TABLE sessions SET (autovacuum_enabled = false);, simulating a table falling behind its normal maintenance) and ran six full-table UPDATEs that each touched the indexed column, deliberately generating dead index entries with nothing reclaiming them:

avg_leaf_density: 83.27   leaf_fragmentation: 49.97   index_size: 12 MB

Real, measured bloat: the index grew from 1,568 KB to 12 MB, roughly 8x, on a table whose live row count never changed, and pgstattuple on the underlying table confirmed the mechanism directly: 200,072 dead tuples against 200,000 live ones (a dead-to-live ratio over 100%), with the table itself reporting 65.68% free space. That figure lands close to 1x, not the roughly 6x you might expect from six full-table updates with nothing reclaiming space in between: even with autovacuum disabled, Postgres still opportunistically prunes already-dead, no-longer-visible tuples out of a heap page whenever a later update on that same page needs room to write its next version, so each round's update tends to clear out the previous round's dead version as it goes, leaving only the most recent round's dead tuples still sitting there uncollected by the time VACUUM finally runs. I confirmed this directly by reproducing the same six-update sequence against a fresh PostgreSQL 16 instance and measured 200,043 dead tuples, the same roughly-1x result, not 6x. This is exactly the shape of evidence that confirms bloat as the cause rather than, say, a data-volume increase or a plan regression: the row count is unchanged, but the physical structure carrying those rows has ballooned.

Remediation: REINDEX INDEX CONCURRENTLY idx_sessions_user_id;. This completed cleanly with no error and no lock held against normal table access, and the measurements afterward confirmed a full recovery:

avg_leaf_density: 90.72   leaf_fragmentation: 0   index_size: 1568 kB

Back to within a rounding error of the original baseline, on all three measured dimensions, with the rebuild done live.

Rebuilding with minimal impact on live traffic: comparing the three real options.

Trade-offs and pitfalls

REINDEX CONCURRENTLY avoids the exclusive lock, but it isn't free: it requires roughly double the disk space during the rebuild (the old and new copies of the index briefly coexist), it takes noticeably longer wall-clock time than a plain REINDEX because it has to do extra work to stay concurrent-safe, and if it's interrupted partway through, it can leave behind an invalid index (visible via pg_index.indisvalid = false) that needs to be manually dropped rather than automatically retried, so check that flag after running it rather than assuming success from a lack of error output. pgstattuple's functions do a real, if usually brief, scan of the object being measured, so running them against a very large table or index during an active incident is itself a small additional load; that cost is worth paying to confirm the diagnosis before committing to a rebuild, but it's not literally free. The single biggest pitfall is skipping confirmation entirely and reindexing on a hunch: if the actual cause of the slowdown was a plan regression, a missing index elsewhere, or genuine data growth rather than bloat, a REINDEX changes nothing about the real problem and burns a maintenance window or a chunk of I/O budget for no benefit, which is exactly why measuring first with pgstattuple/pgstatindex matters more than jumping straight to the fix.