PostgreSQL

psql "postgres://app:secret@localhost:5432/app"
psql -h localhost -U app -d app -c 'select now()'
psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -f migrate.sql
psql -X -At -c 'select count(*) from users'  # bare value

\set QUIET 1
\pset null '∅'
\x auto
\timing on
\set HISTCONTROL ignoredups
\set PROMPT1 '%n@%/%R%x%# '
\unset QUIET

docker run -d --name pg \
  -e POSTGRES_PASSWORD=secret -e POSTGRES_DB=app \
  -p 5432:5432 \
  -v pgdata:/var/lib/postgresql \
  postgres:18 -c shared_preload_libraries=pg_stat_statements
docker exec -it pg psql -U postgres -d app

services:
  db:
    image: postgres:18
    environment:
      POSTGRES_PASSWORD: secret
      POSTGRES_DB: app
    ports: ["5432:5432"]
    volumes:
      - pgdata:/var/lib/postgresql
      - ./db/init:/docker-entrypoint-initdb.d:ro
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U postgres -d app"]
      interval: 2s
      retries: 15
volumes:
  pgdata:

postgres://user:p%40ss@db.example.com:5432/app?sslmode=verify-full&application_name=api
postgresql:///app?host=/var/run/postgresql      # Unix socket
host=localhost port=5432 dbname=app user=app    # keyword form

# host:port:database:user:password   (* = any)
localhost:5432:app:app:secret
*.example.com:5432:*:readonly:another-secret

SELECT '2026-09-25 10:00+02'::timestamptz AS at,
       now() AS tx_start,        -- fixed per transaction
       clock_timestamp() AS real_now,
       now() AT TIME ZONE 'Europe/Berlin' AS local,
       interval '90 minutes' * 2 AS three_h,
       '{a,b}'::text[] AS arr,
       ARRAY[1, 2, 3] AS arr2,
       '{"a": 1}'::jsonb AS doc,
       uuid_extract_timestamp(uuidv7()) AS uuid_time,
       CAST('42' AS int) + '8'::int AS fifty;

