Database Setup

Query history bootstrap — lets Gold Lapel read your historical query stats on first connection, skipping the warm-up period.

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.

Trigram indexes for LIKE/ILIKE — enables index-backed wildcard text searches that would otherwise require sequential scans.

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 to install.

Phonetic search indexes — enables expression indexes on soundex() and dmetaphone() for fuzzy name matching.

Phonetic functions like soundex() and dmetaphone() map words to phonetic codes — "Smith" and "Smyth" produce the same code, so WHERE soundex(name) = soundex('Smyth') matches both spellings. Without an index, PostgreSQL must compute the phonetic code for every row on every query.

When Gold Lapel detects repeated phonetic equality queries, it creates B-tree expression indexes on the function output — turning sequential scans into fast index lookups.

Without it: Phonetic queries are proxied to PostgreSQL as normal. They work, but remain sequential scans. Gold Lapel cannot create phonetic indexes without the extension.

Install

CREATE EXTENSION IF NOT EXISTS fuzzystrmatch;

Part of the PostgreSQL contrib package. Available on all major providers. No restart required.

Vector similarity HNSW indexes — enables index-backed approximate nearest neighbor search for <=> cosine distance queries on embeddings.

Enables HNSW indexes for vector similarity search. When Gold Lapel detects repeated vector search queries — for example, ORDER BY embedding <=> '[0.1, 0.2, ...]' LIMIT 10 — it creates HNSW indexes that turn sequential scans into fast approximate nearest neighbor lookups. Embedding-based search (semantic search, recommendation engines, RAG retrieval) benefits dramatically from HNSW indexing.

Without it: Vector similarity queries are proxied to PostgreSQL as normal. They work, but each query performs a sequential scan comparing the query vector against every row. The queries produce correct results, just slower than they need to be.

Install

CREATE EXTENSION IF NOT EXISTS vector;

Available on most managed providers (RDS, Supabase, Neon, Cloud SQL). No restart required.

Instant materialized view refresh — every write to a base table immediately updates dependent matviews. Zero staleness.

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.

Geospatial queries — required for Gold Lapel's geoadd, georadius, and geodist methods.

PostGIS adds geographic object support to PostgreSQL — spatial data types, spatial indexes, and hundreds of functions for distance calculations, containment checks, and geometry operations. Gold Lapel's geospatial convenience methods (part of the Redis Replacement API) use PostGIS under the hood for ST_DWithin radius queries and ST_Distance calculations.

Unlike the other extensions on this page, PostGIS requires a system-level package installation before PostgreSQL can load it. geoadd() auto-creates the SQL extension, but the underlying library must already be present on the server.

Without it: All non-geospatial Gold Lapel features work normally. Only the geoadd, georadius, and geodist methods require PostGIS. If you are not using geospatial queries, you do not need this extension.

Install the system package

# Ubuntu/Debian
sudo apt-get install postgresql-16-postgis-3

# macOS (Homebrew)
brew install postgis

Create the extension in PostgreSQL

CREATE EXTENSION IF NOT EXISTS postgis;

PostGIS is a large extension with its own release cycle. Version availability varies by provider — most managed providers offer PostGIS, but check your provider's extension list. No shared_preload_libraries entry or restart required.

Server-side Lua scripting — required for Gold Lapel's script() method for atomic multi-step operations.

pllua embeds a Lua interpreter inside PostgreSQL, allowing you to execute Lua code directly on the database server. Gold Lapel's script() method uses pllua under the hood to run atomic multi-step operations — the Redis equivalent of EVAL / Lua scripting. This enables complex read-modify-write sequences that execute entirely within the database, without round-trips between your application and Postgres.

Like PostGIS, pllua requires a system-level package installation before PostgreSQL can load it. script() auto-creates the SQL extension, but the underlying library must already be present on the server.

Without it: All non-scripting Gold Lapel features work normally. Only the script() method requires pllua. PL/pgSQL functions still work via standard SQL — pllua is only needed for the Redis-style Lua scripting interface.

Install the system package

# Ubuntu/Debian
sudo apt install postgresql-16-pllua

Create the extension in PostgreSQL

CREATE EXTENSION IF NOT EXISTS pllua;

Not available on most managed providers (RDS, Neon, Supabase). Available on self-hosted PostgreSQL and future Gold Lapel Manor. No shared_preload_libraries entry or restart required.