v1.0.0 to v1.0.0

---

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

**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)