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.
Transactions, Isolation and Locking in PostgreSQL: Serializable SSI, SELECT FOR UPDATE SKIP LOCKED and Advisory Locks
In PostgreSQL a reader never waits for a writer at any isolation level; REPEATABLE READ and SERIALIZABLE resolve conflicts with a 40001 error rather than locks, so a retry is mandatory. Lost update and write skew experiments, FOR NO KEY UPDATE instead of a FOR UPDATE that blocks foreign keys, a four-worker outbox relay with SKIP LOCKED, advisory locks, deadlocks, the lock queue behind ALTER TABLE and retries with the EF Core execution strategy.
PostgreSQL with Npgsql and Dapper: NpgsqlDataSource, Type Mapping, COPY and LISTEN/NOTIFY
From .NET you connect to PostgreSQL through a single NpgsqlDataSource for the whole application; only UTC values can be written to timestamptz, yet Dapper can silently shift a DateTime with an unspecified Kind by three hours. DI and pool defaults, = ANY(@ids), DateOnly and jsonb, Dapper sending enums as integers, Execute, NpgsqlBatch and binary COPY on 100,000 rows, automatic preparation, cache invalidation with LISTEN/NOTIFY and OpenTelemetry.
EF Core with PostgreSQL: the Npgsql Provider, snake_case, Array/JSON Mapping, Enums, xmin Concurrency and Migration Differences
Moving EF Core to PostgreSQL takes more than UseNpgsql: comparisons are case-sensitive, and an == search that works on SQL Server silently returns nothing. snake_case with EFCore.NamingConventions, text[] and the @> translation, complex types mapped to jsonb and jsonb_set through ExecuteUpdate, PostgreSQL enums, uuid v7, xmin as the concurrency token, a generated tsvector, and CREATE INDEX CONCURRENTLY pushing a migration outside the transaction.
Security and Operations Basics in PostgreSQL: Roles, GRANT, pg_hba, SCRAM, Backup and Major Upgrades
Security in PostgreSQL starts with three roles: the schema owner, an application that only runs DML and a superuser for maintenance; default privileges apply only to objects created by the role they name. GRANT, pg_read_all_data, RLS, pg_hba.conf, SCRAM, SSL Mode=VerifyFull in Npgsql, restore testing with pg_dump/pg_restore, PITR with a WAL archive, and pg_upgrade with 18's checksum and statistics changes.