PostgreSQL Tricks I Wish I Knew 5 Years Ago

import Post from ’../../layouts/Post.astro’; export const prerender = true;

After years of writing raw SQL in production, these are the PostgreSQL features that actually changed how I think about databases.

1. DISTINCT ON — The Query You Didn’t Know You Needed

Ever needed the latest row per group, but didn’t want to use a subquery?

-- Get the latest order per customer
SELECT DISTINCT ON (customer_id)
    customer_id,
    order_id,
    total_amount,
    created_at
FROM orders
ORDER BY customer_id, created_at DESC;

-- vs the old way (subquery)
SELECT *
FROM orders o
WHERE created_at = (
    SELECT MAX(created_at)
    FROM orders
    WHERE customer_id = o.customer_id
);

DISTINCT ON is cleaner and typically faster. It’s PostgreSQL-specific and underused.

2. INSERT … ON CONFLICT (Upsert)

No more “check if exists, then insert, handle race condition, pray”:

INSERT INTO users (id, email, name)
VALUES (1, '[email protected]', 'Aymen')
ON CONFLICT (id) DO UPDATE
    SET email = EXCLUDED.email,
        name = EXCLUDED.name,
        updated_at = NOW();

Works on any unique constraint — primary key, unique index, or explicit ON CONFLICT (email).

3. EXPLAIN ANALYZE — Actually Reading It

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT *
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at > NOW() - INTERVAL '30 days';

Key things to look at:

• Seq Scan — full table scan, usually bad on large tables
• cost=X..Y — planner’s estimate (X=startup, Y=total)
• actual time=X..Y — real execution time
• rows=N — rows processed
• Buffers: hit/read — cache hits vs disk reads

If actual rows differs wildly from rows=N, your statistics are stale. Run ANALYZE.

4. Partial Indexes — Index Only What You Need

-- Instead of indexing all orders:
CREATE INDEX idx_orders ON orders(created_at);

-- Index only recent, unfulfilled orders:
CREATE INDEX idx_orders_pending_recent
ON orders(created_at)
WHERE status = 'pending'
  AND created_at > NOW() - INTERVAL '90 days';

-- This index is smaller and faster for the specific query
SELECT * FROM orders
WHERE status = 'pending'
  AND created_at > NOW() - INTERVAL '30 days';

5. JSONB — When You Need Flexibility

PostgreSQL is a better document store than MongoDB for most use cases:

CREATE TABLE events (
    id BIGSERIAL PRIMARY KEY,
    payload JSONB NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Store anything
INSERT INTO events(payload) VALUES (
    '{"user_id": 42, "action": "signup", "metadata": {"source": "organic"}}'
);

-- Query it with Postgres operators
SELECT * FROM events
WHERE payload ->> 'action' = 'signup'
  AND payload @> '{"metadata": {"source": "organic"}}';

-- GIN index for fast JSON queries
CREATE INDEX idx_events_payload ON events USING GIN(payload);

6. CTE (WITH) Queries — Readable Complex Queries

WITH monthly_revenue AS (
    SELECT
        DATE_TRUNC('month', created_at) AS month,
        SUM(total_amount) AS revenue,
        COUNT(*) AS order_count
    FROM orders
    WHERE created_at > NOW() - INTERVAL '12 months'
    GROUP BY DATE_TRUNC('month', created_at)
),
avg_revenue AS (
    SELECT AVG(revenue) AS avg_monthly
    FROM monthly_revenue
)
SELECT
    month,
    revenue,
    order_count,
    revenue - avg_revenue.avg_monthly AS vs_avg
FROM monthly_revenue, avg_revenue
ORDER BY month;

Readable. Debuggable. The optimizer handles it as efficiently as a subquery.

7. Row-Level Security (RLS)

For multi-tenant apps, enforce access control at the database layer:

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- Users can only see their own orders
CREATE POLICY user_orders ON orders
    USING (user_id = current_setting('app.current_user_id')::BIGINT);

-- Set at connection time
SET app.current_user_id = '42';
SELECT * FROM orders; -- only returns user's orders

No application-level filter to forget.

---

These seven features alone cover 80% of the PostgreSQL tricks that make production queries faster and your code simpler. Start with DISTINCT ON and ON CONFLICT — you’ll use them every day.