PostgreSQL

What is PostgreSQL?

PostgreSQL is an open-source relational database with a reputation for doing the boring things correctly. It stores data in tables, enforces rules with constraints, wraps changes in real transactions and exposes all of it through standard SQL. It has been developed continuously since the 1980s and is governed by a broad community rather than a single company.

That longevity shows up in the details. Postgres has the richest type system of any mainstream database, an extension mechanism that lets third parties add whole new capabilities, and a query planner that handles everything from a simple point lookup to a windowed analytical query. It is the default relational database for new applications at most companies, and it is available as a managed service almost everywhere.

If you are choosing one database to learn deeply, this is the one that repays the time.

Why teams choose Postgres

Three properties explain most of its popularity.

It is correct by default. Constraints are enforced in the engine, not in application code. A CHECK cannot be bypassed by a buggy service, a FOREIGN KEY cannot be ignored by a batch job, and a UNIQUE index cannot be raced by two concurrent requests. Data integrity becomes a property of the schema.

It is extensible. Rather than bake every feature into the core, Postgres exposes hooks for new types, operators, index methods and procedural languages. That is how PostGIS, pgvector, TimescaleDB and dozens of other projects exist as extensions rather than forks.

It speaks standard SQL. Skills and queries transfer between databases, ORMs and tools. You are not learning a proprietary dialect to get started.

The relational model, briefly

A relational database stores data in tables made of rows and columns. Each table has a primary key that uniquely identifies a row, and relationships are expressed with foreign keys that point at other tables. The goal is to store each fact once and let joins reassemble it.

Consider users and orders. Rather than repeat a customer’s email on every order, you store the email once in users and reference the user from orders by user_id. This is normalization, and it prevents the classic anomaly where one row is updated and a thousand others still hold the old value.

Normalization is not a religion. Third normal form is the sensible default, and deliberate denormalization — a cached order_count, a materialized view — is a performance decision you make later, with measurements in hand.

Data types that earn their keep

Postgres offers more types than most projects need, but a handful matter constantly:

• text — variable-length string with no arbitrary limit. Prefer it to varchar(n) unless the limit is a real business rule.
• integer and bigint — whole numbers. Use bigint for anything that could grow without bound, such as IDs from a sequence.
• numeric(p, s) — exact decimal arithmetic. This is the correct type for money, not real or double precision.
• timestamptz — a timestamp stored in UTC with time-zone awareness. Always prefer it to timestamp for application data.
• uuid — a 128-bit identifier, useful when clients generate IDs or when you do not want to leak counts.
• jsonb — binary JSON you can index and query.
• Arrays and composite types — text[], integer[] and custom row types, handy for tags and small ordered lists.

The money example is worth internalising: 0.1 + 0.2 is not 0.3 in binary floating point. Store amounts as integer minor units (total_cents) or as numeric, never as float.

Creating tables and constraints

A schema is a contract. Every constraint you declare is a class of bug you cannot ship.

