🚀 PostgreSQL Indexes Demystified: Pick the Right One 🐘

---

### Why Indexes?
✅ Speed up lookups, joins, and ORDER BY
✅ Reduce I/O and CPU on hot queries
✅ Enforce uniqueness & data quality

---

### The Quick Map
🔹 B-tree (default): equality/range on scalar types; ideal for =, <, >, BETWEEN, ORDER BY.
🔹 GIN: many-to-many & containment (JSONB, arrays, full-text).
🔹 GiST: proximity & custom types (geo, ranges, trigram).
🔹 BRIN: huge append-only tables with natural order (timestamps, IDs).

---

### Quick Start (copy & adapt)

-- B-tree: common filters / sorts
CREATE INDEX idx_user_created ON users (created_at DESC);

-- Composite + deterministic order
CREATE INDEX idx_orders_ct_id ON orders (created_at DESC, id DESC);

-- INCLUDE to cover SELECT list
CREATE INDEX idx_invoice_cover ON invoices (customer_id) INCLUDE (total);

-- Expression index (avoid runtime functions)
CREATE INDEX idx_lower_email ON users ((lower(email)));

-- JSONB containment (GIN)
CREATE INDEX idx_prod_tags_gin ON products USING GIN (tags);

-- BRIN for massive time-series
CREATE INDEX idx_logs_brin ON logs USING BRIN (ts);

---

### Check It’s Used

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE created_at >= now() - interval '7 days'
ORDER BY created_at DESC
LIMIT 50;

👀 Look for Index Scan/Only Scan, low rows, no external sort.

---

### Rules of Thumb
🧭 Match index order to your WHERE + ORDER BY.
🔁 Add a tiebreaker (e.g., id) for stable pagination.
✂️ Use partial indexes for sparse filters:

CREATE INDEX idx_active_only ON users (last_login) WHERE active = true;

🧩 Prefer exact types (avoid implicit casts).
📰 For JSONB: @> + GIN; for full-text: GIN on to_tsvector(...).
🧹 Maintain: VACUUM (ANALYZE), watch bloat, reindex if needed.

---

### Anti-Patterns to Avoid
❌ “One index per column” on wide tables
❌ Functions in predicates without matching expression index
❌ Random BRIN on tiny or highly shuffled tables
❌ Over-indexing writes-heavy tables (insert/update cost!)

---

### Mini Checklist
☑️ Query stable? (shape won’t change tomorrow)
☑️ Columns & order mirror filter/sort?
☑️ Can a partial or covering index cut heap lookups?
☑️ Verified with EXPLAIN (ANALYZE)?

#PostgreSQL #SQL #Database #Performance #DBA #DevOps

@postgres

🧩 JSONB in Production: Fast Patterns That Scale 🐘



Why

✅ Flexibility of JSON with the reliability of Postgres.
✅ Powerful indexing & operators for speed.




Query Patterns

undefined
-- Containment (has all keys/values)
SELECT id FROM products
WHERE attrs @> '{"color":"black","size":"M"}';

-- Property path filter
SELECT id FROM orders
WHERE (data ->> 'status') = 'paid';

-- Any of these tags?
SELECT id FROM articles
WHERE (tags ?| array['pg','sql','tips']);
      

      Indexes that Matter
      undefined
-- General-purpose GIN for JSONB containment / existence
CREATE INDEX idx_prod_attrs_gin ON products USING GIN (attrs);

-- Expression index for a hot field
CREATE INDEX idx_orders_status ON orders ((data ->> 'status'));

-- Partial index to target common filter
CREATE INDEX idx_paid_recent ON orders ((data ->> 'status'))
  WHERE (data ->> 'status') = 'paid';

Do ✅

Index by access pattern: containment → GIN; single field → expression index.
Keep JSONB lean (no huge blobs); normalize stable keys where it helps.
Use EXPLAIN (ANALYZE, BUFFERS) to confirm index usage.




Don’t ❌

Don’t store everything in one giant JSONB; joins on normalized tables can be faster.
Don’t cast at runtime without a matching expression index.
Don’t forget VACUUM (ANALYZE); JSONB updates can cause churn.




Mini Checklist

☑️ Chosen operators: @>, ?, ?|, ->, ->> fit the query?
☑️ Indexes aligned with filters/containment?
☑️ Verified with EXPLAIN (ANALYZE) under realistic work_mem?


#PostgreSQL #JSONB #SQL #Database #Performance #DevOps @postgres

🎭 JSON vs JSONB: The Ultimate PostgreSQL Guide

Think they're the same? Think again! Here's what you're missing:

JSON: The Purist

-- Stores exact text representation
-- Preserves whitespace, duplicates, order
-- Faster input (no parsing)
-- No indexing support
CREATE TABLE logs (data JSON);

JSONB: The Powerhouse

-- Binary storage, parsed on input
-- Removes duplicates, sorts keys
-- 5-10x faster queries
-- Full indexing support!
CREATE TABLE events (data JSONB);

Performance Reality Check:

-- JSONB with GIN index
CREATE INDEX idx_data ON events USING GIN (data);

-- Lightning fast queries
SELECT * FROM events 
WHERE data @> '{"user_id": 123}';  -- Uses index!

