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/

• MOOLLM: repo · Skill index and docs: skills/README