跳到正文

本页主要内容为英文。翻译此页

技能与技术

PostgreSQL & SQL roadmap

Learn SQL and PostgreSQL from the first SELECT to indexes, transactions, JSON and vector search for AI apps.

时间
⏱ 4–5 weeks
Level
Beginner
Steps
6 steps
Architecture ↓Cheat sheet ↓6 interview Q&As ↓
分享

The roadmap

Click a step to open the full guide.

Best architecture: a highly available PostgreSQL setup

The usual production database layout.

PostgreSQL HA

Primary (writes)
  • Sync standby (failover)
  • Read replica (reports)
  • Connection pooler
  • Continuous backups (PITR)

Why it's built this way

  • Connection pooling in front of the database.
  • A standby automatic failover; 年 replicas reads. 年
  • Point-in-time recovery backups, tested regularly.

PostgreSQL & SQL cheat sheet

The essentials on one screen. Bookmark it for revision.

Queries

SelectSELECT a, b FROM t WHERE x > 1 ORDER BY a LIMIT 10;
GroupSELECT city, COUNT(*) FROM t GROUP BY city;
JoinFROM a JOIN b ON b.a_id = a.id
UpsertINSERT … ON CONFLICT (id) DO UPDATE SET …
WindowSUM(x) OVER (PARTITION BY g ORDER BY d)
CTEWITH t AS (SELECT …) SELECT … FROM t

Admin & performance

ExplainEXPLAIN ANALYZE SELECT …;
IndexCREATE INDEX ON orders (customer_id);
Index without lockingCREATE INDEX CONCURRENTLY …
Update statsANALYZE orders;
psql: list tables\dt
psql: describe table\d orders

Improve performance

  • Add indexes for WHERE, JOIN and ORDER BY columns, and verify with EXPLAIN ANALYZE.
  • Select only the columns you need; avoid SELECT * in apps.
  • Use connection pooling (e.g. PgBouncer) for many short connections.
  • Paginate with keyset pagination (WHERE id > last_id) for big tables instead of large OFFSETs.
  • Keep statistics fresh (autovacuum/ANALYZE) and archive old data.
  • Batch inserts and updates instead of one row at a time.

Security checklist

  • Always use parameterised queries to prevent SQL injection.
  • Give each app its own database user with least privileges (no superuser).
  • Enable TLS for connections; never expose port 5432 to the internet.
  • Use row-level security for multi-tenant data when appropriate.
  • Automate encrypted backups and test restores regularly.

Most important interview questions

Click a question to see a strong answer. Each step also has its own questions.

1an index and what are its trade-offs 是什么？

A data structure (usually B-tree) that speeds up lookups and sorting. It costs disk space and slows writes, so add indexes based on real query patterns.

2Explain the types of joins.

INNER returns matching rows; LEFT returns all left rows plus matches; RIGHT the reverse; FULL returns all rows from both; CROSS returns every combination.

3a deadlock and how do you avoid it 是什么？

Two transactions each wait for locks the other holds. Avoid by locking rows in a consistent order, keeping transactions short, and retrying on deadlock errors.

4How would you find and fix a slow query?

Find it (pg_stat_statements or logs), run EXPLAIN ANALYZE, look for sequential scans or bad estimates, add or adjust indexes, rewrite the query, and re-measure.

5MVCC 是什么？

Multi-version concurrency control: PostgreSQL keeps row versions so readers don't block writers; VACUUM later removes old versions.

6SQL vs NoSQL: when to choose which?

SQL for structured data, relationships and transactions (most apps). NoSQL for very flexible documents, huge scale key-value access, or specific patterns like caching.

Related 条学习路线

6

Backend Developer

6 steps · 5–6 months · Intermediate

Build the APIs, databases and services behind apps.

View roadmap →
6

Data Analyst

6 steps · 3–4 months · Beginner

Turn data into answers and dashboards.

View roadmap →
6

FastAPI

6 steps · 3–4 weeks · Intermediate

Build fast, documented Python APIs.

View roadmap →
6

RAG: Retrieval-Augmented Generation

6 steps · 3–4 weeks · Intermediate

Build AI that answers from your own documents.

View roadmap →
1

PostgreSQL & SQL · Step 1 of 6 · 1 week

SQL basics

Query and filter data.

SQL describes what data you want, and the database figures out how to get it. Practise on a dataset with real meaning, such as sales or movies.

What you'll learn

  • SELECT and WHERE
  • ORDER BY and LIMIT
  • Aggregates: COUNT, SUM, AVG
  • GROUP BY and HAVING
  • NULL handling

The order SQL actually runs in

  1. FROM / JOIN
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. SELECT
  6. ORDER BY
  7. LIMIT

Key concepts

表格
Rows (records) and columns (fields), like a strict spreadsheet.
WHERE vs HAVING
WHERE filters rows before grouping; HAVING filters groups after.
NULL
'Unknown'. Compare with IS NULL, not = NULL.