CREATE TABLE users (
  id         bigint GENERATED ALWAYS AS IDENTITY
             PRIMARY KEY,
  public_id  uuid NOT NULL DEFAULT uuidv7() UNIQUE,
  email      text NOT NULL UNIQUE
             CHECK (email = lower(email)),
  name       text NOT NULL CHECK (name <> ''),
  role       text NOT NULL DEFAULT 'member'
             CHECK (role IN ('member', 'admin')),
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
 
CREATE TABLE posts (
  id           bigint GENERATED ALWAYS AS IDENTITY
               PRIMARY KEY,
  author_id    bigint NOT NULL
               REFERENCES users (id) ON DELETE CASCADE,
  title        text NOT NULL,
  body         text NOT NULL DEFAULT '',
  tags         text[] NOT NULL DEFAULT '{}',
  meta         jsonb NOT NULL DEFAULT '{}',
  created_at   timestamptz NOT NULL DEFAULT now(),
  published_at timestamptz,
  -- virtual (the default in 18): computed on read
  slug         text GENERATED ALWAYS AS
               (lower(replace(title, ' ', '-'))),
  -- stored: computed on write, can be indexed
  search       tsvector GENERATED ALWAYS AS (
                 to_tsvector('english', title || ' ' || body)
               ) STORED
);
CREATE INDEX posts_author_id_idx ON posts (author_id);

ALTER TABLE users ADD COLUMN bio text;
ALTER TABLE users ALTER COLUMN bio SET DEFAULT '';
ALTER TABLE users RENAME COLUMN bio TO about;
ALTER TABLE users DROP COLUMN IF EXISTS about;
 
ALTER TABLE posts ADD CONSTRAINT posts_title_len
  CHECK (length(title) <= 200) NOT VALID;
ALTER TABLE posts VALIDATE CONSTRAINT posts_title_len;
 
COMMENT ON COLUMN users.role IS 'member or admin';

CREATE TABLE events (
  id      bigint GENERATED ALWAYS AS IDENTITY,
  at      timestamptz NOT NULL,
  payload jsonb NOT NULL,
  PRIMARY KEY (id, at)       -- must include the key
) PARTITION BY RANGE (at);
 
CREATE TABLE events_2026_09 PARTITION OF events
  FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');
CREATE TABLE events_default PARTITION OF events DEFAULT;

INSERT INTO users (email, name)
VALUES ('ada@example.com', 'Ada'),
       ('alan@example.com', 'Alan')
RETURNING id, public_id;
 
-- upsert: needs a unique index/constraint on (email)
INSERT INTO users (email, name)
VALUES ('ada@example.com', 'Ada Lovelace')
ON CONFLICT (email) DO UPDATE
  SET name = EXCLUDED.name, updated_at = now()
  WHERE users.name IS DISTINCT FROM EXCLUDED.name
RETURNING id;
 
INSERT INTO users (email, name)
VALUES ('ada@example.com', 'Ada')
ON CONFLICT DO NOTHING;           -- returns no row
 
-- 18+: old/new in RETURNING
UPDATE users SET role = 'admin'
WHERE email = 'ada@example.com'
RETURNING old.role AS was, new.role AS is_now;

-- UPDATE with a join
UPDATE posts p
SET    meta = p.meta || jsonb_build_object('by', u.name)
FROM   users u
WHERE  u.id = p.author_id AND p.published_at IS NULL;
 
-- DELETE with a join
DELETE FROM posts p
USING  users u
WHERE  u.id = p.author_id
  AND  u.email LIKE '%@spam.test';
 
-- bulk insert from two arrays (one round trip)
INSERT INTO users (email, name)
SELECT * FROM unnest(
  ARRAY['b@x.dev', 'c@x.dev'], ARRAY['Bea', 'Cy']
);

CREATE TABLE inventory (sku text PRIMARY KEY, qty int);
CREATE TABLE staging   (sku text PRIMARY KEY, qty int);
 
MERGE INTO inventory AS t
USING staging AS s ON t.sku = s.sku
WHEN MATCHED AND s.qty = 0 THEN DELETE
WHEN MATCHED THEN UPDATE SET qty = s.qty
WHEN NOT MATCHED THEN
  INSERT (sku, qty) VALUES (s.sku, s.qty)
WHEN NOT MATCHED BY SOURCE THEN DELETE   -- 17+
RETURNING merge_action(), t.*;           -- 17+

WITH recent AS (
  SELECT * FROM posts
  WHERE created_at > now() - interval '7 days'
)
SELECT u.name, count(r.id) AS posts
FROM users u
LEFT JOIN recent r ON r.author_id = u.id
GROUP BY u.id                 -- PK: name is implied
ORDER BY posts DESC;

-- data-modifying CTE: move rows in one statement
CREATE TABLE posts_archive (LIKE posts);
WITH moved AS (
  DELETE FROM posts
  WHERE created_at < now() - interval '5 years'
  RETURNING *
)
INSERT INTO posts_archive SELECT * FROM moved;

CREATE TABLE categories (
  id        int PRIMARY KEY,
  parent_id int REFERENCES categories (id),
  name      text NOT NULL
);
 
WITH RECURSIVE tree AS (
  SELECT id, parent_id, name, 1 AS depth
  FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.parent_id, c.name, t.depth + 1
  FROM categories c
  JOIN tree t ON c.parent_id = t.id
)
SEARCH DEPTH FIRST BY name SET ord
CYCLE id SET is_cycle USING path
SELECT * FROM tree ORDER BY ord;

SELECT author_id, id, created_at,
  row_number() OVER w AS nth,
  count(*) OVER (PARTITION BY author_id) AS total,
  lag(created_at) OVER w AS previous_at,
  created_at - lag(created_at) OVER w AS gap
FROM posts
WINDOW w AS (PARTITION BY author_id ORDER BY created_at);

-- latest post per author
SELECT DISTINCT ON (author_id) author_id, id, title
FROM posts
ORDER BY author_id, created_at DESC;
 
-- 3 latest posts per user
SELECT u.name, p.title
FROM users u
CROSS JOIN LATERAL (
  SELECT title FROM posts
  WHERE author_id = u.id
  ORDER BY created_at DESC
  LIMIT 3
) p;
 
SELECT author_id,
  count(*) AS total,
  count(*) FILTER (WHERE published_at IS NULL) AS drafts
FROM posts
GROUP BY author_id;
 
SELECT date_trunc('month', created_at) AS month,
       author_id, count(*), grouping(author_id) AS sub
FROM posts
GROUP BY ROLLUP (month, author_id);

UPDATE posts SET meta['seo']['title'] = '"Hello"'
WHERE id = 1;                     -- creates missing keys
UPDATE posts
SET meta = jsonb_set(meta, '{views}', to_jsonb(10))
WHERE id = 1;
 
SELECT p.id, t.tag
FROM posts p,
     jsonb_array_elements_text(p.meta -> 'labels') t(tag);
 
SELECT jsonb_build_object('id', id, 'title', title),
       to_jsonb(p) - 'search' AS whole_row
FROM posts p;
 
SELECT * FROM JSON_TABLE(          -- 17+
  '[{"sku":"a","qty":2}]'::jsonb, '$[*]'
  COLUMNS (sku text PATH '$.sku', qty int PATH '$.qty')
) AS jt;
 
-- containment: @>, ?, ?|, ?&
CREATE INDEX posts_meta_gin ON posts USING gin (meta);
-- smaller and faster, but only @>, @?, @@
CREATE INDEX posts_meta_path_gin
  ON posts USING gin (meta jsonb_path_ops);
-- one hot key: btree on the expression
CREATE INDEX posts_status_idx ON posts ((meta ->> 'status'));

CREATE INDEX posts_search_gin ON posts USING gin (search);
 
SELECT id, title,
  ts_rank(search, q) AS rank,
  ts_headline('english', body, q) AS snippet
FROM posts,
  websearch_to_tsquery('english', '"row level" -mysql') q
WHERE search @@ q
ORDER BY rank DESC
LIMIT 20;

CREATE INDEX CONCURRENTLY posts_author_pub_idx
  ON posts (author_id, published_at DESC)
  INCLUDE (title)
  WHERE published_at IS NOT NULL;
 
REINDEX INDEX CONCURRENTLY posts_author_pub_idx;
DROP INDEX CONCURRENTLY IF EXISTS posts_old_idx;

EXPLAIN SELECT * FROM posts WHERE author_id = 42;
EXPLAIN (ANALYZE, BUFFERS, SETTINGS)   -- runs the query
SELECT * FROM posts WHERE author_id = 42;
 
BEGIN;                                 -- safe for writes
EXPLAIN ANALYZE DELETE FROM posts WHERE author_id = 42;
ROLLBACK;

Limit  (cost=0.43..8.95 rows=10 width=48) (actual time=0.031..0.052 rows=10.00 loops=1)
  Buffers: shared hit=13
  ->  Index Scan using posts_author_pub_idx on posts  (cost=0.43..351.2 rows=412 width=48) (actual time=0.030..0.049 rows=10.00 loops=1)
        Index Cond: (author_id = 42)
        Index Searches: 1
        Buffers: shared hit=13
Planning Time: 0.210 ms
Execution Time: 0.081 ms

BEGIN;
UPDATE users SET name = 'A' WHERE id = 1;
SAVEPOINT before_risky;
UPDATE users SET name = '' WHERE id = 2;  -- CHECK fails
ROLLBACK TO SAVEPOINT before_risky;
COMMIT;
 
BEGIN ISOLATION LEVEL SERIALIZABLE;
SELECT count(*) FROM posts WHERE author_id = 1;
COMMIT;

SET lock_timeout = '3s';            -- per session
SET statement_timeout = '30s';
SET idle_in_transaction_session_timeout = '60s';
SET LOCAL statement_timeout = '5s'; -- rest of this tx
 
-- app-level mutex, released at commit
SELECT pg_advisory_xact_lock(hashtext('nightly-report'));
SELECT pg_try_advisory_lock(42);    -- true or false

CREATE ROLE migrator LOGIN PASSWORD 'change-me';
CREATE ROLE app_rw LOGIN PASSWORD 'change-me';
CREATE ROLE readonly NOLOGIN;
 
GRANT USAGE ON SCHEMA public TO readonly, app_rw;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
GRANT SELECT, INSERT, UPDATE, DELETE
  ON ALL TABLES IN SCHEMA public TO app_rw;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO app_rw;
 
-- tables the migrator creates later
ALTER DEFAULT PRIVILEGES FOR ROLE migrator
  IN SCHEMA public GRANT SELECT ON TABLES TO readonly;
 
CREATE ROLE alice LOGIN IN ROLE readonly;  -- membership
ALTER ROLE app_rw SET statement_timeout = '15s';

ALTER TABLE posts ENABLE ROW LEVEL SECURITY;
ALTER TABLE posts FORCE ROW LEVEL SECURITY; -- owner too
 
CREATE POLICY posts_owner ON posts
  FOR ALL TO app_rw
  USING (author_id =
    (SELECT current_setting('app.user_id', true)::bigint))
  WITH CHECK (author_id =
    (SELECT current_setting('app.user_id', true)::bigint));
 
-- per request, in the app's transaction
BEGIN;
SELECT set_config('app.user_id', '42', true); -- tx-local
SELECT * FROM posts;                  -- only user 42's
COMMIT;

-- tables with the most dead rows
SELECT relname, n_live_tup, n_dead_tup,
       last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;
 
-- biggest tables
SELECT relname,
  pg_size_pretty(pg_total_relation_size(relid)) AS total,
  pg_size_pretty(pg_indexes_size(relid)) AS indexes
FROM pg_stat_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;
 
-- vacuum a hot table sooner (defaults: 0.2 / 0.1)
ALTER TABLE posts SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_analyze_scale_factor = 0.01
);
 