CREATE TABLE users (
  id         bigserial PRIMARY KEY,
  email      text NOT NULL UNIQUE,
  name       text NOT NULL CHECK (length(name) > 0),
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE orders (
  id          bigserial PRIMARY KEY,
  user_id     bigint NOT NULL REFERENCES users (id) ON DELETE CASCADE,
  status      text NOT NULL DEFAULT 'pending'
                CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled')),
  total_cents integer NOT NULL CHECK (total_cents >= 0),
  created_at  timestamptz NOT NULL DEFAULT now(),
  metadata    jsonb NOT NULL DEFAULT '{}'::jsonb
);

The important choices here are deliberate. NOT NULL rules out a whole family of missing-data bugs. UNIQUE on email enforces the invariant at the database, which is the only place a race cannot defeat it. The CHECK on status documents and enforces the state machine. ON DELETE CASCADE decides what happens when a user disappears, instead of leaving orphans.

Prefer adding constraints in the same migration that creates the table. Adding a NOT NULL or a foreign key later means first cleaning up whatever invalid rows accumulated while the rule was absent.

Querying with joins

Most real queries combine tables. A join matches rows from two relations on a condition, and the choice of join type decides what happens to unmatched rows.

SELECT u.email,
       o.id,
       o.total_cents
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.status = 'paid'
ORDER BY o.created_at DESC
LIMIT 50;

JOIN (or INNER JOIN) keeps only matching pairs. LEFT JOIN keeps every row on the left and fills the right with NULL when nothing matches — the standard way to ask “which users have no orders”:

SELECT u.email, count(o.id) AS orders
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.email
HAVING count(o.id) = 0;

Aggregates like count, sum, avg, min and max collapse groups of rows into one. Every column in the SELECT that is not inside an aggregate must appear in the GROUP BY. The WHERE clause filters rows before grouping; HAVING filters groups afterward. Mixing them up is a common source of confusing results.

Indexes and EXPLAIN ANALYZE

An index is a sorted structure that lets the planner find rows without scanning the whole table. The default is a B-tree, which serves equality and range predicates and supports ORDER BY directly.

CREATE INDEX orders_user_created_idx
  ON orders (user_id, created_at DESC);

A composite index like this covers queries that filter on user_id and sort by created_at. Column order matters: the leftmost column must appear in the query for the index to be useful for filtering. This is the leftmost-prefix rule, and it is why index design starts from the queries, not the columns.

Other index types cover different shapes:

• GIN — inverted indexes for jsonb, arrays and full-text search (tsvector).
• GiST — geometry, ranges and nearest-neighbour search, used heavily by PostGIS.
• BRIN — tiny indexes over naturally ordered data such as append-only timestamp tables.
• Hash — equality-only lookups, rarely needed since B-tree handles them.

Never guess whether an index is used. Ask the planner:

EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 10;

The output shows the plan tree, the estimated cost, and — with ANALYZE — the actual rows and time.

Limit  (cost=0.43..12.94 rows=10 width=48)
       (actual time=0.031..0.079 rows=10 loops=1)
  ->  Index Scan Backward using orders_user_created_idx on orders
        (cost=0.43..521.10 rows=417 width=48)
        (actual time=0.029..0.070 rows=10 loops=1)
        Index Cond: (user_id = 42)
Planning Time: 0.184 ms
Execution Time: 0.108 ms

Read it from the inside out. Here the Index Scan with Index Cond: (user_id = 42) confirms the composite index is doing its job. Look for Seq Scan on a large table where you expected an Index Scan, and for a large gap between estimated and actual rows, which usually means stale statistics. Run ANALYZE orders; to refresh them, and EXPLAIN (ANALYZE, BUFFERS) to see how much data was read from cache versus disk.

Transactions and MVCC

A transaction groups statements so they either all take effect or none do. Postgres implements transactions with MVCC: instead of locking rows for readers, it keeps multiple versions and gives each transaction a snapshot.

BEGIN;
UPDATE accounts SET balance_cents = balance_cents - 5000 WHERE id = 1;
UPDATE accounts SET balance_cents = balance_cents + 5000 WHERE id = 2;
COMMIT;

If anything fails, ROLLBACK undoes the whole transaction. Application code should wrap multi-step writes in a transaction and be prepared to retry on serialization failures when using the stricter isolation levels.

Isolation levels decide what a transaction can observe:

• Read Committed (default) — each statement sees a fresh snapshot; good for most workloads.
• Repeatable Read — the whole transaction sees one snapshot; useful when you read the same rows repeatedly.
• Serializable — transactions behave as if run one after another; the strongest guarantee, and the one most likely to require retries.

MVCC has a cost: updated and deleted rows leave dead tuples behind. Autovacuum reclaims them in the background. Long-running transactions hold back cleanup and bloat tables, so keep transactions short and monitor vacuum on high-churn tables.

Connection pooling with PgBouncer

Postgres handles each client connection with a separate operating-system process. That is robust, but it means connections are heavier than in MySQL, and a few hundred idle clients can consume meaningful memory. The practical limit is max_connections, and exceeding it produces errors, not graceful degradation.

The fix is a connection pooler. PgBouncer sits between the application and Postgres, keeps a small pool of real connections, and multiplexes many client connections onto them. It offers three modes:

• Session pooling — a server connection is held for the client’s whole session.
• Transaction pooling — a server connection is held only for a transaction; the most common choice for web apps.
• Statement pooling — a connection is held for one statement; the most aggressive and the most restrictive.

Transaction pooling breaks session-scoped features such as SET, advisory locks held across statements and LISTEN. Use SET LOCAL inside transactions and check that your ORM behaves well in transaction mode.

Whatever you choose, pool on the application side too. A pool per process with a sane maximum, plus a pooler in front, is the standard shape. Never open a new connection per request.

JSONB: when to use it

jsonb stores JSON in a decomposed binary form that supports indexing and a rich set of operators. It is genuinely useful, and it is also easy to overuse.

SELECT id, metadata->>'coupon' AS coupon
FROM orders
WHERE metadata @> '{"channel": "mobile"}';

The @> containment operator can use a GIN index:

CREATE INDEX orders_metadata_idx ON orders USING gin (metadata);

Use JSONB for data whose shape is genuinely variable: webhook payloads, third-party responses, user-defined attributes, feature flags. Do not use it to avoid writing a migration. Fields you filter, join, constrain or aggregate should be columns with real types. The test is simple: if you find yourself casting metadata->>'price' to a number in most queries, it should have been an integer.

A middle path is a hybrid: stable fields as columns, everything optional in a JSONB attributes bag. That gives you constraints and indexes where they matter and flexibility where they do not.

Extensions that change what’s possible

CREATE EXTENSION installs a bundled module into a database. A few are worth knowing by name:

• pg_stat_statements — records normalized query text with timing and I/O, the first thing to enable when investigating performance.
• PostGIS — geographic types and functions; the reason many teams choose Postgres for location data.
• pgvector — vector columns and approximate nearest-neighbour indexes for embeddings and semantic search.
• pgcrypto — cryptographic functions such as gen_random_uuid() on older versions.
• pg_trgm — trigram indexes for fast LIKE and fuzzy matching.

Enable only what you use, and remember that managed providers often require a setting or a support request before an extension can be installed.

Roles and permissions

Postgres separates roles (which can log in or own objects) from privileges (what a role may do). Grant the least privilege that works.

CREATE ROLE app_user LOGIN PASSWORD 'secret';

GRANT CONNECT ON DATABASE shop TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE
  ON ALL TABLES IN SCHEMA public TO app_user;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;

The ALTER DEFAULT PRIVILEGES line matters because it applies to tables created later, not just existing ones. Avoid connecting as the database owner or a superuser from the application; a compromised app should not be able to DROP TABLE. For migrations, use a separate role with DDL rights.

Backups and recovery

Backups come in two flavours. Logical backups use pg_dump to produce a portable script or archive of one database, and pg_dumpall to capture roles and globals. They are simple and version-tolerant, but slower to restore at scale.

pg_dump --format=custom --file=shop.dump shop
pg_restore --dbname=shop_restore shop.dump

Physical backups copy the data directory and the write-ahead log. pg_basebackup plus continuous WAL archiving enables point-in-time recovery, letting you restore to a specific moment before a bad migration. This is the approach managed providers use.

Whichever you pick, the rule is the same: automate it, store it off the primary host, and regularly restore it into a throwaway database. A backup you have never restored is a hope, not a plan.

Full-text search without another service

Postgres has real full-text search built in, which is often enough to avoid running a separate search cluster. It works by converting text into a tsvector of lexemes and queries into a tsquery, then matching them.

ALTER TABLE posts
  ADD COLUMN search tsvector
    GENERATED ALWAYS AS (
      to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))
    ) STORED;

