Menu
CoddyTech

PostgreSQL Indexes and Performance Interview Questions

B-tree and other index types, partial, expression and covering indexes, reading EXPLAIN, planner statistics and indexing without blocking writes.

11 questions
  1. What is the default index type in PostgreSQL, and which queries can it serve?

    B-tree. CREATE INDEX builds one unless you name another method, and so do primary keys and unique constraints. It serves =, <, >, BETWEEN, IN, IS NULL and ORDER BY, because it keeps values sorted.

    It serves LIKE 'abc%' only with the C collation or an index built with text_pattern_ops, and never LIKE '%abc'. Every index also slows writes, since each INSERT updates it too.

  2. When do you use GIN, GiST, BRIN or hash indexes in PostgreSQL?

    Choose by the operator the query uses:

    IndexUse it for
    B-treeequality, ranges, sorting (the default)
    GINjsonb (@>, ?), arrays, full-text search, trigrams
    GiSTranges and geometry (&&, nearest neighbor), exclusion constraints
    BRINhuge tables whose physical order follows a column, like a log by time
    Hashequality only

    GIN is fast to search but slower to update. BRIN keeps one summary per block range, so it is tiny, and useless when values are scattered. Index types

  3. Can a unique index allow duplicates for soft-deleted rows in PostgreSQL?

    PostgreSQL
    CREATE TABLE accounts (id int PRIMARY KEY, email text NOT NULL, deleted_at timestamptz);
    CREATE UNIQUE INDEX accounts_live_email ON accounts (email)
      WHERE deleted_at IS NULL;
    INSERT INTO accounts VALUES (1, 'asha@example.com', '2026-01-10');
    INSERT INTO accounts VALUES (2, 'asha@example.com', '2026-05-02');
    INSERT INTO accounts VALUES (3, 'asha@example.com', NULL);
    SELECT count(*) FROM accounts;

    It returns 3. A partial index covers only the rows that match its WHERE, so uniqueness applies to live accounts only; a second live row with that email would fail. The planner uses a partial index only when the query's WHERE implies the index condition, so queries must repeat deleted_at IS NULL.

  4. Why does WHERE lower(email) = ... not use the index on email, and how do you fix it?

    An index on email stores the raw values, so it cannot answer a condition on lower(email). Index the expression itself and query with exactly the same expression.

    PostgreSQL
    CREATE TABLE users (id int PRIMARY KEY, email text);
    INSERT INTO users
      SELECT g, 'User' || g || '@Example.com' FROM generate_series(1, 1000) g;
    CREATE INDEX users_lower_email_idx ON users (lower(email));
    ANALYZE users;
    EXPLAIN SELECT id FROM users WHERE lower(email) = 'user42@example.com';

    Look for Index Scan using users_lower_email_idx in the plan; with WHERE email = ... it falls back to a Seq Scan. CREATE UNIQUE INDEX ON users (lower(email)) also makes emails unique regardless of case.

  5. What is a covering index with INCLUDE, and when do you get an index-only scan?

    INCLUDE (PostgreSQL 11 and later) stores extra columns in the index without making them part of the key. A query that reads only indexed columns can then skip the table: an Index Only Scan.

    PostgreSQL
    CREATE TABLE orders (id int PRIMARY KEY, customer_id int, total numeric);
    INSERT INTO orders
      SELECT g, g % 100, g * 1.5 FROM generate_series(1, 10000) g;
    CREATE INDEX orders_customer_idx ON orders (customer_id) INCLUDE (total);
    VACUUM ANALYZE orders;
    EXPLAIN SELECT customer_id, total FROM orders WHERE customer_id = 7;

    Look for Index Only Scan using orders_customer_idx. The catch: pages not marked all-visible in the visibility map still need a table visit, and VACUUM maintains that map. Heap Fetches in EXPLAIN ANALYZE counts those visits. Index-only scans

  6. Does column order matter in a multicolumn index in PostgreSQL?

    Yes. A B-tree on (customer_id, created_at) is sorted by customer first, then by time within each customer. It serves customer_id = 7 alone or together with a range or ORDER BY on created_at. For created_at alone it is a poor fit, because the matching entries are spread through the whole index.

    Put equality columns first, then the range or sort column. PostgreSQL 18 added skip scan, which helps when the leading column has few distinct values.

  7. What is the difference between EXPLAIN and EXPLAIN ANALYZE in PostgreSQL?

    EXPLAIN shows the chosen plan with its estimates, without running the query. EXPLAIN ANALYZE runs it and adds the actual time, rows and loops of every node.

    PostgreSQL
    CREATE TABLE orders (id int PRIMARY KEY, customer_id int, total numeric);
    INSERT INTO orders
      SELECT g, g % 100, g * 1.5 FROM generate_series(1, 10000) g;
    ANALYZE orders;
    EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 7;

    Compare estimated and actual rows per node: a large gap is a misestimate, the usual root of a bad plan. Here you see a Seq Scan with Rows Removed by Filter, since customer_id has no index. EXPLAIN ANALYZE runs the statement for real, so wrap an UPDATE or DELETE in BEGIN and ROLLBACK. Using EXPLAIN

  8. Why is PostgreSQL not using my index?

    Either the planner estimates a sequential scan is cheaper, or the index cannot match the condition. The usual causes:

    • A small table, or a query that returns a large share of the rows.
    • A function on the column: date(created_at) = ...; write a range instead.
    • A type mismatch that casts the column: id = 5.0 on an integer column becomes id::numeric = 5.0.
    • A leading wildcard: LIKE '%term'.
    • A filter on only the second column of a multicolumn index.
    • Stale statistics; run ANALYZE.

    To test, run SET enable_seqscan = off in your session and compare the plans.

  9. What does ANALYZE do in PostgreSQL, and why does it matter for performance?

    It samples a table and stores per-column statistics (share of NULLs, distinct values, most common values, a histogram) that the planner uses to estimate row counts. Bad estimates mean bad join orders and scan choices.

    PostgreSQL
    CREATE TABLE signups (country text, plan text);
    INSERT INTO signups
      SELECT CASE WHEN g % 10 < 7 THEN 'IN' ELSE 'US' END, 'free'
      FROM generate_series(1, 1000) g;
    ANALYZE signups;
    SELECT attname, n_distinct, most_common_vals, most_common_freqs
    FROM pg_stats WHERE tablename = 'signups' ORDER BY attname;

    The planner now knows 70% of the rows are IN. Autovacuum analyzes after enough changes, but can lag behind a bulk load and skips temporary tables, so run ANALYZE yourself then. CREATE STATISTICS covers correlated columns such as city and country.

  10. How do you add an index to a large production table without blocking writes?

    Use CREATE INDEX CONCURRENTLY. A plain CREATE INDEX blocks INSERT, UPDATE and DELETE for the whole build; the concurrent build lets writes continue.

    CREATE INDEX CONCURRENTLY orders_customer_idx ON orders (customer_id);
    

    The price: it scans the table twice, waits for older transactions to finish, and cannot run inside a transaction block. If it fails, it leaves an INVALID index that serves no queries but still slows writes: drop it and retry. REINDEX CONCURRENTLY exists since PostgreSQL 12. CREATE INDEX

  11. How do you speed up LIKE '%term%' searches in PostgreSQL?

    With a trigram index from the pg_trgm extension. A B-tree cannot help a pattern that starts with a wildcard, but a GIN index on three-character chunks of the text serves LIKE, ILIKE and regular expressions that match anywhere.

    CREATE EXTENSION IF NOT EXISTS pg_trgm;
    CREATE INDEX products_name_trgm ON products USING gin (name gin_trgm_ops);
    SELECT id, name FROM products WHERE name ILIKE '%wireless%';
    

    For searching words rather than substrings, full-text search (tsvector with a GIN index) handles word forms and ranking. Trigram indexes are large, so add one where a search box needs it, not on every text column.

Coddy programming languages illustration

Learn to code with Coddy

Get started