-- wraparound: alarm well before 2 billion
SELECT datname, age(datfrozenxid) FROM pg_database;

# logical, custom format: compressed, selective restore
pg_dump -Fc -d "$DATABASE_URL" -f app.dump
pg_dump -Fc --schema-only -d app -f schema.dump
pg_dump -d app -t 'public.users' > users.sql   # plain SQL
pg_dumpall --globals-only > globals.sql  # roles etc.
 
pg_restore -l app.dump                 # list contents
pg_restore -d app_copy -j 4 --no-owner --clean \
  --if-exists app.dump
psql -d app -f users.sql
 
docker exec pg pg_dump -U postgres -Fc app > app.dump

SELECT pid, usename, state, wait_event_type,
       now() - query_start AS runtime,
       left(query, 60) AS query
FROM pg_stat_activity
WHERE state <> 'idle' AND pid <> pg_backend_pid()
ORDER BY runtime DESC;
 
SELECT pg_cancel_backend(12345);    -- cancel its query
SELECT pg_terminate_backend(12345); -- close the session
 
-- unused indexes (unique ones may still enforce rules)
SELECT indexrelid::regclass AS index, idx_scan,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

SHOW work_mem;
ALTER SYSTEM SET work_mem = '32MB';  -- postgresql.auto.conf
SELECT pg_reload_conf();
SELECT name, setting, source FROM pg_settings
WHERE source <> 'default';

