#!/usr/bin/env bash
# run.sh — PostgreSQL json vs jsonb, measured. Now-Next, November 2026.
#
#   ./run.sh                      # PostgreSQL 17 and 18, full size (about 20 minutes per version on our machine)
#   VERSIONS=17 ./run.sh          # one version
#   SCALE=0.02 ./run.sh           # quick trial with 2% of the rows (writes to results-scale-0.02/)
#
# Needs: bash, docker, python3 (standard library only) and about 30 GB of free disk:
# 2 GB of generated documents, about 8 GB of tables, up to 16 GB of WAL. The script starts its own
# throwaway containers (json-bench-pg17, json-bench-pg18) and removes them again,
# including their anonymous volumes. Nothing else is touched.
#
# Output in results/: res-<v>.csv (every timing), sizes-<v>.csv, plans-<v>.csv
# (EXPLAIN ANALYZE), shapes-<v>.csv, behaviour-<v>.txt (what jsonb does to your
# data), version-<v>.txt, machine.txt, manifest.csv and summary.md.
#
# Method
#   - Six document shapes, generated from a fixed seed, in two forms each: compact
#     (c, no whitespace) and pretty-printed (w, indent=2). Both forms of a shape
#     hold exactly the same values.
#   - Every shape x form is loaded into four tables: json and jsonb, each with
#     default_toast_compression pglz and lz4.
#   - Timings are taken server-side (clock_timestamp() in PL/pgSQL, see setup.sql),
#     with one untimed warm-up per query. Medians of 5 runs for reads, 3 for loads,
#     index builds and updates.
#   - Parallel query is off (max_parallel_workers_per_gather=0) except where a
#     variant says par2. autovacuum and JIT are off.
set -euo pipefail
export LC_ALL=C

HERE="$(cd "$(dirname "${BASH_SOURCE[0]}")" && pwd)"
SCALE="${SCALE:-1}"
VERSIONS="${VERSIONS:-17 18}"
if [ "$SCALE" = "1" ]; then OUT="$HERE/results"; else OUT="$HERE/results-scale-$SCALE"; fi
GEN="$(mktemp -d "${TMPDIR:-/tmp}/json-bench-gen.XXXXXX")"
chmod 755 "$GEN"; mkdir -p "$OUT"

name() { echo "${CONTAINER:-json-bench-pg$1}"; }
cleanup() {
  for v in $VERSIONS; do docker rm -f -v "$(name "$v")" >/dev/null 2>&1 || true; done
  rm -rf "$GEN"
}
trap cleanup EXIT
for v in $VERSIONS; do docker rm -f -v "$(name "$v")" >/dev/null 2>&1 || true; done

log() { echo "[$(date +%H:%M:%S)] $*"; }

log "generating documents (SCALE=$SCALE)"
python3 -I - "$GEN" "$SCALE" <<'PY'
import json, random, string, sys, hashlib
out, scale = sys.argv[1], float(sys.argv[2])
WORDS = ("account invoice ledger booking flight aircraft pilot member balance tenant "
 "schedule settlement payment refund receipt customer project hangar report export "
 "import status review approved pending draft closed monthly weekly annual total net "
 "gross amount currency reference period journal entry line item product service "
 "support request response timeout retry session token region cluster").split()
COUNTRY = ["NL","BE","DE","FR","GB","ES","IT","SE","NO","DK","AT","CH"]
ALNUM = string.ascii_letters + string.digits

def base(r, i):
    return r.choices(["active","paused","closed"], [60,25,15])[0], "r%03d" % r.randrange(1000)

def sent(r, n): return " ".join(r.choice(WORDS) for _ in range(n))
def hexs(r, n): return "%0*x" % (n, r.getrandbits(n*4))
def date(r):
    return "2026-%02d-%02dT%02d:%02d:%02dZ" % (r.randint(1,12), r.randint(1,28),
            r.randrange(24), r.randrange(60), r.randrange(60))

