Blog
Mert Özen's blog posts on software development, backend, frontend, and DevOps.
Everyday Work with psql: Meta-Commands, \x, \timing, \copy, \watch, \gexec, .psqlrc, Schemas and search_path
In psql, COPY writes the file to the server disk and needs a special privilege; \copy does the same on the client machine with your privileges. The \d family, \x and \timing, generated SQL with \gset and \gexec, CSV with \copy, \watch, scripts with ON_ERROR_STOP, .psqlrc, schemas and the search_path trap, the public schema privilege change in PostgreSQL 15, and bringing a single table into a dev environment with pg_dump.
PostgreSQL Triggers: BEFORE/AFTER, Row and Statement Level, Transition Tables, updated_at and Audit Triggers, Event Triggers
In PostgreSQL, changing the row itself belongs in a BEFORE ROW trigger through NEW, writing to other tables in an AFTER trigger; AFTER ROW triggers run once the statement has finished. updated_at with a WHEN condition, a jsonb audit trigger, the cost of row and transition-table statement triggers on a 200,000-row UPDATE, INSTEAD OF for a join view, an event trigger that records DDL, and reading trigger values back in EF Core with RETURNING.
Functions and Procedures in PostgreSQL: SQL vs PL/pgSQL, RETURNS TABLE, Volatility, SECURITY DEFINER and Transaction Control with CALL
In PostgreSQL a function returns rows or values, is called with SELECT and cannot end the transaction; a procedure is called with CALL and can COMMIT inside. An unmarked function counts as VOLATILE and blocks index use. Inlining of LANGUAGE sql functions, RETURNS TABLE, PARALLEL SAFE, an ISBN check in PL/pgSQL used in a CHECK constraint, stock reservation, error handling with SQLSTATE codes, SECURITY DEFINER and calling it all from Npgsql and Dapper.
Views and Materialized Views in PostgreSQL: Updatable Views, security_invoker, REFRESH CONCURRENTLY and Scheduling with pg_cron
In PostgreSQL a materialized view stores its result on disk and changes only with REFRESH; a plain REFRESH blocks reads, CONCURRENTLY does not but needs a unique index. The SELECT * and column change limits of views, automatically updatable views and WITH CHECK OPTION, what security_invoker changes on a table with RLS, a refresh every 10 minutes with pg_cron, keyless entity mapping in EF Core, and when to use a separate read model table.
PostgreSQL Aggregate, Array and Conditional Functions: FILTER, Ordered string_agg/array_agg, unnest, ANY/ALL, COALESCE and GREATEST
In PostgreSQL, FILTER (WHERE ...) gives each aggregate its own condition, and with no rows every aggregate except count returns NULL, not an empty array. Ordered string_agg, array_agg and jsonb_agg, median and percentiles, unnest and array operators, a GIN index that works with @> but not with = ANY, an IN list that is really = ANY, the NULL trap of NOT IN, and how GREATEST treats NULL differently across dialects.
Dates, Times and Intervals in PostgreSQL: now() vs clock_timestamp(), date_trunc, extract, AT TIME ZONE, age, date_bin and generate_series
In PostgreSQL, now() returns the moment the transaction started and does not change during the transaction; clock_timestamp() gives the real clock. Both directions of AT TIME ZONE, reports by Istanbul day, month and daylight saving rules, age and justify_interval, extract, time buckets with date_trunc and date_bin, filling empty days with generate_series, and why a WHERE with date_trunc cannot use an index, with SQL Server equivalents.
PostgreSQL String, Regex and Formatting Functions: concat_ws, format(), split_part, regexp_replace, to_char and ICU Collations
In PostgreSQL, || loses the whole string because of one NULL, while concat_ws skips it; in dynamic SQL, values go in with USING and table or column names with format('%I'). split_part, substring, translate, the regexp_* functions, Turkish number formats with to_char/to_number, initcap and ILIKE with the ICU tr-TR collation, text_pattern_ops for LIKE prefix searches and pg_trgm for ILIKE '%x%', with ISBN validation and slug generation on Bookstore.
Managing Schema Changes: SQL Database Projects and DACPAC (State-Based) vs DbUp and Flyway (Migration-Based)
A state-based tool doesn't know about renames: after a column was renamed in the project, SqlPackage tried to drop and re-add it and was stopped only by BlockOnPossibleDataLoss; with a refactorlog it generated sp_rename. DACPACs with Microsoft.Build.Sql 2.3.0, deploy scripts and DeployReport, a hand-added index dropped by default, a CHECK definition that causes a false diff, and a changed migration DbUp misses and Flyway catches by checksum.
Moving Data in SQL Server: BULK INSERT, bcp, OPENROWSET, BACPAC Export/Import and Copying Databases
MAXERRORS in BULK INSERT doesn't skip every error: a single CSV row with the wrong column count stopped a 200,000-row load at zero. The file path resolved on the server, CODEPAGE not supported on Linux, the CR stuck to the last column of a CRLF file, staging and validation, log down from 43 MB to 0.5 MB with BULK_LOGGED and TABLOCK, OPENROWSET and bcp, and BACPAC export/import with SqlPackage from an anonymized copy.
SQL Server Agent: Jobs, Steps, Schedules, Alerts, Operators and Database Mail; Agent on Linux and in Containers
In SQL Server on Docker, Agent is off by default: the job definition is written to msdb, but sp_start_job says 'SQLServerAgent is not currently running' and nothing runs. An idempotent report procedure, a job script kept in source control, retry and notification settings, job owners and msdb roles, alerts that aren't supported on Linux, Database Mail XPs, and the line between an Agent job and a background job in the application.
SQL Server Maintenance: Index Fragmentation, Rebuild vs Reorganize, Statistics Updates, DBCC CHECKDB and Ola Hallengren
In index maintenance what matters is page density and cost, not fragmentation: on a 39% fragmented index REORGANIZE produced 3.6 times and an ONLINE rebuild 7 times the log of an offline REBUILD, and UPDATE STATISTICS after a rebuild replaced full-scan statistics with an 11% sample. Repairing a corrupt nonclustered index with DBCC CHECKDB without data loss, and Ola Hallengren's IndexOptimize, DatabaseIntegrityCheck and DatabaseBackup procedures.
Backup and Restore in SQL Server: Recovery Models, Full/Differential/Log Backups, Point-in-Time Restore and Transaction Log Growth
In FULL recovery only a log backup empties the log: after two full backups the log was still 19.6% full and waiting on LOG_BACKUP, and a 400 MB log finally raised Msg 9002. The full, differential and log chain, how a backup without COPY_ONLY breaks the differential (Msg 3136), ZSTD compression, bringing back 618 accidentally deleted orders from a side database with STOPAT and STOPBEFOREMARK, and untrusted .bak files.