PostgreSQL in practice: one database that replaces five systems

# macOS
brew install postgresql@18
brew services start postgresql@18

# Debian and Ubuntu - use the PGDG repository, not the distro package
sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
sudo apt install -y postgresql-18

# Docker - the fastest way to get a throwaway instance
docker run --name pg -e POSTGRES_PASSWORD=secret -p 5432:5432 -d postgres:18

createdb shop
psql shop

-- or connect with a URL
psql postgresql://user:secret@localhost:5432/shop

\l              -- list databases
\c shop         -- connect to a database
\dt             -- list tables
\d orders       -- describe a table: columns, indexes, constraints, triggers
\di             -- list indexes
\df            -- list functions
\dx             -- list installed extensions
\x              -- toggle expanded output - essential for wide rows
\timing         -- show how long each query takes
\e              -- edit the last query in $EDITOR
\i script.sql   -- run a file
\copy (SELECT * FROM orders) TO 'orders.csv' CSV HEADER   -- export client-side
\watch 2        -- rerun the last query every 2 seconds
\?              -- all of them
\q              -- quit

CREATE TABLE customers (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email       text NOT NULL UNIQUE,
    full_name   text NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE orders (
    id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id  bigint NOT NULL REFERENCES customers(id) ON DELETE RESTRICT,
    status       text NOT NULL DEFAULT 'pending',
    total_cents  integer NOT NULL,
    placed_at    timestamptz NOT NULL DEFAULT now(),

    CONSTRAINT orders_total_positive CHECK (total_cents > 0),
    CONSTRAINT orders_status_valid
        CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled'))
);

CREATE INDEX orders_customer_placed_idx
    ON orders (customer_id, placed_at DESC);

ALTER TABLE orders ADD COLUMN metadata jsonb NOT NULL DEFAULT '{}';

UPDATE orders
SET metadata = '{"channel": "mobile", "coupon": "EID25", "items": 3}'
WHERE id = 1;

-- -> returns jsonb, ->> returns text
SELECT id, metadata ->> 'channel' AS channel
FROM orders
WHERE metadata ->> 'coupon' = 'EID25';

-- @> is containment, and it is the operator a GIN index accelerates
SELECT id FROM orders WHERE metadata @> '{"channel": "mobile"}';

-- nested paths
SELECT metadata #>> '{shipping,city}' FROM orders WHERE id = 1;

CREATE INDEX orders_metadata_idx ON orders USING gin (metadata);

-- Arrays
ALTER TABLE orders ADD COLUMN tags text[] NOT NULL DEFAULT '{}';
SELECT * FROM orders WHERE tags @> ARRAY['priority'];
SELECT * FROM orders WHERE 'gift' = ANY(tags);

-- Enums: compact storage, ordered, but adding values needs DDL
CREATE TYPE order_status AS ENUM ('pending', 'paid', 'shipped', 'cancelled');

-- Ranges: a first-class interval type with proper operators
CREATE TABLE room_bookings (
    room_id  bigint NOT NULL,
    period   tstzrange NOT NULL,
    EXCLUDE USING gist (room_id WITH =, period WITH &&)
);

-- Domains: a reusable constrained type
CREATE DOMAIN email AS text
    CHECK (VALUE ~ '^[^@\s]+@[^@\s]+\.[^@\s]+$');

CREATE TABLE subscribers (
    id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    address  email NOT NULL UNIQUE
);

INSERT INTO customers (email, full_name)
VALUES ('naiem@example.com', 'Naiem Inahid')
ON CONFLICT (email) DO UPDATE
    SET full_name = EXCLUDED.full_name
RETURNING id, (xmax = 0) AS was_inserted;

-- Standard: get the generated id back with the insert
INSERT INTO orders (customer_id, total_cents)
VALUES (1, 4999)
RETURNING id, placed_at;

-- PostgreSQL 18: see the before and after values
UPDATE orders SET status = 'paid' WHERE id = 1
RETURNING OLD.status AS previous, NEW.status AS current;

WITH monthly AS (
    SELECT date_trunc('month', placed_at) AS month,
           sum(total_cents)               AS revenue_cents
    FROM orders
    WHERE status = 'paid'
    GROUP BY 1
)
SELECT month,
       revenue_cents / 100.0 AS revenue,
       lag(revenue_cents) OVER (ORDER BY month) / 100.0 AS previous
FROM monthly
ORDER BY month DESC;

WITH RECURSIVE subtree AS (
    SELECT id, parent_id, name, 1 AS depth
    FROM categories
    WHERE id = 3

    UNION ALL

    SELECT c.id, c.parent_id, c.name, s.depth + 1
    FROM categories c
    JOIN subtree s ON c.parent_id = s.id
)
SELECT repeat('  ', depth - 1) || name AS tree FROM subtree ORDER BY depth;

SELECT
    customer_id,
    placed_at,
    total_cents,
    row_number() OVER w                       AS order_number,
    sum(total_cents) OVER w                   AS running_total,
    total_cents - lag(total_cents) OVER w     AS change_from_previous,
    avg(total_cents) OVER (
        PARTITION BY customer_id
        ORDER BY placed_at
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    )                                         AS moving_average
FROM orders
WINDOW w AS (PARTITION BY customer_id ORDER BY placed_at);

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

-- Partial: index only the rows you actually query
CREATE INDEX orders_pending_idx ON orders (placed_at)
    WHERE status = 'pending';

-- Expression: index the result of a function
CREATE INDEX customers_email_lower_idx ON customers (lower(email));
-- now this uses the index:
SELECT * FROM customers WHERE lower(email) = 'naiem@example.com';

-- Covering: carry extra columns so the query never touches the table
CREATE INDEX orders_customer_idx ON orders (customer_id) INCLUDE (total_cents);

-- Build without locking writes - always use this in production
CREATE INDEX CONCURRENTLY orders_status_idx ON orders (status);

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT c.full_name, count(*)
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.placed_at > now() - interval '30 days'
GROUP BY c.full_name;

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

CREATE INDEX articles_search_idx ON articles USING gin (search);

SELECT id, title, ts_rank(search, query) AS rank
FROM articles, websearch_to_tsquery('english', 'postgres index tuning') AS query
WHERE search @@ query
ORDER BY rank DESC
LIMIT 20;

CREATE TABLE jobs (
    id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    payload      jsonb  NOT NULL,
    run_after    timestamptz NOT NULL DEFAULT now(),
    attempts     integer NOT NULL DEFAULT 0,
    locked_until timestamptz
);

CREATE INDEX jobs_ready_idx ON jobs (run_after)
    WHERE locked_until IS NULL;

-- A worker claims one job atomically
UPDATE jobs SET
    locked_until = now() + interval '5 minutes',
    attempts     = attempts + 1
WHERE id = (
    SELECT id FROM jobs
    WHERE run_after <= now()
      AND (locked_until IS NULL OR locked_until < now())
    ORDER BY run_after
    FOR UPDATE SKIP LOCKED
    LIMIT 1
)
RETURNING id, payload;

-- Listener
LISTEN order_paid;

-- Publisher, from a trigger or application code
SELECT pg_notify('order_paid', json_build_object('order_id', 42)::text);

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE chunks (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    doc_id     bigint NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
    content    text   NOT NULL,
    embedding  vector(1536)
);

CREATE INDEX chunks_embedding_idx ON chunks
    USING hnsw (embedding vector_cosine_ops);

-- <=> is cosine distance; <-> is L2, <#> is inner product
SELECT id, content, 1 - (embedding <=> $1) AS similarity
FROM chunks
WHERE doc_id = ANY($2)          -- ordinary SQL filtering, in the same query
ORDER BY embedding <=> $1
LIMIT 10;

-- A materialised view for a dashboard
CREATE MATERIALIZED VIEW daily_revenue AS
SELECT date_trunc('day', placed_at)::date AS day,
       count(*)                           AS order_count,
       sum(total_cents)                   AS revenue_cents
FROM orders
WHERE status = 'paid'
GROUP BY 1;

CREATE UNIQUE INDEX daily_revenue_day_idx ON daily_revenue (day);

-- The unique index is what allows this to refresh without blocking readers
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue;

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- The 20 queries consuming the most total time
SELECT calls,
       round(total_exec_time::numeric, 1)  AS total_ms,
       round(mean_exec_time::numeric, 2)   AS mean_ms,
       rows,
       left(query, 90)                     AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

BEGIN;
  -- Row-level lock, held until commit
  SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

-- Advisory locks: application-level mutual exclusion, no table needed
SELECT pg_try_advisory_lock(12345);
-- … do the work only one process should do …
SELECT pg_advisory_unlock(12345);

shared_buffers = 25% of RAM        -- PostgreSQL's own cache
effective_cache_size = 50-75% RAM  -- a hint to the planner, not an allocation
work_mem = 16MB                    -- per sort or hash node, per query - multiply carefully
maintenance_work_mem = 512MB       -- vacuum and index builds
max_connections = 100              -- keep it low; use a pooler for the rest
random_page_cost = 1.1             -- for SSDs; the 4.0 default assumes spinning disks
wal_compression = on
log_min_duration_statement = 500   -- log anything slower than 500ms

# Logical dump - portable, restorable per object, slow on large databases
pg_dump -Fc shop > shop.dump
pg_restore -d shop_restored shop.dump

# Physical base backup - the basis of point-in-time recovery
pg_basebackup -D /backup/base -Ft -z -P

# Check what the database itself thinks about vacuum health
SELECT relname, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC LIMIT 10;

-- Keyset pagination: constant time regardless of how deep you go
SELECT id, placed_at, total_cents
FROM orders
WHERE (placed_at, id) < ($1, $2)
ORDER BY placed_at DESC, id DESC
LIMIT 20;