def small(r, i):
    st, tag = base(r, i)
    return {"first": hexs(r,8), "id": i, "status": st, "tag": tag,
            "plan": r.choice(["free","pro","team"]), "country": r.choice(COUNTRY),
            "score": round(r.random()*100, 2), "active": r.random() < .8,
            "email": "user%d@example.com" % i, "created": date(r), "last": hexs(r,8)}

LONG = {"first":"first_customer_reference","id":"internal_record_identifier",
  "status":"subscription_lifecycle_status","tag":"marketing_segment_tag",
  "plan":"commercial_pricing_plan","country":"billing_country_code",
  "score":"calculated_engagement_score","active":"account_is_currently_active",
  "email":"primary_contact_email_address","created":"record_creation_timestamp",
  "last":"last_customer_reference"}
def smalllong(r, i):
    # the same values, longer keys; only the size of this shape is measured
    return {LONG[k]: v for k, v in small(r, i).items()}

def medium(r, i):
    st, tag = base(r, i)
    return {"first": hexs(r,8), "id": i, "status": st, "tag": tag,
      "customer": {"name": sent(r,2), "email": "user%d@example.com" % i,
        "address": {"street": sent(r,2), "number": r.randint(1,300), "city": r.choice(WORDS),
                    "postcode": "%04d AB" % r.randint(1000,9999), "country": r.choice(COUNTRY)}},
      "order": {"currency": "EUR", "created": date(r),
        "lines": [{"sku": "s%04d" % r.randrange(5000), "name": sent(r,3),
                   "qty": r.randint(1,9), "price": round(r.random()*500,2),
                   "vat": 0.21} for _ in range(7)],
        "totals": {"net": round(r.random()*3000,2), "vat": round(r.random()*600,2)}},
      "events": [{"type": r.choice(WORDS), "at": date(r), "actor": "user%d" % r.randrange(9999),
                  "note": sent(r,4)} for _ in range(5)],
      "meta": {"source": r.choice(WORDS), "version": 3, "flags": ["a","b","c"]},
      "last": hexs(r,8)}

def large(r, i):
    st, tag = base(r, i)
    return {"first": hexs(r,8), "id": i, "status": st, "tag": tag,
      "customer": {"name": sent(r,2), "email": "user%d@example.com" % i},
      "items": [{"sku": "s%04d" % r.randrange(5000), "name": sent(r,3), "qty": r.randint(1,9),
                 "price": round(r.random()*500,2), "note": sent(r,6)} for _ in range(165)],
      "last": hexs(r,8)}

def flat(r, i):
    # 780 distinct keys with random values: compresses badly
    st, tag = base(r, i)
    d = {"first": hexs(r,8), "status": st, "tag": tag}
    for k in range(780):
        d["k%04d" % k] = "".join(r.choices(ALNUM, k=16))
    d["last"] = hexs(r,8)
    return d

def num(r, i):
    st, tag = base(r, i)
    return {"first": hexs(r,8), "status": st, "tag": tag,
            "samples": [r.randrange(100000) for _ in range(3500)], "last": hexs(r,8)}

SHAPES = [("small",small,1_000_000,"cw"),("smalllong",smalllong,1_000_000,"c"),
          ("medium",medium,100_000,"cw"),("large",large,5_000,"cw"),
          ("flat",flat,5_000,"cw"),("num",num,5_000,"cw")]
with open(out+"/manifest.csv","w") as m:
    for name, fn, n, forms in SHAPES:
        n = max(int(n*scale), 40)
        for f in forms:
            r = random.Random(hashlib.sha256(name.encode()).digest())  # same values in c and w
            tot = 0
            with open("%s/%s_%s.tsv" % (out,name,f),"w") as fh:
                for i in range(1, n+1):
                    d = fn(r, i)
                    s = json.dumps(d, separators=(",",":")) if f=="c" else json.dumps(d, indent=2)
                    tot += len(s.encode())
                    fh.write("%d\t%s\n" % (i, s.replace("\\","\\\\").replace("\n","\\n")))
            m.write("%s,%s,%d,%.1f\n" % (name,f,n,tot/n))
