Blog
Mert Özen's blog posts on software development, backend, frontend, and DevOps.
What's New in PostgreSQL 18: Asynchronous I/O, Skip Scan, uuidv7, Virtual Generated Columns, RETURNING OLD/NEW and Temporal Constraints
In PostgreSQL 18 generated columns are virtual by default: adding one to a million-row table no longer rewrites it as on 17, but it cannot be indexed either. Asynchronous I/O with io_method, skip scan, EXPLAIN with BUFFERS by default, uuidv7(), RETURNING OLD/NEW, WITHOUT OVERLAPS, data checksums by default, OAuth and logical replication changes, each next to its 17 equivalent.
Reading Older PostgreSQL Projects: Key Changes from 13 to 17
What breaks a project moving from 14 to 17 is not the new features but what was removed or moved: pg_start_backup went in 15, CREATE on the public schema in 15, and the checkpoint columns of pg_stat_bgwriter in 17. The same SQL file run on 13 to 17 shows when multiranges, MERGE, SQL/JSON, JSON_TABLE and the builtin collation arrived, how pg_upgrade carries the public privilege over, and why leaving the unsupported 14 comes first.
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.
PostgreSQL Extensions: pg_stat_statements, pg_trgm, pgcrypto, citext, unaccent and Extensions on Managed Services
Who can install an extension is decided by the trusted/untrusted split, and when it works by shared_preload_libraries: the database owner can install citext, pg_buffercache needs a superuser, and pg_stat_statements does not work until it is preloaded. Version updates, the pitfalls of citext and pgcrypto, pg_cron, TimescaleDB and Citus licensing, allow lists on Azure and RDS, and a missing library that stops pg_upgrade.
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.
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.
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.
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.
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.
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.
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.
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.