SimHacker 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-optimizationafter 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 -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 -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>— 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— mechanismpostgresin 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