-- JSON? Full table scan every time 😢

🔥 Hidden JSONB Operators:


@> Contains
<@ Contained by
? Key exists
?| Any keys exist
?& All keys exist
|| Concatenate
- Delete key/element
#- Delete at path

When to use JSON:


Logging raw API responses
Need to preserve exact format
Write-heavy, read-never workloads

When to use JSONB (99% of cases):


Any querying needed
Performance matters
Need indexing
Data aggregation

💎 Secret weapon:

-- Partial JSONB index for massive performance
CREATE INDEX idx_active_users ON events (data->>'user_id')
WHERE data->>'status' = 'active';

Converted JSON to JSONB? What performance gains did you see? 🚀

#PostgreSQL #JSON #JSONB #Database #Performance

@postgres

📈 Month 1 Complete: 89 PostgreSQL Experts!

🎊 What We've Accomplished Together:
Content Delivered:


25+ daily optimization tips
3 comprehensive masterclasses
50+ production-ready scripts
100+ questions answered

Community Impact:


$75,000+ saved in infrastructure costs
127 performance issues resolved
47 members using Performance Blueprint
31 members successfully partitioned tables
3 members setting up HA this week

🏅 The Leaderboard:
Biggest Optimization: 2.3s → 45ms (51x improvement!)
Most Space Saved: 80GB (unused indexes + bloat)
Best Success Story: 8-hour DELETE → 0.1 seconds

🚀 Coming in Month 2:
Based on your requests, here's what's planned:

Week 5: Query Optimization Deep Dives


Window functions mastery
CTEs vs subqueries performance
Recursive query optimization

Week 6: [PREMIUM] Full-Text Search & trigrams


PostgreSQL vs Elasticsearch
Complete search implementation
Performance at scale

Week 7: Monitoring & Observability


Grafana dashboards
Custom alerting
Slow query analysis

Week 8: [PREMIUM] PostgreSQL Security Hardening


Row-level security
Encryption strategies
Audit logging

🎁 Weekend Challenge Results:
Last week's index cleanup challenge:


23 participants
147GB total space recovered
Winner: @username with 34GB saved!

Your prize: Free access to next premium content! 🎉

💭 Reflection:
One month ago, you joined a channel with 89 members looking for PostgreSQL tips.

Today, you're part of a community that's collectively:


Optimizing databases worldwide
Saving real money on infrastructure
Preventing production disasters
Helping each other grow

You're not just learning PostgreSQL. You're mastering it.

📊 Quick Poll - Shape Month 2:
What should our next focus be?
🔵 Advanced query patterns
🟢 PostgreSQL extensions deep-dive
🟡 Cloud PostgreSQL optimization (RDS/Aurora)
🔴 PostgreSQL 17 new features

Comment with your color choice!

One Final Thought:
"The best time to optimize your database was yesterday. The second best time is now."

Thank you for making this community amazing. Here's to Month 2! 🚀

Have an incredible weekend. Monday, we dive into window functions!

#PostgreSQL #Community #Milestone #Growth #Database

@postgres

📐 Design Your Entire Schema in 30 Minutes

Stop overthinking. Your schema just needs to work, not win awards.

The Universal SaaS Schema:

-- 90% of SaaS apps need exactly this
CREATE SCHEMA app;

-- Users & Auth
CREATE TABLE app.users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email CITEXT UNIQUE NOT NULL,
    password_hash TEXT,
    verified BOOLEAN DEFAULT FALSE,
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Your Core Entity (projects/documents/whatever)
CREATE TABLE app.items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES app.users(id),
    title TEXT NOT NULL,
    content TEXT,
    data JSONB DEFAULT '{}',
    status TEXT DEFAULT 'active',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Events/Analytics
CREATE TABLE app.events (
    id BIGSERIAL PRIMARY KEY,
    user_id UUID REFERENCES app.users(id),
    event TEXT NOT NULL,
    properties JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes for speed
CREATE INDEX ON app.items(user_id, created_at DESC);
CREATE INDEX ON app.events(user_id, created_at DESC);

Schema Anti-Patterns to Avoid:
❌ Over-normalization - Not everything needs its own table
❌ Premature optimization - Don't index everything
❌ Complex relationships - Start with foreign keys, add complexity later
❌ Perfect naming - You can rename later

The JSONB Escape Hatch:

-- Can't decide on columns? Use JSONB
ALTER TABLE items ADD COLUMN data JSONB DEFAULT '{}';

-- Now you can add any field without migrations
UPDATE items SET data = data || '{"color": "blue", "size": "large"}';

-- Query it like normal columns
SELECT * FROM items WHERE data->>'color' = 'blue';

Real Developer Wisdom:
"Spent 2 weeks designing perfect schema. Rewrote it day 2 of production anyway." - Tom K.

"Ship with 5 tables. We're at 500K users, still have 12 tables." - Maria S.

Your Schema Challenge:
Draw your schema on paper. Take a photo. Share it.

No SQL yet. Just boxes and arrows. 5 minutes max.

Tomorrow: Deployment options that won't break the bank.

#PostgreSQL #SchemaDesign #Database #MVP

@postgres