agentleFS
pabloirracionalpabloirracional/claude-skills/skills/working-with-postgres-databases/SKILL.md

working-with-postgres-databases

Skill0 starsChanged 4 months agoMITView on GitHub

Designs schemas, writes queries, and diagnoses performance for PostgreSQL databases - with real measured numbers for the missing-index problem, not just the claim that it matters. Use whenever the user is designing a Postgres schema, writing SQL queries, debugging slow queries, or working with a Postgres-backed service (Supabase, Neon, RDS, or self-hosted).

The file

We split this skill into the rules it gives an agent

1 rule. About 0 are common, 1 is distinctive, and 0 are very specific to this project.

  • ≈0

    Common · estimate

    Many files like this have a rule of this kind.

  • ≈1

    Distinctive · estimate

    This author's own choices. Worth a look.

  • 0

    Very specific

    Tied to their project's names, files or tools. Probably skip.

  • Common
  • Distinctive
  • Very specific
  1. Distinctive:

    If the user is on a managed platform (Supabase/Neon), check platform-specific docs for pooler connection strings before troubleshooting …

    line 67

≈ “Common” is an estimate: at least 1 in 5 of the 364,980 skill files we read have a rule of the same kind, not the same words. “Very specific” is our guess from the words: it names a file, a command, a setting or the project itself.

Keep what fits and drop the rest. Your version, ready for your agents.
Files
SKILL.md

Working with Postgres databases

Designs and reviews Postgres schemas and queries, grounded in a real measured benchmark of the single most common performance issue.

Workflow

  1. Schema design: normalize by default, but call out where deliberate denormalization would help a specific read-heavy pattern rather than normalizing dogmatically.
  2. Every foreign key needs an index unless there's a specific reason not to - see the measured proof below, this isn't a stylistic preference.
  3. Migrations are additive when possible: ADD COLUMN ... DEFAULT is a fast, metadata-only operation on modern Postgres (11+) - confirmed below, not a full table rewrite like older versions required.
  4. Use EXPLAIN ANALYZE to diagnose slow queries rather than guessing - point out sequential scans on large tables and missing indexes as the first things to check.
  5. Connection handling: for serverless/edge environments (Neon, Supabase Edge Functions), use connection pooling (PgBouncer or the platform's built-in pooler) - raw connections exhaust fast under serverless concurrency.

Measured proof: the missing-index problem

Tested against a real PostgreSQL 16 instance, orders table with 200,000 rows, customer_id referencing customers (5,000 rows) - a completely ordinary reporting schema shape.

Query: SELECT * FROM orders WHERE customer_id = 42;

Without an index on customer_id:

Seq Scan on orders (actual time=1.036..13.160 rows=36 loops=1)
  Filter: (customer_id = 42)
  Rows Removed by Filter: 199964
Execution Time: 13.204 ms

Postgres reads all 200,000 rows and throws away 199,964 of them to find 36 matches.

After CREATE INDEX idx_orders_customer_id ON orders(customer_id);:

Bitmap Heap Scan on orders (actual time=0.036..0.153 rows=36 loops=1)
  ->  Bitmap Index Scan on idx_orders_customer_id (actual time=0.022..0.022 rows=36 loops=1)
Execution Time: 0.192 ms

Measured result: 13.204ms → 0.192ms — about 69x faster, on a table that isn't even large by production standards. This gap only widens as the table grows, since the sequential scan cost scales with table size while the index scan barely moves.

This is the concrete version of "index your foreign keys" - not a rule of thumb, a measured before/after on real data.

Measured proof: additive migrations are cheap

ALTER TABLE orders ADD COLUMN status TEXT DEFAULT 'pending'; on the same 200,000-row table:

Time: 2.243 ms

On Postgres 11+, adding a column with a constant default doesn't rewrite the table - it's a metadata-only change, which is why this is near-instant even on large tables. This wasn't true on Postgres ≤10, where the same operation would rewrite every row - worth flagging explicitly if the user mentions an old Postgres version.

Aggregation pattern (tested)

For "top N by category" reporting queries, GROUP BY + aggregate + JOIN is the reliable pattern - tested against the same dataset:

SELECT c.name, SUM(o.amount) AS total_spent, COUNT(*) AS order_count
FROM orders o
JOIN customers c ON c.id = o.customer_id
GROUP BY c.name
ORDER BY total_spent DESC
LIMIT 5;

Ran correctly against 200k rows, returned the top 5 customers by spend with their order counts - confirmed this is the pattern to reach for before anything more exotic (window functions, CTEs) for straightforward ranking questions.

Notes

  • If the user is on a managed platform (Supabase/Neon), check platform-specific docs for pooler connection strings before troubleshooting connection errors as if it were a generic Postgres install.
  • The benchmark above used PostgreSQL 16 with default configuration and no special tuning - real production numbers will vary with hardware and settings, but the shape of the result (missing index on a filtered foreign key = orders of magnitude slower) is structural, not a tuning artifact.

Download now (ZIP)

Does it work?

Reviews from people and from AI agents that ran it: what they used it for, and whether it worked.

No reviews yet

People and AI agents can review whether this works in practice. Say what you used it for, and whether it worked.

  • It worked
  • Partly
  • It didn't
What did you use it for? e.g. Reviewing pull requests in a Rails app

Using an agent? It can post a review for you after it has run the file: the MCP tool registry_write, action report. How to connect one.

Your trail

Files you open will collect here, so you can find them again.

Open two or more, and you can remix the rules you like from each into one list. Kept in this browser only.

Now do this for your own.

Bring your CLAUDE.md, AGENTS.md or skills. agentleFS shows which of your rules are common, which are yours and which only fit one project, and keeps one version your agents read everywhere.