Database Setup

pg_stat_statements — Query History Bootstrap

This extension tracks execution statistics for every SQL statement that runs against your database — call count, total time, mean time, rows returned. PostgreSQL maintains this data across restarts.

When Gold Lapel connects to a database with pg_stat_statements available, it reads your historical query stats immediately. Your hottest queries are identified before Gold Lapel observes a single live request. Materialized views can be created for high-impact patterns within the first refresh cycle — no warm-up period.

Without it: Gold Lapel learns query patterns from scratch by observing live traffic through the proxy. For busy applications this takes seconds. For quieter ones, perhaps a few minutes. The extension simply provides a head start.

Install

-- 1. Add to postgresql.conf:
shared_preload_libraries = 'pg_stat_statements'
-- 2. Restart PostgreSQL
-- 3. Create the extension:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Part of the PostgreSQL contrib package — available on virtually every provider and installation. Requires shared_preload_libraries and a PostgreSQL restart because it needs to allocate shared memory at startup. Many managed providers enable it by default — you may find it already installed.

pg_trgm — Trigram Indexes for LIKE/ILIKE

Enables GIN trigram indexes for LIKE, ILIKE, and %pattern% queries. Standard B-tree indexes are useless for leading-wildcard searches — PostgreSQL must fall back to a sequential scan every time. Trigram indexes solve this by indexing three-character subsequences, allowing the planner to use an index for patterns that would otherwise require reading every row.

When Gold Lapel detects wildcard text searches hitting the same columns repeatedly, it creates trigram indexes that make those searches dramatically faster. A WHERE name ILIKE '%smith%' that previously scanned the entire table can use an index instead.

Without it: LIKE and ILIKE queries are proxied to PostgreSQL as normal. They work, but wildcard searches remain sequential scans. The queries are not broken — they are simply slower than they need to be.

Install

CREATE EXTENSION IF NOT EXISTS pg_trgm;

Part of the PostgreSQL contrib package. Available on all major providers. No restart required — this is the easiest of the three to install.

pg_ivm — Instant Materialized View Refresh

This is the extension I would most encourage you to install, if your environment supports it. Standard materialized views in PostgreSQL are static snapshots — they must be explicitly refreshed to reflect new data. pg_ivm changes this entirely. It maintains materialized views via triggers on the base tables: every INSERT, UPDATE, or DELETE immediately updates the matview. Zero staleness.

A write to your orders table instantly updates any materialized view that depends on orders. Reads always return current data, with no rebuild delay and no refresh cycle to wait for.

Without it: Gold Lapel refreshes materialized views on a configurable polling cycle (default: every 60 seconds). When a write is detected, the affected matview is marked stale and subsequent reads route directly to PostgreSQL until the next refresh completes. Gold Lapel never serves stale data — it routes around it. This works reliably, but pg_ivm eliminates the staleness window entirely.

Install

-- 1. Install the pg_ivm package on your PostgreSQL server
-- 2. Add to postgresql.conf:
shared_preload_libraries = 'pg_ivm'
-- 3. Restart PostgreSQL
-- 4. Create the extension:
CREATE EXTENSION IF NOT EXISTS pg_ivm;

Third-party extension — not bundled with PostgreSQL. Requires both a package installation on the server and a shared_preload_libraries entry with a restart. Available on some managed providers; check your provider's extension list. If yours does not offer it, Gold Lapel's polling refresh handles the duty capably.

Multiple shared_preload_libraries

If you are installing both pg_stat_statements and pg_ivm, combine them in a single shared_preload_libraries entry. One restart covers both:

shared_preload_libraries = 'pg_stat_statements, pg_ivm'

The order does not matter. PostgreSQL loads all listed libraries at startup regardless of sequence.

Setting wal_level to logical

Logical decoding requires wal_level = logical. This is the highest WAL level in PostgreSQL, and it includes all the information needed for Gold Lapel to see exactly which tables were modified by any transaction, regardless of where that transaction originated.

First, check your current setting:

SHOW wal_level;

 wal_level
-----------
 logical
(1 row)

If your WAL level is replica (the default) or minimal, you will need to change it:

-- In postgresql.conf:
wal_level = logical

-- Then restart PostgreSQL:
sudo systemctl restart postgresql

