Everything You Need to Master PostgreSQL Performance

Before

-- Your dashboard query taking 4.2 seconds
SELECT o.*, u.name, u.email
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.created_at > now() - interval '30 days'
ORDER BY o.created_at DESC;

-- pg_stat_statements shows:
-- mean_exec_time: 4,281ms | calls: 1,847 | rows: 52,000

After — with DataOwler

-- DataOwler identifies the issue:
-- ⚠️ Sequential scan on orders (4.5M rows)
-- ⚠️ Missing index on (created_at, user_id)
-- ⚠️ SELECT * pulling 28 unused columns

-- Suggested fix:
CREATE INDEX idx_orders_created_user
  ON orders(created_at DESC, user_id);

-- Result: 4,281ms → 3.2ms (99.9% faster)

Before

-- Current indexes on orders table:
-- PRIMARY (id)
-- idx_orders_status (status)  ← rarely used
-- idx_orders_user (user_id)   ← partial coverage

-- Top queries scanning full table:
SELECT * FROM orders
  WHERE customer_id = $1 AND created_at > $2;

SELECT count(*) FROM orders
  WHERE status = 'pending' AND region = $3;

After — with DataOwler

-- DataOwler suggests (95% confidence):
CREATE INDEX idx_orders_customer_date
  ON orders(customer_id, created_at DESC);
  -- Est. gain: ~320% | Storage: +120MB

CREATE INDEX idx_orders_status_region
  ON orders(status, region) WHERE status = 'pending';
  -- Est. gain: ~450% | Storage: +45MB

-- Also flags for removal:
-- idx_orders_status → superseded by new partial index

Before

-- Production alert: API response times spiking
-- pg_stat_activity shows 47 connections in "idle in transaction"
-- Users reporting timeouts on checkout flow

-- Manual investigation:
SELECT * FROM pg_locks WHERE NOT granted;
-- Returns 23 rows... which one is the root blocker?

After — with DataOwler

-- DataOwler lock tree output:
🔴 PID 4521 (BLOCKING) — 45s
   UPDATE accounts SET balance = ... WHERE id = 892
   ├── 🟡 PID 4533 (WAITING 32s)
   │   UPDATE accounts SET balance = ... WHERE id = 892
   ├── 🟡 PID 4540 (WAITING 28s)
   │   SELECT * FROM accounts WHERE id = 892 FOR UPDATE
   └── 🟡 PID 4551 (WAITING 15s)
       INSERT INTO transactions ...

-- Recommendation: PID 4521 holds RowExclusiveLock
-- for 45s. Consider reducing transaction scope.

Before

-- EXPLAIN ANALYZE output (hard to interpret):
Nested Loop Left Join (cost=1.12..845.93 rows=1 width=524)
  (actual time=3421.52..3421.55 rows=1 loops=1)
  Buffers: shared hit=12 read=8423
  -> Index Scan using users_pkey on users u
       (actual time=0.02..0.03 rows=1 loops=1)
  -> Seq Scan on orders o
       (cost=0.00..844.80 rows=1 width=412)
       Filter: (user_id = 42)
       Rows Removed by Filter: 45231

After — with DataOwler

-- DataOwler AI explanation:
"This query is doing a sequential scan on the orders
table (45,231 rows checked, 1 returned). The planner
chose a nested loop but the inner side has no index
on user_id, forcing a full table scan per iteration.

Recommendations:
1. Add index: CREATE INDEX ON orders(user_id)
2. Expected improvement: ~99% (3.4s → <5ms)
3. The 8,423 disk reads indicate this data isn't
   cached — the index will also reduce I/O pressure."

Before

-- Production schema (what you expect):
CREATE TABLE users (
  id BIGINT PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  status VARCHAR(20) DEFAULT 'active',
  created_at TIMESTAMP NOT NULL
);

-- But staging has diverged after a hotfix...
-- Nobody noticed until the next deploy failed.

After — with DataOwler

-- DataOwler drift report:
┌─────────────────────────────────────────┐
│ Schema Drift: production ↔ staging      │
├─────────────────────────────────────────┤
│ 🔴 COLUMN ADDED (staging only):        │
│    users.phone_number VARCHAR(20)       │
│                                         │
│ 🟡 COLUMN MODIFIED:                    │
│    users.status VARCHAR(20) → TEXT      │
│                                         │
│ 🔴 INDEX MISSING (production):         │
│    idx_users_phone ON users(phone)      │
└─────────────────────────────────────────┘

-- Alert sent to #deployments Slack channel
-- Migration file auto-generated for review

Before

-- Current state in most teams:
-- • Queries scattered across Slack messages
-- • 5 engineers wrote 5 versions of the same report
-- • No review process for production queries
-- • Junior dev runs unoptimized query, takes down DB
-- • "Who wrote this query?" — nobody knows

-- revenue_report_v2_final_FINAL.sql
-- revenue_report_sarah_fixed.sql
-- revenue_report_new.sql

After — with DataOwler

-- DataOwler Query Library:
📁 Organization: Acme Corp
├── 📂 Finance Team (4 queries)
│   ├── ✅ Monthly Revenue Report (v3, approved)
│   │   └── Last run: 2.1s | Reviewed by: Sarah
│   ├── ✅ Customer LTV Calculation (v2, approved)
│   └── 🟡 Churn Analysis (v1, pending review)
├── 📂 Engineering (12 queries)
│   ├── ✅ User Growth Metrics (v5, approved)
│   └── ...
└── 📂 My Queries (private)
    └── Draft: New onboarding funnel

-- Full audit trail: who ran what, when, on which DB
-- Auto-perf check before promoting to "approved"