PostgreSQL Administration & SQL AI Skill Guide

Overview & Engine Architecture

PostgreSQL is a relational database with MVCC: updates create new row versions; dead tuples remain until VACUUM. Writers never block readers on row data, but DDL and some migrations take strong locks. Connection storms are a common production failure mode—apps should pool (PgBouncer, RDS Proxy, or app pool) and cap max_connections.

App / ORM
    -> PgBouncer (session vs transaction pool) OR app pool
        -> Postmaster
            -> backends (one per session)
            -> shared_buffers + WAL
            -> heap / TOAST / indexes (B-tree, GIN, GiST, BRIN)
            -> autovacuum + pg_stat_statements

When to use / when not to