Menu
PostgreSQL schema, query, indexing, and performance patterns.
Instalar com Codex ou Claude Copie este prompt, cole no Codex, Claude ou outro assistente e deixe que ele revise a página da skill e instale para você.
Baseado na classificação ocupacional SOC
Optimize token usage and context window discipline. Reduce costs and improve response quality through smart context management.
Unified context lifecycle for FlowDeck sessions — ingest, filter, prune, protect, summarize, and persist with telemetry.
Predict affected files, modules, APIs, tests, and DB paths before changes. Returns an impact map for human review.
Map architecture, conventions, and file structure into `.codebase/`. Use when onboarding or before deep feature work.
Plan differently when the agent has low certainty — ask for clarification or narrow scope instead of pretending full understanding.
Protect critical context from pruning during compaction. Preserve active plans, safety files, pending operations, and user intent anchors.
| name | postgres-patterns |
| description | PostgreSQL schema, query, indexing, and performance patterns. |
| origin | FlowDeck |
When designing database schemas, writing complex queries, or optimizing database performance. Use before creating migrations or writing SQL queries.
-- 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>;
}