SELECT name, default_version, installed_version
FROM pg_available_extensions ORDER BY name;
CREATE EXTENSION IF NOT EXISTS pg_trgm;
 
CREATE INDEX users_name_trgm ON users
  USING gin (name gin_trgm_ops);
SELECT name, similarity(name, 'ada lovlace') AS sim
FROM users
WHERE name % 'ada lovlace'        -- similarity > 0.3
ORDER BY sim DESC
LIMIT 5;
 
CREATE EXTENSION IF NOT EXISTS vector;
CREATE TABLE docs (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  body      text NOT NULL,
  embedding vector(3) NOT NULL    -- e.g. 1536 in practice
);
CREATE INDEX docs_embedding_hnsw ON docs
  USING hnsw (embedding vector_cosine_ops);
SELECT id, body FROM docs
ORDER BY embedding <=> '[0.1, 0.2, 0.3]'
LIMIT 5;

import { SQL } from "bun";
 
const url = process.env.DATABASE_URL;
if (!url) throw new Error("DATABASE_URL is not set");
 
export const sql = new SQL({
  url,
  max: 10,          // pool size
  idleTimeout: 30,  // seconds
});
 
type User = { id: string; email: string; name: string };
 
export async function findUser(email: string) {
  const [user] = await sql<User[]>`
    SELECT id, email, name FROM users
    WHERE email = ${email}
  `;
  return user;                  // User | undefined
}
 
export async function demo(ids: number[]) {
  await sql`INSERT INTO users ${sql({
    email: "ada@example.com",
    name: "Ada",
  })}`;
  const rows = await sql<User[]>`
    SELECT * FROM users WHERE id IN ${sql(ids)}`;
  await sql.begin(async (tx) => {
    await tx`UPDATE users SET role = 'admin'
             WHERE id = ${ids[0] ?? 0}`;
    await tx`DELETE FROM posts WHERE author_id = ${1}`;
  });                           // throws: rolled back
  return rows;
}

import pg from "pg";
 
const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,
});
 
const { rows } = await pool.query<{ id: string }>(
  "SELECT id FROM users WHERE email = $1",
  ["ada@example.com"],
);
 
const client = await pool.connect();  // one connection
try {
  await client.query("BEGIN");
  await client.query(
    "UPDATE users SET role = $1 WHERE id = $2",
    ["admin", rows[0]?.id],
  );
  await client.query("COMMIT");
} catch (err) {
  await client.query("ROLLBACK");
  throw err;
} finally {
  client.release();
}

import postgres from "postgres";
 
