postgresql
/postgresql
Pomaga projektować schematy PostgreSQL, optymalizować zapytania, indeksować i administrować: JSONB, partycjonowanie, RLS, CTE, funkcje okna, EXPLAIN ANALYZE i pule połączeń.
- Autor
- iliaal ↗
- Repozytorium
- whetstone ↗
- Licencja
- MIT
- Ostatnia zmiana repozytorium
- 26.10.2026
Ocena i bezpieczeństwo
- Pliki w paczce
- 2
- Skrypty
- 0
- Komentarze HTML
- 0
- Adresy URL w SKILL.md
- 0
Brak sygnałów. Repozytorium zmienione 26.10.2026, bez odwołań do wycofanych modeli i API.
MIT, otwarta: można używać i zmieniać, z zachowaniem informacji o autorze.
Co robi i kiedy
Pomaga projektować schematy PostgreSQL, optymalizować zapytania, indeksować i administrować: JSONB, partycjonowanie, RLS, CTE, funkcje okna, EXPLAIN ANALYZE i pule połączeń.
Oryginalny opis (EN)
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.
Jak działa
- Data Type Defaults
- Schema Rules
- Index Strategy
- JSONB Patterns
- Query Optimization
- Partitioning
- Transactions & Locking
- Full-Text Search
- Performance Tuning
- Connection Pooling
Podgląd SKILL.md
---
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` |
Pliki w paczce (2)
- SKILL.md 6 KB
- manifest.json 1 KB
Wersje
| Repozytorium | Wynik | Licencja |
|---|---|---|
| Ta strona · 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 |
Migawka bazy z 13.11.2026 · wersja paczki ed2e785c05be (skrót SHA-256 całej paczki)
Pobrania w ostatnich 30 dniach: 0 · ranking
Zgłoś problem z tym skillem: szkodliwa treść, błędny opis albo prośba autora o usunięcie.
Podobne skille
- 90sql-postgresql-guide
Podpowiada przy zapytaniach, schematach i migracjach w PostgreSQL 15+: CTE, indeksy, JSONB, RLS, EXPLAIN ANALYZE i ograniczenia. - 90postgres-best-practices
Sprawdza zapytania, schemat i konfigurację PostgreSQL według 33 zasad wpływu, obejmujących pule połączeń, RLS, blokady, VACUUM i indeksy JSONB. - 100postgresql-optimization
Specjalizuje się w strojeniu wydajności PostgreSQL, optymalizacji zapytań i administracji: analiza EXPLAIN, strategie indeksowania, pule połączeń, partycjonowanie i operacje produkcyjne. - 100postgres-code-review
Przegląda kod PostgreSQL pod kątem strategii indeksowania, operacji JSONB, puli połączeń i bezpieczeństwa transakcji, w zapytaniach SQL i schematach. - 100sql-advanced
Pomaga pisać złożone zapytania SQL: CTE, funkcje okna, zapytania rekurencyjne, optymalizacja i analiza EXPLAIN. - 95postgresql-data-engineering
Projektuje, sprawdza i naprawia schematy, zapytania, migracje i pule połączeń w PostgreSQL, także w przypadku awarii produkcji.