---
name: "postgresql"
description: 'Develop PostgreSQL databases with JSONB, arrays, full-text search, and performance optimization. Use when writing PostgreSQL queries, using JSONB operations, implementing full-text search, optimizing query performance with indexes, or configuring row-level security.'
metadata:
 author: "AgentX"
 version: "1.0.0"
 created: "2025-01-15"
 updated: "2025-01-15"
compatibility:
 languages: ["sql", "plpgsql"]
 platforms: ["windows", "linux", "macos"]
---

# 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](https://github.com/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)

## Decision Tree

```
PostgreSQL Decision
+-- Need semi-structured data?
|   +-- Dynamic schema? -> JSONB columns with GIN index
|   +-- Fixed schema? -> Regular columns with B-tree indexes
+-- Need full-text search?
|   +-- Simple keyword match? -> ILIKE or trigram (pg_trgm)
|   +-- Ranked relevance? -> tsvector + GIN index
+-- Pagination needed?
|   +-- Small dataset (<10K rows)? -> LIMIT/OFFSET acceptable
|   +-- Large dataset? -> Cursor-based (keyset) pagination
+-- Multi-tenant data?
|   +-- Row isolation? -> Row-Level Security (RLS) policies
|   +-- Schema isolation? -> Separate schemas per tenant
+-- Aggregation heavy? -> Materialized views with periodic refresh
```

## 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 18+
**Minimum**: PostgreSQL 14+

---

## Core Rules

1. **Use Parameterized Queries** - Always use `$1, $2` placeholders; never concatenate user input into SQL strings
2. **Index WHERE and JOIN Columns** - Add indexes on columns used in WHERE, JOIN, and ORDER BY clauses
3. **Prefer Specific Columns** - Select only needed columns; avoid `SELECT *` in application queries
4. **Use Connection Pooling** - Use pgBouncer or application-level pooling; never open unlimited connections
5. **EXPLAIN Before Optimizing** - Run `EXPLAIN ANALYZE` to understand query plans before adding indexes or rewriting
6. **Use CTEs for Readability** - Use Common Table Expressions for complex queries; prefer `WITH` over deeply nested subqueries
7. **Keep Transactions Short** - Minimize lock duration by keeping transactions as brief as possible
8. **Use JSONB Over JSON** - Prefer `jsonb` type for storage and querying; `json` is only for write-once scenarios

---

## Anti-Patterns

| 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](https://www.postgresql.org/docs/)
- **pgAdmin**: GUI tool for PostgreSQL
- **pg_stat_statements**: Query performance monitoring
- **EXPLAIN Visualizer**: [explain.depesz.com](https://explain.depesz.com)
- **Awesome Copilot**: [github.com/github/awesome-copilot](https://github.com/github/awesome-copilot)

---

**See Also**: [Skills.md](../../../../Skills.md) - [AGENTS.md](../../../../AGENTS.md) - [Database Skill](../../architecture/database/SKILL.md)

**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](references/postgres-data-types.md)
- [Postgres Query Patterns](references/postgres-query-patterns.md)
- [Postgres Advanced](references/postgres-advanced.md)