8. PostgreSQL¶

Intermediate → Advanced · 16 min read

PostgreSQL ("Postgres") is the default database of most new GenAI stacks: one database holds your users, documents, chat history, LLM logs and embeddings (with pgvector), with real transactions and row-level security. Supabase, Neon, AWS RDS / Aurora, Google Cloud SQL / AlloyDB and Azure all offer it as a managed service.

This topic covers what makes Postgres different from the standard SQL in topics 1–4, and the features worth knowing for GenAI work and interviews.

Version and how to run these examples

Written for PostgreSQL 17 (almost everything works on 15+). Unlike topics 1–6, these examples aren't run on this site's SQLite: start a free local server with Docker and run them yourself. This image includes pgvector:

docker run --name pg -e POSTGRES_PASSWORD=pass -p 5432:5432 -d pgvector/pgvector:pg17
docker exec -it pg psql -U postgres                # interactive shell

8.1 Connect from Python¶

# pip install "psycopg[binary]"          (psycopg 3)
import psycopg
from psycopg.rows import dict_row

with psycopg.connect("postgresql://postgres:pass@localhost:5432/postgres",
                     row_factory=dict_row) as conn:
    rows = conn.execute("SELECT id, name FROM customers WHERE city = %s", ("Pune",)).fetchall()
    conn.execute("UPDATE customers SET plan = %s WHERE id = %s", ("pro", 1))
# leaving the with-block COMMITs (or rolls back if an exception was raised)

Need	Use
async code (FastAPI)	`psycopg.AsyncConnection` or `asyncpg` (placeholders `$1, $2`)
many short connections	a pool: `psycopg_pool.ConnectionPool`, or PgBouncer in front of the server
SQLAlchemy URL	`postgresql+psycopg://postgres:pass@localhost:5432/postgres`
bulk load	`COPY` (`cur.copy("COPY t FROM STDIN")`): far faster than many `INSERT`s

8.2 Data types that matter¶

CREATE TABLE customers (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,   -- modern auto-id (not serial)
    public_id   uuid NOT NULL DEFAULT gen_random_uuid(),          -- built in since 13
    email       text NOT NULL UNIQUE,
    name        text NOT NULL,
    plan        text NOT NULL DEFAULT 'free' CHECK (plan IN ('free', 'pro', 'team')),
    balance     numeric(12, 2) NOT NULL DEFAULT 0,
    tags        text[] NOT NULL DEFAULT '{}',                     -- arrays are real types
    profile     jsonb,
    created_at  timestamptz NOT NULL DEFAULT now()
);

Type	Use it for	Note
`text`	strings	same speed as `varchar(n)`; add a `CHECK` if you need a limit
`timestamptz`	every timestamp	stores an absolute instant (UTC); shown in the session time zone
`timestamp`	rarely	no time zone: "18:30" in which city?
`numeric(p, s)`	money	exact
`uuid`	public ids	`gen_random_uuid()`; Postgres 18 adds time-ordered `uuidv7()`
`jsonb`	flexible attributes	binary, indexable; prefer it over `json`
`text[]`, `int[]`	small lists (tags)	`'refund' = ANY(tags)`, `tags @> ARRAY['refund']`
`tstzrange`, `daterange`	periods	with exclusion constraints: no overlapping bookings (8.6)
`vector(n)`	embeddings	from pgvector (8.8)

Identifiers fold to lower case

CREATE TABLE Users (...) creates users. Quote it ("Users") and you must quote it forever after. Stick to snake_case names and never quote.

8.3 Features you'll use every day¶

RETURNING: get back what an INSERT, UPDATE or DELETE wrote, in the same round-trip:

INSERT INTO conversations (user_id, title) VALUES (42, 'Refund question')
RETURNING id, created_at;

Upsert with ON CONFLICT:

INSERT INTO daily_usage (user_id, day, calls, tokens)
VALUES (42, CURRENT_DATE, 1, 380)
ON CONFLICT (user_id, day) DO UPDATE
SET calls  = daily_usage.calls  + EXCLUDED.calls,     -- EXCLUDED = the row you tried to insert
    tokens = daily_usage.tokens + EXCLUDED.tokens;

INSERT INTO seen_urls (url) VALUES ($1) ON CONFLICT DO NOTHING;   -- dedupe on ingest

MERGE (15+) handles insert, update and delete from a source table in one statement; 17 adds RETURNING to it.

DISTINCT ON: the first row of each group, without a window function:

SELECT DISTINCT ON (customer_id) customer_id, id, amount, created_at
FROM orders
ORDER BY customer_id, created_at DESC;     -- latest order per customer

FILTER: conditional aggregates without CASE:

SELECT model,
       count(*)                                  AS calls,
       count(*) FILTER (WHERE status <> 'ok')    AS failures,
       percentile_cont(0.95) WITHIN GROUP (ORDER BY latency_ms) AS p95_ms
FROM llm_calls
GROUP BY model;

generate_series: a calendar to fill gaps (days with zero sales):