Step by step

  1. 1.1

    Install and connect

    Install PostgreSQL (or use a free hosted one) and connect with psql or a GUI.

  2. 1.2

    Load sample data

    Import a CSV of orders or movies into a table.

  3. 1.3

    Filter and sort

    SELECT … WHERE … ORDER BY … LIMIT.

  4. 1.4

    Aggregate

    COUNT, SUM, AVG with GROUP BY and HAVING.

SELECT city, COUNT(*) AS orders, ROUND(AVG(total), 2) AS avg_total
FROM orders
WHERE created_at >= '2026-01-01'
GROUP BY city
HAVING COUNT(*) > 100
ORDER BY orders DESC
LIMIT 10;

Notes

  • NULL is not equal to anything, including NULL: use IS NULL.

Tips & tricks

  • Install PostgreSQL locally or use a free hosted instance.

Interview questions

What's the difference between WHERE and HAVING?

WHERE filters individual rows before aggregation; HAVING filters aggregated groups after GROUP BY (e.g. HAVING COUNT(*) > 10).

COUNT(*) vs COUNT(column)?

COUNT(*) counts all rows; COUNT(column) counts rows where that column is not NULL.

The quick way to study this

Answer 30 questions about one dataset using only basic SQL.

2

PostgreSQL & SQL · Step 2 of 6 · 1 week

Joins and data modelling

Combine tables and design schemas.

Good schemas store each fact once and connect tables with keys. Joins bring them back together for queries.

What you'll learn

  • INNER, LEFT and FULL joins
  • Primary and foreign keys
  • One-to-many and many-to-many
  • Normalisation
  • Constraints

Common joins

INNER JOIN

  • Only rows that match
  • in both tables

LEFT JOIN

  • All rows from the left
  • + matches (or NULLs)

FULL JOIN

  • All rows from both
  • NULLs where no match

Key concepts

Primary key
A unique ID each row. 年
Foreign key
A column that points to a row in another table.
Normalisation
Storing each fact once to avoid contradictions.

Step by step

  1. 2.1

    Keys

    Primary key identifies a row; foreign key links to another table.

  2. 2.2

    Joins

    INNER (matches only), LEFT (all left rows), FULL (everything).

  3. 2.3

    Relationships

    One-to-many with a foreign key; many-to-many with a join table.

  4. 2.4

    Constraints

    NOT NULL, UNIQUE, CHECK, foreign keys keep data clean.

Notes

  • Check row counts after joins to catch duplicates.

Tips & tricks

  • Draw your tables and relationships before creating them.

Interview questions

How do you find customers with no orders?

SELECT c.* FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.id IS NULL;

normalisation and when would you denormalise 是什么？

Organising tables so each fact is stored once. You might denormalise for read speed (e.g. reporting tables) and accept extra work to keep copies in sync.

The quick way to study this

Design a schema for a shop (customers, products, orders, order_items) and query it.

3

PostgreSQL & SQL · Step 3 of 6 · 1 week

Advanced queries

CTEs, window functions and subqueries.

Window functions compute across related rows without collapsing them, perfect for rankings and month-over-month changes. They are a favourite interview topic.

What you'll learn

  • CTEs (WITH)
  • Window functions: ROW_NUMBER, RANK, LAG
  • Running totals
  • Subqueries
  • CASE expressions

GROUP BY vs window function

GROUP BY

  • One row per group
  • Loses individual rows
  • Totals per city

Window (OVER)

  • Keeps every row
  • Adds a calculated column
  • Each order + city total

Key concepts

Window function
A calculation across related rows without collapsing them into one.
PARTITION BY
Restart the window calculation for each group (e.g. per customer).
CTE
A named temporary result used to make long queries readable.

Step by step

  1. 3.1

    CTEs

    WITH monthly AS (…) SELECT … to break queries into steps.

  2. 3.2

    Window functions

    ROW_NUMBER(), RANK(), LAG(), SUM() OVER (…).

  3. 3.3

    CASE

    Conditional values inside queries.

SELECT customer_id, order_date, total,
       SUM(total) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_total,
       RANK() OVER (ORDER BY total DESC) AS size_rank
FROM orders;

Notes

  • CTEs make long queries readable.

Tips & tricks

  • Rewrite complex queries as a chain of CTEs.

Interview questions

How do you get the top 3 products per category?

Use ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) AS rn in a CTE, then filter WHERE rn <= 3.

RANK vs DENSE_RANK vs ROW_NUMBER?

ROW_NUMBER gives unique numbers; RANK leaves gaps after ties (1,1,3); DENSE_RANK doesn't (1,1,2).

The quick way to study this

Solve 15 window-function practice problems.

4

PostgreSQL & SQL · Step 4 of 6 · 4 days

Indexes and performance

Make queries fast.

An index is like a book's index: it lets PostgreSQL find rows without reading the whole table. EXPLAIN ANALYZE shows exactly how a query runs.

