The Hidden Postgres Toolbox: Features Your Stack Probably Already Replaces

The Adoption Ladder post covered the macro decisions: which Postgres features to build your stack on and where to stop. This post is about the micro decisions. Tools inside Postgres that each replace a small amount of your infrastructure, and which nobody puts in the usual feature tour because they are not dramatic enough to headline a conference talk.

Most senior engineers I know have adopted two or three of these and have no idea the rest exist. They pay for a scheduling service because they never noticed pg_cron. They spin up Redis because nobody told them about advisory locks. These are not 'Postgres can do everything' features. They are 'Postgres already did this and you did not know' features.

LISTEN/NOTIFY, Used Correctly

The ladder post put LISTEN/NOTIFY in Tier 3 because teams try to make it the nervous system of a high-traffic product and get burned. That verdict stands. But it has a correct use case that deserves respect: low-frequency coordination between your application and your database.

Examples that work well: an Angular admin dashboard that should auto-refresh when a support agent updates a ticket. A cache layer that needs to invalidate a specific key when a row changes. A worker process that wakes up when new jobs appear (paired with SKIP LOCKED, this gives you event-driven queues without polling).

Rules of thumb: under ~100 listeners, payloads under 1KB, writes per second in the low hundreds. That is the comfortable zone, not the ceiling. The ladder post puts the practical ceiling near 1000 listeners, and the gap between the two numbers is the headroom you want for the day write load spikes. Inside the comfortable zone it is elegant, reliable, and completely free. Outside it, re-read the ladder.

Foreign Data Wrappers

postgres_fdw, mysql_fdw, mongo_fdw, file_fdw, sqlite_fdw. You can query tables in other databases, CSV files on disk, Parquet files (via parquet_fdw, or parquet_s3_fdw, a derivative that adds S3 support), all as if they were regular Postgres tables. Joins across them work. Views on top of them work.

This one matters most during migrations. Moving a legacy MySQL table into Postgres used to involve an ETL pipeline, a cutover window, and an uncomfortable all-hands Saturday. With mysql_fdw, you create the foreign table, write views that join the legacy data with your new schema, migrate incrementally, and drop the FDW once the last row is local. Zero ETL code, no cutover panic.

pg_cron

Scheduled jobs, inside Postgres, defined as SQL:

sql

SELECT cron.schedule(
  'cleanup-old-events',
  '0 2 * * *',
  $$DELETE FROM events WHERE created_at < NOW() - INTERVAL '90 days'$$
);

That is your entire retention job. No Airflow DAG, no Kubernetes CronJob, no Lambda plus EventBridge. If the thing you need to schedule is a SQL statement or a stored procedure, pg_cron is all you need.

It is a genuine infrastructure eliminator for refreshing materialized views on a schedule, expiring tokens, archiving old partitions, and periodic data quality checks. The moment the scheduled work needs external API calls, you graduate out. Until then, this saves an entire service.

Advisory Locks

Postgres has two separate lock systems. One is the implicit row and table locking you get from transactions. The other is application-defined locks keyed by arbitrary integer IDs. Call pg_advisory_lock(id) and you hold it until you release it or the session ends.

This replaces Redis for cooperative locking in most cases. 'Only one worker should run this cron across all instances' is an advisory lock on a well-known ID. 'Only one service should refresh this cache entry at a time' is an advisory lock keyed on a hash of the cache key. 'Prevent the same user from being processed by two workers simultaneously' is an advisory lock on the user ID.

The catch to know: session-scoped advisory locks do not work with transaction pooling (PgBouncer in transaction mode). Use the transaction-scoped variants (pg_advisory_xact_lock) if you are behind a pooler.

Range Types Plus Exclusion Constraints

Classic hard problem: two people cannot book the same hotel room for overlapping dates. The naive solution involves a lock, a select, a check, an insert, and a retry loop when the race condition bites. The Postgres solution is a constraint:

sql

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE bookings (
  room_id int,
  period tstzrange,
  EXCLUDE USING gist (room_id WITH =, period WITH &&)
);

