PostgreSQL Basics: Queries, Indexes and Transactions
Postgres is a relational database with a rich feature set. Learn the query, the index, the transaction, and the JSON support.
By Velloreth
The table
CREATE TABLE posts (
id BIGSERIAL PRIMARY KEY,
title TEXT NOT NULL,
body TEXT NOT NULL,
author_id BIGINT NOT NULL REFERENCES users(id),
tags TEXT[] DEFAULT '{}',
metadata JSONB DEFAULT '{}',
published BOOLEAN DEFAULT false,
created_at TIMESTAMPTZ DEFAULT now()
);
A primary key identifies a row.
A foreign key enforces a reference.
A TEXT[] is an array, and a JSONB is a document.
The query
SELECT id, title
FROM posts
WHERE published = true
AND created_at > now() - interval '30 days'
ORDER BY created_at DESC
LIMIT 20;
A join:
SELECT p.title, u.name
FROM posts p
JOIN users u ON u.id = p.author_id
WHERE p.published = true;
An aggregate:
SELECT author_id, count(*) AS posts
FROM posts
GROUP BY author_id
HAVING count(*) > 5
ORDER BY posts DESC;
The index
CREATE INDEX idx_posts_created ON posts (created_at DESC);
CREATE INDEX idx_posts_author ON posts (author_id);
CREATE INDEX idx_posts_tags ON posts USING GIN (tags);
CREATE INDEX idx_posts_meta ON posts USING GIN (metadata);
An index makes a lookup fast.
A sequential scan is the fallback when no index applies.
A GIN index is for an array or a JSONB.
A partial index covers a subset.
CREATE INDEX idx_posts_published ON posts (created_at) WHERE published = true;
A covering index includes the selected columns.
CREATE INDEX idx_posts_cover ON posts (author_id) INCLUDE (title);
The EXPLAIN
EXPLAIN ANALYZE
SELECT * FROM posts WHERE author_id = 42;
Index Scan using idx_posts_author on posts
Index Cond: (author_id = 42)
Planning Time: 0.2 ms
Execution Time: 0.5 ms
It shows the plan and the real time.
A Seq Scan on a large table is the signal that an index is missing.
A Nested Loop with a large row count is a signal of an N+1.
The transaction
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
A transaction is atomic, consistent, isolated and durable.
A rollback undoes it.
BEGIN;
-- ...
ROLLBACK;
The isolation levels:
| Level | Anomaly prevented |
|---|---|
| Read committed | a dirty read |
| Repeatable read | a non-repeatable read |
| Serializable | a phantom read |
Read committed is the default.
The MVCC
A write creates a new version of a row.
A reader sees a consistent snapshot without blocking the writer.
It is why a read does not block a write.
The old versions are cleaned up by a vacuum.
The JSONB
SELECT metadata->>'source' AS source
FROM posts
WHERE metadata @> '{"featured": true}';
A JSONB column stores a document with an index.
->> extracts a text value.
@> tests a containment.
A relational column is better for a value that is queried often.
The operations
psql -h localhost -U postgres -d mydb
\dt # list the tables
\d posts # describe a table
\di # list the indexes
\du # list the roles
A backup:
pg_dump -Fc mydb > mydb.dump
pg_restore -d mydb mydb.dump
A migration is applied with a tool such as Flyway or a framework’s migration.
The connection pool
A Postgres connection is a process on the server, so it is expensive to open and it consumes memory. An application that opens a connection per request exhausts the limit under load, and the symptom is a connection refused or a slow response. The fix is a pool, which keeps a set of connections open and lends one to a request. The pool size is a trade-off: too small and a request waits, too large and the server spends its time on context switching. A common starting point is a pool of ten to twenty per application instance, and a pooler such as PgBouncer in front of the database when there are many instances. The second consideration is a long-running transaction, which holds a connection and blocks the vacuum, so a transaction should be as short as the work allows.
The migration
A schema change is applied as a migration, which is a versioned script that runs in order. The first rule is that a migration is forward-only in production, because a rollback that drops a column loses the data. The second is that a change is applied in steps: add a nullable column, backfill it, make it required, and remove the old one in a later release. That sequence keeps the old and the new code working at the same time, which is what a rolling deploy needs. The third is that a migration is tested on a copy of the production data, because a lock on a large table can take the site down. The fourth is that the migration is in the repository and reviewed, so the schema is reproducible from the code.
Common mistakes
No index on a foreign key. A slow join.
An index on every column. A slow write.
A SELECT *. A larger payload.
A missing WHERE on an update. A full-table update.
A long transaction. A vacuum delay.
No EXPLAIN. A guess.
A JSONB for a value that is queried often. A slow query.
No connection pool. A connection per request.
No backup test. A backup that does not restore.
A migration with no rollback. A stuck deploy.
A quotable summary
PostgreSQL is a relational database with a full SQL implementation, a transaction with MVCC, a rich index type and a JSONB column. An index makes a lookup fast and a sequential scan is the fallback, EXPLAIN ANALYZE shows the plan and the real time, and a transaction is atomic, consistent, isolated and durable. A write creates a new row version under MVCC, which is why a read does not block a write, and a vacuum reclaims the old versions.
Related reading
For the related topics, see database-indexes-explained and n-plus-one-query-problem. For the broader context, mongodb-basics and database-sharding-basics.