Postgresql Optimization

PostgreSQL optimization

Optimize ${selection} or the current project by applying PostgreSQL-specific data types, operators, indexes, query plans, extensions, and maintenance practices, then return paste-ready SQL and evidence-backed recommendations.

When to invoke

• "Optimize this PostgreSQL query."
• "Design indexes for these JSONB and array filters."
• "Review this EXPLAIN ANALYZE output."
• "Use PostgreSQL full-text search or range types here."
• "Improve our pg_stat_statements slow queries."

PostgreSQL feature map

Query and index patterns

Performance workflow

1. Capture the slow SQL and its parameters; never concatenate user input into SQL.
2. Run EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) on representative data.
3. Inspect sequential scans on large tables, join algorithms, sort and aggregate nodes, row estimate errors, filter selectivity, and buffer reads.
4. Check pg_stat_statements for query, calls, total_time, mean_time, rows, and cache hit percentage:

SELECT query, calls, total_time, mean_time, rows,
       100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

5. Propose the smallest safe change: rewrite SQL, add CREATE INDEX, add constraints, adjust schema types, or schedule maintenance.
6. Validate with a before/after plan, expected trade-offs, and rollback-safe SQL.

Schema and data type guidance

CREATE TABLE events (
    id SERIAL PRIMARY KEY,
    data JSONB NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_events_data_gin ON events USING gin(data);

CREATE TYPE order_status AS ENUM ('pending', 'processing', 'shipped', 'delivered', 'cancelled');
CREATE DOMAIN email_address AS TEXT
CHECK (VALUE ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    email email_address NOT NULL,
    status order_status DEFAULT 'pending'
);

Use TIMESTAMPTZ for instants, TEXT when length is not a business rule, CITEXT or an expression index for case-insensitive text, domains for reusable validation, and declarative partitioning for large time- or tenant-partitioned tables.

Monitoring and maintenance

Security rules

• Use parameterized queries exclusively; placeholders such as $last_id are acceptable, string concatenation is not.
• Implement proper access controls and row-level security where needed.
• Audit sensitive data access and use secure connection methods.
• Prefer pgcrypto for database-side cryptographic functions only when the architecture justifies it; avoid storing plaintext secrets.

Technical index

Preserve these PostgreSQL identifiers, plan terms, and example names when producing SQL: ALTER, CONSTRAINT, COUNT, DISTINCT, EXISTS, EXTENSION, EXTRACT, GOOD, INTEGER, JOIN, LEFT, OLAP, OLTP, PARTITION, RECURSIVE, UNION, UPDATE, UUID, Lag/Lead, address_type, category_tree, parent_id, postal_code, price_range, product_id, sale_date, search_vector, service_area, running_total, moving_avg, monthly_rank, prev_amount, order_count, idx_active_users, idx_documents_search, idx_locations_coords, idx_table_column, idx_tup_read, idx_tup_fetch, no_overlap, uuid_generate_v4, high-concurrency, and multi-column.

Output template

## Query Performance Analysis

**Status:** optimized | needs data | blocked
**Original Query:**
```sql
<original SQL>

Evidence:

• Plan reviewed: EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
• Main issue: <sequential scan, missing index, join order, sort, row estimate, lock, or maintenance issue>

Issues Identified:

• <issue with cost, rows, buffers, or table size evidence>

Optimized Query:

<improved SQL>

Recommended Indexes / Schema Changes:

CREATE INDEX <index_name> ON <table>(<columns>);

Performance Impact: Validation: <before/after command or reason it could not be run>

## Quality gate

- [ ] Every SQL change is PostgreSQL-specific where PostgreSQL features add value.
- [ ] Expensive queries are backed by `EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)` or a stated blocker.
- [ ] Index recommendations name the index type, columns/order, predicate, and write/storage trade-off.
- [ ] JSONB, arrays, full-text search, ranges, geometric types, custom types, and extensions are used only when they fit the data model.
- [ ] Security guidance uses parameterized queries and avoids SQL injection.
- [ ] Maintenance recommendations cover `VACUUM`, `ANALYZE`, `pg_stat_statements`, connection usage, or log review when relevant.
- [ ] Output includes paste-ready SQL and does not claim measured speedups without evidence.