Skip to content
Velloreth
Technology 5 min read

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

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:

LevelAnomaly prevented
Read committeda dirty read
Repeatable reada non-repeatable read
Serializablea 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.

For the related topics, see database-indexes-explained and n-plus-one-query-problem. For the broader context, mongodb-basics and database-sharding-basics.

Frequently asked questions

What is PostgreSQL?
An open-source relational database with a strong feature set: a full SQL implementation, a transaction with MVCC, a rich index type, a JSONB column, a full-text search, and an extension system. It is the common default for a new application that needs a relational database.
What is the difference between a primary key and a unique index?
A primary key is a unique, not-null constraint that identifies a row, and a table has one. A unique index enforces uniqueness on one or more columns, and a table can have several. A primary key is implemented with a unique index.
What is MVCC?
Multi-version concurrency control, where a write creates a new version of a row rather than overwriting it, so a reader sees a consistent snapshot without blocking the writer. It is why a read does not block a write in Postgres. The old versions are cleaned up by a vacuum.
What is EXPLAIN ANALYZE?
A command that runs the query and shows the plan with the real time and the row counts. It is the main tool for a slow query, because it shows whether an index was used and where the time went. EXPLAIN without ANALYZE shows the plan without running the query.
What is a JSONB column?
A column that stores a JSON document in a binary form, which supports an index and a query into the document. It is useful for a flexible attribute, and a relational column is better for a value that is queried often. JSONB is the indexed form, and JSON is the text form.
What is a vacuum?
A background process that reclaims the space of the old row versions that MVCC leaves behind, and updates the statistics the planner uses. An autovacuum runs automatically, and a manual vacuum is used after a large change. A table that is not vacuumed grows and slows.