Menu
PostgreSQL database patterns for query optimization, schema design, indexing, and security.
Install with Codex or Claude Copy this prompt, paste it into Codex, Claude, or another assistant, and let it review the skill page and install it for you.
Based on SOC occupation classification
Allure Report integration with pytest — decorators, steps, attachments, severity levels, test categorization, CI/CD integration, and custom report plugins.
API test automation patterns — httpx/requests client wrappers, response validation with Pydantic, test data factories, retry/polling utilities, schema testing, and contract testing.
Async HTTP client patterns for Python — httpx, aiohttp, retry strategies, connection pooling, streaming, and testing with respx.
Celery patterns for distributed task queues — task definitions, retry strategies, scheduling, chains/groups, monitoring, and production configuration with Redis/RabbitMQ.
ClickHouse database patterns, query optimization, analytics, and data engineering best practices for high-performance analytical workloads.
Database migration best practices for schema changes, data migrations, rollbacks, and zero-downtime deployments. Covers Alembic, Django, and raw SQL.
| name | postgres-patterns |
| description | PostgreSQL database patterns for query optimization, schema design, indexing, and security. |
| origin | ECC |
Quick reference for PostgreSQL best practices.
| Query Pattern | Index Type | Example |
|---|---|---|
WHERE col = value | B-tree (default) | CREATE INDEX idx ON t (col) |
WHERE col > value | B-tree | CREATE INDEX idx ON t (col) |
WHERE a = x AND b > y | Composite | CREATE INDEX idx ON t (a, b) |
WHERE jsonb @> '{}' | GIN | CREATE INDEX idx ON t USING gin (col) |
WHERE tsv @@ query | GIN | CREATE INDEX idx ON t USING gin (col) |
| Time-series ranges | BRIN | CREATE INDEX idx ON t USING brin (col) |
| Use Case | Correct Type | Avoid |
|---|---|---|
| IDs | bigint | int, random UUID |
| Strings | text | varchar(255) |
| Timestamps | timestamptz | timestamp |
| Money | numeric(10,2) | float |
| Flags | boolean | varchar, int |
-- Composite Index (equality first, then range)
CREATE INDEX idx ON orders (status, created_at);
-- Covering Index
CREATE INDEX idx ON users (email) INCLUDE (name, created_at);
-- Partial Index
CREATE INDEX idx ON users (email) WHERE deleted_at IS NULL;
-- UPSERT
INSERT INTO settings (user_id, key, value) VALUES (123, 'theme', 'dark')
ON CONFLICT (user_id, key) DO UPDATE SET value = EXCLUDED.value;
-- Cursor Pagination (O(1) vs OFFSET O(n))
SELECT * FROM products WHERE id > $last_id ORDER BY id LIMIT 20;
-- Queue Processing
UPDATE jobs SET status = 'processing'
WHERE id = (
SELECT id FROM jobs WHERE status = 'pending'
ORDER BY created_at LIMIT 1
FOR UPDATE SKIP LOCKED
) RETURNING *;
-- Find unindexed foreign keys
SELECT conrelid::regclass, a.attname
FROM pg_constraint c
JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = ANY(c.conkey)
WHERE c.contype = 'f'
AND NOT EXISTS (
SELECT 1 FROM pg_index i WHERE i.indrelid = c.conrelid AND a.attnum = ANY(i.indkey)
);
-- Find slow queries
SELECT query, mean_exec_time, calls FROM pg_stat_statements
WHERE mean_exec_time > 100 ORDER BY mean_exec_time DESC;