Between episodes, here's something lighter. Still useful. Still PostgreSQL.
You've used PostgreSQL for years. SELECT, INSERT, JOIN, INDEX. Maybe you've partitioned a table or two. But PostgreSQL has been quietly shipping features that most engineers never discover until they're deep in a Stack Overflow thread at 2 AM.
Here are 10 you'll wish you'd known earlier.
1. LISTEN/NOTIFY — Real-time pub/sub built into your database.
No Kafka, no Redis, no external message broker. PostgreSQL can push notifications to connected clients.
-- Terminal 1
LISTEN order_updates;
-- Terminal 2
NOTIFY order_updates, '{"order_id": 12345, "status": "shipped"}';
Terminal 1 receives the payload instantly. Use it for cache invalidation, real-time dashboards, or lightweight event-driven workflows. It doesn't persist messages or guarantee delivery after disconnect, so it won't replace Kafka. But for "hey, this row changed, go refresh your cache" — it's perfect.
2. Advisory Locks — Application-level locking without a separate system.
Need a distributed lock? Before reaching for Redis or Zookeeper:
SELECT pg_advisory_lock(12345);
-- do your critical section work
SELECT pg_advisory_unlock(12345);
The lock lives in PostgreSQL's shared memory. It's fast, it's transactional (use pg_advisory_xact_lock to auto-release on commit), and it doesn't touch any table. Great for preventing duplicate cron jobs or serializing access to an external resource.
3. Generated Columns — Computed values that maintain themselves.
Stop writing triggers for derived fields:
ALTER TABLE products ADD COLUMN price_with_tax NUMERIC
GENERATED ALWAYS AS (price * 1.18) STORED;
The column updates automatically whenever price changes. You can index it. You can query it. You never have to think about keeping it in sync.
4. Row-Level Security (RLS) — Multi-tenancy without WHERE clauses everywhere.
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON orders
USING (tenant_id = current_setting('app.current_tenant')::INT);
Set app.current_tenant at connection time, and every query automatically filters to that tenant's data. No more forgetting a WHERE clause and leaking data across tenants. The database enforces it.
5. Foreign Data Wrappers (FDW) — Query external databases like they're local tables.
CREATE EXTENSION postgres_fdw;
CREATE SERVER remote_server FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'remote-db.internal', dbname 'analytics');
CREATE FOREIGN TABLE remote_events (
id BIGINT,
event_type TEXT,
created_at TIMESTAMPTZ
) SERVER remote_server;
SELECT * FROM local_users u
JOIN remote_events e ON u.id = e.user_id;
Cross-database joins without ETL pipelines. FDW also exists for MySQL, MongoDB, Redis, CSV files, S3, and dozens more. It's not fast enough for high-throughput production queries, but for analytics and reporting it eliminates entire data pipeline projects.
6. Materialized Views — Precomputed query results you can refresh.
CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT date_trunc('month', created_at) AS month,
SUM(total) AS revenue
FROM orders
GROUP BY 1;
-- Refresh without locking reads
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue;
Unlike regular views, materialized views store the result on disk. You can index them. The CONCURRENTLY option lets you refresh without blocking readers. Use for dashboards, reports, or any query that's expensive and doesn't need real-time accuracy.
7. pg_stat_statements — Know exactly which queries are killing your database.
CREATE EXTENSION pg_stat_statements;
SELECT query,
calls,
mean_exec_time,
total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;
This shows the top 20 most expensive queries by total time. Not guessing. Not looking at slow query logs. Actual aggregated execution data. If you're not using this extension, you're optimizing blind.
8. EXCLUDE Constraints — Rules that regular UNIQUE can't express.
Need to prevent overlapping date ranges?
CREATE TABLE room_bookings (
room_id INT,
booked_during TSTZRANGE,
EXCLUDE USING GIST (room_id WITH =, booked_during WITH &&)
);
This guarantees no two bookings for the same room overlap in time. Try expressing that with a UNIQUE constraint. You can't. EXCLUDE constraints use GiST indexes and support arbitrary operators.
9. Table Inheritance — Shared structure without duplication.
CREATE TABLE events (
id SERIAL,
event_type TEXT,
created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE click_events (
url TEXT,
element_id TEXT
) INHERITS (events);
CREATE TABLE purchase_events (
amount NUMERIC,
currency TEXT
) INHERITS (events);
SELECT * FROM events returns all rows from all child tables. Each child has its parent's columns plus its own. It's not a replacement for partitioning (and it predates it), but for certain polymorphic data models it's cleaner than a single table with 40 nullable columns.
10. CTEs with MATERIALIZED / NOT MATERIALIZED — Control when PostgreSQL caches subqueries.
-- Force the CTE to materialize (execute once, cache result)
WITH cached_data AS MATERIALIZED (
SELECT * FROM expensive_function()
)
SELECT * FROM cached_data WHERE id > 100;
-- Force the CTE to inline (let the planner optimize through it)
WITH inlined AS NOT MATERIALIZED (
SELECT * FROM orders WHERE status = 'active'
)
SELECT * FROM inlined WHERE created_at > NOW() - INTERVAL '1 day';
Before PostgreSQL 12, CTEs were always materialized — an optimization fence. Now you have control. NOT MATERIALIZED lets the planner push predicates into the CTE, which can be dramatically faster. MATERIALIZED forces caching, which helps when you reference the same expensive CTE multiple times.
Over to You
Which of these 10 features surprised you the most? Is there a PostgreSQL hidden gem I missed?
If you enjoyed this, I write about production engineering, AI systems, and the messy reality of building software at scale.
Follow me:
This is part of the **Great Stack to Doesn't Work* series — a survival guide for when everything goes wrong in production. Follow the series to catch every episode.*
Top comments (0)