PY
cp "$GEN/manifest.csv" "$OUT/manifest.csv"

{
  echo "date: $(date -Is)"; echo "kernel: $(uname -r)"
  echo "cpu: $(lscpu 2>/dev/null | sed -n 's/^Model name: *//p'), $(nproc) threads"
  echo "ram: $(free -g 2>/dev/null | awk '/^Mem:/{print $2}') GiB"
  echo "docker: $(docker version --format '{{.Server.Version}}')"
  echo "load at start: $(cut -d' ' -f1-3 /proc/loadavg 2>/dev/null)"
} >> "$OUT/machine.txt"

run_version() {
  local v=$1 c; c=$(name "$1")
  log "== PostgreSQL $v: starting container $c"
  docker run -d --name "$c" --shm-size=2g \
    -e POSTGRES_PASSWORD=dummy -e POSTGRES_INITDB_ARGS=--data-checksums \
    -v "$GEN:/gen:ro" "postgres:$v" \
    -c shared_buffers=2GB -c work_mem=64MB -c maintenance_work_mem=1GB \
    -c max_wal_size=16GB -c checkpoint_timeout=1h -c autovacuum=off -c jit=off \
    -c max_parallel_workers_per_gather=0 -c max_parallel_maintenance_workers=0 >/dev/null
  # Over TCP: the temporary server during initdb only listens on the socket.
  until docker exec -e PGPASSWORD=dummy "$c" psql -h 127.0.0.1 -U postgres -Atc 'select 1' >/dev/null 2>&1; do sleep 1; done
  P=(docker exec -i "$c" psql -U postgres -X -v ON_ERROR_STOP=1 -q)

  "${P[@]}" < "$HERE/setup.sql" >/dev/null
  { docker image inspect "postgres:$v" --format 'image: {{index .RepoDigests 0}}'
    "${P[@]}" -At <<'SQL'
SELECT version();
SELECT name||' = '||setting FROM pg_settings WHERE name IN ('data_checksums','server_encoding',
 'default_toast_compression','shared_buffers','io_method','full_page_writes','wal_compression',
 'wal_level','jit','max_parallel_workers_per_gather','autovacuum','lc_collate') ORDER BY 1;
SQL
  } > "$OUT/version-$v.txt"

  "${P[@]}" -c "COPY shapes FROM PROGRAM 'cat /gen/manifest.csv' CSV"

  log "pg$v: loading"
  "${P[@]}" -c 'CALL load_all(3)'
  log "pg$v: VACUUM ANALYZE and sizes"
  "${P[@]}" <<'SQL' >/dev/null
SELECT format('VACUUM (ANALYZE) %I', relname) FROM pg_class
WHERE relkind='r' AND relname LIKE 't\_%' \gexec
CALL measure_sizes();
SQL
  log "pg$v: ->> reads"
  "${P[@]}" -c 'CALL reads(5)'
  log "pg$v: containment and GIN"
  "${P[@]}" -c 'CALL containment(5)'
  log "pg$v: UPDATE and WAL"
  "${P[@]}" -c 'CALL updates()'
  log "pg$v: behaviour"
  docker exec -i "$c" psql -U postgres -X -a -v VERBOSITY=verbose -v SHOW_CONTEXT=never \
    -f - < <(sed -n '/^-- BEHAVIOUR-BEGIN$/,/^-- BEHAVIOUR-END$/p' "${BASH_SOURCE[0]}" | grep -v '^-- BEHAVIOUR') \
    > "$OUT/behaviour-$v.txt" 2>&1 || true

  for t in res sizes plans shapes; do
    docker exec "$c" psql -U postgres -X -c "COPY $t TO STDOUT CSV HEADER" > "$OUT/$t-$v.csv"
  done
  log "pg$v: done; removing container"
  docker rm -f -v "$c" >/dev/null
}

for v in $VERSIONS; do run_version "$v"; done
echo "load at end: $(cut -d' ' -f1-3 /proc/loadavg 2>/dev/null)" >> "$OUT/machine.txt"

