postgres-pro
Deep PostgreSQL know-how — indexing, tuning, JSONB, and administration — use when working with or troubleshooting Postgres.
- License 许可
- MIT
- 最近更新
- 2026-11-07
- Stars
- ★ 0
- Forks
- 1
01 前言 Front Matter
- name
- postgres-pro
- category
- database
- description
- Deep PostgreSQL know-how — indexing, tuning, JSONB, and administration — use when working with or troubleshooting Postgres.
03 SKILL.md 正文
Overview
PostgreSQL is a production workhorse: ACID-compliant, extensible, and generous with features (JSONB, full-text search, arrays, PostGIS). This skill covers the operational knowledge that separates a working Postgres setup from a reliable one: choosing indexes, reading plans, tuning configuration, and safe administration.
When to use
- Choosing between B-tree, GIN, GiST, BRIN, or hash indexes
- Investigating slow queries with
EXPLAIN (ANALYZE, BUFFERS) - Tuning
shared_buffers,work_mem,maintenance_work_mem, autovacuum - Designing schemas with JSONB, arrays, or full-text search
- Managing backups, replication, connections, and upgrades
Core concepts
Index types matter. B-tree is the default and right for most lookups and range
queries. GIN powers JSONB containment (@>) and full-text search; GiST handles
geometric and range types; BRIN shines for very large, naturally ordered tables
(time-series). A GIN index on a JSONB column can turn a 10-second scan into a
10-millisecond lookup — but adds write overhead.
MVCC and bloat. Postgres never overwrites rows in place; updates create new
row versions and dead tuples are reclaimed by VACUUM. Long-running transactions
block cleanup and cause bloat. Monitor pg_stat_user_tables for tables where
n_dead_tup grows faster than autovacuum cleans.
EXPLAIN (ANALYZE, BUFFERS) is ground truth. It shows actual row counts,
timing per node, and buffer hits vs disk reads. Compare estimated vs actual rows:
a 100x misestimate usually means stale statistics (ANALYZE) or a bad plan forced
by them.
Connection discipline. Postgres uses a process per connection; thousands of
idle connections waste memory. Use a pooler (PgBouncer in transaction mode) in
front of the database and keep max_connections modest.
JSONB done right. Store flexible documents in jsonb, query with -> / ->>,
index hot paths with GIN or expression indexes. Don't use JSONB as an excuse to
skip schema design — frequently queried fields often deserve real columns.
Practical workflow
- Diagnose with data:
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)on the slow query; find the node consuming most time. - Check the usual suspects: missing index (Seq Scan on a large filtered
table), stale stats (
ANALYZE table_name), bloat (pgstattuple), lock waits (pg_stat_activity). - Create indexes deliberately: one composite index on
(filtered_col, ordered_col)often beats two single-column indexes; useCONCURRENTLYin production to avoid locking the table:CREATE INDEX CONCURRENTLY idx_orders_customer_date ON orders (customer_id, order_date DESC); - Tune conservatively: start with
shared_buffers≈ 25% of RAM,effective_cache_size≈ 75% of RAM,work_memsmall (4–64MB) — raise only with evidence of sorts spilling to disk (EXPLAINshows "Disk:" sorts). - Backups before changes: verify
pg_dump/pg_basebackuprestores actually work — an untested backup is not a backup. Test restores on a schedule. - Safe migrations: add columns as nullable (or with defaults via metadata-only
changes in modern versions), backfill in batches, then add
NOT NULLwithVALIDATE CONSTRAINTto avoid long locks.
Common pitfalls
- Killing autovacuum or disabling it — leads to bloat, transaction-ID wraparound risk, and eventually downtime. Tune it, don't disable it.
- Creating indexes without
CONCURRENTLYon live tables — takes anACCESS EXCLUSIVElock and blocks writes. - Wrapping indexed columns in functions in
WHERE(e.g.WHERE lower(email) = ...) — defeats the index unless you create an expression index onlower(email). SELECT ... FOR UPDATEwithout ordering — invites deadlocks under concurrency; lock rows in a consistent order.- Timezone bugs:
timestamp without time zonesilently shifts meaning. Prefertimestamptzand convert at the presentation layer. - Running out of connections under load — add pooling before raising
max_connections; each connection costs real memory.
04 同仓库更多 Skills
web-scrape
Turn web pages into LLM-ready markdown via the Firecrawl API (renders JS, anti-bot handling). Use when browser_open/fetch fails on JS-heavy pages, or when you need clean markdown/structured extraction from a site. Requires FIRECRAWL_API_KEY.
design-taste-frontend
tasteskill v2, the anti-slop frontend skill. Art direction for websites, distilled from a study of 247 curated gallery sites (One Page Love, Awwwards Sites of the Day). Deals researched directions (idea, composition, type, palette, photographs, section blueprints) from a library, enforces a strict no-slop house style with a real-browser inspector, and runs a look-and-fix loop on screenshots. Use this whenever you build, design, redesign, restyle, polish or review a landing page, marketing site, portfolio, product page, one-pager, hero or any public-facing web page, in any stack (HTML/CSS, React, Next.js, Vue, Svelte, Astro, Tailwind) — also when the user says a page looks generic, templated, "AI-made", sloppy, or asks to make it look designed, premium, award-level or less like every other site. Not for dashboards or app screens.
test-driven-development
Use when implementing any feature or bugfix, before writing implementation code
systematic-debugging
Use when encountering any bug, test failure, or unexpected behavior, before proposing fixes
requesting-code-review
Use when completing tasks, implementing major features, or before merging to verify work meets requirements
brainstorming
You MUST use this before any creative work - creating features, building components, adding functionality, modifying behavior, or planning anything new, in software or out of it (a talk, a business, a renovation).