postgresql
/postgresql
PostgreSQL schema design, query optimization, indexing, and administration. Use when writing schemas, queries, migrations, or mentions PostgreSQL, Postgres, JSONB, partitioning, RLS, CTEs, window functions, EXPLAIN ANALYZE, or connection pooling.
- Author
- iliaal ↗
- Repository
- whetstone ↗
- Licence
- MIT
- Last repository change
- 26.10.2026
Assessment and security
- Files in this package
- 2
- Scripts
- 0
- HTML comments
- 0
- URLs in SKILL.md
- 0
No signals detected. Repository changed on 26.10.2026, without references to retired models or APIs.
MIT, open: use and modify while retaining author attribution.
What it does and when to use it
PostgreSQL schema design, query optimization, indexing, and administration. Use when writing schemas, queries, migrations, or mentions PostgreSQL, Postgres, JSONB, partitioning, RLS, CTEs, window functions, EXPLAIN ANALYZE, or connection pooling.
Original author description
PostgreSQL schema design, query optimization, indexing, and administration. Use when writing schemas, queries, migrations, or mentions PostgreSQL, Postgres, JSONB, partitioning, RLS, CTEs, window functions, EXPLAIN ANALYZE, or connection pooling.
How it works
- Data Type Defaults
- Schema Rules
- Index Strategy
- JSONB Patterns
- Query Optimization
- Partitioning
- Transactions & Locking
- Full-Text Search
- Performance Tuning
- Connection Pooling
SKILL.md preview
---
name: postgresql
description: >-
PostgreSQL schema design, query optimization, indexing, and administration.
Use when writing schemas, queries, migrations, or mentions PostgreSQL,
Postgres, JSONB, partitioning, RLS, CTEs, window functions, EXPLAIN ANALYZE,
or connection pooling.
---
# PostgreSQL
## Data Type Defaults
| Need | Use | Avoid |
|------|-----|-------|
| Primary key | `BIGINT GENERATED ALWAYS AS IDENTITY` | `SERIAL`, `BIGSERIAL` |
| Timestamps | `TIMESTAMPTZ` | `TIMESTAMP` (loses timezone) |
| Text | `TEXT` | `VARCHAR(n)` unless constraint needed |
| Money | `NUMERIC(precision, scale)` | `MONEY`, `FLOAT` |
| Boolean | `BOOLEAN` with `NOT NULL DEFAULT` | nullable booleans |
| JSON | `JSONB` | `JSON` (no indexing), text JSON |
| UUID | `gen_random_uuid()` (PG13+) | `uuid-ossp` extension |
| IP addresses | `INET` / `CIDR` | text |
| Ranges | `TSTZRANGE`, `INT4RANGE`, etc. | pair of columns |
## Schema Rules
- Every FK column gets an index (PG does NOT auto-create these)
- `NOT NULL` on every column unless NULL has business meaning
- `CHECK` constraints for domain rules at DB level
- `EXCLUDE` constraints for range overlaps: `EXCLUDE USING gist (room WITH =, during WITH &&)`
- Default `created_at TIMESTAMPTZ NOT NULL DEFAULT now()`
- Separate `updated_at` with trigger, never trust app layer alone
- Use `BIGINT` PKs — cheaper JOINs than UUID, better index locality
## Index Strategy
| Type | Use When |
|------|----------|
| B-tree (default) | Equality, range, sorting, `LIKE 'prefix%'` |
| GIN | JSONB (`@>`, `?`, `?&`), arrays, full-text (`tsvector`) |
| GiST | Geometry, ranges, full-text (smaller but slower than GIN) |
| BRIN | Large tables with natural ordering (timestamps, serial IDs) |
**Index rules:**
- Composite: most selective column first, max 3-4 columns
- Partial: `WHERE status = 'active'` — smaller, faster
- Covering: `INCLUDE (col)` — avoids heap lookup
- Expression: `ON (lower(email))` — for function-based WHERE
- Drop unused indexes: `SELECT * FROM pg_stat_user_indexes WHERE idx_scan = 0`
## JSONB Patterns
```sql
-- GIN index for containment queries
CREATE INDEX ON items USING gin (metadata);
SELECT * FROM items WHERE metadata @> '{"status": "active"}';
-- Expression index for specific key access
CREATE INDEX ON items ((metadata->>'category'));
SELECT * FROM items WHERE metadata->>'category' = 'electronics';
```
Prefer typed columns over JSONB for frequently queried, well-structured data. Use JSONB for truly dynamic/variable attributes.
## Query Optimization
- Always `EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)` before optimizing
- Sequential scan on large table → add index or check `WHERE` for function wrapping
- High `rows removed by filter` → index doesn't match predicate
- `CTE` is an optimization fence before PG12; use `MATERIALIZED`/`NOT MATERIALIZED` hints (PG12+)
- Prefer `EXISTS` over `IN` for correlated subqueries
- Use `LATERAL JOIN` when subquery needs outer row reference
- Cursor pagination (`WHERE id > $last ORDER BY id LIMIT $n`) over `OFFSET`
## Partitioning
Use when table exceeds ~100M rows or needs TTL purge:
- `RANGE` — time-series (by month/year), most common
- `LIST` — categorical (by region, tenant)
- `HASH` — even distribution when no natural key
Partition key must be in every unique/PK constraint. Create indexes on partitions, not parent.
## Transactions & Locking
- Keep transactions short — long txns block vacuum and bloat tables
- `SELECT ... FOR UPDATE SKIP LOCKED` — job queue / work claiming pattern
- Advisory locks for application-level mutual exclusion: `pg_advisory_xact_lock(key)`
- Check blocked queries: `SELECT * FROM pg_stat_activity WHERE wait_event_type = 'Lock'`
## Full-Text Search
```sql
-- Weighted tsvector column with trigger
ALTER TABLE articles ADD COLUMN search_vector tsvector
GENERATED ALWAYS AS (
setweight(to_tsvector('english', coalesce(title,'')), 'A') ||
setweight(to_tsvector('english', coalesce(body,'')), 'B')
) STORED;
CREATE INDEX ON articles USING gin (search_vector);
SELECT * FROM articles WHERE search_vector @@ websearch_to_tsquery('english', $1);
```
## Performance Tuning
Key `postgresql.conf` parameters (adjust for available RAM):
- `shared_buffers` = 25% of RAM
- `effective_cache_size` = 75% of RAM
- `work_mem` = RAM / max_connections / 4 (start 4-16MB)
- `maintenance_work_mem` = 256MB-1GB
- `random_page_cost` = 1.1 for SSD (default 4.0 is for HDD)
## Connection Pooling
Always pool in production. Direct connections cost ~10MB each.
- PgBouncer in `transaction` mode for most workloads
- `statement` mode if no session-level features (prepared statements, temp tables, advisory locks)
## Maintenance
- `pg_stat_statements` extension — find slow queries by total time, not just duration
- `pg_stat_user_tables` — check `n_dead_tup` for vacuum needs
- `autovacuum` tuning: lower thresholds for hot tables
- Monitor cache hit ratio: `SELECT sum(heap_blks_hit) / sum(heap_blks_hit + heap_blks_read) FROM pg_statio_user_tables` — should be > 99%
## Anti-Patterns
| Anti-Pattern | Fix |
|-------------|-----|
| `SERIAL` / `BIGSERIAL` for PKs | `BIGINT GENERATED ALWAYS AS IDENTITY` |
| No FK indexes | Add index on every FK column |
| `OFFSET` pagination | Cursor-based: `WHERE id > $last` |
| `SELECT *` | List needed columns |
| `TIMESTAMP` without timezone | `TIMESTAMPTZ` |
| Functions in WHERE (`lower(col)`) | Expression index or citext extension |
| Storing structured data as text | `JSONB` with GIN index |
| Long-running transactions | Keep txns short, use `idle_in_transaction_session_timeout` |
Files in this package (2)
- SKILL.md 6 KB
- manifest.json 1 KB
Versions
| Repository | Score | Licence |
|---|---|---|
| This page · iliaal/whetstone | 100 | MIT |
| speakeasy-api/gram | 100 | AGPL-3.0 |
| TerminalSkills/skills | 100 | Apache-2.0 |
| itechmeat/llm-code | 100 | MIT |
| claude-dev-suite/claude-dev-suite | 100 | MIT |
| yvssdheeraj/opencode-engineering-harness | 100 | MIT |
| G1Joshi/Agent-Skills | 100 | MIT |
| bcastelino/agent-skills-kit | 100 | MIT |
| MARUCIE/openclaw-foundry | 100 | MIT |
Database snapshot: 13.11.2026 · package version ed2e785c05be (SHA-256 digest of the entire package)
Downloads in the last 30 days: 0 · ranking
Report a problem with this skill: harmful content, incorrect descriptions or an author’s removal request.
Similar skills
- 90sql-postgresql-guide
Use when editing PostgreSQL 15+ queries, schemas, or migrations. Triggers on `.sql` files, migration files, schema files, and prompts about CTEs, indexing, JSONB, RLS, EXPLAIN ANALYZE, or constraints, even when the user doesn't say 'Postgres'. - 90postgres-best-practices
Supabase PostgreSQL best practices library — 33 impact-rated rules (CRITICAL→LOW) covering connection pooling, RLS, locking, VACUUM, full-text search, and JSONB indexing. Use when writing, reviewing, or optimizing PostgreSQL queries, schema, or configuration. - 100postgresql-optimization
Expert in PostgreSQL performance tuning, query optimization, and database administration. Specializes in EXPLAIN analysis, indexing strategies, connection pooling, partitioning, and production-grade PostgreSQL operations. Use when the user explicitly needs postgresql… - 100postgres-code-review
Reviews PostgreSQL code for indexing strategies, JSONB operations, connection pooling, and transaction safety. Use when reviewing SQL queries, database schemas, JSONB usage, or connection management. - 100sql-advanced
Advanced SQL patterns including CTEs, window functions, recursive queries, query optimization, and EXPLAIN analysis. Use for complex query writing and performance tuning. USE WHEN: user mentions "CTE", "window functions", "recursive queries", "EXPLAIN", "query optimization",… - 95postgresql-data-engineering
Design, review, troubleshoot, or improve PostgreSQL schemas, queries, transactions, migrations, pooling, security, and production recovery. Use for PostgreSQL engineering decisions; skip unrelated backend work and trivial SQL or text edits.