Chapter 20: Gold Lapel and Documents

Why a Proxy, Not a Library

Gold Lapel for Cache and for Search lived inside your application process. The Documents product takes a different posture — it moves out of process and stands in front of PostgreSQL instead.

┌────────┐       ┌─────────────────────────┐       ┌──────────┐
│  app   │──────▶│  goldlapel proxy        │──────▶│ postgres │
└────────┘       │                         │       └──────────┘
                 │  ┌───────────────────┐  │
                 │  │ pattern detector  │  │
                 │  ├───────────────────┤  │
                 │  │ result cache      │  │
                 │  ├───────────────────┤  │
                 │  │ matview planner   │  │
                 │  └───────────────────┘  │
                 └─────────────────────────┘

The reason is straightforward, and I should like to state it plainly. JSONB workloads produce query patterns that only become visible at the wire. A library sees the single call the current process happens to have made. A proxy sees the distribution across every process in the fleet — every replica, every worker, every cron job arriving at three in the morning with a report to generate. That distribution is the raw material auto-indexing requires. A pattern fired 14 times by one process is noise; the same pattern fired 847 times across 40 processes in an hour is a standing question the database keeps answering the hard way. I find the database resents this, in its quiet way.

The proxy speaks the PostgreSQL wire protocol v3 natively, so there is no translation layer and no re-serialization cost. Cache hits are served from the proxy's memory; cache misses are passed through to PostgreSQL entirely unmodified, with the extra hop costing a handful of microseconds on a local socket — current figures are maintained on the benchmarks page and not in this book, because a benchmark that does not update is a benchmark that is quietly lying.

PostgreSQL JSONB Auto Indexing, Explained

Here is the headline, such as it is. When your application issues a JSONB query, the proxy sees it, records its shape, and — once a threshold has been crossed — creates the index PostgreSQL would have wanted all along. The proxy does the bookkeeping your pager would otherwise do for you.

Suppose your orders table stores documents, and the application is doing this:

SELECT *
  FROM orders
 WHERE data @> '{"status":"shipped"}';

The proxy records that shape. After enough firings, the detection log reads something like the following:

[goldlapel] pattern observed: orders WHERE data @> {...}
            count=847 window=1h selectivity=0.03
            recommendation: GIN (jsonb_path_ops)
            action: CREATE INDEX CONCURRENTLY

And the DDL the proxy executes, exactly as written, is this:

CREATE INDEX CONCURRENTLY idx_orders_doc_pathops
  ON orders USING GIN (data jsonb_path_ops);

One recommendation. One DDL statement. One index.

CREATE INDEX CONCURRENTLY (available since PostgreSQL 8.2, a courtesy from the PostgreSQL project that has aged beautifully) is used in every auto-create path. The proxy never issues a blocking CREATE INDEX. If the concurrent build fails partway through — a rare but entirely possible outcome — the proxy detects the invalid index, drops it, and either retries or backs off as the situation warrants.

The interesting part of that DDL, if I may direct your attention, is the operator class. PostgreSQL offers three practical choices for JSONB, and selecting the wrong one discards roughly half the index's value:

• jsonb_ops (the default, since PostgreSQL 9.4). Indexes every key and every value. Supports containment (@>), the key-existence operators (?, ?&, ?|), and — on PostgreSQL 12 and later — the jsonpath operators @? and @@ (which correspond to the jsonb_path_exists and jsonb_path_match functions). Largest on disk.
• jsonb_path_ops (also 9.4). Indexes a hash of each path-and-value pair. Because only the path-and-value hash is stored rather than every key and value separately, the index is smaller on disk and typically faster for containment lookups — a documented property of the operator class since 9.4. The trade is that it supports a strictly narrower set of operators: @> on every supported version, and (from PostgreSQL 12 onward) the jsonpath operators @? and @@. It does not support ?, ?&, or ?|. This is the trade-off the operator-class name does not volunteer.
• Expression indexes on extracted paths, for instance ((data->>'email')). These are B-tree indexes on one field. They are the right answer when the application is doing equality or range filtering on a known key rather than containment across the whole document.

The proxy distinguishes these by inspecting the query, not by guessing. A pure @> workload earns jsonb_path_ops. A workload mixing @> and ? earns jsonb_ops. A workload of the form WHERE data->>'email' = $1 earns the expression index:

CREATE INDEX CONCURRENTLY idx_users_doc_email
  ON users ((data->>'email'));

A full-table GIN index on data would also answer that email query, but it would be larger, slower to build, and slower to update on write. The expression index is the smaller, cheaper answer, and the proxy selects it whenever the query shape permits. Using the smallest index that answers the question is, in my view, a form of good manners the database repays with interest.

Defaults for when auto-creation fires — min_pattern_count and min_selectivity — live in goldlapel.toml, and we shall look at those values presently.

What the Proxy Sees That a Library Cannot

