This database lives in my house.

PostgreSQL runs here on a Minisforum mini PC small enough to hide behind a monitor. Nothing below runs until you press a button. Then it's a real query on that machine, with nothing mocked and no result cached.

Version: —
Up for: —
This database: —
Round trip: —

How your click gets to my house

Your device
1. BrowserLoads the page, then sends a request when you press a button.
HTTPS
Cloudflare's network
1. Nearest data centerYour way in. The page's scripts, styles and fonts are served from here.
2. Placement runs the Worker near Chicago, close to the database
3. WorkervinextServes the prerendered page, and loading the page stops here. Pressing a button goes further: it rate limits the request, then runs one fixed query using node-postgres.
4. Postgres protocol
5. HyperdriveHolds a small pool of open connections, so no request pays for a new login. Result caching is off.
6. Postgres protocol
7. Workers VPC servicepg-homePrivate route into the tunnel, with no public hostname. It also checks the server's certificate.
8. Into the tunnel at Chicago or Detroit
9. Cloudflare Tunnelhome-k3sFour connections held open from the house to the nearby data centers.
QUIC, dialed out from the house. The router has no open port, so nothing on the internet can connect in.
My house · Minisforum mini PC · k3s
1. cloudflaredpodReceives the connection and looks up pg-home.jilles.dev.
2. CoreDNS rewrites that name to the in-cluster service
3. Kubernetes servicepostgres-prod-rwAlways points at the current primary.
4. TLS with a Cloudflare Origin CA certificate, ending at Postgres
5. PostgreSQL 18postgres-prod-1One pod run by CloudNativePG, limited to 2 CPUs and 2 GB of memory.

The machine has an 8-core Ryzen 9 7940HS, and Postgres shares it with a few other apps. There is one instance and no replica, with backups going to object storage. If the power goes out here, the button above will tell you.

Try it

A calendar that can't double-book

The overlap rule is part of the primary key: PRIMARY KEY (room, hours WITHOUT OVERLAPS). The app doesn't check for clashes and there's no race to lose, because the database refuses the row. The slot picked below clashes, so press the button to see the real error, then try a free one. Every attempt is rolled back, so the calendar never changes.

Trunk Room

SlonikDumboBabar

Tusk Room

HortonSlonik

BEGIN;
INSERT INTO booking (room, hours, who)
VALUES ($1, int4range($2, $3), 'You')
RETURNING id, room, hours;

-- not run yet

PG 18
Temporal constraints
uuidv7

Search without a search server

Search the history of Postgres. A stored tsvector column handles stemming, ranking and highlighting, which is why “replicate” finds “replication”. Trigram matching catches typos, so try replicashun and watch the first query find nothing while the second still does. Both are ordinary SQL against the same table.

SELECT year,
       ts_headline('english', title || '. ' || body, query,
                   'StartSel=[[, StopSel=]], MaxWords=18, MinWords=8') AS match,
       round(ts_rank(search, query)::numeric, 3) AS rank
FROM fact, websearch_to_tsquery('english', $1) AS query
WHERE search @@ query
ORDER BY ts_rank(search, query) DESC, year
LIMIT 4;

-- not run yet

SELECT title,
       round(word_similarity($1, title)::numeric, 2) AS score
FROM fact
WHERE word_similarity($1, title) > 0.3
ORDER BY score DESC, year
LIMIT 4;

-- not run yet

tsvector
pg_trgm
GIN

Documents, with an index

A column of JSON documents about some famous elephants. The @? operator asks a jsonpath question of each one, and a GIN index is available to speed that up once a table gets big enough to need it.

SELECT doc->>'name' AS name,
       jsonb_path_query_array(doc, $1::jsonpath) AS matched
FROM elephant
WHERE doc @? $1::jsonpath
ORDER BY id;

-- not run yet

JSONB
jsonpath
GIN

A fractal, because it can

Nobody needs this, but SQL is powerful enough to do it. A recursive query iterates every point of the Mandelbrot set, and DISTINCT ON keeps the last iteration of each. Press the button and the mini PC draws it, just for you.

WITH RECURSIVE
grid AS (
  SELECT x, y, -2.25 + x * 0.05 AS cr, -1.2 + y * 0.1 AS ci
  FROM generate_series(0, 63) AS x, generate_series(0, 24) AS y
),
orbit (x, y, cr, ci, zr, zi, i) AS (
  SELECT x, y, cr, ci, 0::float8, 0::float8, 0 FROM grid
  UNION ALL
  SELECT x, y, cr, ci, zr * zr - zi * zi + cr, 2 * zr * zi + ci, i + 1
  FROM orbit
  WHERE i < 40 AND zr * zr + zi * zi < 4
),
escaped AS (
  SELECT DISTINCT ON (x, y) x, y, i
  FROM orbit
  ORDER BY x, y, i DESC
)
SELECT string_agg(substr(' .:-=+*%#@', 1 + i * 9 / 40, 1), '' ORDER BY x) AS mandelbrot
FROM escaped
GROUP BY y
ORDER BY y;

-- not run yet

WITH RECURSIVE
generate_series
DISTINCT ON