Indexes and write cost

A workspace member opens the document library and asks for its 50 most recently updated documents. Each row contains a document ID, a bounded title and an update time; PDF bytes live elsewhere. Use workspace W9 and doc-456 as the running example. The table contains many workspaces, while this request may return only W9's rows. Authorization still happens before the query; an index cannot decide who belongs to W9.

SELECT document_id, title, updated_at
FROM documents
WHERE workspace_id = :workspace_id
ORDER BY updated_at DESC, document_id DESC
LIMIT 50;

Assume updated_at is non-null and document_id is unique within a workspace. The ID breaks ties when two updates share a timestamp. The exact query matters: filtering by workspace, ordering by time and ID descending, and limiting the response are three separate requirements. Saying “the documents table is slow” does not tell us which access path to build.

Start without an extra list index. The database can inspect rows, keep those belonging to W9, identify the latest 50 and return them. That may be perfectly adequate for a small internal library. As the table grows, repeatedly examining unrelated work can become expensive, even though the API returns a short list.

We will compare a maintained ordered index with that baseline. The goal is less work for this real read at an acceptable total operating cost. No table size, latency improvement or read/write ratio here is a measurement of InterviewLayer. The example teaches the decision and the evidence needed to make it.

Think of the index as another organized structure maintained alongside the table. In this candidate, entries first group by workspace. Inside W9 they follow updated_at descending, then document_id descending. The database can locate W9's range and walk its newest entries instead of discovering the workspace's order anew for each request.

CREATE INDEX documents_workspace_recent
ON documents (workspace_id, updated_at DESC, document_id DESC);

The illustrated CREATE INDEX matches that arrangement. PostgreSQL's B-tree supports ordered scans, and leading equality constraints help restrict a multicolumn scan. Those documented properties make this a plausible path for our query. They do not force the planner to choose it or prove an end-to-end latency target.

An index on workspace_id alone could help find W9, but it does not encode our time-and-ID ordering. An index beginning with updated_at serves a different arrangement: entries from many workspaces can be interleaved by time. Do not infer that a nonleading condition is impossible to use; explain why this particular leading workspace key directly fits the selected operation.

Follow doc-456 after it is edited. Its new updated_at changes where it belongs in the ordered structure. The list can now place it nearer the front, but maintaining that position is work performed because the row changed. The useful read arrangement and its update cost are two sides of the same choice, rather than unrelated database features.

Suppose W9 contains a tiny fraction of all documents. Finding its entries can avoid much unrelated work. Now change the request to an administrative export of nearly every row and many columns. Reading the table more sequentially can be a better option. The list and the export are different operations; a plan chosen for one is not a verdict about the other.

The planner estimates costs from the query, available paths and statistics. Inspect its chosen plan instead of treating a sequential scan as an error by definition. For our small ordered list, a matching index may help both finding and ordering. How many rows match is relevant, but selectivity alone does not describe every benefit of an ordered LIMIT.

Add a new filter, status = READY. If many recent W9 documents are still processing, the existing ordered path can pass over candidates before collecting 50 READY rows. A limit bounds returned rows, not necessarily inspected rows. An index specialized for that frequent filter may help, while adding another maintained structure and changing the comparison.

First establish whether the new filter is important and whether the slow case is representative. A one-off report may tolerate a sort or a longer scan. A common interactive query may justify a better access path. This keeps the schema tied to observed operations instead of adding an index for every column a future request might mention.

Our lean index contains the workspace and ordering keys. The query also returns title. An ordinary index scan may locate an entry and then fetch its table row, called a heap row in PostgreSQL, to obtain the remaining data. The index is not automatically a replacement for every value stored in the document record.

-- Alternative to compare with the lean index, not an automatic addition.
CREATE INDEX documents_workspace_recent_cover
ON documents (workspace_id, updated_at DESC, document_id DESC)
INCLUDE (title);

An alternative adds title as an INCLUDE payload. PostgreSQL can consider an index-only scan when the necessary values are available, but snapshot visibility still matters. Its visibility map can avoid heap checks for all-visible pages; otherwise the scan must visit the heap. Recently changing document rows can therefore retain heap fetches despite coverage.

