Write Detection

Every write, from every source. Detected automatically so your caches are never stale.

The problem: writes that Gold Lapel does not see

Gold Lapel sits between your application and PostgreSQL. Every query that passes through the proxy is visible — including writes. When your application issues an INSERT, UPDATE, or DELETE through the proxy, Gold Lapel knows about it immediately and invalidates the relevant caches.

But your application is not the only source of writes.

Migrations alter table structures. Cron jobs update aggregates. A colleague runs an ad-hoc UPDATE from psql. A separate microservice writes directly to the same database. An ETL pipeline bulk-loads data overnight. None of these writes pass through the proxy, which means none of them — by default — would trigger cache invalidation.

If Gold Lapel ignored these writes, it would serve stale data from its caches and materialized views. That is not acceptable. A proxy that serves incorrect results is worse than no proxy at all.

Write detection solves this. Gold Lapel subscribes to PostgreSQL itself — either through the WAL stream or through NOTIFY triggers — so it learns about every write to every tracked table, regardless of where that write originated. The cache is invalidated correctly, the materialized views are marked stale, and your application receives fresh data.

Two modes are available, and Gold Lapel selects the best one automatically.

Mode 1: Logical decoding (WAL subscription)

This is Gold Lapel's preferred write detection method. It is enabled by default when the PostgreSQL server supports it.

Logical decoding subscribes to PostgreSQL's Write-Ahead Log — the same stream that powers replication. Every committed write to every table appears in the WAL, regardless of which connection, application, or tool originated it. Gold Lapel creates a replication slot, subscribes to changes, and receives a real-time feed of every INSERT, UPDATE, and DELETE across the entire database.

What it catches

Everything. Every write from every source:

• Application writes through the proxy
• Application writes that bypass the proxy (direct connections)
• Migrations and schema changes
• Cron jobs, background workers, and scheduled tasks
• Manual queries from psql, pgAdmin, or any SQL client
• Other microservices writing to the same database
• ETL pipelines, bulk imports, and COPY commands
• Trigger-generated writes and stored procedures

If PostgreSQL committed it, Gold Lapel sees it. There are no blind spots.

Requirement: wal_level = logical

Logical decoding requires one PostgreSQL configuration change: the wal_level must be set to logical. By default, PostgreSQL ships with wal_level = replica, which supports physical replication but not logical decoding.

To check your current setting:

SHOW wal_level;

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

If the output shows replica or minimal, logical decoding is not available. You will need to change this setting and restart PostgreSQL.

How to enable: self-hosted PostgreSQL

# In postgresql.conf:
wal_level = logical

# Restart PostgreSQL for the change to take effect
sudo systemctl restart postgresql

This is the only change required. A PostgreSQL restart is necessary because wal_level is a server-start parameter — it cannot be changed with ALTER SYSTEM and reloaded. Plan the restart accordingly.

How to enable: managed providers

Each managed provider exposes this setting differently. Here is a brief summary — for a complete checklist including extensions and permissions, see the Database Setup guide.

If your provider is not listed, check whether logical replication is available in its documentation. The setting name varies, but the underlying requirement is always the same: wal_level = logical.

Performance considerations

Logical decoding adds minimal overhead. The WAL is already written for crash recovery — changing wal_level from replica to logical increases the amount of information recorded per write, but the difference is modest for most workloads. PostgreSQL's documentation characterizes the overhead as "small."

The replication slot Gold Lapel creates (_goldlapel_slot) must be consumed regularly to prevent WAL retention from growing unboundedly. Gold Lapel consumes from the slot continuously while running. If Gold Lapel is stopped for an extended period, the replication slot retains WAL segments until it reconnects. On managed providers with limited storage, be aware that a dormant slot can accumulate WAL. Gold Lapel drops its replication slot cleanly on shutdown to prevent this.

What the startup log shows

goldlapel v0.14.0
  :7932 -> localhost:5432 (butler, pool = 20)
  write detection: logical decoding (WAL)
  dashboard: http://127.0.0.1:7933

INFO goldlapel::wal: logical decoding slot created slot="_goldlapel_slot"
INFO goldlapel::wal: write detected table="orders" operation="INSERT"
INFO goldlapel::cache: invalidated cache entries tables=["orders"] matviews=["mv_00b59a8157e6f5ae"]

Mode 2: NOTIFY trigger fallback

