PostgreSQL Database Development

Purpose: Production-ready PostgreSQL development for high-performance, scalable applications. Audience: Backend engineers and database administrators working with PostgreSQL. Standard: Follows github/awesome-copilot PostgreSQL patterns.

---

When to Use This Skill

• Writing PostgreSQL queries with JSONB or array operations
• Implementing full-text search in PostgreSQL
• Optimizing PostgreSQL query performance with indexes
• Using window functions and CTEs
• Configuring row-level security (RLS)

Prerequisites

• PostgreSQL 14+ installed or accessible
• psql or pgAdmin client

Quick Reference

Need	Solution	Pattern
JSONB query	Containment operator	WHERE data @> '{"status": "active"}'::jsonb
Array operations	ANY operator	WHERE id = ANY(ARRAY[1,2,3])
Full-text search	GIN index + tsvector	CREATE INDEX ON posts USING gin(to_tsvector('english', content))
Window functions	ROW_NUMBER, RANK	ROW_NUMBER() OVER (PARTITION BY category ORDER BY created_at DESC)
Upsert	INSERT ... ON CONFLICT	ON CONFLICT (id) DO UPDATE SET ...
JSON aggregation	jsonb_agg	SELECT jsonb_agg(row_to_json(t)) FROM ...

---

PostgreSQL Version

Current: PostgreSQL 16+ Minimum: PostgreSQL 14+

---

Common Pitfalls

Issue	Problem	Solution
N+1 queries	Loading relations one by one	Use JOINs or array_agg
Missing indexes	Slow queries	Add indexes on WHERE, JOIN, ORDER BY columns
OFFSET pagination	Slow for large offsets	Use cursor-based pagination
**SELECT ***	Unnecessary data transfer	Select only needed columns
Unparameterized queries	SQL injection risk	Always use parameterized queries
No connection pooling	Too many connections	Use pgBouncer or application pooling

---

Resources

• PostgreSQL Docs: postgresql.org/docs
• pgAdmin: GUI tool for PostgreSQL
• pg_stat_statements: Query performance monitoring
• EXPLAIN Visualizer: explain.depesz.com
• Awesome Copilot: github.com/github/awesome-copilot

---

See Also: Skills.md - AGENTS.md - Database Skill

Last Updated: January 27, 2026

Troubleshooting

Issue	Solution
Slow JSONB queries	Create GIN index on JSONB column
Full-text search not matching	Check tsvector configuration matches query language, rebuild indexes
Deadlock detected	Access tables in consistent order across transactions, keep transactions short

References

• Postgres Data Types
• Postgres Query Patterns
• Postgres Advanced