Agent-Skills.md

Agent Skills: PostgreSQL

Server PostgreSQL operations — connect, migrate, Timescale, pgvector, introspection; delegates deep tuning to postgres-optimization.

UncategorizedID: simhacker/moollm/postgres

Repository

simhacker/moollm
SimHackerLicense: MIT
365

Install this agent skill to your local

pnpm dlx add-skill https://github.com/SimHacker/moollm/tree/HEAD/skills/postgres

Skill Files

Browse the full folder contents for postgres.

Download Skill

Loading file tree…

skills/postgres/SKILL.md

Skill Metadata

Name
postgres
Description
Server PostgreSQL operations — connect, migrate, Timescale, pgvector, introspection; delegates deep tuning to postgres-optimization.

PostgreSQL

Part of MOOLLM · skills/postgres/

Treat this skill as the MOOLLM wrapper around PostgreSQL’s full stack: the psql client, pg_dump / pg_restore, connection libraries, extension packages, and the on-disk / managed-server ecosystem—not a reimplementation of the engine. The postgres schemapedia mechanism points here; sql remains the abstract language family.

This skill covers operating PostgreSQL: getting connected, migrations, extensions (including TimescaleDB and pgvector), catalog introspection, and operational vocabulary. Index design, constraint tricks, and deep EXPLAIN work live in postgres-optimization — use this skill first for wiring and safety, then delegate when the problem is plan-level.

When to use

  • New service or container needs a repeatable way to apply DDL.
  • Choosing extensions: time-series (Timescale), embeddings (pgvector), query stats (pg_stat_statements), text (pg_trgm, citext).
  • Debugging connection issues (URI vs env vars, SSL, roles).
  • Not the first stop for “this one query is slow” — use postgres-optimization after you have a query and schema.

Connection: URI and environment

Single URI (typical in apps):

postgresql://user:password@host:5432/dbname?sslmode=require

psql accepts a URI:

psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -c "
SELECT current_database(), current_user, version();
"

If tooling uses PG_CONNECTION_STRING instead of DATABASE_URL, normalize in shell:

psql "${DATABASE_URL:-$PG_CONNECTION_STRING}" -v ON_ERROR_STOP=1 -f ./migrations/001_apply.sql

Split variables (classic DBA style): PGHOST, PGPORT, PGUSER, PGDATABASE, PGPASSWORD. psql picks these up when no URI is passed.

Safe DDL and scripts

  • -v ON_ERROR_STOP=1 — stop the script on first error (essential for migrations).
  • Transactions — wrap multi-statement deploys when appropriate; know that some DDL takes stronger locks.
  • Idempotent patterns — CREATE TABLE IF NOT EXISTS, CREATE EXTENSION IF NOT EXISTS — avoid blind rewrites in production without review.

Migrations (practice)

  • Single source of truth — one ordered apply path, or a migration tool (Flyway, Sqitch, Alembic, Liquibase, ORM) with explicit version table.
  • Roles and grants — separate “superuser bootstrap” from application DDL where possible.
  • Rollback story — forward-only migrations are common; document how to recover (restore from backup, or compensating migration).

Introspection (starter queries)

Version and extensions:

SELECT version();
SELECT extname, extversion FROM pg_extension ORDER BY 1;

Table sizes (rough):

SELECT relname, pg_total_relation_size(oid) AS bytes
FROM pg_class
WHERE relkind = 'r' AND relnamespace = 'public'::regnamespace
ORDER BY bytes DESC
LIMIT 20;

Load pg_stat_statements (if installed) and read the PostgreSQL docs for setup — then inspect top queries by total time.

TimescaleDB (time-series)

Timescale extends PostgreSQL with hypertables (time-partitioned tables), compression, retention policies, and continuous aggregates (rollup views). Read the product docs before turning this on in production: chunk intervals, retention, and compression interact with query patterns.

Canonical entry points:

  • https://docs.timescale.com/
  • Hypertables: https://docs.timescale.com/use-timescale/latest/hypertables/
  • Continuous aggregates: https://docs.timescale.com/use-timescale/latest/continuous-aggregates/

Typical enable: CREATE EXTENSION IF NOT EXISTS timescaledb; then create_hypertable(...) per current API in your Timescale version.

pgvector (embeddings)

pgvector stores vectors in-table and supports nearest-neighbor search with distance operators. Install the extension package for your Postgres major, then:

CREATE EXTENSION IF NOT EXISTS vector;

Details and operator list: https://github.com/pgvector/pgvector

Extension catalog

See EXTENSIONS.md for a link table (Timescale, pgvector, pg_stat_statements, pg_trgm, PostGIS, and core manual links).

Backup and restore vocabulary

  • Logical dump: pg_dump / pg_restore — portable, version-sensitive.
  • Base backups: file-system level + WAL archiving — streaming replication and PITR territory; follow your platform’s runbook.
  • Do not paste real credentials into chat logs; use env vars and secret stores.

Relationship to other skills

| Skill | Role | |-------|------| | postgres-optimization | Unconventional and advanced performance (indexes, EXPLAIN, plans). | | sqlite | Embedded file database — when to stay local vs move to Postgres. | | schema | Schemapedia registry — sql language family, postgres mechanism. |

Related skills

  • postgres-optimization — use when this skill’s connection and ops context is not enough and the bottleneck is query or index design.
  • sqlite — compare embedded vs server tradeoffs.
  • schema — mechanism postgres in the registry points here.

Part of MOOLLM

This skill's directory (browse and fetch everything): skills/postgres/