The btree_gist extension is what lets GiST index the equality operator on scalar types like int; without it, room_id WITH = will not work. That constraint makes overlapping ranges impossible at the database level. No race condition, no retry logic, no distributed lock. Reservations, scheduling, resource allocation, any time-range problem maps cleanly to this pattern. The && operator checks range overlap; the exclusion constraint forbids it within the same room_id.

I have seen teams build entire microservices to handle this with Redis locks and correctness bugs, when seven lines of SQL would have done it.

pg_stat_statements

Built-in query performance observability. Every statement your database runs, aggregated by shape (parameters stripped), with total time, mean time, call count, and buffer reads. Add pg_stat_statements to shared_preload_libraries in postgresql.conf, restart Postgres, then run CREATE EXTENSION pg_stat_statements. The restart is the part that catches people: a plain CREATE EXTENSION without the preload does not work.

This is the first thing I enable on any new Postgres instance. It answers the most common 'why is the database slow' question without an APM. Distributed tracing and app-level context still need one. This covers the database side and nothing above it.

Pair it with pg_stat_activity (live queries) and pg_stat_user_indexes (index usage), and you have a working observability stack for the database with zero external dependencies.

Unlogged Tables

Regular Postgres tables write to the WAL (write-ahead log) for durability. Unlogged tables do not. They are not crash-safe (truncated on unclean shutdown), but writes to them are 2-3x faster and do not pressure replication.

Use cases that fit: session storage, rate-limiting counters, aggregated metrics with short retention, ephemeral analytics buckets. Any Redis-shaped workload where losing data on a rare crash is acceptable.

They are not a Redis replacement in general (no in-memory semantics, no pub/sub, no TTL support). But for specific cases where you want Redis-ish write performance without running another service, they are a legitimate option that nobody remembers exists.

How These Compound

Each of these on its own is a small convenience. The reason they matter together is that every one you adopt is a service you do not run. No scheduler instance, no Redis cluster for locks, no ETL pipeline, no separate pub/sub broker, no APM just to see slow queries.

That is the actual argument for 'just use Postgres', stripped of the hype: fewer things to monitor, fewer things to upgrade, fewer things to page you at 3am. The ladder tells you where to draw the line. The toolbox tells you how much ground you cover before you get there.

References

LISTEN and NOTIFY (PostgreSQL docs): https://www.postgresql.org/docs/current/sql-notify.html
Foreign Data Wrappers / postgres_fdw (PostgreSQL docs): https://www.postgresql.org/docs/current/postgres-fdw.html
Advisory Locks (PostgreSQL docs): https://www.postgresql.org/docs/current/explicit-locking.html
Range Types (PostgreSQL docs): https://www.postgresql.org/docs/current/rangetypes.html
Exclusion Constraints (PostgreSQL docs): https://www.postgresql.org/docs/current/ddl-constraints.html
pg_stat_statements (PostgreSQL docs): https://www.postgresql.org/docs/current/pgstatstatements.html
CREATE UNLOGGED TABLE (PostgreSQL docs): https://www.postgresql.org/docs/current/sql-createtable.html
pg_cron (Citus Data, now Microsoft): https://github.com/citusdata/pg_cron
mysql_fdw (EnterpriseDB): https://github.com/EnterpriseDB/mysql_fdw
mongo_fdw (EnterpriseDB): https://github.com/EnterpriseDB/mongo_fdw
parquet_fdw (query local Parquet files as tables): https://github.com/adjust/parquet_fdw
parquet_s3_fdw (PGSpider, derived from parquet_fdw, adds Amazon S3 support): https://github.com/pgspider/parquet_s3_fdw
file_fdw (PostgreSQL docs): https://www.postgresql.org/docs/current/file-fdw.html
PostgreSQL Unlogged Tables (Crunchy Data): https://www.crunchydata.com/blog/postgresl-unlogged-tables
Unlogged tables in Postgres: Is their data truly gone after a crash? (pganalyze): https://pganalyze.com/blog/5mins-postgres-unlogged-tables
Logged vs Unlogged table performance comparison: https://dev.to/yet_anotherdev/logged-vs-unlogged-table-performance-in-postgres-11pk