What you'll learn

  • B-tree indexes
  • EXPLAIN ANALYZE
  • Composite indexes
  • Partial indexes
  • When indexes hurt

With and without an index

No index

  • Seq Scan on orders
  • Reads all 10M rows
  • Seconds

Index on customer_id

  • Index Scan
  • Reads only matching rows
  • Milliseconds

Key concepts

Index
A sorted lookup structure, like a book's index, that avoids scanning every row.
Sequential scan
Reading the whole table: fine for small tables, slow for big ones.
Write cost
Every index slows inserts and updates a little.

Step by step

  1. 4.1

    See the plan

    EXPLAIN ANALYZE your slow query.

  2. 4.2

    Add the right index

    On columns in WHERE, JOIN and ORDER BY.

  3. 4.3

    Composite and partial indexes

    Multi-column for combined filters; partial for a subset (WHERE active).

  4. 4.4

    Re-check

    Run EXPLAIN again and compare the time.

Notes

  • Indexes speed reads but slow writes; add them where queries need them.

Tips & tricks

  • Index foreign keys and columns used in WHERE and ORDER BY.

Interview questions

Why might PostgreSQL ignore your index?

The query returns a large share of the table (a scan is cheaper), the column is wrapped in a function, types don't match, or statistics are outdated (run ANALYZE).

How does a composite index's column order matter?

An index on (a, b) helps filters on a or a and b, but not on b alone. Put the most commonly filtered or most selective column first.

The quick way to study this

Load a million rows, time a query, add an index and compare.

5

PostgreSQL & SQL · Step 5 of 6 · 4 days

Transactions, JSON and extensions

PostgreSQL's power features.

Transactions keep data consistent: all changes succeed or none do. JSONB lets you store flexible documents inside a relational database, with indexes.

What you'll learn

  • Transactions and ACID
  • Isolation levels basics
  • JSONB columns
  • Full-text search
  • Extensions

A money transfer transaction

  1. BEGIN
  2. Subtract from A
  3. Add to B
  4. Both OK?
  5. COMMIT (else ROLLBACK)

Key concepts

ACID
Atomic, Consistent, Isolated, Durable: all or nothing, always valid, safe under concurrency, saved permanently.
JSONB
Binary JSON column type that can be indexed and queried.
Isolation level
How much concurrent transactions can see each other's changes.

Step by step

  1. 5.1

    Transactions

    BEGIN … COMMIT, or ROLLBACK on error.

  2. 5.2

    JSONB

    Store flexible attributes; query with ->> and index with GIN.

  3. 5.3

    Full-text search

    to_tsvector and to_tsquery keyword search. 年

Notes

  • Wrap multi-step changes (like money transfers) in a transaction.

Tips & tricks

  • Use JSONB for flexible attributes, but keep core fields as real columns.

Interview questions

What does ACID mean?

Atomicity (all or nothing), Consistency (rules always hold), Isolation (concurrent transactions don't interfere), Durability (committed data survives crashes).

The quick way to study this

Build a table with a JSONB column and query inside it.

6

PostgreSQL & SQL · Step 6 of 6 · 3 days

pgvector for AI

Vector search inside PostgreSQL.

pgvector adds vector columns and similarity search to PostgreSQL, so you can build RAG apps without a separate vector database.

What you'll learn

  • Installing pgvector
  • Storing embeddings
  • Similarity search
  • Vector indexes (HNSW)
  • Combining vector and keyword search

Vector search in SQL

  1. Question embedding
  2. WHERE filters (user, doc type)
  3. ORDER BY distance
  4. LIMIT 5
  5. Chunks for RAG

Key concepts

pgvector
Extension that adds vector columns and similarity search to PostgreSQL.
<=> operator
Cosine distance (smaller = more similar).
HNSW
Fast approximate nearest-neighbour index.

Step by step

  1. 6.1

    Enable pgvector

    CREATE EXTENSION vector; and add a vector(n) column.

  2. 6.2

    Store embeddings

    Insert each chunk's text, metadata and embedding.

  3. 6.3

    搜索

    ORDER BY embedding <=> $1 LIMIT 5 cosine distance. 年

  4. 6.4

    Index

    Add an HNSW index when the table grows.

CREATE EXTENSION IF NOT EXISTS vector;
CREATE TABLE chunks (id bigserial PRIMARY KEY, content text, embedding vector(1536));

-- nearest 5 chunks to a query embedding
SELECT id, content FROM chunks
ORDER BY embedding <=> $1
LIMIT 5;

Notes

  • Filter by metadata (user, document) in the same query as similarity search.

Tips & tricks

  • Start without an index; add HNSW once you have many rows.

Interview questions

Why use pgvector instead of a separate vector database?

One database for app data, metadata, permissions and vectors: simpler, transactional and filterable with SQL. Dedicated vector DBs may scale further for very large collections.

The quick way to study this

Store embeddings for 1,000 text chunks and run similarity queries.

RAG deep dive →

分享此页