CREATE INDEX posts_search_idx ON posts USING gin (search);

SELECT id, title
FROM posts
WHERE search @@ plainto_tsquery('english', 'connection pooling')
ORDER BY ts_rank(search, plainto_tsquery('english', 'connection pooling')) DESC
LIMIT 20;

A generated column keeps the vector in sync automatically, and the GIN index makes the @@ match fast. plainto_tsquery safely turns user input into a query, while ts_rank orders by relevance. You can add ts_headline to highlight matches in the results.

Reach for a dedicated search engine only when you need faceting, fuzzy typo tolerance at scale, or cross-document relevance tuning. For most applications the built-in version removes an entire moving part.

Views and materialized views

A view is a stored query that behaves like a table. It does not store data; it is a named way to encapsulate a common shape and keep permissions consistent.

CREATE VIEW paid_orders AS
SELECT id, user_id, total_cents, created_at
FROM orders
WHERE status = 'paid';

SELECT * FROM paid_orders WHERE created_at >= now() - interval '7 days';

A materialized view does store the result, which makes expensive aggregations cheap to read at the cost of staleness.

CREATE MATERIALIZED VIEW daily_revenue AS
SELECT date_trunc('day', created_at) AS day,
       sum(total_cents)             AS revenue_cents
FROM orders
WHERE status = 'paid'
GROUP BY 1;

REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue;

CONCURRENTLY refreshes without blocking readers, but it requires a unique index on the view. Schedule refreshes around your tolerance for stale numbers, and remember that a materialized view is a cache: it can always be rebuilt from the base tables.

Changing schemas safely

On a live database, DDL takes locks. The goal is to avoid long ACCESS EXCLUSIVE locks that block reads and writes while a table is rewritten.

ALTER TABLE orders ADD COLUMN channel text;
ALTER TABLE orders ALTER COLUMN channel SET DEFAULT 'web';

CREATE INDEX CONCURRENTLY orders_channel_idx ON orders (channel);

Adding a nullable column with no default is instant in modern Postgres, and setting a default is metadata-only. CREATE INDEX CONCURRENTLY builds the index without holding a write lock, though it cannot run inside a transaction and can fail, leaving an invalid index to drop and retry. Adding a NOT NULL constraint to a large table should be done in steps: add a CHECK that is validated separately, then convert it.

Version every change as a migration so every environment reaches the same schema in the same order. Never edit a production table by hand; you will forget what you did.

Bulk loading with COPY

Inserting rows one statement at a time is the slowest way to load data. Postgres has COPY, which streams data in a single command and can be an order of magnitude faster than a loop of INSERTs.

COPY orders (user_id, status, total_cents, created_at)
FROM '/data/orders.csv'
WITH (FORMAT csv, HEADER true);

COPY (SELECT id, email FROM users WHERE created_at > now() - interval '30 days')
TO '/tmp/recent-users.csv'
WITH (FORMAT csv, HEADER true);

COPY runs on the server, so the file must be readable by the database process; use \copy in psql to read from the client instead. For application code, the driver’s copy API streams rows without staging a file. Wrap a load in a transaction when you need it to be all-or-nothing, and drop or rebuild indexes afterward for very large loads, because maintaining indexes during a bulk insert is the main cost.

Batch inserts are a simpler middle ground when COPY is impractical:

INSERT INTO orders (user_id, status, total_cents)
VALUES (1, 'paid', 1999),
       (2, 'paid', 4599),
       (3, 'pending', 999);

Best practices

• Choose text, bigint, numeric, timestamptz and jsonb deliberately instead of defaulting to varchar and floats.
• Declare NOT NULL, UNIQUE, CHECK and foreign keys in the same migration that creates the table.
• Design indexes from real queries and confirm them with EXPLAIN ANALYZE.
• Keep transactions short and choose the weakest isolation level that is still correct.
• Put a pooler such as PgBouncer in front of the database and pool in the application too.
• Use JSONB for genuinely variable data, not as a way to skip schema design.
• Grant least privilege to application roles and keep DDL in a migration role.
• Enable pg_stat_statements, monitor autovacuum, and restore a backup on a schedule.
• Version every schema change as a migration so environments stay in sync.

Common mistakes

• Storing money in double precision and losing cents to rounding.
• Using timestamp instead of timestamptz and storing local times by accident.
• Forgetting WHERE on an UPDATE or DELETE and touching every row.
• Creating one index per column instead of composite indexes that match the query.
• Opening a new connection per request and exhausting max_connections.
• Holding a transaction open across an HTTP call or user interaction.
• Treating NULL as equal to NULL; comparisons need IS NULL and IS NOT NULL.
• Assuming an index is used without checking the query plan.
• Running application queries as the database owner or superuser.

Where to go next

Postgres rewards depth, and the natural next step is fluency in the language it speaks: read the SQL guide to sharpen joins, CTEs and window functions. If you are weighing an alternative, MySQL covers the other dominant open-source relational database, MongoDB explains the document model, and Redis shows the caching layer that usually sits in front of a relational store.