JSON
Turning a Deeply Nested API Response Into Three Tables
Split a nested order response into customers, orders and order_items in PostgreSQL with keys and constraints, and see what the nested address belongs to.
7 min read
Library
Everything you need to level up your coding skills.
JSON
Flattening JSON in SQL With Lateral Joins in PostgreSQL
Flatten JSON arrays into rows in PostgreSQL with CROSS JOIN LATERAL, jsonb_to_recordset and WITH ORDINALITY, keep empty arrays, and compare MySQL and SQLite.
8 min read
PostgreSQL
Indexes and Primary Keys on Partitioned Tables in PostgreSQL
Indexes and primary keys on partitioned tables in PostgreSQL: the partition key rule, local indexes, ON ONLY builds, foreign keys and lookup patterns.
7 min read
JSON
Checking a JSON Column Against a Schema in PostgreSQL
Enforce the shape of a PostgreSQL jsonb column with CHECK constraints, NOT VALID and jsonpath, including the NULL trap that lets a string id pass.
8 min read
CSV
Converting CSV to SQL INSERT Statements the Safe Way
Generate INSERT statements from a CSV without breaking on O'Neil or running a DROP TABLE hidden in a value. Batches of 1,000 loaded 193,708 rows in 1.6 s.
13 min read
PostgreSQL
Tuning PostgreSQL for SSDs With random_page_cost and More
Tune PostgreSQL for SSDs: lower random_page_cost, set effective_io_concurrency, check changed plans, use per tablespace values and keep scans that win.
7 min read
CSV
Automating a Nightly CSV Export With cron and psql
A cron export that cannot publish a half-written file: lock, temporary name, row-count check, atomic rename. Real cron failed first, on the environment.
10 min read
CSV
Exporting CSV From the Database Straight to a Gzip File
Pipe a PostgreSQL export into gzip or zstd: 130 MB became 42 MB in 4.6 s, with six settings compared and the pipeline that exits 0 after a failed export.
8 min read
CSV
Writing Dates and Booleans to CSV That Others Can Read
One timestamp exported as 09:30+00, 16:30+07 and 04:30-05, and dd.mm.yyyy that pandas read as 3 January. Fix dates and booleans in the query, not the session.
9 min read
CSV
Exporting Large Tables to CSV Without Running Out of Memory
Export a 2,000,000 row table to CSV six ways: peak memory ranged from 11 MB to 2 GB, time from 1.6 s to 14 s. fetchall and pandas are the ones that run out.
9 min read
PostgreSQL
Comparing PostgreSQL Plans Before and After a Change
Compare PostgreSQL query plans before and after a change: capture both, control cache and noise, line up nodes and decide if the gain is real or warm cache.
7 min read
CSV
Exporting a PostgreSQL Query to CSV With psql and COPY
Export a PostgreSQL query to CSV with psql: NULL against empty string, delimiter and quoting options, encodings, why variables fail and the file left behind.
9 min read
Get new articles by email
We send one email when a new article is published. No spam.