Multi-version concurrency control: each transaction sees a snapshot of data and changes create new row versions. That is why reads do not block writes.

When to use JSONB instead of separate columns?

For semi-structured or variable attributes. For data you filter and join on often, regular typed and indexed columns are better.

Why is an index needed and what does it cost?

An index speeds up lookups and sorting but takes space and slows inserts and updates. EXPLAIN ANALYZE shows whether the planner uses it.

It removes dead row versions left by MVCC and frees space for reuse. Autovacuum runs it automatically.

PostgreSQL: what it is, when to use it, interview questions

What it is

PostgreSQL descends from the POSTGRES project at Berkeley. Today it is an open database governed by a community with no single owner. It is known for reliability, ACID transactions and closeness to the SQL standard.

Besides classic tables it handles JSONB, arrays, full-text search, geodata (PostGIS), vector search (pgvector) and custom types and extensions. Concurrency uses MVCC: readers do not block writers.

It has streaming and logical replication, partitioning, window functions and CTEs. It is usually the best default choice for a new web project.

Use it when

The main database of a web app, SaaS or financial data where integrity matters.
Complex queries, reports, JSON next to relational data.
Geodata or vector search through extensions.

Avoid it when

Huge event streams and analytics over billions of rows: consider ClickHouse.
Horizontal write scaling without complex setups: look at Cassandra or CockroachDB.

Key concepts

MVCC: Row versioning so reads and writes do not block each other.
VACUUM: Cleaning old row versions and updating statistics.
Index types: B-tree, GIN, GiST, BRIN for different queries.
Extensions: PostGIS, pgvector, pg_trgm and others.
WAL: The write-ahead log for recovery and replication.

Example

A table with JSONB, an index and an aggregation.

Interview questions

1. What is MVCC?
Multi-version concurrency control: each transaction sees a snapshot of data and changes create new row versions. That is why reads do not block writes.
2. When to use JSONB instead of separate columns?
For semi-structured or variable attributes. For data you filter and join on often, regular typed and indexed columns are better.
3. Why is an index needed and what does it cost?
An index speeds up lookups and sorting but takes space and slows inserts and updates. EXPLAIN ANALYZE shows whether the planner uses it.
4. What does VACUUM do?
It removes dead row versions left by MVCC and frees space for reuse. Autovacuum runs it automatically.

More questions: SQL

Comparisons with this technology

Logos belong to their owners and are used for identification only. Icons: Simple Icons (CC0), Devicon (MIT).

PostgreSQL

What it is

Use it when

Avoid it when

Key concepts

Example

Interview questions

1. What is MVCC?

2. When to use JSONB instead of separate columns?

3. Why is an index needed and what does it cost?

4. What does VACUUM do?

Comparisons with this technology

Related technologies

Explore the platform