log "writing summary"
python3 -I - "$OUT" $VERSIONS > "$OUT/summary.md" <<'PY'
import csv, statistics as st, sys, collections
o, V = sys.argv[1], sys.argv[2:]
def rd(n, v): return list(csv.DictReader(open("%s/%s-%s.csv" % (o,n,v))))
for v in V:
    print("# PostgreSQL %s\n" % v)
    sz = {(r["shape"],r["form"],r["comp"],r["typ"]): r for r in rd("sizes",v)}
    man = {(r["shape"],r["form"]): r for r in rd("shapes",v)}
    print("## Size (bytes, total including TOAST)\n")
    print("| shape | form | compression | rows | source avg | json total | jsonb total | jsonb vs json | json compressed/external | jsonb compressed/external |")
    print("|---|---|---|---|---|---|---|---|---|---|")
    for k in sorted({x[:3] for x in sz}):
        j, b = sz[k+("json",)], sz[k+("jsonb",)]
        J, B = int(j["total"]), int(b["total"])
        print("| %s | %s | %s | %s | %s | %d | %d | %+.1f%% | %s/%s | %s/%s |" % (k[0],k[1],k[2],j["rows"],
              man[k[:2]]["avg_bytes"],J,B,(B/J-1)*100,j["n_compressed"],j["n_external"],b["n_compressed"],b["n_external"]))
    g = collections.defaultdict(list)
    for r in rd("res",v):
        k = (r["test"],)+tuple(x for x in (r["shape"],r["form"],r["comp"],r["typ"],r["variant"]) if x)
        g[k].append(r)
    print("\n## Medians (ms; for updates also WAL bytes and full-page images per row)\n")
    print("| test | key | n | median ms | min | max | extra |\n|---|---|---|---|---|---|---|")
    for k in sorted(g):
        rs = g[k]; ms = [float(r["ms"]) for r in rs]; extra = ""
        if k[0] in ("contain","gin_build"):
            extra = "val " + ",".join(sorted({r["val"] for r in rs}))
        if k[0] == "update":
            n = float(rs[0]["n_rows"])
            extra = "rows %d, WAL/row %.0f, FPI/1000 rows %.1f" % (n,
                st.median(float(r["val"])/n for r in rs), st.median(float(r["fpi"])/n*1000 for r in rs))
        print("| %s | %s | %d | %.2f | %.2f | %.2f | %s |" % (k[0]," ".join(k[1:]),len(ms),st.median(ms),min(ms),max(ms),extra))
    print()
PY
log "done: $OUT"
exit 0

# The lines below are used by run_version as a psql script (the heredoc keeps bash from parsing them).
: <<'BEHAVIOUR'
-- BEHAVIOUR-BEGIN
\pset null '<NULL>'
SELECT version();
-- duplicate keys
SELECT '{"a":1,"a":2}'::json AS j, '{"a":1,"a":2}'::jsonb AS b;
SELECT '{"a":1,"a":2}'::json->>'a' AS json_arrow, '{"a":1,"a":2}'::jsonb->>'a' AS jsonb_arrow;
SELECT json_object_keys('{"a":1,"a":2}'::json);
SELECT '{"a":1,"a":2}' IS JSON WITH UNIQUE KEYS AS is_unique;
-- key order
SELECT '{"zebra":1,"a":2,"mm":3}'::json AS j, '{"zebra":1,"a":2,"mm":3}'::jsonb AS b;
-- whitespace
SELECT '{ "a" :  1,
  "b" : [1,   2] }'::json AS j, '{ "a" :  1,
  "b" : [1,   2] }'::jsonb AS b;
