메뉴
PostgreSQL schema, query, indexing, and performance patterns.
Codex 또는 Claude로 설치 이 Prompt를 복사해 Codex, Claude 또는 다른 어시스턴트에 붙여 넣으면 Skill 페이지를 검토하고 설치를 진행할 수 있습니다.
SOC 직업 분류 기준
| 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>;
}
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.