A single call is not a workload; a thousand calls arriving in the same shape, however, rather is. The proxy's advantage is the aggregate view — and I should like to illustrate it with a small TypeScript scene.

Consider this innocuous fragment:

for (const id of ids) {
  await gl.doc_find('users', { _id: id });
}

Every iteration compiles to roughly the same parameterised SQL, differing only in the bound parameter. Inside the application, this looks perfectly reasonable. Inside the proxy, it looks rather like this:

[goldlapel] N+1 pattern: users WHERE _id = $1
            fired 47 times in 312ms by pid=8421
            suggest: gl.doc_find('users', { _id: { $in: [...] } })

The N+1 heuristic watches for repeated query shapes issued in quick succession from a single connection. The dashboard surfaces both the pattern and the rewrite, so that the discovery and the remedy arrive together.

The same wire-level visibility powers the short-term result cache. Two identical queries arriving within the configured TTL — default 60 seconds, per-query configurable — are answered from memory rather than re-executed. Repeated aggregation pipelines, the third category, become materialized view candidates. We shall attend to those shortly.

The Dashboard — JSONB Index Optimisation at a Glance

When you open the UI, four panels present themselves in this order. Every decision the proxy makes on your behalf lives here, visible and reversible. A household staff that cannot show its work is a household staff that has not earned its keep.

Mode. Whether the proxy is in passthrough, cached, or optimising mode; the detected WAL level on the upstream database (relevant because logical replication changes what the proxy is willing to do on the primary versus a replica); which management connection is in use for DDL. If the proxy cannot run DDL — a read-only replica, an insufficient role — this panel says so in plain language, and the Index panel switches to suggestion-only. No silent failures, and no unsolicited heroics.

Environment. A grid of what the proxy found when it connected. Installed extensions (pg_trgm, pgvector, pg_stat_statements), TLS posture, server version, and the detected serverless platform. Platform detection uses connection-time signals to distinguish Neon, Supabase, RDS, and self-hosted — each of which is, in my experience, quite good company.

Index. Every index the proxy has auto-created, laid out as a table:

Name                          Type        Hits (7d)   Size      Status
─────────────────────────────────────────────────────────────────────────
idx_orders_doc_pathops        GIN path    412,908     142 MB    active
idx_users_doc_email           expr btree   98,441      38 MB    active
idx_events_doc_ops            GIN          12            4 MB    would drop
idx_sessions_doc_token        expr btree   88,210      22 MB    active

A "would drop" flag appears when an index has been unused long enough to have demonstrably earned its rest; the exact window is configurable in goldlapel.toml and defaults to a value that errs on the side of patience. The proxy never drops an index without a human clicking the button — that is a line I should never like to cross — but it will tell you which ones are earning their keep and which are merely exacting a quiet tax on every write.

Query. Top patterns by frequency, each showing the SQL the proxy generates, the matview or cache status, and the p50/p95 served from each path. If a pattern is hot and uncovered, it sits at the top wearing a yellow stripe, which I find to be the correct colour for a polite but firm suggestion.

Materialised Views, Automatically

Chapter 12 described the architecture by which Gold Lapel compiles aggregation pipelines to SQL, and the 84x figure that architecture produced on the book's sample workload. The proxy's role here is narrower, and happily so: notice when a compiled pipeline is being re-run often enough that materialisation would pay, and manage the resulting matview with the attention it deserves.

The proxy watches the query log. When the same pipeline — structurally identical SQL, parameters aside — appears frequently enough across a configurable window to justify the build cost, it becomes a materialised view candidate. The exact thresholds live in the config file; the dashboard shows, for each candidate, the observed count, the interval, and the estimated cost-vs-savings. Consider a repeated daily aggregation:

SELECT date_trunc('day', created_at) AS day,
       data->>'status'                AS status,
       count(*)                      AS n
  FROM orders
 WHERE created_at >= $1 AND created_at < $2
 GROUP BY 1, 2;

Observed often enough, the proxy creates the matview, stripped of the parameter range so that it can serve many date windows:

CREATE MATERIALIZED VIEW mv_orders_daily_by_status AS
  SELECT date_trunc('day', created_at) AS day,
         data->>'status'                AS status,
         count(*)                      AS n
    FROM orders
   GROUP BY 1, 2;

CREATE UNIQUE INDEX mv_orders_daily_by_status_pk
  ON mv_orders_daily_by_status (day, status);

The unique index is not incidental, and I should like a brief moment to say why. REFRESH MATERIALIZED VIEW CONCURRENTLY (PostgreSQL 9.4) requires one; without it, refresh falls back to the blocking form that takes an ACCESS EXCLUSIVE lock — and an ACCESS EXCLUSIVE lock, as you may imagine, is not something I am inclined to inflict upon a busy table during business hours. The proxy creates the unique index automatically based on the GROUP BY keys.

