Installs into .claude/skills of the current project.
Are you the author of Postgresql?
Add the live security badge to your README. It updates with every re-scan.
[](https://www.skillsdirectory.com/skills/skeletorflet-postgresql)
---
name: postgresql
description: PostgreSQL — advanced SQL, indexing, extensions, partitioning, EXPLAIN ANALYZE.
---
## postgresql
### Advanced SQL
- CTEs: `WITH cte AS (SELECT ...) SELECT * FROM cte` for readable queries.
- Window functions: `ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC)`.
- Recursive CTEs: `WITH RECURSIVE` for tree/graph traversal queries.
- `LATERAL` subqueries, `GROUPING SETS`, `FILTER` clause for conditional aggregates.
### Indexing
- B-tree (default) for equality/range; GiST for full-text/geometric; GIN for array/JSON/tsvector; BRIN for large sequential data.
- Partial indexes: `CREATE INDEX active_users ON users(created_at) WHERE status = 'active'`.
- Covering indexes with `INCLUDE (col1, col2)` for index-only scans.
### Extensions
- PostGIS: spatial queries — `ST_Distance`, `ST_Within`, `ST_AsGeoJSON`.
- pgvector: `CREATE INDEX ON items USING ivfflat (embedding vector_cosine_ops)` for ANN.
- pg_cron: schedule SQL jobs via cron syntax.
- `uuid-ossp`, `pg_trgm`, `citext`, `hstore` for common utilities.
### Partitioning
- Range/list/hash partitioning via `PARTITION BY RANGE (created_at)`.
- Detach/attach partitions for zero-downtime data movement.
- Partition pruning for query performance on large tables.
### EXPLAIN ANALYZE
- `EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)` for execution plan analysis.
- Focus on sequential scans, nested loop joins, high row estimates.
### Tools
- psql: `\dt`, `\d table`, `\l`, `\di`, `\x auto` for expanded display.
- pgAdmin: GUI with query planner visualizer and dashboard.