Ask what problem the extra payload would solve. If fetching 50 titles dominates this frequent read and measurements show useful avoided heap work, the candidate deserves comparison. If the application immediately fetches the full document metadata anyway, or those rows change constantly, the extra stored title may buy less than its name suggests.

Keep PDF contents out of this example's covering payload. The list needs a bounded title, not the original file or extracted text. Adding wider values duplicates more data and consumes space. Start with the lean index, compare the covering candidate under the actual update mix, and retain the extra payload only when the measured read benefit justifies maintaining it.

When a new document row is inserted, its relevant indexes must represent it too. When doc-456 gets a new updated_at, our ordering key changes. The application does not get to keep the old cheap insert path while permanently receiving the new read arrangement. PostgreSQL maintains indexes with table changes; the precise work depends on the operation and index.

For ordinary logged tables, changes also participate in write-ahead logging. Index space, changed pages and WAL traffic belong in the comparison with the read saving. There is no fixed rule that adding one index doubles write latency. Page state, batching, cache behavior and the workload affect the cost, so we have to measure rather than assign a decorative multiplier.

Updates leave row versions whose space later needs maintenance. Routine vacuuming makes obsolete space reusable and supports other database housekeeping. It is an operating obligation, not a reason to run VACUUM FULL after every edit. A busy library that changes titles and timestamps frequently has a different maintenance workload from a mostly static archive.

Avoid the opposite overstatement that every update rewrites every index in exactly the same way. This article's concrete hot path changes an indexed timestamp, so it clearly has index consequences. Additional implementation optimizations have conditions; they cannot make this particular changed ordering key free. Record the actual insert and edit patterns when deciding how many read paths to maintain.

Use EXPLAIN to inspect the planned scan, ordering and estimated rows for the exact query. In an appropriate test environment, EXPLAIN ANALYZE executes it and adds actual observations. Compare estimates with actual rows and look for filtering or sorting that explains the slow operation. A cost number is not a promise of milliseconds.

EXPLAIN (ANALYZE, BUFFERS)
SELECT document_id, title, updated_at
FROM documents
WHERE workspace_id = 'W9'
ORDER BY updated_at DESC, document_id DESC
LIMIT 50;

The illustrated read uses ANALYZE and BUFFERS on a representative W9 fixture. No output is supplied because we have not executed this database. Compare ordinary and large workspaces, useful status filters and both lean and covering candidates. Keep the requested columns and query parameters realistic so the exercise does not optimize a query the application never sends.

Repeat relevant reads after cache warming and under realistic cold conditions. A shared-buffer hit and a read describe that cache layer; a read need not prove a physical disk access. Also measure page latency from the application, because query execution alone does not include all connection, serialization and network costs.

Then compare the write path and storage under the same workload. ANALYZE really runs its statement: do not casually point it at a production UPDATE. Use an isolated fixture for write experiments, including any trigger effects. A database rollback is not a general undo for arbitrary external actions. Keep those experiments separate from the safe-looking SELECT example shown here.

For a new empty table, creating the chosen index before traffic begins can be straightforward. An existing busy table changes the decision. PostgreSQL's normal index build blocks writes while it runs. A concurrent build allows ongoing writes but does more work, can wait, and can leave an invalid index after failure. CONCURRENTLY cannot run inside a transaction block.

That makes a build plan part of the proposed improvement. Choose a quiet window or a concurrent approach from the service's availability needs. Watch storage headroom, write latency and build completion; a command being started is not evidence that a usable index exists. Investigate failure and clean up or rebuild an invalid result deliberately.

After rollout, verify the real list and its write cost again. If W9's query changes, the original justification can disappear. Keep ownership of the index tied to the access pattern, and check constraints and other queries before removing an apparently redundant structure. A rare but important operation can matter even if a short observation window saw little traffic.

The final decision is specific: one workspace-leading ordered index is a candidate for this latest-document list. A small table may keep the baseline; a frequently used READY filter may justify another path; excessive edit cost may favor a different product read model. Explain the trigger and evidence for each change instead of concluding that every table should accumulate more indexes.