postgres-best-practices
neondatabase/postgres-skills
Best practices and guidelines for PostgreSQL schema design, indexing, query optimization, and common pitfalls.
What is postgres-best-practices?
A comprehensive reference for working with PostgreSQL 14–18, covering schema design, indexing strategies, query optimization, migrations, and performance diagnostics. Use when writing SQL, designing database schemas, optimizing queries, or setting up a Postgres database.
- Schema design patterns, data types, normalization, and partitioning strategies
- Indexing guidance including index types, composite indexes, and covering indexes
- Query optimization via EXPLAIN ANALYZE, planner tuning, and bottleneck identification
- Query patterns including CTEs, window functions, lateral joins, UPSERT, and JSONB
- Performance diagnostics using pg_stat views, lock analysis, and VACUUM management
- Logical replication, hot standby, streaming replication, and read replica setup
How to install postgres-best-practices
npx skills add https://github.com/neondatabase/postgres-skills --skill postgres-best-practicesHow to use postgres-best-practices
- 1.Install the skill using the provided npm command
- 2.Consult the appropriate reference guide based on your task (schema design, indexing, query optimization, etc.)
- 3.Apply the recommended patterns and best practices to your code or database configuration
- 4.Use EXPLAIN ANALYZE and pg_stat views to validate performance improvements
- 5.Review version-specific features (tagged [PG15+], [PG18+]) for your PostgreSQL version
Use cases
- Designing normalized database schemas with appropriate data types and constraints
- Creating and tuning indexes to improve query performance on large tables
- Analyzing slow queries with EXPLAIN ANALYZE and implementing optimization fixes
- Setting up logical replication for live migrations or change-data-capture pipelines
- Configuring streaming replication and read replicas for high availability
- Database architects designing new Postgres systems
- Backend engineers writing SQL and optimizing queries
- DevOps and database administrators managing Postgres infrastructure
- Data engineers building ETL pipelines and bulk-loading workflows
- Teams implementing high-availability and disaster-recovery setups
postgres-best-practices FAQ
PostgreSQL 14 through 18. Version-specific features are tagged (e.g., [PG15+], [PG18+]). Always run the latest minor release within your major version.
Consult the indexing reference guide, which covers B-tree, hash, GiST, GIN, and BRIN indexes with guidance on when each is appropriate based on query patterns and data characteristics.
Use EXPLAIN ANALYZE to identify bottlenecks, then refer to the query optimization reference for planner tuning, index strategies, and common anti-patterns to avoid.
The hot standby reference covers streaming replication and read replica setup; the logical replication reference covers pub/sub replication for live migrations and CDC.
Consult the major version upgrades reference for pg_upgrade procedures, logical replication migration strategies, and pre/post-upgrade checklists.
Full instructions (SKILL.md)
Source of truth, from neondatabase/postgres-skills.
name: postgres-best-practices description: Best practices and guidelines for working with Postgres. Covers schema design, indexing strategies, query optimization, migrations, and common pitfalls. Use when writing SQL, designing database schemas, optimizing queries, or setting up a Postgres database.
Postgres Best Practices
Guidelines and best practices for working with Postgres, covering schema design, indexing, query optimization, and common pitfalls.
Supported Versions
This skill covers PostgreSQL 14 through 18. Version-specific features are tagged (e.g., [PG15+], [PG18+]); environment-dependent examples identify required privileges, extensions, or multi-node setup.
PostgreSQL provides 5 years of support per major version. Always run the latest minor release.
| Version | Initial Release | End of Life |
|---|---|---|
| 18 | September 2025 | November 2030 |
| 17 | September 2024 | November 2029 |
| 16 | September 2023 | November 2028 |
| 15 | October 2022 | November 2027 |
| 14 | September 2021 | November 2026 |
Source: postgresql.org/support/versioning
References
| Area | Resource | When to Use |
|---|---|---|
| Schema Design | references/schema-design.md | Designing tables, choosing data types, normalizing, partitioning |
| Indexing | references/indexing.md | Choosing index types, composite indexes, partial/covering indexes |
| Query Optimization | references/query-optimization.md | Reading EXPLAIN ANALYZE, fixing bottlenecks, planner tuning |
| Query Patterns | references/query-patterns.md | CTEs, window functions, lateral joins, UPSERT, JSONB, anti-patterns |
| Performance Diagnostics | references/performance-diagnostics.md | pg_stat views, lock analysis, VACUUM, connection management |
| Logical Replication | references/logical-replication.md | Pub/sub replication, live migrations, CDC |
| Hot Standby | references/hot-standby.md | Streaming replication, read replicas, failover |
| Transaction Isolation | references/transaction-isolation.md | Isolation levels, lost updates, serialization failures, retry logic |
| Backup & Restore | references/backup-restore.md | pg_dump/pg_restore, pg_basebackup, PITR, recovery |
| Security & Roles | references/security-roles.md | Privileges, RLS, pg_hba.conf, authentication, SSL |
| Bulk Data Loading | references/bulk-loading.md | COPY patterns, ETL staging, optimizing large loads, batch ops |
| Connection Pooling | references/connection-pooling.md | PgBouncer config, pool modes, prepared statements, sizing |
| Major Version Upgrades | references/major-version-upgrades.md | pg_upgrade, logical replication migration, pre/post checklists |
Related skills
More from neondatabase/postgres-skills and the wider catalog.
netlify-access-control
Route Netlify site protection to the right layer: Password Protection, Private projects, or Team SSO.
netlify-agent-runner
Run AI coding agents remotely on Netlify to automate development tasks on your site.
netlify-ai-gateway
Call OpenAI, Anthropic, Google Gemini, TypeSafe, or OpenRouter models from Netlify Functions with injected credentials.
netlify-blobs
Store and retrieve unstructured objects, files, and cache-like state on Netlify with key/value and blob storage.
netlify-caching
Cache dynamic and static responses on Netlify's CDN with headers, cache tags, and programmatic APIs.
netlify-cli-and-deploy
Guide for using the Netlify CLI and deploying sites. Use when installing the CLI, linking sites, deploying (Git-based or manual), managing environment variables, or running local development. Covers netlify dev, netlify deploy, Git vs non-Git workflows, and environment variable management.