Postgres Extensions

pg_stat_statements — Query History

Tracks execution statistics for all SQL statements — call count, total time, mean time, rows returned. PostgreSQL maintains this data across restarts, which means Gold Lapel can read your hottest queries on startup and begin optimizing them immediately. No warm-up period — matviews are created for your most impactful queries within the first refresh cycle.

Without it: Gold Lapel learns query patterns from scratch by observing traffic through the proxy. This works perfectly well — for busy applications it takes seconds, for quieter ones perhaps a few minutes. The extension simply gives Gold Lapel a head start.

Install

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

Part of the PostgreSQL contrib package. Available on all major providers. Many providers enable it by default — you may well find it is already waiting for you.

pg_trgm — Trigram Indexes

Enables GIN trigram indexes for LIKE, ILIKE, and %pattern% queries. When Gold Lapel detects wildcard text searches hitting the same columns repeatedly, it creates trigram indexes that make those searches orders of magnitude faster. A WHERE name ILIKE '%smith%' that previously required a full sequential scan can use an index instead.

Without it: LIKE/ILIKE queries are proxied to PostgreSQL as normal — they work, but without trigram indexes they fall back to sequential scans on every search. 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 (RDS, Supabase, Neon, Cloud SQL). No restart required. Gold Lapel will auto-create it if permissions allow — you may not need to run this yourself.

fuzzystrmatch — Phonetic Search Indexes

Enables phonetic search indexes for soundex() and dmetaphone() queries. When Gold Lapel detects equality comparisons using these functions — for example, WHERE soundex(name) = soundex('Smith') — it creates B-tree expression indexes on the phonetic function output. This turns sequential scans into index lookups for fuzzy name matching.

Phonetic search is useful for matching names despite spelling variations: "Smith" matches "Smyth", "Stephen" matches "Steven". Gold Lapel indexes both soundex() (simpler, English-focused) and dmetaphone() (more accurate, handles more languages).

Without it: Phonetic queries are proxied to PostgreSQL as normal — they work, but each query requires a sequential scan computing the phonetic code for every row. The queries produce correct results, just slower than they need to be.

Install

CREATE EXTENSION IF NOT EXISTS fuzzystrmatch;

Part of the PostgreSQL contrib package. Available on all major providers. No restart required. Gold Lapel will auto-create it if permissions allow.

pgvector — Vector Similarity Indexes

Enables HNSW indexes for vector similarity search (<=> cosine distance). When Gold Lapel detects repeated vector search queries, it auto-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. Gold Lapel will auto-create it if permissions allow.

Full-Text Search (built-in)

PostgreSQL's built-in full-text search — to_tsvector() and @@ — requires no extension at all. When Gold Lapel detects repeated full-text search queries like WHERE to_tsvector('english', body) @@ to_tsquery('search terms'), it auto-creates GIN indexes on the tsvector expression. This is the same approach Elasticsearch uses internally — inverted indexes on tokenized text — except it runs entirely within PostgreSQL.

No installation needed. Gold Lapel detects the @@ operator and to_tsvector() function automatically and creates the appropriate GIN index.

pg_ivm — Instant Matview Refresh

This is the extension I would most encourage you to install. It maintains materialized views via triggers — every INSERT, UPDATE, or DELETE to a base table instantly updates the matview. Zero staleness. A write to orders immediately updates any matview that depends on orders. Reads always return fresh data, with no rebuild delay.

Without it: Gold Lapel refreshes matviews on a polling cycle (default: every 60 seconds). When a write is detected, the matview is marked stale and subsequent reads route directly to PostgreSQL — Gold Lapel never serves stale cached data. The matview is refreshed on the next cycle and reads resume from it. This works reliably, but pg_ivm eliminates the window entirely. The staleness window is configurable via refresh_interval_secs.

Install

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

Third-party extension — not bundled with PostgreSQL. Available on some managed providers. If yours does not offer it, Gold Lapel's polling refresh handles the duty capably.

pllua — Server-Side Lua Scripting

pllua embeds a Lua interpreter inside PostgreSQL, enabling Gold Lapel's script() method — the equivalent of Redis's EVAL command. This allows atomic multi-step operations to execute entirely within the database: read a value, compute, write the result, all without round-trips between your application and Postgres.

Without it: The script() method raises an error explaining that pllua is required. All other Gold Lapel features work normally. PL/pgSQL functions continue to work via standard SQL — pllua is only needed for the Redis-style Lua scripting interface.

Install

# 1. Install the pllua package on your Postgres server:
# Ubuntu/Debian: sudo apt install postgresql-16-pllua
# 2. Create the extension:
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.

Citus — Distributed PostgreSQL

Gold Lapel auto-detects Citus at startup. When the citus extension is installed, Gold Lapel queries for worker node count and displays the distributed topology in the dashboard connectivity diagram. No configuration required — Gold Lapel works transparently with Citus, whether you have distributed tables or not.

With distributed tables: The dashboard shows the worker node count (e.g. "citus: distributed mode (4 worker nodes)"). Gold Lapel's optimizations — caching, matviews, indexes — operate at the coordinator level as usual.

Without distributed tables: Gold Lapel notes that Citus is installed but no distributed tables exist yet. Everything works normally.

Citus is detected automatically and requires no Gold Lapel configuration. The detection is logged at startup alongside extension status.

Checking Your Extensions

If you are unsure which extensions are already available, a quick query will settle the matter:

SELECT extname FROM pg_extension
WHERE extname IN ('pg_trgm', 'pg_ivm', 'pg_stat_statements', 'fuzzystrmatch', 'vector', 'pllua');

On startup, Gold Lapel checks for these and logs the result:

extensions: all installed (pg_trgm, pg_ivm, pg_stat_statements, fuzzystrmatch, vector)

extensions: install missing extensions for optimal performance installed="pg_trgm" missing="pg_ivm, pg_stat_statements, fuzzystrmatch, vector"

Gold Lapel also attempts to create these extensions automatically on startup if it has sufficient permissions. If it cannot, it logs the command you would need to run yourself.