Use jsonb. It gets containment (@>), key-existence (?), path queries, equality, and GIN indexing; json has none of those and exists to store your input byte-for-byte. Pick json only when exact text matters — key order, duplicate keys, whitespace. The PostgreSQL docs say the same: prefer jsonb unless you have “legacy assumptions about ordering of … Read more
Use PgBouncer when the problem is “too many connections”: it is a single small process that multiplexes thousands of client connections onto a handful of server ones. Use Pgpool-II when you need a routing layer in front of several PostgreSQL servers — read load balancing, automatic failover — and accept operating something closer to a … Read more
In MySQL 8.0.20 and later, join_buffer_size sizes the hash join buffer. It is not a dial you turn for a smooth percentage gain — on the workload measured below it did nothing until the buffer was large enough for the hash table to stop spilling to disk, then it stopped mattering again. Find your own … Read more
Create a TEMPORARY table with CREATE TEMPORARY TABLE name (…) — it’s visible only in the session that created it, disappears when that session ends, and can share a name with a permanent table without an error (it shadows it, which is the part most guides skip). Below is real output from MySQL 8.4.10 for … Read more
A MySQL deadlock is two transactions each holding a row lock the other one needs next — InnoDB’s deadlock detector picks one, kills it with error 1213, and the other proceeds. Below is a real one, caused on purpose on a live MySQL 8.4.10 server: two connections locking the same two rows in opposite order, … Read more
Keep the MySQL root password off the docker run command line: instead of -e MYSQL_ROOT_PASSWORD=…, set MYSQL_ROOT_PASSWORD_FILE to the path of a Docker secret or a bind-mounted file. The official mysql image’s initialization script loads the password from that file (Docker Hub’s text is quoted below). Honesty first: this machine has no Docker installed, and … Read more
A mysqldump –all-databases into a dated directory every night is a full backup, not an incremental one. And applying an incremental by piping mysqlbinlog output into mysql database_name silently does not restore it. Both are reproduced below on MySQL 8.4.10. An incremental backup in MySQL is a slice of the binary log, and it is … Read more
MySQL 8.4 already encrypts in transit: it auto-generates a CA and server certificate in the data directory the first time it starts, and a stock 8.4.10 negotiates a real TLSv1.3 connection with zero configuration. The hardening step that is not the default is require_secure_transport=ON — until you set it, plaintext connections still succeed. Generate your … Read more
Try a bigger box (vertical scaling) first. Partition next if the problem is one huge table with one slow query shape, and add replicas if it is read volume rather than table size. Shard last, and only once you’ve measured that a single server’s write throughput, not query shape, is the wall. Sizing a bigger … Read more
Use InnoDB unless you have a specific, tested reason not to — it’s the default engine in MySQL 8.4, it’s the only one of the two with transactions, row-level locking, foreign keys, and crash recovery, and since MySQL 5.6 it also does full-text search, which used to be MyISAM’s one exclusive feature. The sections below … Read more
An index doesn’t make a query faster in the abstract — it changes the plan MySQL chooses, and EXPLAIN ANALYZE proves it. On a 500,000-row table, filtering on an unindexed column costs 50000 and takes ~100ms; the same filter after an index costs 875 and takes ~3ms. Below is that comparison unedited, plus a note … Read more
MySQL 8.4 already ships the monitoring most people install a tool for. performance_schema and the sys schema are on by default and answer “what’s slow” and “what’s using memory” without installing anything. What follows is real output from this machine’s server. What’s actually running, right now The single most useful query for “what is my … Read more
“performance tuning” (buffer pool sizing, query indexing) is scaling up — a bigger, better-tuned single server. This page is about scaling out: adding replicas so more than one server can serve reads. If you have not yet decided which of those you need, the choice breaks into three independent decisions, and read volume is only … Read more
PREPARE saves PostgreSQL from re-parsing and re-planning a query on every execution — but past the fifth call it may silently swap in a generic, parameter-unaware plan instead of continuing to plan around your actual values, controlled by plan_cache_mode. That switch is the real performance story here, and it’s demonstrated below with unedited EXPLAIN output … Read more
A materialized view stores the result of a query on disk. You query it like a table, and it stays stale until you run REFRESH MATERIALIZED VIEW: CREATE MATERIALIZED VIEW daily_sales AS SELECT date_trunc(‘day’, created_at) AS day, count(*) AS orders, sum(amount) AS revenue FROM orders GROUP BY 1; REFRESH MATERIALIZED VIEW daily_sales; Two things decide … Read more
MySQL’s own manual gives one sizing rule: “On a dedicated database server, you might set the buffer pool size to 80% of the machine’s physical memory size.” If the server was started with –innodb-dedicated-server, you don’t even set it — MySQL calculates it for you. Everything below is either a command run against a real … Read more
A PostgreSQL procedure is created with CREATE PROCEDURE, invoked with CALL, and may run COMMIT and ROLLBACK inside its own body. A function cannot. That is the whole reason procedures exist, and it is the one thing a function can never be rewritten to do: CREATE FUNCTION f_commit() RETURNS void LANGUAGE plpgsql AS $$ BEGIN … Read more
An LVM snapshot is not a backup, and calling it “version control” is the part of this page most likely to cost you data. A classic (non-thin) LVM snapshot lives in the same volume group as the volume it snapshots, shares its fate if that volume group’s storage fails, and — this is the part … Read more
ls -lAh is the listing you want most of the time — long format, hidden entries included, sizes in readable units. Add -t to sort newest first, -S for largest first, and -r to reverse either. The rest covers where ls reports something other than it appears to. Every block below is unedited output from … Read more
Since PostgreSQL 12, a plain, non-recursive, side-effect-free WITH CTE referenced once is inlined into the surrounding query by default — the planner treats it the same as an equivalent subquery, filters can push down into it, and it produces an identical plan. Before 12, a CTE was always an optimization fence: computed once, in isolation, … Read more
For a nightly incremental backup, use this: rsync -a –delete –link-dest=/backup/previous /source/directory/ /backup/2026-08-09/ Please read this first. Do not build a backup on –ignore-existing: rsync -av –ignore-existing /source/directory/ /backup/directory/ skips updating files that already exist at the destination, so modified files are never transferred. If you built a nightly job from that command, your backup … Read more
Choose on authentication, not throughput. Samba joined to a domain authenticates the user — Kerberos or NTLMv2, per connection. NFS with sec=sys, the default, authenticates the host, and only by source port. If your clients are machines you fully control on a segment you fully control, NFS is simpler and defensible; for anything else, use … Read more
Partitioning splits one big table into smaller pieces on one PostgreSQL server; sharding splits data across multiple servers. Partitioning is native — CREATE TABLE … PARTITION BY ships in core PostgreSQL. Sharding is not: core PostgreSQL has no built-in sharding, full stop. Getting there means an extension (Citus, not installed here) or building it yourself … Read more
The working form first, since this page is otherwise all warnings. Use an explicit –format with a separator that cannot appear in the event field, with the path last, and parse it with IFS= and read -r so filenames survive intact: inotifywait -m -r -e create,modify,delete,move –format ‘%e|%w%f’ /path | while IFS=’|’ read -r event … Read more
cp -r and cp -R are not the same command on macOS, even though they are on Linux. That single divergence, confirmed below on both a BSD and a GNU cp, is the main thing worth knowing that a generic cheat sheet won’t tell you. -r vs -R: identical on Linux, different on macOS Both … Read more