CSVGuide
Exporting Query Results to CSV From Five Major Databases
The same eight awkward rows exported from PostgreSQL, MySQL, SQLite, DuckDB and MongoDB, then read back: four tools return all 8, one recipe returns 4.
25 min read
Topic
PostgreSQL
The database that keeps saying yes: JSONB, window functions, extensions, and the tricks that make it run like it has more cores than it does.
JSON
Streaming JSON Lines Into PostgreSQL Using COPY and psql
Load a JSON Lines file into a PostgreSQL jsonb column with COPY. The backslash trap in text format, the csv-format fix, bad-line errors and timings.
8 min read
PostgreSQL
Reading BUFFERS Output in EXPLAIN for PostgreSQL Queries
Read the BUFFERS output in PostgreSQL EXPLAIN: shared hit, read, dirtied and written, temp blocks, I/O timing, and what the numbers say about cache and disk.
7 min read
XML
Indexing XML Columns in PostgreSQL Without Wrecking Inserts
An expression index on xpath turned a 245 ms scan into 1 ms on 50,000 documents and made loading 2.8 times slower. A generated column does the same.
6 min read
XML
Streaming XML Into PostgreSQL Without a DOM Using COPY
Parse a 127 MB XML file with iterparse and feed two tables through COPY. 400,000 orders loaded in 6.5 seconds and 44 MB, against 38 seconds for row inserts.
6 min read
JSON
Choosing Between a JSON Column and a Document Database
When a JSON column in PostgreSQL, MySQL or SQLite is enough, and when a document database such as MongoDB earns its place, with measured numbers.
8 min read
PostgreSQL
Why the Planner Estimates the Wrong Number of Rows
Why the PostgreSQL planner estimates the wrong number of rows: stale statistics, skew, correlated columns, expressions and parameters, with the fix for each.
7 min read
PostgreSQL
Nested Loop Hash Join and Merge Join Compared in Plans
Nested loop, hash join and merge join compared in PostgreSQL: how each works, cost shape, memory and batches, plan clues and fixes when the choice is wrong.
7 min read
YAML
Using DuckDB to Query Data Stored as YAML With read_yaml
Install DuckDB's yaml community extension and query YAML files with read_yaml: inferred types, nested structs, unnest, globs, mixed types and broken files.
6 min read
CSV
Streaming a Huge CSV Into a Database From Node the Fast Way
Load 2,000,000 CSV rows into PostgreSQL and SQLite from Node and compare per-row inserts, batches, transactions and COPY, with timings and failure behavior.
10 min read
YAML
Flattening Nested YAML Into Rows of Path and Value
Turn a nested YAML document into path, type and value rows with a short recursive generator, then load the rows into SQL to search and compare configs.
6 min read
YAML
Reading YAML Into SQLite for Quick Queries With json_extract
Load a YAML list into an in-memory SQLite table as JSON text and query it with json_extract and json_each, with a json_valid check that rejects bad rows.
6 min read
Get new articles by email
We send one email when a new article is published. No spam.