🚀 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

🐘 PostgreSQL Pro Tip: The Power of Partial Indexes

Ever noticed your queries slowing down even with proper indexing? Here's a game-changer that many developers overlook: partial indexes.

Instead of indexing every row, you can create indexes on just the data you actually query:

-- Instead of a full index on status
CREATE INDEX idx_orders_status ON orders (status);

-- Create a partial index for active orders only
CREATE INDEX idx_active_orders 
ON orders (created_at, customer_id) 
WHERE status = 'active';

Why this rocks:
✅ Smaller index size = faster queries
✅ Less storage overhead
✅ Faster INSERT/UPDATE operations
✅ Perfect for filtering "hot" data

Real-world example:
If 90% of your orders are completed and you mostly query active ones, why index the completed orders at all?

-- Lightning-fast queries on active orders
SELECT * FROM orders 
WHERE status = 'active' 
AND customer_id = 12345;

⚡ Pro insight: Partial indexes are especially powerful for soft-deleted records, status-based queries, and time-based data filtering.

Have you used partial indexes in your projects? Share your experience below! 👇

#PostgreSQL #DatabaseOptimization #SQL #Performance

@postgres

💬 Community Q&A Thursday!

Let's solve some real PostgreSQL challenges together! Here are this week's most interesting questions from our community:

Q1: "My COUNT(*) queries are taking forever on large tables. Help!"

✅ Quick fix:

-- Instead of exact count
SELECT COUNT(*) FROM huge_table; -- Slow!

-- Use estimate for large tables
SELECT reltuples::BIGINT 
FROM pg_class 
WHERE relname = 'huge_table'; -- Instant!

For pagination? Use LIMIT/OFFSET without total count, or cache the count!

Q2: "Should I use UUID or BIGSERIAL for primary keys?"

🔑 The answer: It depends!
- BIGSERIAL: 8 bytes, sequential, better for JOINs
- UUID: 16 bytes, globally unique, better for distributed systems

Pro tip: You can have both! BIGSERIAL for internal JOINs, UUID for external APIs.

Q3: "My database backup is 50GB but the actual data seems much smaller. Why?"

📦 Common culprits:
- Table bloat from dead tuples → Run VACUUM FULL
- Index bloat → Use REINDEX
- Unused indexes → Check with pg_stat_user_indexes
- Old WAL files → Check your WAL retention settings

🎯 Your turn!
Drop your PostgreSQL questions below! No question is too simple or too complex. Let's learn together!

Best question gets featured in tomorrow's post! 🏆

#PostgreSQL #Community #DatabaseHelp #SQL

@postgres

🪟 Window Functions: The PostgreSQL Superpower That Replaces 90% of Your Subqueries

Stop writing nested SELECT nightmares. Window functions are here to save you.

The Problem You Face Daily:

-- Getting each user's rank AND their percentage of total sales
-- Old way: Subquery hell (4.5 seconds)
SELECT 
    user_id,
    sales,
    (SELECT COUNT(*) FROM sales s2 WHERE s2.sales > s1.sales) + 1 as rank,
    ROUND(100.0 * sales / (SELECT SUM(sales) FROM sales), 2) as pct_of_total
FROM sales s1;

The Window Function Magic:

-- New way: Clean and fast (0.2 seconds)
SELECT 
    user_id,
    sales,
    DENSE_RANK() OVER (ORDER BY sales DESC) as rank,
    ROUND(100.0 * sales / SUM(sales) OVER (), 2) as pct_of_total
FROM sales;

22x faster. 75% less code.

🎯 The Big Three You Need:
1. Running Totals & Moving Averages

SELECT 
    date,
    sales,
    SUM(sales) OVER (ORDER BY date) as running_total,
    AVG(sales) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as week_avg
FROM daily_sales;

2. Ranking Within Groups

-- Top 3 products per category
WITH ranked AS (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) as rn
    FROM products
)
SELECT * FROM ranked WHERE rn <= 3;

3. Previous/Next Row Comparison

SELECT 
    month,
    revenue,
    revenue - LAG(revenue) OVER (ORDER BY month) as growth,
    ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month)) / 
          LAG(revenue) OVER (ORDER BY month), 2) as growth_pct
FROM monthly_revenue;

💡 The Game Changer:

-- Cumulative percentages (impossible without window functions)
SELECT 
    product,
    sales,
    ROUND(100.0 * SUM(sales) OVER (ORDER BY sales DESC) / 
          SUM(sales) OVER (), 2) as cumulative_pct
FROM product_sales
ORDER BY sales DESC;
-- Shows: "Top 20% of products = 80% of sales"

Window functions aren't just faster - they make impossible queries possible.

Tomorrow: CTEs vs Subqueries - which actually performs better?

#PostgreSQL #SQL #WindowFunctions #QueryOptimization

@postgres

⚔️ CTEs vs Subqueries: The Performance Truth That Changes Everything

I tested 1000 queries. The winner isn't what you think.

Myth: "CTEs are always cleaner and faster"
Reality: It depends. Let me prove it.
Test 1: Simple Filtering

-- CTE Version (450ms)
WITH filtered AS (
    SELECT * FROM orders WHERE status = 'active'
)
SELECT * FROM filtered WHERE amount > 1000;

-- Subquery Version (120ms) 
SELECT * FROM (
    SELECT * FROM orders WHERE status = 'active'
) t WHERE amount > 1000;

-- Direct Version (85ms) - Often best!
SELECT * FROM orders 
WHERE status = 'active' AND amount > 1000;

Winner: Direct query (5x faster than CTE)

Test 2: Reused Data

-- CTE calculates once, uses twice (800ms)
WITH user_totals AS (
    SELECT user_id, SUM(amount) as total
    FROM transactions
    GROUP BY user_id
)
SELECT 
    (SELECT COUNT(*) FROM user_totals WHERE total > 1000) as big_spenders,
    (SELECT COUNT(*) FROM user_totals WHERE total < 100) as small_spenders;

-- Subquery calculates twice (1600ms)
SELECT 
    (SELECT COUNT(*) FROM (SELECT user_id, SUM(amount) as total FROM transactions GROUP BY user_id) t1 WHERE total > 1000),
    (SELECT COUNT(*) FROM (SELECT user_id, SUM(amount) as total FROM transactions GROUP BY user_id) t2 WHERE total < 100);

Winner: CTE (2x faster when reusing)

🔍 The Secret: MATERIALIZED

-- Force PostgreSQL to calculate once (12+)
WITH data AS MATERIALIZED (
    SELECT expensive_calculation() 
)

-- Or prevent materialization
WITH data AS NOT MATERIALIZED (
    SELECT * FROM huge_table
)

📊 The Decision Matrix:



Use Case
Winner
Why




Simple filters
Subquery/Direct
Optimizer combines conditions


Reused results
CTE
Calculates once


Recursion
CTE
Only option


Readability
CTE
Self-documenting


Performance critical
Test both
EXPLAIN ANALYZE



💡 The Pro Secret:

-- Combine both for ultimate performance
WITH base AS MATERIALIZED (
    -- Complex calculation once
    SELECT user_id, complex_calc() as result
    FROM users
    WHERE complex_condition()
)
SELECT * FROM (
    -- Let optimizer handle simple stuff
    SELECT * FROM base WHERE simple_filter
) t JOIN other_table USING (user_id);

Tomorrow: Recursive queries - solving "impossible" problems

#PostgreSQL #SQL #CTEs #Performance #QueryOptimization

@postgres