Postgres

Postgres is a community skill for working with PostgreSQL databases, covering schema design, query optimization, indexing strategies, stored procedures, and administration tasks for relational database management.

What Is This?

Overview

Postgres provides patterns for designing, querying, and managing PostgreSQL databases in production environments. It covers table design with proper data types, constraints, and normalization for data integrity, query writing with joins, aggregations, window functions, and CTEs for complex data retrieval, index creation including B-tree, GIN, and GiST types for query performance, stored procedures and triggers for server-side logic, and backup and replication configuration for database reliability. The skill enables database engineers to build schemas and queries that balance correctness, performance, and maintainability for data-intensive applications.

Who Should Use This

This skill serves backend developers writing SQL queries and designing schemas for PostgreSQL-backed applications, DBAs managing PostgreSQL instances in production environments, and data engineers building ETL pipelines that read from or write to PostgreSQL.

Why Use It?

Problems It Solves

Designing normalized schemas that prevent data anomalies while supporting efficient query patterns requires understanding of relational modeling. Writing queries that perform well on large tables needs proper indexing and query plan analysis. Managing schema migrations without downtime demands careful ordering of DDL operations. Monitoring query performance and identifying slow queries requires configuration of logging and statistics.

Core Highlights

Schema designer creates tables with appropriate constraints, types, and relationships. Query optimizer analyzes EXPLAIN plans and recommends index strategies. Migration planner generates DDL statements that avoid locking issues on large tables. Performance monitor tracks slow queries and connection pool utilization.

How to Use It?

Basic Usage

-- Schema design with constraints
CREATE TABLE users (
    id BIGINT GENERATED ALWAYS
        AS IDENTITY PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    created_at TIMESTAMPTZ
        NOT NULL DEFAULT NOW()
);

CREATE TABLE orders (
    id BIGINT GENERATED ALWAYS
        AS IDENTITY PRIMARY KEY,
    user_id BIGINT NOT NULL
        REFERENCES users(id),
    total_cents BIGINT NOT NULL
        CHECK (total_cents >= 0),
    status VARCHAR(20) NOT NULL
        DEFAULT 'pending',
    created_at TIMESTAMPTZ
        NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_orders_user_id
    ON orders(user_id);
CREATE INDEX idx_orders_status_created
    ON orders(status, created_at DESC);

-- Window function query
SELECT user_id, total_cents,
    ROW_NUMBER() OVER (
        PARTITION BY user_id
        ORDER BY created_at DESC
    ) AS order_rank
FROM orders
WHERE status = 'completed';

Real-World Examples

-- CTE for revenue analysis
WITH monthly_revenue AS (
    SELECT DATE_TRUNC('month',
               created_at) AS month,
        SUM(total_cents) AS revenue,
        COUNT(*) AS order_count
    FROM orders
    WHERE status = 'completed'
    GROUP BY DATE_TRUNC('month',
                 created_at)
),
growth AS (
    SELECT month, revenue, order_count,
        LAG(revenue) OVER (
            ORDER BY month
        ) AS prev_revenue
    FROM monthly_revenue
)
SELECT month,
    revenue / 100.0 AS revenue_usd,
    order_count,
    ROUND(
        (revenue - prev_revenue)::NUMERIC
        / NULLIF(prev_revenue, 0) * 100,
        1
    ) AS growth_pct
FROM growth
ORDER BY month DESC
LIMIT 12;

-- Partial index for active records
CREATE INDEX idx_orders_pending
    ON orders(created_at)
    WHERE status = 'pending';

-- EXPLAIN ANALYZE for query tuning
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, COUNT(o.id) AS total
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.status = 'completed'
GROUP BY u.name
ORDER BY total DESC
LIMIT 10;

Advanced Tips

Use partial indexes to cover frequent query patterns on subsets of data, reducing index size and improving write performance. Run EXPLAIN ANALYZE with BUFFERS to identify whether queries are doing sequential scans that could benefit from indexes. Use pg_stat_statements to identify the most time-consuming queries across the entire workload.

When to Use It?

Use Cases

Build a multi-tenant SaaS schema with row-level security policies for data isolation between tenants. Create a reporting layer using materialized views that precompute complex aggregations on schedule. Implement a full-text search system using GIN indexes and tsvector columns for document retrieval.

Related Topics

Relational database design, SQL query optimization, database indexing, schema migrations, and database administration.

Important Notes

Requirements

PostgreSQL 14 or later for modern SQL features like generated columns and improved JSON support. A SQL client or driver library for the target programming language. Monitoring configuration for production query performance tracking.

Usage Recommendations

Do: use EXPLAIN ANALYZE to verify query plans before deploying new queries to production. Create indexes on foreign key columns used in joins and WHERE clauses. Use transactions for multi-step operations that must succeed or fail atomically.

Don't: add indexes on every column without considering write performance overhead and storage costs. Use SELECT * in production queries when only specific columns are needed. Run ALTER TABLE with long-running locks on busy tables without using CONCURRENTLY or staged migration approaches.

Limitations

Complex analytical queries on very large tables may require partitioning or specialized analytical databases. Connection management needs external pooling solutions like PgBouncer for high-concurrency applications. Some advanced features like logical replication have limitations with DDL changes and certain data types.