Database Management Services

MVCC (Multi-Version Concurrency Control) is PostgreSQL's mechanism for handling concurrent transactions without locking: instead of overwriting a row when updated, PostgreSQL creates a new version of the row alongside the old version. Readers see the version current when their transaction started. This enables readers and writers to never block each other. The consequence: deleted and updated rows are not immediately removed the old versions remain as 'dead tuples' until VACUUM reclaims them. Without regular vacuuming: tables accumulate dead tuples (more disk pages to scan), queries become slower (more I/O to scan pages with dead tuples), index bloat occurs (index entries for dead tuples remain), and most critically PostgreSQL's 32-bit transaction ID counter wraps around after approximately 2 billion transactions, causing transaction ID wraparound (a very serious condition preventing all new writes until resolved). PostgreSQL's autovacuum daemon handles this automatically for most tables, but must be configured appropriately for high-write tables.

PostgreSQL handles each client connection as a separate operating system process each connection consumes approximately 5-10MB of memory. RDS PostgreSQL instances have a maximum connection limit based on instance size (db.t3.medium: 170 connections; db.r5.large: 1,373 connections). Modern application architectures create many short-lived connections serverless functions, microservices with multiple instances, and ORMs with connection pools all contribute to connection count. Without connection pooling, a modestly-sized application (20 ECS tasks, each with a Prisma connection pool of 10) creates 200 database connections perfectly feasible to exhaust the limit. PgBouncer solves this by multiplexing thousands of application connections over a small number of actual database connections (a PgBouncer pool of 20 connections can serve hundreds of application connections in transaction pooling mode). RDS Proxy is AWS's managed alternative to PgBouncer particularly valuable for Lambda functions where per-invocation connection creation would otherwise overwhelm PostgreSQL.