SELECT d::date AS day, coalesce(sum(o.amount), 0) AS revenue
FROM generate_series('2026-10-01'::date, '2026-10-31', interval '1 day') AS d
LEFT JOIN orders o ON o.created_at >= d AND o.created_at < d + interval '1 day'
GROUP BY d ORDER BY d;

LATERAL: a subquery per row, e.g. the top 3 orders of each customer:

SELECT c.name, o.id, o.amount
FROM customers c
CROSS JOIN LATERAL (
    SELECT id, amount FROM orders
    WHERE customer_id = c.id
    ORDER BY amount DESC LIMIT 3
) AS o;

8.4 Text, dates and time zones¶

Need	PostgreSQL
Case-insensitive match	`name ILIKE '%priya%'`
Regex	`email ~ '@gmail\.com$'` · `~*` case-insensitive · `regexp_replace(s, '\s+', ' ', 'g')`
Split	`split_part(email, '@', 2)` · `string_to_array(s, ',')`
List per group	`string_agg(name, ', ' ORDER BY name)`
Truncate to a month	`date_trunc('month', created_at)`
Parts	`extract(dow FROM created_at)`, `extract(epoch FROM t2 - t1)` (seconds)
Local time	`created_at AT TIME ZONE 'Asia/Kolkata'`
Integer division	`7 / 2` = 3; `7 / 2.0` or `7::numeric / 2` = 3.5
Cast	`'42'::int`, `created_at::date`

Group by the user's day, not the server's

date_trunc('day', created_at AT TIME ZONE 'Asia/Kolkata') groups by Indian calendar days. Without it, a chat at 01:00 IST lands on the previous UTC day: a classic off-by-one in usage dashboards.

8.5 `jsonb`: query and index semi-structured data¶

SELECT id,
       meta -> 'tokens'              AS tokens_json,   -- jsonb
       meta ->> 'feature'            AS feature,       -- text
       (meta #>> '{tokens,in}')::int AS tokens_in      -- nested path as text, then cast
FROM llm_calls
WHERE meta @> '{"feature": "chat"}';                   -- containment: can use a GIN index

SELECT id FROM tickets WHERE meta ? 'urgent';           -- key exists
SELECT jsonb_array_elements_text(meta -> 'tags') AS tag, count(*)
FROM tickets GROUP BY tag;                              -- array → rows

CREATE INDEX idx_llm_meta ON llm_calls USING gin (meta jsonb_path_ops);   -- serves @> queries
CREATE INDEX idx_llm_feature ON llm_calls ((meta ->> 'feature'));         -- serves = on one key

jsonb_path_ops makes a smaller, faster index that supports @> only; the default jsonb_ops also supports ?.

8.6 Constraints that do real work¶

-- no two bookings of the same room may overlap
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE bookings (
    room   text NOT NULL,
    during tstzrange NOT NULL,
    EXCLUDE USING gist (room WITH =, during WITH &&)       -- && = "overlaps"
);

-- partial unique index: only one ACTIVE subscription per user
CREATE UNIQUE INDEX one_active_sub ON subscriptions (user_id) WHERE status = 'active';

The database enforces these rules even when two requests race each other: an application-level "check, then insert" can't promise that.

8.7 Full-text search¶

ALTER TABLE docs ADD COLUMN tsv tsvector
    GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || body)) STORED;
CREATE INDEX idx_docs_tsv ON docs USING gin (tsv);

SELECT id, title, ts_rank(tsv, q) AS score
FROM docs, websearch_to_tsquery('english', 'refund -shipping') AS q
WHERE tsv @@ q
ORDER BY score DESC
LIMIT 5;

to_tsvector('english', …) stems words, so "refunds" and "refunded" match "refund". websearch_to_tsquery accepts Google-style input ("exact phrase", -exclude, or). For typo-tolerant matching, add the pg_trgm extension and a trigram GIN index, which also speeds up ILIKE '%term%'.

8.8 pgvector: embeddings next to your data¶

CREATE EXTENSION IF NOT EXISTS vector;
CREATE TABLE chunks (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tenant_id  bigint NOT NULL,
    doc_id     bigint NOT NULL,
    content    text NOT NULL,
    embedding  vector(1536) NOT NULL
);
CREATE INDEX ON chunks USING hnsw (embedding vector_cosine_ops);

SELECT id, content, 1 - (embedding <=> $1) AS similarity   -- <=> cosine distance
FROM chunks
WHERE tenant_id = $2
ORDER BY embedding <=> $1                                   -- order by the operator → index used
LIMIT 5;

Operator	Distance	Index ops class
`<=>`	cosine	`vector_cosine_ops`
`<->`	Euclidean (L2)	`vector_l2_ops`
`<#>`	negative inner product	`vector_ip_ops`

Index	Strengths	Watch out
HNSW	best speed/recall; no training step	more memory; slower to build
IVFFlat	smaller, faster to build	build after loading data; tune `lists` and `ivfflat.probes`

Filters and approximate indexes

With a selective filter (one small tenant), HNSW may find fewer than LIMIT matching rows. Raise SET hnsw.ef_search = 100;, or turn on iterative scans (pgvector 0.8+: SET hnsw.iterative_scan = relaxed_order;). For very large tenants, partition by tenant. Use halfvec to halve the storage of large embeddings.