Refresh cadence is tied to the observed query arrival rate. If the pipeline is queried every five minutes, the matview refreshes on a five-minute schedule — always concurrent, always logged, always revocable from the dashboard. There is nothing magical occurring here. The proxy never creates a matview silently; every creation is an entry in the decision log with the pattern count, the interval observed, and the DDL executed. Quiet, auditable, reversible. That is the standard.

Configuration — goldlapel.toml and Live Reload

Everything in this chapter is configurable, and everything is optional. The shipping config surface is intentionally small; the shape a typical goldlapel.toml takes looks roughly like this:

[jsonb]
auto_index       = true
min_pattern_count = 500
min_selectivity   = 0.10
prefer_path_ops   = true

[cache]
result_ttl_seconds = 60

[matview]
auto_create      = true
refresh_strategy = "concurrent"

To disable JSONB auto-indexing for a single run without editing the file:

goldlapel --disable-jsonb-indexes

This flag disables DDL execution but leaves the detector running, so the dashboard continues to show suggestions — you simply run the CREATE INDEX yourself. If you would prefer silence entirely, set auto_index = false in the TOML and the proxy shall comply without a murmur.

Live reload is a SIGHUP, as it should be:

kill -HUP $(pgrep goldlapel)

No connection is dropped. In-flight queries complete on the old configuration; new queries pick up the new configuration. Reloadable keys are documented per-section; keys that require a restart are explicitly flagged in the dashboard's Mode panel rather than failing silently.

The Three Roads

Cache became an automatic layer. Search became an automatic layer. Documents becomes an automatic layer. The silhouette is the same across all three books: something watches, something learns, something proposes, something executes, and the dashboard shows its work so that a human may overrule it at any point without ceremony. Consistency, I have come to believe, is worth a great deal in infrastructure — the same idea repeated at three layers is not a lack of imagination; it is a claim that the idea was correct the first time.

Honest Boundary — What Auto-Indexing Will Not Do

The book carries two standing boundaries: the 2.5–4x write gap and the 50+ node sharding limit. This chapter adds a third, product-specific one, and I should be forthcoming about all three lest I be the sort of waiter who overstates his case.

The proxy will not index a pattern it has not seen. Cold-start workloads are unaffected. The first firing of a novel query shape runs against whatever indexes already exist; the proxy only helps the second, the tenth, and the five-hundredth firings. Pre-launch profiling remains your job, and I would not presume otherwise.

The proxy will not fix a bad schema. If your JSONB documents conflate three entity types under one table, no index choice repairs the model. Chapter 16's schema rules apply here too, and they come first in the reading order for that very reason. A well-chosen index on a confused schema is a polished handle on the wrong door.

The proxy will not accelerate writes. Every index it creates is a write tax — small per index but real, and cumulative across a growing set of auto-created indexes. The 2.5–4x write gap the book names elsewhere is the floor here too; more indexes push that floor down, not up. The proxy's N+1 detector flags indexes that have ceased to earn their keep, and the dashboard exposes the "would drop" flag so that the write-tax stays visible. Write acceleration for the library itself — parallel COPY across partitioned tables and libpq pipeline mode for small-insert batches — is on the public roadmap and not yet shipped. I would rather tell you so plainly than have you discover it at an inconvenient hour.

These are limits of the proxy, not limits of PostgreSQL, and not comparisons to anything else. MongoDB, for its part, has spent many years solving document storage with considerable skill; the question this book asks is not whether MongoDB is good — it is — but whether you need it for your particular situation.

A Side-by-Side of the Proxy On and Off

Every row in the table is a shipping feature. Items on the roadmap — parallel COPY, libpq pipeline-mode writes — are named where they appear in the chapter rather than quietly listed here.

I would note, for fairness, that the left-hand column is entirely sufficient for a great many workloads. A careful operator with pg_stat_statements and a patient disposition can reproduce most of what the right-hand column offers. The product is a convenience at scale, not a prerequisite for competence.

The Seven Languages, One Proxy

Gold Lapel ships SDKs for Python, Node.js, Ruby, Java, PHP, Go, and .NET (C#). By the time a query reaches the proxy, the language has been stripped away; what arrives is SQL on the wire, and the wire has no opinions about taste in programming languages.

# Python
gl.doc_find("orders", {"status": "shipped"})

// Go
gl.DocFind(ctx, "orders", map[string]any{"status": "shipped"})

Both compile to the same single line:

SELECT _id, data, created_at FROM orders WHERE data @> '{"status":"shipped"}';

Two languages. One SQL statement. No favouritism.

The generated SQL is identical across SDKs, to the byte where the query plan is concerned — the same WHERE clauses, the same operator classes, the same parameterisation. This is the payoff of the "visible SQL" design: every optimisation in this chapter applies to every SDK equally, because the thing being optimised is the thing on the wire.