This requires a PostgreSQL restart. Plan accordingly — on production systems, coordinate this with a maintenance window. The restart itself is fast; the configuration change is what requires planning.

What happens without logical WAL

Gold Lapel does not require wal_level = logical. When logical decoding is unavailable, Gold Lapel falls back to NOTIFY-based write detection automatically. This mode installs lightweight triggers on tracked tables that send a PostgreSQL NOTIFY event on each write. It catches the vast majority of external writes without any server configuration changes.

The NOTIFY fallback works well for most deployments. Logical decoding provides broader coverage — it catches writes from sources that bypass triggers (such as COPY commands, logical replication subscribers, or bulk operations with triggers disabled). If your write sources are primarily your application and standard migrations, the NOTIFY fallback is likely sufficient.

For a complete comparison of both detection modes, their trade-offs, and guidance on which to choose, see Write Detection.

What Gold Lapel creates

• _goldlapel schema — all Gold Lapel objects live in a dedicated schema, cleanly separated from your application's tables
• Materialized views — pre-computed query results in _goldlapel.mv_<hash>
• Indexes — targeted indexes on both base tables and matviews in _goldlapel.idx_<hash>
• Metadata tables — _goldlapel.meta and _goldlapel.index_meta for tracking what was created and why
• Extensions — CREATE EXTENSION for pg_trgm, pg_stat_statements, and pg_ivm if not already present

Recommended privileges

The simplest and most reliable approach: make the Gold Lapel user the owner of the target database. This grants all necessary privileges without fine-grained permission management:

-- Option A: Make the GL user the database owner (recommended)
ALTER DATABASE mydb OWNER TO goldlapel;

-- Option B: Grant specific privileges
GRANT CREATE ON DATABASE mydb TO goldlapel;
GRANT CREATE ON SCHEMA public TO goldlapel;

If database ownership is not possible, the minimum privileges Gold Lapel needs are:

• CREATE on the database — to create the _goldlapel schema
• CREATE on the public schema — to create indexes on your tables
• SELECT on application tables — to read data for matview creation and shadow verification
• CREATE EXTENSION privilege — to install extensions if not already present (optional; you can install them manually)

Gold Lapel never modifies your application tables or data. It reads from them to build materialized views and creates its own objects in a separate schema. The goldlapel clean command removes everything Gold Lapel created, leaving your database exactly as it was.

Checking current privileges

Connect as the user Gold Lapel will use and run:

-- Check current user's privileges on the database
SELECT has_database_privilege(current_user, current_database(), 'CREATE');

-- Check schema creation privilege
SELECT has_schema_privilege(current_user, 'public', 'CREATE');

-- Check if the user owns the database (ideal)
SELECT d.datname, pg_catalog.pg_get_userbyid(d.datdba) AS owner
FROM pg_catalog.pg_database d
WHERE d.datname = current_database();

If any of these return f (false), you will need to grant the corresponding privilege before Gold Lapel can fully optimize. Gold Lapel still starts and proxies queries without these privileges — it simply cannot create the database objects that power its optimizations.

AWS RDS / Aurora

RDS manages PostgreSQL configuration through parameter groups. You cannot edit postgresql.conf directly — instead, you modify the parameter group attached to your instance.

-- In your RDS parameter group:
shared_preload_libraries = 'pg_stat_statements'
rds.logical_replication = 1

-- Then reboot the RDS instance for changes to take effect.
-- After reboot, connect and create the extensions:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS pg_trgm;

Supabase

Supabase provides a web dashboard for managing extensions and has several configuration defaults that work in Gold Lapel's favor.

-- Extensions dashboard: Database → Extensions
-- Or via SQL:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS pg_trgm;

Neon

Neon's serverless PostgreSQL has a few considerations around connection limits and auto-suspend that are worth knowing about.

-- Extensions available via SQL:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS pg_trgm;

Heroku Postgres

Heroku's extension availability depends on your plan tier.

-- Standard and Premium plans:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS pg_trgm;

Google Cloud SQL

Cloud SQL uses database flags for server configuration, accessible via the Cloud Console, gcloud CLI, or Terraform.

-- In Cloud SQL flags:
-- cloudsql.enable_pg_stat_statements = on
-- Then:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS pg_trgm;