Blog
Mert Özen's blog posts on software development, backend, frontend, and DevOps.
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.
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.
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.
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.