Why Database Migrations Fail in Production
When a database migration passes in staging but disrupts a live service, the migration usually met different conditions in production. A database…
In PostgreSQL, an index helps the database find rows without checking every row in a table. The two index types serve different searches. A B-tree keeps values in order. A GIN index maps parts of a value made up of multiple components, such as array elements or keys in a JSONB document (PostgreSQL’s format for storing JSON data), to the rows that contain them. PostgreSQL uses B-tree by default, but the query’s operators, the symbols and keywords that specify a search, and the way its data is stored determine which index fits better. PostgreSQL’s index types spell out the methods and operators each supports.
Use B-tree for filters that look for one exact value or a range of values, for matching rows across tables (joins), and for sorting. Use GIN when a query searches for elements inside arrays, keys or values inside JSONB documents, or words in text prepared for full-text search. Neither choice guarantees a faster query: PostgreSQL chooses a plan based on its cost estimates, and an index helps only when the query matches the index’s supported operators.
A B-tree stores index entries in sorted order. PostgreSQL uses those sorted entries to locate a value, scan a range of values, or return rows in index order. B-tree suits columns that hold one value per row, such as an account ID, timestamp, or price. It is PostgreSQL’s default index method.
For example, an events table might need to fetch the latest events for one account:
CREATE INDEX events_account_time_idx
ON events (account_id, created_at DESC);
SELECT event_id, created_at
FROM events
WHERE account_id = $1
ORDER BY created_at DESC
LIMIT 50;
The index places rows for each account together and orders those rows by created_at, so PostgreSQL has an index that matches both the filter and sort. The planner still decides whether to use it. Table size, the number of matching rows, and the columns the query returns all affect that decision.
Column order matters when a B-tree index covers more than one column. PostgreSQL gets the most benefit when a query filters on the first columns listed in the index, such as account_id here. A query that filters only on created_at may get less benefit from this index. PostgreSQL’s multicolumn index rules describe how leading columns affect scans.
B-tree also supports some text patterns anchored at the beginning, such as LIKE 'foo%'. It does not provide general substring search for a pattern such as LIKE '%bar'. For non-C locales, PostgreSQL may require a special operator class for anchored pattern matching. The class defines which SQL operators work with an index method.
GIN, short for Generalized Inverted Index, stores keys extracted from a value and links each key to the rows that contain it. This structure fits searches for a component inside an array or JSONB document, and searches for terms in a full-text search vector. The data type’s GIN operator class, a set of rules that defines which query operators the index supports, determines which searches the index can handle.
A containment query checks whether an array of product tags includes a specified element, and a GIN index can support that query:
CREATE INDEX products_tags_gin
ON products USING GIN (tags);
SELECT product_id
FROM products
WHERE tags @> ARRAY['clearance'];
PostgreSQL’s built-in array operator class also supports operators such as overlap (&&) and contained-by (<@). The query must use an operator the index supports. Filtering on an array column alone does not mean the index can handle every condition. The GIN documentation describes its structure, operator classes, and maintenance behavior.
PostgreSQL provides two common GIN operator classes for JSONB. The default jsonb_ops supports key-existence operators such as ?, ?|, and ?&, as well as containment and JSONPath operators. jsonb_path_ops supports fewer operators, including containment (@>) and JSONPath operators, and PostgreSQL documents it as usually smaller and more specific for those searches. It does not support key-existence operators. It can also perform poorly when a search targets a structure with no values, such as {"a": {}}. Choose the class according to the operators the queries actually use:
-- Supports a broader set of JSONB operators
CREATE INDEX api_doc_gin
ON api USING GIN (jdoc);
-- Supports a narrower set of containment and JSONPath searches
CREATE INDEX api_doc_path_gin
ON api USING GIN (jdoc jsonb_path_ops);
An index on a whole JSONB column does not automatically support every operator applied to an expression inside that column. For example, a query using jdoc -> 'tags' ? 'qui' applies the key-existence operator to the extracted tags value. An expression index on (jdoc -> 'tags') can match that expression. Alternatively, rewrite the query using a supported containment operation on the whole JSONB column when that expresses the same search. PostgreSQL’s JSONB indexing guidance covers operator-class support and expression indexes.
GIN also suits PostgreSQL full-text search. A tsvector is a normalized representation of searchable text, and @@ tests whether it matches a text-search query. One option is to store a generated search vector and index it:
ALTER TABLE articles
ADD COLUMN search_vector tsvector
GENERATED ALWAYS AS (
to_tsvector(
'english',
coalesce(title, '') || ' ' || coalesce(body, '')
)
) STORED;
CREATE INDEX articles_search_gin
ON articles USING GIN (search_vector);
SELECT id, title
FROM articles
WHERE search_vector @@ plainto_tsquery('english', $1);
The text-search configuration, such as english, controls how PostgreSQL processes words. Build the indexed vector and query with the same configuration so the search matches the stored representation. PostgreSQL identifies GIN as its preferred index type for full-text search.
Every index adds work when PostgreSQL inserts or deletes table rows, or updates them. GIN can require extra work because one composite value may contribute many component keys, such as the keys from one JSONB document or the elements of one array. A change to one JSONB document or array can therefore update multiple GIN entries. PostgreSQL’s index introduction describes the general cost of keeping indexes synchronized with table data.
GIN’s fastupdate setting can defer some index work by placing new entries on a pending list for later cleanup. This can improve average update speed, but searches also check pending entries. When the list grows large, searches can slow, and cleanup can make an individual write take longer. PostgreSQL documents autovacuum and the gin_pending_list_limit setting as ways to manage cleanup; disabling fastupdate favors steadier response times over faster updates. Test these choices against the actual write and read patterns rather than treating one setting as universally best.
Test candidate indexes with representative data and query parameters. EXPLAIN shows the plan PostgreSQL estimates it will use. EXPLAIN ANALYZE runs the query and reports actual timing and row counts. Adding BUFFERS also shows buffer activity, which helps reveal how much data PostgreSQL reads from memory or storage.
EXPLAIN (ANALYZE, BUFFERS)
SELECT event_id, created_at
FROM events
WHERE account_id = 42
ORDER BY created_at DESC
LIMIT 50;
Compare the estimated and actual row counts, how PostgreSQL scans the table and filters rows, and the buffer activity. If PostgreSQL chooses a sequential scan, the plan reflects its estimate that scanning the table costs less than using the index. Check whether the index matches the query’s columns and expression. Then confirm that the index supports the query’s operators before changing the design. The PostgreSQL EXPLAIN documentation explains how to read estimated and actual plans. EXPLAIN ANALYZE executes the statement, so use care with data-changing statements: their effects occur unless you roll them back.
Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.
Take a look at vroni.com