Interview Q&A / PostgreSQL

1. What index types does PostgreSQL support, and why is my index sometimes not used?

   B-tree is the default and handles equality, ranges and sorting. GIN indexes values that contain many items, like JSONB, arrays and full-text search. BRIN is a tiny index for huge tables whose rows are stored in a natural order, like time-series data. The planner skips an index when a full scan looks cheaper, or when the query is written in a way the index cannot match.

   IntermediateIn-depth4 min read
2. Does the column order in a multi-column B-tree index matter?

   Yes. A composite index is sorted by the first column, then the second within it, and so on. It helps queries that filter on a leading prefix of its columns, and it is much less useful if only a later column is filtered.

   BeginnerQuick answer1 min read
3. What is a partial index in PostgreSQL and when is it useful?

   A partial index has a WHERE clause and indexes only the rows that match it. It is smaller and faster to maintain, and ideal when queries target a small, well-defined slice such as unprocessed jobs or active users.

   BeginnerQuick answer1 min read
4. What is an expression index, and how do you make WHERE lower(email) = 'x' fast?

   An expression index stores the result of a function or expression instead of the raw column. Create an index on lower(email) and queries that use exactly lower(email) can use it; a plain index on email cannot help.

   BeginnerQuick answer1 min read
5. How do you index one specific key inside a JSONB column?

   Create a B-tree expression index on the extracted value, such as (data ->> 'email'), and query with the same expression. This is smaller and faster than a GIN index for equality or range on one known key.

   IntermediateQuick answer1 min read
6. Is the hash index in PostgreSQL worth using compared to B-tree?

   A hash index supports only equality lookups. Since PostgreSQL 10 it is crash-safe and can be slightly smaller than a B-tree for long keys, but B-tree also handles equality plus ranges and sorting, so B-tree stays the default.

   IntermediateQuick answer1 min read
7. What does CREATE INDEX CONCURRENTLY do and what are its trade-offs?

   It builds the index without blocking writes to the table, by scanning the table in multiple passes. It takes longer, cannot run inside a transaction block, and can leave an INVALID index behind if it fails.

   IntermediateQuick answer1 min read
8. What is a BRIN index and when is it better than B-tree?

   BRIN stores only the minimum and maximum value for each block range of the table, so it is tiny. It works well on huge tables where the column's value follows the physical row order, like an append-only timestamp.

   IntermediateQuick answer1 min read
9. What is a covering index and what does the INCLUDE clause do?

   A covering index holds every column a query needs, so PostgreSQL can answer from the index alone with an index-only scan. INCLUDE adds extra payload columns to the index leaf without making them part of the sort key or uniqueness.

   IntermediateQuick answer1 min read
10. What is a GIN index and which PostgreSQL features use it?

    GIN is an inverted index: it maps each element (a word, an array item, a JSONB key) to the rows that contain it. It powers full-text search, array and JSONB containment queries, and trigram search, with slower writes than B-tree.

    IntermediateQuick answer1 min read
11. What is a GiST index, and how does it differ from GIN?

    GiST is a flexible tree framework used for ranges, geometry, nearest-neighbour search and exclusion constraints. GIN is faster for lookups of elements inside values, while GiST is smaller and cheaper to update but lossy for text.

    IntermediateQuick answer1 min read
12. Why does PostgreSQL sometimes show Index Only Scan but still read the table?

    An index does not store row visibility. PostgreSQL checks the visibility map, and for pages not marked all-visible it must fetch the heap row. EXPLAIN ANALYZE shows this as Heap Fetches, which VACUUM reduces.

    AdvancedQuick answer1 min read