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.
PostgreSQL Lab: PostgreSQL 18 in Docker, psql, pgAdmin and Porting Bookstore
In the PostgreSQL 18 image the volume is now mounted at /var/lib/postgresql and the data lives in an 18/docker subdirectory; with data at the old /data path the image refuses to start. A permanent Bookstore lab: a named volume, separate owner, app and superuser roles, ALTER DEFAULT PRIVILEGES, a ported schema with snake_case, IDENTITY and one million orders, psql meta-commands and psqlrc, pg_hba.conf, reload versus restart with ALTER SYSTEM, and the quoted "Books" table trap from EF Core.
PostgreSQL Data Types: text, numeric, timestamptz, uuid, IDENTITY, Enums, Arrays and Ranges
timestamptz does not store a time zone; it converts the value to UTC, keeps it as an instant and displays it in the session's TimeZone, which is why every instant in Bookstore is timestamptz. With real output: text versus varchar(n), cent errors and numeric, IDENTITY instead of serial, uuidv7 filling B-tree pages to 90%, enum values that can't be dropped, arrays and daterange, Turkish İ/ı and sorting with ICU, nondeterministic collations and casefold().
PostgreSQL-Specific SQL and Constraints: RETURNING, ON CONFLICT, DISTINCT ON, Writable CTEs, EXCLUDE and Generated Columns
An upsert in PostgreSQL is a single statement: INSERT ... ON CONFLICT inserts or updates without unique violations even under concurrency, and 18's RETURNING OLD/NEW returns old and new values together. Bookstore carts and stock reservations, MERGE, DISTINCT ON, LATERAL, generate_series and FILTER, a writable CTE that archives and deletes in one statement, deferrable foreign keys, non-overlapping campaigns with EXCLUDE, 18's temporal keys, NOT NULL NOT VALID and virtual generated columns.
JSONB in PostgreSQL: Operators, SQL/JSON Functions, GIN Indexes and When Not to Use It
JSONB parses JSON once on write and stores it in a binary structure; it doesn't save disk, but it can be queried with @>, ? and jsonpath and indexed with GIN. On Bookstore's books.attributes column: json versus jsonb, operators, jsonb_set, 17's JSON_VALUE and JSON_TABLE, which queries jsonb_ops and jsonb_path_ops can serve, expression indexes, the TOAST trap where one counter costs 175 kB of WAL, bad estimates and EAV in JSON form.
PostgreSQL Index Types: B-tree, Hash, GIN, GiST, SP-GiST, BRIN and Partial/Expression Indexes
In PostgreSQL the shape of the query, not the column type, decides the index type: B-tree for equality and ranges, GIN for arrays, JSONB and ILIKE '%...%', GiST for overlaps, BRIN for tables that grow over time. A query that reads 26 pages instead of 10,328 with 18's skip scan, deduplication, INCLUDE and index-only scans, a 24 kB BRIN instead of a 21 MB B-tree, hash, partial and expression indexes, an INVALID index left by CONCURRENTLY and an index that breaks HOT updates.
Reading EXPLAIN in PostgreSQL: ANALYZE, BUFFERS, Scan and Join Nodes, Statistics and pg_stat_statements
When you read a PostgreSQL plan, you first compare each node's estimated row count with the actual value from EXPLAIN ANALYZE; the planner's decisions rest on that estimate. Calculating cost by hand, BUFFERS on by default in 18, loops and fractional rows, nested loop, hash and merge joins, CREATE STATISTICS fixing an estimate off by 13 times, pg_stat_statements, GENERIC_PLAN and auto_explain catching an EF Core query's plan with its parameters.
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.
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 Full-Text Search: tsvector, tsquery, the Turkish Configuration, unaccent and pg_trgm
For Turkish search in PostgreSQL the turkish configuration is a reasonable start, but its stemmer isn't perfect: "kasabalar" can be found while "kasaba" isn't; prefix search, unaccent and pg_trgm close those gaps. tsvector/tsquery, websearch_to_tsquery, full-text search using the collation provider in 18 and the dotted/dotless i, a weighted STORED generated column with GIN, ts_rank, ts_headline, typo tolerance and when to move to Elasticsearch.
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.