Blog
Mert Özen's blog posts on software development, backend, frontend, and DevOps.
What Is PostgreSQL? Architecture for SQL Server Developers: Processes, Shared Buffers, WAL and MVCC
PostgreSQL starts a separate operating system process for every connection, leaves most memory to the operating system cache and keeps old row versions inside the table rather than in a separate version store; most differences a SQL Server developer meets come from this. With real output: the postmaster and backends, the 53300 error after 97 connections, connection pooling, shared_buffers, WAL and checkpoints, MVCC through xmin/xmax, a concept map, licensing and the release schedule.
MVCC, VACUUM and Autovacuum: Dead Tuples, Bloat, HOT Updates and Transaction ID Wraparound
In PostgreSQL an UPDATE doesn't change a row in place but writes a new version; VACUUM removes the old one but doesn't shrink the file, it makes the space reusable. A single UPDATE that doubles a 40,000-row table, the lock of VACUUM FULL that stops even reads, 100% HOT updates with fillfactor 80, per-table autovacuum thresholds, an open transaction stopping VACUUM, and freezing against transaction ID wraparound.