Combine it with 8.7 to get hybrid search: run both queries and merge the ranks with Reciprocal Rank Fusion (1 / (60 + rank)), as shown in the free SQL Interview Cheat Sheet.

8.9 Row-level security for multi-tenant RAG¶

Make the database itself refuse to return another tenant's chunks, even if the app (or an LLM-written query) forgets the WHERE tenant_id = …:

ALTER TABLE chunks ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON chunks
    USING (tenant_id = current_setting('app.tenant_id')::bigint);

-- per request, inside the transaction:
SET LOCAL app.tenant_id = '42';
SELECT count(*) FROM chunks;        -- only tenant 42's rows are visible

Policies don't apply to the table owner or superusers: connect the app as a separate, non-owner role. For text-to-SQL, give that role SELECT only and set statement_timeout.

8.10 Performance: `EXPLAIN ANALYZE`, MVCC and VACUUM¶

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42 AND created_at >= now() - interval '30 days';

Plan node	Meaning
`Seq Scan`	reads the whole table
`Index Scan`	index, then the table for each match
`Index Only Scan`	answered from the index alone (covering index, `INCLUDE`)
`Bitmap Heap Scan`	collects many matches from an index, then reads the pages in order
`Nested Loop` / `Hash Join` / `Merge Join`	the three join strategies

Compare rows= (estimate) with actual rows=: a big gap means stale statistics, so run ANALYZE table. Find the queries that cost the most in total with the pg_stat_statements extension.

More index tools: partial indexes (WHERE status = 'open'), expression indexes (lower(email)), covering indexes (INCLUDE (amount)), BRIN for huge append-only time-ordered tables, and CREATE INDEX CONCURRENTLY to build one without blocking writes.

MVCC: an UPDATE writes a new version of the row; the old one stays until no transaction can see it. Readers never block writers. VACUUM (run automatically by autovacuum) cleans up the dead versions. If long-running transactions hold it back, tables bloat and queries slow down, so don't leave transactions idle.

8.11 Transactions and concurrency¶

The default isolation is READ COMMITTED: each statement sees data committed before it began. Under SERIALIZABLE, Postgres detects anomalies and aborts one transaction with SQLSTATE 40001. Your code must retry it.

-- a job queue many workers can poll safely
UPDATE jobs SET status = 'running', started_at = now()
WHERE id = (
    SELECT id FROM jobs WHERE status = 'queued'
    ORDER BY id
    FOR UPDATE SKIP LOCKED
    LIMIT 1
)
RETURNING id, payload;

SET statement_timeout = '5s';                       -- kill runaway queries (great for text-to-SQL)
SET idle_in_transaction_session_timeout = '30s';    -- don't let idle transactions block VACUUM

Big tables: declarative partitioning (PARTITION BY RANGE (created_at)) lets queries skip old partitions, and dropping an old month is instant instead of a huge DELETE.

8.12 PostgreSQL interview checklist¶

Use timestamptz, identity columns and jsonb, and explain why.
Write an upsert with ON CONFLICT … DO UPDATE using EXCLUDED.
Latest row per group three ways: DISTINCT ON, ROW_NUMBER(), LATERAL.
Index jsonb with GIN and use @>.
Build vector search with pgvector and HNSW, and handle filtered queries.
Isolate tenants with row-level security.
Read EXPLAIN (ANALYZE, BUFFERS); explain MVCC, VACUUM and bloat.
Build a queue with FOR UPDATE SKIP LOCKED.

Interview questions¶

Why do so many GenAI teams store embeddings in Postgres instead of a vector database?

One system to run, back up and secure; vectors join directly with documents, permissions and metadata in SQL; transactions keep chunks and embeddings consistent; and row-level security handles multi-tenancy. pgvector with HNSW comfortably serves millions of vectors. A dedicated vector database earns its place at very large scale, at very high query rates, or when you need its built-in hybrid search and sharding.

What is MVCC, and what problem does VACUUM solve?

Multi-version concurrency control keeps several versions of a row so readers see a consistent snapshot without locking writers. Updates and deletes leave dead versions behind; VACUUM marks their space as reusable and updates visibility information, and autovacuum runs it automatically. Long-running transactions stop it from cleaning up, which causes bloat.

DISTINCT ON vs ROW_NUMBER()?

Both return one row per group. DISTINCT ON (key) … ORDER BY key, sort_col is shorter and often faster, but it exists only in Postgres and returns exactly one row per group. ROW_NUMBER() is portable, and with RANK() you can keep ties or the top N.

How would you stop a text-to-SQL feature from hurting the production database?

Run it as a read-only role with access only to approved views, with row-level security for the tenant, a statement_timeout, a row LIMIT and EXPLAIN checks before execution. Point it at a read replica, and log every generated query.

Practice¶

Load 10,000 random 384-dimension vectors into chunks, then compare query time and results with and without the HNSW index.
Add RLS to chunks, connect as a non-owner role, and prove tenant 1 can't see tenant 2's rows.
Use EXCLUDE USING gist so a double booking fails, then try to insert one.

Next: SQL Server — T-SQL for enterprise data.