Architecture Postgresql Internals

Use when the user asks about Postgres MVCC, the write-ahead log (WAL), table bloat, autovacuum, dead tuples, transaction ID wraparound, checkpoints, HOT updates, EXPLAIN ANALYZE or why Postgres is slow. Produces diagnosis queries and tuning settings. Not for schema design (use data-relational-database) or sharding (use architecture-database-scaling).

45 stars 6 forksSoftware Development

Install

skills CLI (Claude Code, Cursor, Codex and more)
npx skills add j4flmao/agent-skills --skill architecture-postgresql-internals
Manual install
git clone --depth 1 https://github.com/j4flmao/agent-skills.git /tmp/agent-skills
mkdir -p ~/.claude/skills
cp -r /tmp/agent-skills/skills/architecture/databases/postgresql-internals ~/.claude/skills/architecture-postgresql-internals

Personal skills live in ~/.claude/skills. Use .claude/skills in a project folder to share a skill with your team.

From the SKILL.md

# PostgreSQL Internals

## Purpose
Most PostgreSQL production issues (bloat, sudden slowdowns, replication lag, wraparound emergencies, bad plans) are direct consequences of how MVCC, VACUUM, WAL, and the planner work. This skill explains those mechanisms precisely and maps each one to the queries and settings used to diagnose and tune it.

## Agent Protocol

### Trigger
"MVCC", "bloat", "autovacuum", "VACUUM FULL", "dead tuples", "xid wraparound", "WAL", "checkpoint", "HOT update", "fillfactor", "shared_buffers", "EXPLAIN ANALYZE", "slow query".

### Input Context
- PostgreSQL major version, hardware/managed service, workload (OLTP/analytics, write rate).
- Symptoms with timestamps; `pg_stat_*` output; slow query plans.
- Long-running transactions or replication slots in use.

### Output Artifact
Diagnosis with evidence queries, root cause tied to the mechanism, and concrete setting/schema changes.

### Response Format
```
Symptom: {what}
Mechanism: {MVCC|VACUUM|WAL|checkpoint|planner|buffer}
Evidence: {query + key numbers}
Fix: {setting/table option/query change} (reload|restart required)
Verify: {metric to watch}
```

### Completion Criteria
- [ ] Root cause supported by catalog/statistics evidence.
- [ ] Changes state whether they need reload or restart.
- [ ] Long transactions, idle-in-transaction sessions, and stale replication slots checked.

## MVCC
- Every row version (tuple) stores `xmin` (creating transaction) and `xmax` (deleting/locking transaction).
Read the full skill on GitHub

Details

Skill name
architecture-postgresql-internals
Path
skills/architecture/databases/postgresql-internals/SKILL.md
License
MIT
Repo updated
17 November 2026

FAQ

What does the Architecture Postgresql Internals skill do?

Use when the user asks about Postgres MVCC, the write-ahead log (WAL), table bloat, autovacuum, dead tuples, transaction ID wraparound, checkpoints, HOT updates, EXPLAIN ANALYZE or why Postgres is slow. Produces diagnosis queries and tuning settings. Not for schema design (use data-relational-database) or sharding (use architecture-database-scaling).

How do I install the Architecture Postgresql Internals skill?

skills CLI (Claude Code, Cursor, Codex and more): npx skills add j4flmao/agent-skills --skill architecture-postgresql-internals. Skills go in ~/.claude/skills (personal) or .claude/skills (project), and Claude loads them automatically when a task matches the description.

Is Architecture Postgresql Internals popular?

It lives in j4flmao/agent-skills, which has 45 GitHub stars and 6 forks.

Related skills

Agent Observability

45

j4flmao/agent-skills

Use when the user asks about agent observability, tracing agent reasoning, debugging an agent loop, OpenTelemetry GenAI spans for LLM calls, LLM cost tracking per agent, agent audit logs or hallucination/eval monitoring in production. Produces instrumentation config, trace schema, dashboards, alerts and cost attribution. Not for generic service tracing without LLMs (use devops-opentelemetry).

DevOps & Cloud

AI Agent Architectures

45

j4flmao/agent-skills

Use when the user asks to build an AI agent, design an agent that calls tools and keeps memory, write an agent system prompt or pick an agent framework (Claude Agent SDK, OpenAI Agents SDK, LangGraph). Produces an architecture brief covering loop, tools, memory, guardrails and evals, routing depth to child skills. Not for workflow patterns (use ai-agentic-workflows).

AI, LLMs & Agents

AI Agent Core Loop

45

j4flmao/agent-skills

Use when the user asks about the agent loop, ReAct vs plan-and-solve, re-planning, Reflexion, an agent stuck in a loop, max iterations or termination conditions. Produces a loop spec or patch with state, success predicate, caps and no-progress detection. Not for overall agent design (use ai-agent-architectures) or tool schemas (use tool-calling-principles).

AI, LLMs & Agents

Authoritative Server

45

j4flmao/agent-skills

Use when the user asks about an authoritative game server, dedicated server, server tick loop, snapshots, input authority, server reconciliation, rollback or lag compensation on the server. Produces tick loop and world-state design. Not for client-side prediction (use multiplayer-netcode) or fleet scaling (use scaling-architecture).

Software Development

Architecture Domain Driven Design

45

j4flmao/agent-skills

Use when the user asks about DDD, domain-driven design, bounded contexts, aggregates and aggregate roots, ubiquitous language, context maps, anti-corruption layers, event storming or value objects. Produces strategic and tactical domain models. Not for simple CRUD apps or folder layering (use backend-clean-architecture) or service decomposition (use microservices).

Software Development

Backend API Design

45

j4flmao/agent-skills

Use when the user asks about REST API design, designing REST endpoints, URL and resource naming, HTTP methods and status codes, pagination, filtering or error formats. Produces endpoint contracts with conventions. Not for the response envelope (use backend-api-response) or versioning (use backend-api-versioning).

Software Development

Browse the most popular Claude skills, all software development skills, or write your own with the skills generator.