When logical decoding is not available — because wal_level is not set to logical, or because the PostgreSQL user lacks replication privileges — Gold Lapel falls back to NOTIFY-based write detection automatically. No configuration needed. No action on your part.

This mode uses PostgreSQL's built-in NOTIFY/LISTEN mechanism. Gold Lapel installs lightweight triggers on each table it tracks. When a write occurs on that table — from any connection, not just the proxy — the trigger fires a NOTIFY event. Gold Lapel is listening, and it invalidates the relevant caches immediately.

How it works

Gold Lapel installs statement-level triggers on each table that has at least one active materialized view or cached query. The triggers are minimal — a single pg_notify call per statement, not per row. They are installed in the _goldlapel schema and named predictably.

Here is what the triggers look like. You do not need to create these yourself — Gold Lapel manages them entirely:

-- Gold Lapel auto-installs these triggers on tracked tables.
-- You do not need to create them yourself.
-- Shown here for transparency.

CREATE OR REPLACE FUNCTION _goldlapel.notify_orders()
RETURNS trigger AS $$
BEGIN
  PERFORM pg_notify('_goldlapel_invalidate', 'orders');
  RETURN NULL;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER _goldlapel_notify_orders
  AFTER INSERT OR UPDATE OR DELETE ON orders
  FOR EACH STATEMENT
  EXECUTE FUNCTION _goldlapel.notify_orders();

The trigger fires once per statement (not once per row), so a bulk UPDATE orders SET status = 'shipped' WHERE ... that modifies 10,000 rows generates a single NOTIFY event, not 10,000. The overhead is negligible.

What it catches

NOTIFY triggers catch writes from any source — the same breadth as logical decoding. Any connection that executes an INSERT, UPDATE, or DELETE on a tracked table will fire the trigger and generate the NOTIFY event. This includes direct psql sessions, cron jobs, other services, and migrations.

Limitations compared to logical decoding

NOTIFY mode is effective for the vast majority of deployments, but it has a few constraints worth understanding:

• Coverage is table-by-table. Triggers are installed only on tables that Gold Lapel has already observed queries for. If a write lands on a table Gold Lapel has not yet tracked, there is no trigger to fire. Logical decoding, by contrast, sees every table from the moment it connects. In practice, the gap is small — Gold Lapel discovers tables within minutes of first traffic — but it exists during the initial warm-up period.
• DDL changes are not captured. NOTIFY triggers fire on data changes (INSERT/UPDATE/DELETE), but not on schema changes (ALTER TABLE, DROP TABLE, TRUNCATE). Logical decoding captures TRUNCATE events natively. For ALTER TABLE, both modes rely on Gold Lapel's separate DDL detection mechanism.
• TRUNCATE requires special handling. PostgreSQL does not fire standard DML triggers on TRUNCATE. Gold Lapel installs separate TRUNCATE triggers where supported, but coverage varies by PostgreSQL version. Logical decoding handles TRUNCATE natively in PostgreSQL 11 and later.
• Notification delivery is asynchronous. There is a small delay — typically milliseconds — between the NOTIFY event being sent and Gold Lapel receiving it. In logical decoding mode, the latency is similar (WAL shipping is also asynchronous), but the delivery guarantees are stronger: WAL events survive connection interruptions, while NOTIFY events are transient.

For most applications, these limitations are academic. NOTIFY mode provides correct cache invalidation for every write that touches tables Gold Lapel knows about — which, after the first few minutes of operation, is every table your application queries.

What the startup log shows

goldlapel v0.14.0
  :7932 -> localhost:5432 (butler, pool = 20)
  write detection: NOTIFY triggers (wal_level = replica, logical decoding unavailable)
  dashboard: http://127.0.0.1:7933

INFO goldlapel::notify: installed trigger on "orders"
INFO goldlapel::notify: write detected via NOTIFY table="orders"
INFO goldlapel::cache: invalidated cache entries tables=["orders"] matviews=["mv_00b59a8157e6f5ae"]

Graceful degradation

Gold Lapel's default behavior is to select the best available mode without requiring any configuration from you. The sequence is:

1. Check wal_level. Gold Lapel queries SHOW wal_level at startup. If the result is logical, it proceeds to create a replication slot and subscribe to the WAL stream.
2. Check replication permissions. If wal_level is logical but the database user lacks the REPLICATION privilege (or equivalent), the slot creation fails. Gold Lapel logs a clear message explaining what permission is needed and falls back to NOTIFY.
3. Fall back to NOTIFY. If wal_level is not logical, Gold Lapel installs NOTIFY triggers on tracked tables. No error, no failure — just a different mode of operation, logged at startup.

