skilld
All skills
planetscale avatar

/postgres

@2b8cda6 official
by planetscaleplanetscale/database-skills693 stars
61

PostgreSQL best practices, query optimization, connection troubleshooting, and performance improvement. Load when working with Postgres databases.

Ask your Agent

Use this Skill: https://skilld.dev/gh/planetscale/database-skills/postgres

This session only. Nothing lands on disk.

referencesstorage-layout.md

≈624 tokens on demand. Your agent reads this file only when SKILL.md points to it.

Storage Layout and Tablespaces

PGDATA Structure

  • base/ — database files (one subdirectory per database, named by OID)
  • global/ — cluster-wide shared catalogs (pg_database, pg_authid, pg_tablespace)
  • pg_wal/ — WAL files
  • pg_xact/ — transaction commit status

"Cluster" in PostgreSQL = single instance with one PGDATA, not an HA cluster. Each table/index = one or more files, split into 1GB segments. Tables have companion _fsm (free space map) and _vm (visibility map); indexes have _fsm only (no _vm), except hash indexes.

Visibility Map and Free Space Map

  • _vm tracks all-visible pages — VACUUM skips these
  • _fsm tracks free space per page — INSERT uses this to find pages with room
  • Both are small files but critical for performance

TOAST

TOAST triggers when a row exceeds ~2KB. Large values are compressed and/or moved out-of-line to pg_toast.pg_toast_<oid> tables. Strategies: PLAIN (no TOAST), EXTENDED (compress+out-of-line, default for text/bytea), EXTERNAL (out-of-line, no compression — use for pre-compressed data), MAIN (compress, avoid out-of-line). TOAST tables bloat like regular tables — they need VACUUM. SELECT * fetches all TOAST columns; always SELECT only needed columns. Move large rarely-accessed columns to separate tables.

Fillfactor

Controls how full pages are packed (default 100%). Lower fillfactor (70–80%) leaves room for HOT (Heap-Only Tuple) updates, which avoid index entries and reduce bloat on UPDATE-heavy tables. Keep 100% for insert-only or read-mostly tables. ALTER TABLE t SET (fillfactor = 70);

Tablespaces

pg_default (base/), pg_global (global/) are built-in. Custom tablespaces: symbolic links in pg_tblspc/ to other filesystem locations. Use for separating hot data (SSD) from archives (HDD). Moving tablespaces requires exclusive lock on affected tables.

Disk Monitoring

  • pg_database_size('dbname'), pg_total_relation_size('tablename'), pg_relation_size('tablename')
  • Monitor disk usage: >80% = at risk; >90% = critical (VACUUM may fail if disk capacity is insufficient)
  • Check inode usage (df -i) — can run out even with free space
  • pg_wal/ suddenly large = check replication slots and archiving

Source: SKILL.md on GitHub

Third-party checks

1 alert17d5 checks · Risk SAFE
  • Gen Agent Trust Hub17d

    The skill provides comprehensive documentation, best practices, and diagnostic SQL queries for PostgreSQL and PlanetScale-specific features. No security issues were identified.

  • Socket17d

    No alerts

  • Snyk17d

    Risk: LOW · No issues

  • Runlayer6mo

    18/23 files flagged

  • ZeroLeaks5mo

    Score: 93/100 · 2 sections analyzed

Provenance

Signed by skilld at 2b8cda6. This ties the file your Agent reads to that commit on GitHub. It does not review the instructions.

Last checked against GitHub last month.

Activeupdated 7 months ago

Capability

metadata
{
  "author": "planetscale",
  "version": "1.0.0"
}

Topics

  • Database
  • postgres
  • schema-design
  • indexing
  • query-optimization
  • replication
  • monitoring
  • backup-recovery
  • planetscale

README badge

README badge for planetscale/database-skills/postgres

What it does

Provides Postgres best practices, query optimization guidance, indexing strategies, and schema design patterns. Includes PlanetScale-specific connection pooling, monitoring, and CLI reference for hosted Postgres deployments.

Generated from the current SKILL.md.

Frequently asked

Does this skill cover PlanetScale-specific features like connection pooling?
Yes. The skill includes references for PlanetScale connection pooling, PgBouncer configuration, the pscale CLI, and PlanetScale Insights for slow query analysis.
What topics does this skill cover?
Schema design, indexing, query optimization, partitioning, MVCC and transactions, replication, WAL operations, monitoring, backup/recovery, and PlanetScale-specific connection and deployment workflows.
Does this skill help with query performance troubleshooting?
Yes. It includes guidance on query patterns, index optimization, MVCC transaction isolation, and monitoring via pg_stat views and pg_stat_statements.
Can I use this skill if I self-host Postgres instead of PlanetScale?
Yes. The skill covers generic Postgres best practices and operations. PlanetScale-specific references are optional and only used if you choose PlanetScale for hosting.

Generated from the current SKILL.md. These answers refresh after source changes.