SELECT length('{ "a" :  1 }'::json::text) AS json_len, length('{ "a" :  1 }'::jsonb::text) AS jsonb_len;
-- numbers
SELECT '1.10'::json AS j, '1.10'::jsonb AS b;
SELECT '1e2'::json AS j, '1e2'::jsonb AS b;
SELECT '1E-2'::json AS j, '1E-2'::jsonb AS b;
SELECT '1.0e2'::json AS j, '1.0e2'::jsonb AS b;
SELECT '-0'::json AS j, '-0'::jsonb AS b, '0.0'::jsonb AS b2;
SELECT '123456789012345678901234567890.123456789'::json AS j, '123456789012345678901234567890.123456789'::jsonb AS b;
SELECT '1e400'::json AS j, length('1e400'::jsonb::text) AS b_len;
SELECT '1e-400'::json AS j, length('1e-400'::jsonb::text) AS b_len;
SELECT '1e131071'::json AS j;
SELECT length('1e131071'::jsonb::text) AS b_len;
SELECT '1e131072'::jsonb;
SELECT '1e200000'::json AS j;
SELECT '1e200000'::jsonb;
SELECT '1e-16384'::jsonb::text = '0' AS is_zero;
SELECT 'NaN'::json;
SELECT 'NaN'::jsonb;
SELECT '[1.10]'::jsonb = '[1.1]'::jsonb AS eq_110_11, '1e2'::jsonb = '100'::jsonb AS eq_1e2_100;
SELECT '{"a":1.10}'::jsonb->>'a' AS from_jsonb, '{"a":1.10}'::json->>'a' AS from_json;
-- \u0000
SELECT '"\u0000"'::json AS j;
SELECT '"\u0000"'::json->>0 AS attempt;
SELECT '{"a":"\u0000"}'::json->>'a' AS attempt;
SELECT '"\u0000"'::jsonb;
SELECT '"\u0000"'::json::jsonb;
SELECT '"\\u0000"'::jsonb AS literal_backslash;
-- other escapes
SELECT '"é"'::json AS j, '"é"'::jsonb AS b;
SELECT '"\ud83d"'::json AS j;
SELECT '"\ud83d"'::jsonb;
-- equality
SELECT '{"a":1}'::json = '{"a":1}'::json;
SELECT '{"a":1}'::jsonb = '{"a":1}'::jsonb AS b, '{"a":1,"b":2}'::jsonb = '{"b":2,"a":1}'::jsonb AS ignores_order;
SELECT '{"a":1}'::json::text = '{"a": 1}'::json::text AS via_text;
SELECT '{"a":1}'::json::jsonb = '{"a": 1}'::json::jsonb AS via_jsonb;
SELECT '{"a":1}'::json <> '{"a":1}'::json;
SELECT '{"a":1}'::json < '{"a":2}'::json;
-- GROUP BY, DISTINCT, ORDER BY, UNION, IN
CREATE TEMP TABLE g(j json, b jsonb);
INSERT INTO g VALUES ('{"a":1}','{"a":1}'),('{"a": 1}','{"a": 1}'),('{"a":1.0}','{"a":1.0}');
SELECT j, count(*) FROM g GROUP BY j;
SELECT b, count(*) FROM g GROUP BY b;
SELECT DISTINCT j FROM g;
SELECT DISTINCT b FROM g;
SELECT j FROM g ORDER BY j;
SELECT b FROM g ORDER BY b;
SELECT j FROM g UNION SELECT j FROM g;
SELECT j FROM g UNION ALL SELECT j FROM g;
SELECT * FROM g WHERE j IN ('{"a":1}'::json);
SELECT min(j) FROM g;
SELECT count(DISTINCT j) FROM g;
SELECT count(DISTINCT b) FROM g;
CREATE TABLE pk_json(j json PRIMARY KEY);
CREATE TABLE uq_json(j json UNIQUE);
CREATE TABLE pk_jsonb(b jsonb PRIMARY KEY);
CREATE INDEX ON g USING gin (j);
CREATE INDEX ON g USING btree (j);
SELECT '{"a":1}'::json @> '{"a":1}'::json;
SELECT '{"a":1}'::json ? 'a';
SELECT jsonb_set('{"a":1}'::json, '{a}', '2');
SELECT json_set('{"a":1}'::json, '{a}', '2');
-- BEHAVIOUR-END
BEHAVIOUR