This means Gold Lapel works correctly out of the box on any PostgreSQL server, with any configuration, at any permission level that allows creating triggers. You can upgrade from NOTIFY to logical decoding later by changing wal_level and restarting PostgreSQL — Gold Lapel will detect the change on its next startup and switch modes automatically.

The active mode is always visible in three places: the startup log, the dashboard, and the /api/stats JSON endpoint.

Which mode is right for you

If you can set wal_level = logical, do so. It is the more comprehensive mode and requires no ongoing maintenance. If you cannot — because your provider does not support it, because you do not have access to change server configuration, or because the required restart is not feasible at this time — NOTIFY mode will serve you well.

A practical recommendation: start with whatever your current PostgreSQL configuration supports. Gold Lapel will auto-select the best mode. If you later want to upgrade from NOTIFY to logical decoding, the change is one configuration line and a restart. Gold Lapel switches modes on its next startup without any changes to its own configuration.

Provider-specific notes

AWS RDS / Aurora

Logical replication is available but not enabled by default. You need to set rds.logical_replication = 1 in a custom Parameter Group and reboot the instance. The rds_superuser role has the necessary replication permissions. Aurora PostgreSQL supports logical replication from version 10.6 onward.

# AWS RDS / Aurora
# 1. Create or modify a Parameter Group
# 2. Set rds.logical_replication = 1
# 3. Apply the parameter group to your instance
# 4. Reboot the instance

Supabase

Supabase ships with wal_level = logical enabled by default. No action needed — Gold Lapel will use logical decoding automatically. Replication permissions are available to the postgres role.

Neon

Neon supports logical replication and has it enabled by default. Gold Lapel auto-detects Neon and configures its idle timeout to allow Neon's compute to scale to zero when inactive.

Google Cloud SQL

# Google Cloud SQL
# 1. Edit your instance
# 2. Under Flags, add:
#    cloudsql.logical_decoding = on
# 3. Save — Cloud SQL restarts the instance automatically

Cloud SQL restarts the instance automatically when you apply this flag. The cloudsql.iam_authentication role or the default postgres user has sufficient permissions.

Heroku Postgres

Logical replication is available on Standard, Premium, and Private tiers. Hobby and Mini plans do not support it — Gold Lapel will use NOTIFY mode on these plans. No configuration change is needed on supported tiers; Heroku enables the capability at provisioning time.

Azure Database for PostgreSQL

Set wal_level = logical under Server Parameters in the Azure portal. Azure restarts the server automatically. The change typically takes 2-3 minutes. Both Single Server and Flexible Server support it.

Configuration reference

Write detection works automatically with no configuration. These flags are available for cases where you want explicit control.

# Explicitly enable logical decoding (on by default when wal_level = logical)
goldlapel --logical-decoding --upstream 'postgresql://user:pass@localhost:5432/mydb'

# Force NOTIFY mode for testing or when logical decoding is not desired
goldlapel --notify-fallback --upstream 'postgresql://user:pass@localhost:5432/mydb'

# goldlapel.toml

# Write detection mode
# "auto" (default) — tries logical decoding, falls back to NOTIFY
# "logical" — require logical decoding, fail if unavailable
# "notify" — force NOTIFY triggers, skip logical decoding
write_detection = "auto"

All three configuration surfaces — CLI flags, TOML file, and environment variables — are equivalent. Flags take precedence over TOML, which takes precedence over environment variables. The setting is live-reloadable via TOML: changing write_detection in goldlapel.toml takes effect without a proxy restart, though switching from NOTIFY to logical decoding requires that wal_level already be set to logical on the PostgreSQL server.

Cleanup

Gold Lapel cleans up after itself. When the proxy shuts down, it drops its replication slot (in logical decoding mode) and removes its triggers and functions (in NOTIFY mode). The goldlapel clean command also removes all write detection artifacts along with matviews, indexes, and the _goldlapel schema.

If Gold Lapel is terminated unexpectedly (kill -9, power failure), the replication slot persists. On next startup, Gold Lapel reuses the existing slot rather than creating a new one. If you need to remove an orphaned slot manually: SELECT pg_drop_replication_slot('_goldlapel_slot');