Databases
PostgreSQL
A powerful open source relational database with extensions, JSONB, full-text search and strong SQL support.
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
Cheat sheet
| Command or code | What it does |
|---|---|
psql -h localhost -U app -d mydb | Connect to a database |
\dt | List tables in psql |
\d users | Table structure: columns, indexes, keys |
CREATE INDEX ON users (email); | Create a B-tree index |
EXPLAIN ANALYZE SELECT * FROM users WHERE id = 1; | Query plan with real timings |
SELECT data->>'name' FROM events; | Extract a jsonb field as text |
WITH t AS (SELECT ...) SELECT * FROM t; | CTE: a named subquery |
BEGIN; UPDATE ...; COMMIT; | Transaction: all or nothing |
pg_dump -Fc mydb > mydb.dump | Back up a database in compressed format |
pg_restore -d mydb mydb.dump | Restore a database from a dump |
How to learn it
- Install PostgreSQL (locally or in Docker) and connect with
psql: create a database and a table, then run INSERT and SELECT. - Learn SQL from scratch: JOIN, GROUP BY, subqueries, CTEs, window functions. The SQL course on ShortLesson helps.
- Get the data types right:
jsonb,uuid,timestamptz, arrays,numericfor money. - Design a schema of 4 to 5 tables with primary and foreign keys, constraints and migrations.
- Add indexes (B-tree, GIN) and read
EXPLAIN ANALYZEto see the query plan change. - Understand transactions, isolation levels, locking and MVCC, and reproduce a deadlock with two sessions.
- Set up backups (
pg_dump, WAL), a least privilege role and connection pooling (PgBouncer). - Try replicas, partitioning and extensions (pg_trgm, PostGIS, pgvector) on a real task.
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 ANALYZEshows 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.
Interview questions from the bank
- A money transfer in NestJS "half succeeds": the debit happened, the credit failed, but there is no rollback. Inside
dataSource.transaction(...)some queries go through an injected repository. What is the cause?MiddleNode.js - You need an endpoint that exports 5 million rows from Postgres to CSV. The current code does
findAll,mapandres.send, and the process dies with OOM. Design a solution. What happens if the client is slow or closes the tab?SeniorNode.js - Design the contract of
GET /orderswith filters, sorting and pagination for an infinite feed. Why does offset break here, and how do you protect filters and sorting from abuse?MiddleHTTP - What does
DISTINCTdo and how is it different fromGROUP BY?JuniorSQL - How do
ORDER BYandLIMITwork, and why isLIMITwithoutORDER BYdangerous?JuniorSQL - What is the difference between
UNIONandUNION ALL?JuniorSQL - What is the difference between
DELETE,TRUNCATEandDROP TABLE?JuniorSQL - What is a subquery and how does
EXISTSdiffer fromIN?JuniorSQL
Comparisons with this technology
Related technologies
- MySQLOne of the most widespread relational databases, the foundation of many web projects and the LAMP stack.
- SQLiteAn embedded serverless database in a single file: the most widely deployed database in the world.
- CockroachDBA distributed SQL database, PostgreSQL wire compatible, with horizontal scaling and fault tolerance.
- MongoDBThe most popular document NoSQL database, storing data as JSON-like documents (BSON).
- PrismaAn ORM for Node.js and TypeScript with a schema file, a generated type-safe client and migrations.
- SQLThe standard declarative query language for relational databases: selecting, changing and defining data.
Logos belong to their owners and are used for identification only. Icons: Simple Icons (CC0), Devicon (MIT).