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
Short answer: 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 … Read more
Honesty first: this machine has no Docker installed, and none was installed to research this page. Every claim below about Docker or the official mysql image is a direct quote from Docker’s documentation or the image’s Docker Hub page, marked CITED. Every claim about MySQL server settings was actually checked against a live MySQL 8.4.10 … Read more
The script this page used to publish was not an incremental backup. It ran mysqldump –all-databases into a dated directory — a full dump, every night, with the word “incremental” over it. And the restore procedure it gave silently does not restore. Both were reproduced below on MySQL 8.4.10. An incremental backup in MySQL 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
Short answer: “vertical vs. horizontal” for MySQL isn’t really one decision — it’s three separate ones this corpus already answers individually. Sizing a bigger box is answered, measured, in the buffer pool guide. Read scaling via replicas is answered, with a real replication setup and a measured lag number, in the MySQL scale-out page. What’s … 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
Short answer: 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 … Read more
Short answer: 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 — a listicle of vendor tools isn’t, and the old version of … Read more
Short answer: “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 … 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
Short answer: MySQL’s own manual gives one sizing rule, not four: “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 … 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 WITH CTE 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, before the outer query … Read more
For a nightly incremental backup, use this: rsync -a –delete –link-dest=/backup/previous /source/directory/ /backup/2026-08-09/ Correction, and please read this first. An earlier version of this page recommended rsync -av –ignore-existing /source/directory/ /backup/directory/ and described –ignore-existing as transferring “only new or modified ones”. That is wrong in the direction that destroys backups: modified files are never transferred. … 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
Short answer: 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 … Read more
The working form first, since this page is otherwise all warnings. Use an explicit –format with a separator that cannot appear in a path, 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 target; do printf ‘event=%s target=%s\n’ … 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 … Read more
A pgbench TPS figure is meaningless on its own. Publish it with the four things that produced it — scale factor, client count, duration, and whether pgbench ran on the database host. pgbench prints all four; quote the whole block, not the last line: $ pgbench -c 10 -j 2 -T 60 hw_w13_pgbench pgbench (17.10 … Read more