I’ve worked with PostgreSQL for 12 years. Most developers use maybe 10% of what it offers. Here are the features and techniques that made the biggest difference in real production systems — the stuff that doesn’t make it into tutorials.
<h2>1. EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)</h2>
Everyone knows EXPLAIN. Few use it properly. The full incantation:
<pre><code class="language-sql">EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) 
SELECT * FROM orders WHERE created_at &gt; &#x27;2026-01-01&#x27;;
</code></pre>
<code>ANALYZE</code> actually runs the query (not just estimates). <code>BUFFERS</code> shows cache hits vs disk reads. <code>FORMAT JSON</code> gives machine-parseable output you can feed into tools like pgMustard or <a href="http://explain.dalibo.com" target="_blank" rel="noopener nofollow">explain.dalibo.com</a>.
The difference between estimated and actual rows is where performance problems hide. If the planner estimates 100 rows but gets 100,000, your index strategy is wrong.
<h2>2. Partial indexes save disk and speed</h2>
Most indexes cover the entire table. But often you only query a subset:
<pre><code class="language-sql">-- Instead of indexing all orders:
CREATE INDEX idx_orders_status ON orders(status);

-- Index only what you actually query:
CREATE INDEX idx_orders_active ON orders(status) 
 WHERE status IN (&#x27;pending&#x27;, &#x27;processing&#x27;);
</code></pre>
If 95% of your orders are ‘completed’ and you only ever query active ones, the partial index is 20x smaller and faster. I’ve seen this cut query times from 200ms to 3ms.
<h2>3. CTEs are optimization fences (sometimes)</h2>
Before PostgreSQL 12, CTEs (WITH queries) were always materialized — the database computed the entire CTE result before using it. Since PG12, simple CTEs are inlined, but some complex ones still aren’t.
If your CTE-based query is slow, try rewriting it as a subquery. If it speeds up dramatically, you hit the optimization fence.
<pre><code class="language-sql">-- Might be slow (materialized CTE):
WITH recent AS (
 SELECT * FROM orders WHERE created_at &gt; now() - interval &#x27;1 day&#x27;
)
SELECT * FROM recent WHERE total &gt; 100;

-- Often faster (inlined subquery):
SELECT * FROM orders 
WHERE created_at &gt; now() - interval &#x27;1 day&#x27; AND total &gt; 100;
</code></pre>
<h2>4. pg_stat_statements is your best friend</h2>
This extension tracks execution statistics for every query. Enable it:
<pre><code class="language-sql">CREATE EXTENSION pg_stat_statements;
</code></pre>
Then find your slowest queries:
<pre><code class="language-sql">SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements 
ORDER BY total_exec_time DESC 
LIMIT 10;
</code></pre>
This tells you exactly where to focus optimization. Don’t guess which queries are slow — measure.
<h2>5. LISTEN/NOTIFY for real-time without polling</h2>
Need to react to database changes without polling? PostgreSQL has a built-in pub/sub:
<pre><code class="language-sql">-- In one connection:
LISTEN order_updates;

-- In another (or trigger):
NOTIFY order_updates, &#x27;{&quot;order_id&quot;: 12345, &quot;status&quot;: &quot;shipped&quot;}&#x27;;
</code></pre>
Your application receives the notification instantly. No polling, no external message queue needed for simple use cases. I’ve used this to build real-time dashboards with zero infrastructure beyond PostgreSQL itself.
<h2>6. GENERATED ALWAYS AS for computed columns</h2>
Stop computing values in your application code and duplicating the logic:
<pre><code class="language-sql">ALTER TABLE products ADD COLUMN search_text tsvector 
 GENERATED ALWAYS AS (
 to_tsvector(&#x27;english&#x27;, coalesce(name, &#x27;&#x27;) || &#x27; &#x27; || coalesce(description, &#x27;&#x27;))
 ) STORED;

CREATE INDEX idx_products_search ON products USING GIN(search_text);
</code></pre>
The computed column updates automatically when the source columns change. One source of truth, always in sync.
<h2>7. Advisory locks for application-level coordination</h2>
Need to ensure only one worker processes a specific job? Advisory locks are lighter than row locks:
<pre><code class="language-sql">-- Try to acquire lock (non-blocking):
SELECT pg_try_advisory_lock(hashtext(&#x27;process-order-12345&#x27;));
-- Returns true if acquired, false if someone else has it

-- Release when done:
SELECT pg_advisory_unlock(hashtext(&#x27;process-order-12345&#x27;));
</code></pre>
No separate Redis or distributed lock service needed. The lock exists purely in PostgreSQL’s memory and is released automatically if the connection drops.
<h2>8. The connection pool matters more than the query</h2>
The single biggest performance improvement I’ve ever made wasn’t a query optimization — it was properly configuring PgBouncer. A Rails app was opening 200 direct connections to PostgreSQL. Each connection uses ~10MB of RAM on the server. Putting PgBouncer in front with transaction-level pooling dropped active connections from 200 to 20 and freed 1.8GB of RAM.
If your PostgreSQL server has more than 100 connections, you almost certainly need a connection pooler.
PostgreSQL is absurdly powerful out of the box. Most performance problems I see aren’t PostgreSQL problems — they’re “we didn’t know PostgreSQL could do that” problems.
What’s your favorite PostgreSQL underappreciated feature?

sirajju

A collection of advanced PostgreSQL techniques from 12 years of production experience. Covers proper use of EXPLAIN with ANALYZE/BUFFERS/FORMAT JSON for query analysis, partial indexes for performance gains, CTE materialization behavior changes since PG12, pg_stat_statements for identifying slow queries, LISTEN/NOTIFY for real-time pub/sub without polling, GENERATED ALWAYS AS for computed columns, advisory locks for lightweight coordination, and PgBouncer connection pooling as often the biggest performance win.

PostgreSQL Tips That Senior DBAs Don't Share (Until You Buy Them a Beer)

Advisory locks are criminally underused. I replaced an entire Redis-based distributed locking system with pg_try_advisory_lock and eliminated a dependency, a failure mode, and 200ms of latency per lock acquisition. More people need to know about this.