postgres-patterns

When to Activate

When designing database schemas, writing complex queries, or optimizing database performance. Use before creating migrations or writing SQL queries.

Steps

1. Design indexes strategically - Create individual btree indexes on each column for multi-column searches (allows BitmapAnd)
2. Use EXPLAIN ANALYZE - Always verify query plans before and after optimization
3. Choose correct index type - B-tree for equality/range, Bloom for multi-column filters with high selectivity
4. Avoid multi-column btree on non-leading columns - Queries on non-first columns of multi-column btree indexes will do sequential scans
5. Use parameterized queries - Let the planner cache and reuse query plans
6. Run ANALYZE regularly - Keep statistics fresh for optimal planner decisions

Examples

-- AVOID: Multi-column btree for non-leading column queries
CREATE INDEX btreeidx ON tbloom (i1, i2, i3, i4, i5, i6);
-- Query on i2 and i5 will do sequential scan, not use the index

-- PREFER: Individual btree indexes for multi-column searches
CREATE INDEX btreeidx1 ON tbloom (i1);
CREATE INDEX btreeidx2 ON tbloom (i2);
CREATE INDEX btreeidx3 ON tbloom (i3);
CREATE INDEX btreeidx4 ON tbloom (i4);
CREATE INDEX btreeidx5 ON tbloom (i5);
CREATE INDEX btreeidx6 ON tbloom (i6);
-- Bitmap Index Scan with BitmapAnd is used for multi-column queries

-- Bloom Index for multi-column filtering (good for low selectivity columns)
CREATE INDEX bloomidx ON tbloom USING bloom (i1, i2, i3, i4, i5, i6);
-- More efficient than btree for queries filtering on many columns
-- Smaller index size, faster Bitmap Index Scans

-- Always verify with EXPLAIN ANALYZE
EXPLAIN ANALYZE SELECT * FROM tbloom WHERE i2 = 898732 AND i5 = 123451;

-- Query Planner Configuration (temporary fix only)
SET enable_hashjoin = off;           -- Force nested-loop or merge join
SET enable_seqscan = off;           -- Prefer index scans
SET random_page_cost = 1.1;         -- Make index scans cheaper (SSD)
SET effective_cache_size = '8GB';   -- Help planner estimate

-- Better approaches:
-- 1. Run ANALYZE to update statistics
ANALYZE;
-- 2. Increase statistics for specific columns
ALTER TABLE orders SET STATISTICS = 500;
ANALYZE orders;
-- 3. Adjust planner cost constants (postgresql.conf)

-- Repository Pattern in SQL
-- Define standard operations
interface OrderRepository {
  findAll(filter: OrderFilter, pagination: Pagination): Promise<Order[]>;
  findById(id: string): Promise<Order | null>;
  create(order: CreateOrderDTO): Promise<Order>;
  update(id: string, attributes: UpdateOrderDTO): Promise<Order>;
  delete(id: string): Promise<void>;
  count(filter?: OrderFilter): Promise<number>;
}

Related Skills

• api-design
• backend-patterns
• database-migrations
• postgres-performance