PostgreSQL — Interview Q&A Matrix

Parent stack: Persistence & Caching Technology: PostgreSQL Levels: Junior · Senior · Architect Questions: 4

Each entry contains a Question, the Expected Depth, an Ideal Answer, and Red Flags.

Junior

Q1 — Indexes: what, why, and the cost

Question: What is a database index, why does it speed up reads, and what does it cost?

Expected Depth:

• Explains how an index avoids reading every table row.
• Knows the read, write, storage, cache, and maintenance costs.
• Understands selectivity and why PostgreSQL may choose a sequential scan.
• Knows common index types: B-tree, GIN, GiST, and BRIN.
• Can choose column order for a composite index and explain partial and covering indexes.

Ideal Answer:

• An index is a separate data structure that helps PostgreSQL find rows without reading the whole table. The default B-tree index works well for equality, range, and ordered queries.
• An index is not free. It uses disk and memory, and every INSERT, UPDATE, or DELETE may need to update it. Too many indexes slow writes, vacuum work, backups, and schema changes.
• PostgreSQL compares plan costs. For a query that returns a large part of a small table, a sequential scan can be faster than many random index lookups.
• Create indexes for real query patterns, not only for individual columns:

CREATE INDEX idx_orders_customer_created
ON orders (customer_id, created_at DESC);

• A composite index can support filtering by customer_id and returning recent rows in order. Column order must match the important filters and sort operations.
• A low-cardinality column is not automatically useless. A partial index can be effective when the indexed subset is small:

CREATE INDEX idx_orders_unpaid
ON orders (created_at)
WHERE paid = false;

• Choose other index types when needed:
• GIN for arrays, jsonb, and full-text search.
• GiST for ranges, geometry, and nearest-neighbor searches.
• BRIN for very large tables where values follow physical order, such as timestamps in append-only data.
• Remove unused or duplicate indexes only after checking workload statistics and deployment dependencies.

Red Flags:

• Says to index every column.
• Claims that an index always makes a query faster.
• Cannot explain why PostgreSQL may choose a sequential scan.
• Says a boolean column can never usefully be indexed.
• Adds overlapping indexes without checking write cost or usage statistics.

Senior

Q2 — Reading EXPLAIN ANALYZE and fixing a slow query

Question: A query that filters and sorts is slow. Walk through diagnosing it with EXPLAIN ANALYZE in PostgreSQL and the fixes you'd consider.

Expected Depth:

• Uses EXPLAIN (ANALYZE, BUFFERS) and knows that ANALYZE executes the query.
• Reads plan nodes, loops, timing, rows, buffers, and sort information.
• Compares estimated rows with actual rows to find statistics problems.
• Understands sequential, index, index-only, and bitmap scans.
• Designs composite, partial, expression, and covering indexes for the query shape.
• Checks locks, query frequency, data distribution, and application behavior before changing the schema.

Ideal Answer:

• Capture the exact SQL and parameters from production metrics or pg_stat_statements. Test with realistic data because a plan on an empty database is not useful.
• EXPLAIN ANALYZE executes the statement. Use a transaction and roll it back for a write query, or start with plain EXPLAIN in a sensitive environment.

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total
FROM orders
WHERE customer_id = 42 AND status = 'PAID'
ORDER BY created_at DESC
LIMIT 20;

• Read from the most expensive nodes upward. Check actual time, rows, loops, shared buffer hits/reads, sort method, and whether rows were removed by a filter.
• A large difference between estimated and actual rows may mean stale statistics, correlated columns, or skewed data. Run ANALYZE and consider extended statistics for related columns.
• For this query, a composite index can support both filters and the sort. INCLUDE stores total as a non-key column and may allow an index-only scan when the visibility map permits it:

CREATE INDEX idx_orders_cust_status_created
ON orders (customer_id, status, created_at DESC)
INCLUDE (id, total);

• Do not apply "equality columns first" without checking the full workload. Column order affects which query prefixes can use the index, sorting, index size, and data distribution.
• If a query applies a function, consider an expression index:

CREATE INDEX idx_users_lower_email
ON users (lower(email));

• A leading wildcard such as LIKE '%term%' cannot use a normal B-tree efficiently. Consider pg_trgm with a GIN index when substring search is required.
• Re-run the plan and compare latency, buffers, CPU, write overhead, and index size. A faster single query is not enough if the new index harms the total workload.

Red Flags:

• Adds an index before reading the plan or measuring query frequency.
• Runs EXPLAIN ANALYZE on a production UPDATE without understanding that it executes the change.
• Looks only at the top-level cost and ignores rows, loops, buffers, and child nodes.
• Treats every sequential scan as a problem.
• Never compares estimated rows with actual rows.
• Adds a covering index with many large columns and ignores storage and write cost.

Q3 — Connection pooling (HikariCP) and pool sizing

Question: Under load the app throws "connection is not available" timeouts. Explain connection pooling and how you'd size the pool.

Expected Depth:

• Explains connection reuse and why a pool must be bounded.
• Sizes the pool from database capacity, application instances, workload, and measured wait time.
• Distinguishes connection acquisition time, query time, transaction time, and network timeouts.
• Uses HikariCP metrics and leak detection carefully.
• Keeps transactions short and does not hold a connection during remote calls.
• Knows when a proxy such as PgBouncer may help.

Ideal Answer:

• Opening a database connection requires network setup, authentication, and server memory. HikariCP reuses a bounded set of physical connections.
• A connection-acquisition timeout means no connection became available before connectionTimeout. The cause may be a small pool, slow queries, long transactions, leaked connections, or an overloaded database.
• There is no universal pool-size formula. Count every application instance and reserve connections for administration, migrations, and background jobs. Start small, load-test, and watch database CPU, active sessions, query latency, and HikariCP pending threads.

spring.datasource.hikari.maximum-pool-size=10
spring.datasource.hikari.connection-timeout=3000
spring.datasource.hikari.validation-timeout=1000
spring.datasource.hikari.max-lifetime=1700000
spring.datasource.hikari.leak-detection-threshold=20000

• Set maxLifetime slightly below any database, proxy, or network connection lifetime. Use leak detection during investigation because very low thresholds create noise.
• Set a database statement timeout for runaway SQL, separately from pool acquisition timeout:

SET statement_timeout = '2s';

• Keep transaction boundaries around database work only. Do not hold a transaction and connection while calling an HTTP API.
• Use try-with-resources for direct JDBC so connections return to the pool:

try (Connection connection = dataSource.getConnection();
   PreparedStatement statement = connection.prepareStatement(sql)) {
  statement.setLong(1, customerId);
  try (ResultSet rows = statement.executeQuery()) {
    // map rows
  }
}

• PgBouncer can reduce PostgreSQL backend connection pressure for many short-lived clients. Transaction pooling has feature limits, so test prepared statements, session settings, and advisory locks before adopting it.

Red Flags:

• Raises every application pool to hundreds of connections without checking the total.
• Treats the pool as the source of capacity instead of checking database CPU and query latency.
• Holds a connection during a slow remote call.
• Uses one timeout for acquisition, SQL execution, and network operations.
• Enables leak detection permanently with an unrealistically low threshold.
• Adds PgBouncer without checking transaction-pooling restrictions.

Architect

Q4 — Database sharding and partitioning strategy

Question: A single PostgreSQL primary can no longer hold the write volume or data size. As architect, design a horizontal scaling strategy and its trade-offs.

Expected Depth:

• Separates vertical scaling, read replicas, table partitioning, and multi-node sharding.
• Knows that PostgreSQL table partitioning does not distribute writes across servers.
• Selects a shard key for even distribution and query locality.
• Handles global IDs, cross-shard queries, transactions, uniqueness, and referential integrity.
• Plans shard routing, rebalancing, schema migrations, backups, and failure recovery.
• Evaluates managed or distributed PostgreSQL options before building custom routing.

Ideal Answer:

• First confirm the bottleneck. Improve queries and indexes, remove unnecessary writes, scale the primary vertically, and move tolerant reads to replicas. Replicas scale reads, not primary write capacity, and they introduce replication lag.
• Native table partitioning keeps one logical database. It helps partition pruning, retention, vacuum scope, and maintenance, but it does not distribute writes across several servers:

CREATE TABLE events (
    id bigint NOT NULL,
    created_at timestamptz NOT NULL,
    payload jsonb NOT NULL
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_08 PARTITION OF events
FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');

• Shard only when one primary cannot meet write or storage needs. Choose a key such as tenant_id that spreads load while keeping common joins and transactions on one shard.
• Avoid keys that grow in one direction or create a large tenant hotspot. Very large tenants may need dedicated placement or sub-sharding.
• Use globally unique IDs, such as UUIDs or IDs with a shard component. Database uniqueness and foreign keys are easiest to enforce inside one shard.
• A routing layer maps each key to a shard. A directory service supports moving tenants more easily than a fixed modulo rule, but it adds a highly available dependency.
• Cross-shard joins and transactions are expensive. Prefer query-local data, denormalized read models, and sagas for workflows that cross shards. Make eventual consistency visible in the product contract.
• Rebalancing requires copying data, capturing new writes, verifying counts or checksums, switching routing, and keeping a rollback window. Schema migrations, backups, restores, and monitoring must work across every shard.
• Evaluate Citus, a managed distributed PostgreSQL service, or another distributed database before building custom sharding. The operational cost may be larger than the original database problem.

Red Flags:

• Treats table partitioning as multi-server sharding.
• Jumps to sharding before measuring and optimizing the primary.
• Chooses a key that creates hotspots or frequent cross-shard queries.
• Assumes that global foreign keys and unique constraints work normally across shards.
• Has no routing, rebalancing, backup, restore, or schema-migration plan.
• Adds distributed transactions without discussing latency and availability costs.

Scoring Rubric