const sql = postgres(process.env.DATABASE_URL ?? "", {
  max: 10,
  prepare: false,       // behind PgBouncer transaction mode
});
const users = await sql<{ id: string; name: string }[]>`
  select id, name from users where role = ${"admin"}
`;
await sql.end();

INSERT INTO users (email, name)
SELECT * FROM unnest(
  $1::text[],           -- emails
  $2::text[]            -- names
)
ON CONFLICT (email) DO UPDATE
  SET name = EXCLUDED.name, updated_at = now()
RETURNING id, (old.id IS NULL) AS inserted;

CREATE INDEX posts_feed_idx
  ON posts (created_at DESC, id DESC);
 
-- first page
SELECT id, created_at, title FROM posts
ORDER BY created_at DESC, id DESC
LIMIT 20;
 
-- next page: pass the last row's created_at and id
SELECT id, created_at, title FROM posts
WHERE (created_at, id) < ($1::timestamptz, $2::bigint)
ORDER BY created_at DESC, id DESC
LIMIT 20;

CREATE TABLE jobs (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  queue     text NOT NULL DEFAULT 'default',
  payload   jsonb NOT NULL,
  run_at    timestamptz NOT NULL DEFAULT now(),
  attempts  int NOT NULL DEFAULT 0,
  locked_at timestamptz
);
CREATE INDEX jobs_ready_idx ON jobs (queue, run_at)
  WHERE locked_at IS NULL;
 
-- claim one (autocommit); stale locks can be reset later
UPDATE jobs SET locked_at = now(), attempts = attempts + 1
WHERE id = (
  SELECT id FROM jobs
  WHERE queue = 'default' AND locked_at IS NULL
    AND run_at <= now()
  ORDER BY run_at
  LIMIT 1
  FOR UPDATE SKIP LOCKED
)
RETURNING id, payload, attempts;
-- done: DELETE FROM jobs WHERE id = $1
-- failed: set locked_at = NULL, run_at = now() + backoff

-- needs shared_preload_libraries = 'pg_stat_statements'
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
 
SELECT calls,
  round(total_exec_time::numeric) AS total_ms,
  round(mean_exec_time::numeric, 2) AS mean_ms,
  round(100 * total_exec_time
    / sum(total_exec_time) OVER ())::int AS pct,
  rows,
  left(query, 60) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
 
SELECT pg_stat_statements_reset();  -- start a new window

SELECT blocked.pid AS blocked_pid,
       left(blocked.query, 40) AS blocked_query,
       blocker.pid AS blocker_pid,
       blocker.state AS blocker_state,
       now() - blocker.xact_start AS blocker_tx_age,
       left(blocker.query, 40) AS blocker_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocker
  ON blocker.pid = ANY (pg_blocking_pids(blocked.pid))
ORDER BY blocker_tx_age DESC;

CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  NEW.updated_at := now();
  RETURN NEW;
END
$$;
 
CREATE TRIGGER users_set_updated_at
BEFORE UPDATE ON users
FOR EACH ROW
WHEN (OLD.* IS DISTINCT FROM NEW.*)  -- skip no-op updates
EXECUTE FUNCTION set_updated_at();

ALTER TABLE users ADD COLUMN deleted_at timestamptz;
ALTER TABLE users DROP CONSTRAINT users_email_key;
CREATE UNIQUE INDEX users_email_live_key
  ON users (email) WHERE deleted_at IS NULL;
 
CREATE VIEW live_users AS
  SELECT * FROM users WHERE deleted_at IS NULL;
 
UPDATE users SET deleted_at = now()
WHERE email = 'alan@example.com';
 
-- upserts must name the index predicate
INSERT INTO users (email, name)
VALUES ('alan@example.com', 'Alan')
ON CONFLICT (email) WHERE deleted_at IS NULL
DO NOTHING;

SET lock_timeout = '3s';     -- fail fast, retry later
ALTER TABLE users ADD COLUMN handle text;
 
-- backfill in batches; repeat until 0 rows updated
UPDATE users SET handle = split_part(email, '@', 1)
WHERE id IN (
  SELECT id FROM users WHERE handle IS NULL LIMIT 5000
);
 
-- 18+: add unvalidated, then validate without blocking
ALTER TABLE users ADD CONSTRAINT users_handle_nn
  NOT NULL handle NOT VALID;
ALTER TABLE users VALIDATE CONSTRAINT users_handle_nn;
-- 17 and older: CHECK (handle IS NOT NULL) NOT VALID,
-- VALIDATE, then SET NOT NULL (skips the scan)