DBA Handbook

Ranges, byte sizes, and typical uses of numeric, text, date/time, JSON, and spatial types. Includes the relevant differences between MySQL and MariaDB.

Applies to: MySQL 5.7+ MariaDB 10.5+ Aurora 2+

The choice of data type affects on-disk size, index speed, and data integrity. This guide summarizes the most used types in MySQL and MariaDB, with their ranges, byte size, and typical uses.

Integer numerics

- TINYINT — 1 byte, signed range −128…127 (unsigned 0…255). Useful for boolean flags or small states.
- SMALLINT — 2 bytes, −32 768…32 767. Age, small quantities.
- MEDIUMINT — 3 bytes, −8 388 608…8 388 607. Unique to MySQL/MariaDB; rarely used outside that ecosystem.
- INT (INTEGER) — 4 bytes, ±2.1·10⁹. Default type for primary keys in medium tables.
- BIGINT — 8 bytes, ±9.2·10¹⁸. Primary keys in large tables, distributed identifiers.

CREATE TABLE orders (
    id        BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    quantity  SMALLINT UNSIGNED NOT NULL,
    status    TINYINT UNSIGNED NOT NULL DEFAULT 0
);

Decimal and floating-point numerics

- DECIMAL(M, D) — exact precision, M total digits and D decimals. Mandatory for money.
- FLOAT — 4 bytes, approximately 7 significant digits. Approximate.
- DOUBLE — 8 bytes, approximately 15 digits. Approximate.

CREATE TABLE prices (
    sku    VARCHAR(32) PRIMARY KEY,
    amount DECIMAL(12, 2) NOT NULL,
    tax    DECIMAL(4, 2)  NOT NULL DEFAULT 13.00
);

Text and strings

- CHAR(N) — fixed length of N characters, up to 255. Fast when every row has the same size (country codes, hashes).
- VARCHAR(N) — variable length, up to 65 535 bytes per row (shared with the rest of the columns). Uses 1 or 2 extra bytes for the length.
- TEXT, MEDIUMTEXT, LONGTEXT — 64 KiB, 16 MiB, 4 GiB. Stored outside the row; cannot be used as a key without a prefix (KEY (col(255))).
- BLOB, MEDIUMBLOB, LONGBLOB — binary equivalents.

Date and time

- DATE — 3 bytes, '1000-01-01'…'9999-12-31'.
- TIME — 3 bytes, '-838:59:59'…'838:59:59'. Yes, it can exceed 24 hours (interval, not time of day).
- DATETIME — 8 bytes, no time zone, no conversion on save/read. Persists the literal string.
- TIMESTAMP — 4 bytes, range 1970…2038 (in MySQL 5.7) or 1970…2106 (in MariaDB 10.4+). Stored in UTC and converted to the connection's time_zone.
- YEAR — 1 byte, 1901…2155.

CREATE TABLE events (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    occurred_at  DATETIME(6) NOT NULL,
    created_at   TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP
);

JSON

Native type in MySQL 5.7+ and MariaDB 10.2+. Lets you index extracted fields with JSON_EXTRACT or ->> and, since MySQL 8.0, generated columns with an index (MULTI-VALUED INDEX on arrays).

CREATE TABLE profiles (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    attributes  JSON NOT NULL,
    email       VARCHAR(120) AS (attributes->>'$.email') STORED,
    INDEX idx_email (email)
);

Spatial (GIS)

- POINT, LINESTRING, POLYGON, GEOMETRY, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION.
- Require a SPATIAL index for efficient queries (MBRContains, ST_Distance, ST_Within).
- In MySQL 8.0, the SRID is mandatory to use spatial indexes.

CREATE TABLE locations (
    id    BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name  VARCHAR(120),
    point POINT NOT NULL SRID 4326,
    SPATIAL INDEX (point)
) ENGINE = InnoDB;

ENUM and SET

- ENUM — stores one of N predefined values (up to 65 535). Compact (1–2 bytes), but rigid: changing the list requires ALTER TABLE.
- SET — combination of up to 64 values as a bitmap. Useful for fixed permissions or labels.

Avoid them if the list of values changes often; a lookup table + foreign key is more maintainable.

How to choose

1. Use the smallest type that covers the expected range. A BIGINT where INT suffices quadruples the index space.
2. Mark columns as UNSIGNED when you do not need negatives: you double the range.
3. Avoid NULL when you can: a NOT NULL DEFAULT … column saves 1 bit per row and per index.
4. VARCHAR(255) is not more expensive than VARCHAR(20) if the real data fits in 20 — only the declared length matters for prefix indexes.

Keywords: data types, INT, BIGINT, VARCHAR, TEXT, JSON, DATETIME, TIMESTAMP, DECIMAL, ENUM, SET, POINT, SPATIAL, BLOB, range, bytes

Index types (B-tree, hash, fulltext, spatial), simple vs composite indexes, and strategies to speed up reads without penalizing writes.

Applies to: MySQL 5.7+ MariaDB 10.5+ Aurora 2+

An index speeds up lookups at the cost of disk space and overhead on every INSERT/UPDATE/DELETE. A good index design is the difference between a millisecond query and a minute-long one.

Index types

- B-tree — Default in InnoDB. Supports equality lookups, range (>, <, BETWEEN), prefix (LIKE 'abc%'), and sorting (ORDER BY).
- Hash — Equality only. Available in the MEMORY engine. InnoDB keeps an internal adaptive hash index that you cannot control directly.
- FULLTEXT — Natural and boolean text search. Available in InnoDB and MyISAM. Useful for long TEXT columns.
- SPATIAL — R-tree for POINT, POLYGON, etc. Requires a NOT NULL column.
- Multi-valued — Indexes elements of a JSON array. Only in MySQL 8.0.17+.

-- Composite B-tree index
CREATE INDEX idx_orders_customer_date
    ON orders (customer_id, order_date DESC);

-- Full-text index
ALTER TABLE articles
    ADD FULLTEXT INDEX ft_title_body (title, body);

-- Spatial index
ALTER TABLE locations
    ADD SPATIAL INDEX sp_point (point);

Simple vs composite

A composite index on (A, B, C) covers prefix lookups: WHERE A = ?, WHERE A = ? AND B = ?, WHERE A = ? AND B = ? AND C = ?, but not WHERE B = ? on its own.

Rule of thumb: order the columns of the composite by selectivity (how many unique values each one has) and by the frequency of filters.

-- Good: most selective column first
CREATE INDEX idx_invoices
    ON invoices (customer_id, status, date)
    -- customer_id (high selectivity) → status → date
;

-- Anti-pattern: redundant index
-- (customer_id) is already covered by (customer_id, status, date)
DROP INDEX idx_invoices_customer ON invoices;

Covering indexes

An index that contains every column read by a query avoids hitting the table. Use EXPLAIN and look for Using index in the Extra column.

-- Query only reads customer_id and total → the index covers it
CREATE INDEX idx_orders_cover
    ON orders (customer_id, total);

SELECT customer_id, SUM(total)
FROM orders
WHERE customer_id IN (1, 2, 3)
GROUP BY customer_id;

Prefix indexes

For long TEXT or VARCHAR columns, index only the first N characters. Reduces index size while keeping reasonable selectivity.

CREATE INDEX idx_url
    ON pages (url(64));   -- first 64 characters

Invisible indexes

ALTER TABLE orders ALTER INDEX idx_legacy INVISIBLE;
-- monitor performance for a few hours
ALTER TABLE orders ALTER INDEX idx_legacy VISIBLE; -- revert
-- or
DROP INDEX idx_legacy ON orders; -- confirm removal

Read vs write impact

Every extra index:
- Speeds up queries that use it.
- Penalizes every INSERT, UPDATE that touches indexed columns, and every DELETE.
- Takes additional space (often between 10 % and 40 % of the table size).

On write-heavy tables (logs, metrics), keep the minimum number of essential indexes.

Optimization strategies

1. Start with EXPLAIN — spot type: ALL (full scan) and key: NULL (no index used).
2. Measure before optimizing — use the slow query log to find the most expensive queries.
3. Combine selectivity with order — the composite index should follow the order of the WHERE and ORDER BY clauses.
4. Avoid redundant indexes — (A), (A, B), (A, B, C) are redundant; (A, B, C) alone is enough.
5. Do not index low-cardinality columns — an index on gender or active (1 or 2 unique values) almost never helps.

-- Diagnosis
EXPLAIN SELECT * FROM orders
WHERE customer_id = 42 AND date >= '2024-01-01';

-- Index usage statistics
SELECT object_schema, object_name, index_name, count_star
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = DATABASE()
ORDER BY count_star DESC;

Keywords: indexes, index, B-tree, hash, fulltext, spatial, covering, composite, selectivity, EXPLAIN, prefix, invisible, cardinality

First, second, and third normal form, when to denormalize, and how to balance data integrity against performance.

Applies to: MySQL 5.7+ MariaDB 10.5+ Aurora 2+ PostgreSQL 13+ SQLite 3.35+

Normalization is the process of organizing a schema to reduce redundancy and prevent insert, update, and delete anomalies. The normal forms are cumulative: a table in 3NF also satisfies 2NF and 1NF.

First Normal Form (1NF)

- Each column holds a single atomic value (no lists or nested JSON representing multiple entities).
- Each row is identifiable by a primary key.
- No repeating groups in columns (phone1, phone2, phone3).

-- Bad: three columns repeating the same "entity"
CREATE TABLE customers_v1 (
    id      BIGINT PRIMARY KEY,
    name    VARCHAR(120),
    phone1  VARCHAR(20),
    phone2  VARCHAR(20),
    phone3  VARCHAR(20)
);

-- Good: a related table
CREATE TABLE customers (
    id BIGINT PRIMARY KEY,
    name VARCHAR(120)
);
CREATE TABLE customer_phones (
    customer_id  BIGINT NOT NULL,
    phone    VARCHAR(20) NOT NULL,
    kind        VARCHAR(10) NOT NULL,
    PRIMARY KEY (customer_id, phone),
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
);

-- SQLite: turned on per connection, before the first transaction
PRAGMA foreign_keys = ON;

CREATE TABLE clientes_telefonos (
    cliente_id  INTEGER NOT NULL,
    telefono    TEXT NOT NULL,
    tipo        TEXT NOT NULL CHECK (tipo IN ('movil', 'casa', 'oficina')),
    PRIMARY KEY (cliente_id, telefono),
    FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE CASCADE
);

Second Normal Form (2NF)

- Satisfies 1NF.
- Every non-key column depends on the entire primary key, not on a part. Applies to composite keys.

Example: a order_line (order_id, product_id, quantity, product_name) table violates 2NF because product_name depends only on product_id, not on the full pair.

-- Bad: product_name is repeated on each line of the same product
CREATE TABLE order_line_v1 (
    order_id      BIGINT,
    product_id    BIGINT,
    quantity      INT,
    product_name  VARCHAR(120),
    PRIMARY KEY (order_id, product_id)
);

-- Good: product_name lives in the products table
CREATE TABLE order_line (
    order_id    BIGINT,
    product_id  BIGINT,
    quantity    INT NOT NULL,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

Third Normal Form (3NF)

- Satisfies 2NF.
- No non-key column depends on another non-key column (no transitive dependencies).

Classic example: employees (id, name, department_id, department_name). The department name depends on department_id, not directly on the employee's key.

-- Bad: transitive dependency
CREATE TABLE employees_v1 (
    id                BIGINT PRIMARY KEY,
    name              VARCHAR(120),
    department_id     BIGINT,
    department_name   VARCHAR(80)
);

-- Good
CREATE TABLE departments (
    id     BIGINT PRIMARY KEY,
    name   VARCHAR(80)  NOT NULL
);
CREATE TABLE employees (
    id             BIGINT PRIMARY KEY,
    name           VARCHAR(120),
    department_id  BIGINT NOT NULL,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

BCNF and higher forms

Boyce-Codd Normal Form (BCNF) tightens 3NF, and 4NF/5NF tackle multi-valued and join dependencies. For most transactional schemas, getting cleanly to 3NF is enough.

When to denormalize

Deliberate denormalization breaks the rules to gain performance. It is valid when:

1. Heavy reads, few writes — a cached field in the table (orders.total_paid) avoids a recurring SUM(...).
2. Reporting / analytics — star or snowflake schemas denormalize on purpose.
3. Pre-computed results — materialized views or summary tables.

Trade-offs you accept:

- Update anomalies — if the denormalized data changes, you must update it in N rows.
- Transient inconsistency — the cached field may drift if the update partially fails.
- Triggers or application logic — you need to keep the data in sync.

-- Example: cache the order total to avoid a SUM on every read
ALTER TABLE orders
    ADD COLUMN total DECIMAL(12, 2) NOT NULL DEFAULT 0;

-- Maintain it with a trigger
DELIMITER //
CREATE TRIGGER order_line_after_insert
AFTER INSERT ON order_line
FOR EACH ROW
BEGIN
    UPDATE orders
       SET total = (SELECT COALESCE(SUM(quantity * unit_price), 0)
                    FROM order_line
                    WHERE order_id = NEW.order_id)
     WHERE id = NEW.order_id;
END//
DELIMITER ;

CREATE FUNCTION recalculate_total() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
    UPDATE orders
       SET total = (SELECT COALESCE(SUM(quantity * unit_price), 0)
                    FROM order_line
                    WHERE order_id = NEW.order_id)
     WHERE id = NEW.order_id;
    RETURN NULL;
END;
$$;

CREATE TRIGGER order_line_after_insert
AFTER INSERT ON order_line
FOR EACH ROW EXECUTE FUNCTION recalculate_total();

CREATE TRIGGER detalle_pedido_after_insert
AFTER INSERT ON detalle_pedido
FOR EACH ROW
BEGIN
    UPDATE pedidos
       SET total = (SELECT COALESCE(SUM(cantidad * precio_unit), 0)
                    FROM detalle_pedido
                    WHERE pedido_id = NEW.pedido_id)
     WHERE id = NEW.pedido_id;
END;

Recommendation

1. Design in 3NF by default. Integrity will thank you.
2. Denormalize only with data — measure the slow query, try a cache, and compare.
3. Document the denormalization. Without a comment in the DDL, the next DBA will "normalize" it back, thinking it is a mistake.

Keywords: normalization, 1NF, 2NF, 3NF, BCNF, denormalization, redundancy, functional dependency, keys, integrity, trigger

INNER, LEFT, RIGHT, CROSS, SELF, and FULL OUTER JOIN: when to use each one, with examples over a typical orders schema.

Applies to: MySQL 5.7+ MariaDB 10.5+ Aurora 2+ PostgreSQL 13+ SQLite 3.35+

A JOIN combines rows from two or more tables based on a condition. The join type determines what happens to rows that do not find a match.

For the examples we assume:

CREATE TABLE customers (
    id      BIGINT PRIMARY KEY,
    name    VARCHAR(120) NOT NULL
);
CREATE TABLE orders (
    id          BIGINT PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    total       DECIMAL(12, 2) NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

INNER JOIN

Returns only rows with matches in both tables. It is the default and most-used join.

SELECT c.name, o.total
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id;

LEFT JOIN (LEFT OUTER JOIN)

All rows from the left table + their matches from the right. Fields without a match on the right are NULL. Useful for "all X, with their Y if it exists".

-- All customers, whether or not they have placed orders
SELECT c.name, COUNT(o.id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;

Customers without orders — classic pattern with WHERE … IS NULL:

SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

RIGHT JOIN

The inverse of LEFT. Almost always written as a LEFT JOIN with the table order swapped — more readable.

-- Equivalent to LEFT JOIN with inverted order
SELECT c.name, o.id
FROM orders o
RIGHT JOIN customers c ON o.customer_id = c.id;

CROSS JOIN

Cartesian product: every row of A with every row of B. No ON clause. Useful for generating all combinations (calendar × products for reports).

-- Generate all (customer, month) combinations for a report
SELECT c.id, m.month
FROM customers c
CROSS JOIN (
    SELECT 1 AS month UNION ALL SELECT 2 UNION ALL SELECT 3
    -- ...through 12
) m;

SELF JOIN

The same table appears twice with different aliases. Useful for hierarchies or comparing rows of the same table.

CREATE TABLE employees (
    id         BIGINT PRIMARY KEY,
    name       VARCHAR(120),
    manager_id BIGINT NULL,
    FOREIGN KEY (manager_id) REFERENCES employees(id)
);

-- Each employee with their manager's name
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;

FULL OUTER JOIN

All rows from both tables; unmatched rows show NULL on the missing side.

-- Native MariaDB
SELECT c.name, o.id
FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.id;

-- Emulation in MySQL
SELECT c.name, o.id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id

UNION

SELECT c.name, o.id
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.id
WHERE c.id IS NULL;

-- Native PostgreSQL
SELECT c.name, o.id
FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.id;

-- SQLite 3.39+
SELECT c.nombre, p.id
FROM clientes c
FULL OUTER JOIN pedidos p ON p.cliente_id = c.id;

STRAIGHT_JOIN

SELECT STRAIGHT_JOIN c.name, o.id
FROM customers c, orders o
WHERE o.customer_id = c.id;

-- SQLite: the written order wins
SELECT c.nombre, p.id
FROM pedidos p
CROSS JOIN clientes c ON p.cliente_id = c.id;

Anti-join and semi-join

Logical patterns, not SQL keywords:

- Semi-join (at least one match exists) → EXISTS or IN.
- Anti-join (no match exists) → NOT EXISTS or LEFT JOIN ... WHERE ... IS NULL.

-- Semi-join: customers with at least one order
SELECT c.id, c.name
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- Anti-join: customers without orders
SELECT c.id, c.name
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

NOT IN is not an anti-join. If the subquery returns even one NULL, the comparison is never true and the result is zero rows. Measured with 5 customers and 6 orders, 3 of them with customer_id set to NULL: NOT EXISTS and LEFT JOIN … IS NULL return 2, and NOT IN returns 0. It happens the same way on PostgreSQL 17.6, on MySQL 8.0.46 and on SQLite 3.51.

Performance

1. The ON columns should be indexed, especially on the "inner" side of the join (the one searched once per outer row).
2. Filter as much as possible before the join (WHERE on each table when applicable).
3. Avoid JOIN on expressions (ON LOWER(a.code) = LOWER(b.code)) — the index is not used.
4. EXPLAIN reveals the read order and method, in each engine's own vocabulary.

-- This is how you ask SQLite for the plan, not with a bare EXPLAIN
EXPLAIN QUERY PLAN
SELECT c.id, c.nombre
FROM clientes c
WHERE NOT EXISTS (SELECT 1 FROM pedidos p WHERE p.cliente_id = c.id);

Keywords: joins, INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN, SELF JOIN, EXISTS, semi-join, anti-join, STRAIGHT_JOIN, nested loop

Critical performance variables: buffer pool, connections, max packet, query cache, and key differences between MySQL and MariaDB.

Applies to: MySQL 5.7+ MariaDB 10.5+ Aurora 2+

The default server configuration is almost never optimal in production. These are the variables that have the biggest impact on performance and stability.

innodb_buffer_pool_size

InnoDB's main cache: tables, indexes, and data. It is the most important variable.

- Rule of thumb: 60 %–80 % of RAM on a server dedicated to MySQL.
- Recommended minimum: 1 GiB in production.
- In MySQL 8.0+ and MariaDB 10.5+ it can be changed online (without restarting).

-- Check the current size
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

-- Change it online (8 GB)
SET GLOBAL innodb_buffer_pool_size = 8 * 1024 * 1024 * 1024;

max_connections

Maximum number of simultaneous connections. Default 151 in MySQL, 100 in MariaDB.

- Each connection consumes memory (thread_stack + per-session buffers, ~256 KiB).
- A value that is too high hurts performance under load (contention).
- Measure with SHOW STATUS LIKE 'Max_used_connections'. If it hits the ceiling, raise gradually.

SHOW STATUS LIKE 'Max_used_connections';
SHOW STATUS LIKE 'Threads_connected';
SET GLOBAL max_connections = 500;

max_allowed_packet

Maximum size of a protocol packet (a large INSERT, a LOAD DATA, a BLOB).

- Default 64 MiB in MySQL 8.0, 16 MiB in earlier versions.
- If an operation exceeds it: Got a packet bigger than 'max_allowed_packet' bytes error.
- Raising to 256 MiB or 1 GiB is common for BLOB-heavy workloads.

SET GLOBAL max_allowed_packet = 256 * 1024 * 1024;
-- The client also has to pass the parameter
-- (command line: --max-allowed-packet=256M)

Query cache

Caches the full result of SELECT queries.

SHOW VARIABLES LIKE 'query_cache%';
SET GLOBAL query_cache_type = 'ON';
SET GLOBAL query_cache_size = 64 * 1024 * 1024;

Logs and durability

innodb_flush_log_at_trx_commit controls when the redo log is flushed:

- 1 (default) — flush and fsync on every commit. Maximum durability, minimum speed. Strict ACID.
- 2 — flush on every commit, fsync once per second. An OS crash can lose ~1s. Near-ACID.
- 0 — flush and fsync once per second. A MySQL crash can lose ~1s. Non-ACID.

In replicas or environments where you can tolerate bounded loss, 2 can triple or quintuple throughput. Do not change it on the primary without understanding the risk.

thread_pool

tmp_table_size / max_heap_table_size

Maximum size of in-memory temporary tables. If an operation exceeds the limit, MySQL spills it to disk and loses speed. Keep both values equal.

SHOW VARIABLES LIKE 'tmp_table_size';
SHOW VARIABLES LIKE 'max_heap_table_size';
SET GLOBAL tmp_table_size = 256 * 1024 * 1024;
SET GLOBAL max_heap_table_size = 256 * 1024 * 1024;

-- How many temp tables went to disk
SHOW STATUS LIKE 'Created_tmp_disk_tables';

innodb_io_capacity / innodb_io_capacity_max

IOPS that InnoDB can use for dirty page cleanup and purges. Default 200 / 2000.

- Modern SSDs: 2000 / 4000 or higher.
- HDDs: keep the defaults.

Persistent configuration

SET PERSIST innodb_buffer_pool_size = 8589934592;
SET PERSIST_ONLY max_connections = 500; -- applied only on restart
RESET PERSIST innodb_buffer_pool_size;   -- remove the persistence

In MariaDB, persistence is done by editing my.cnf (/etc/my.cnf.d/) and restarting, or via includes (!include).

General recommendation

1. Know the workload before touching anything. An OLTP with heavy writes is configured differently than a read-only data warehouse.
2. Change one variable at a time and measure the impact.
3. Document every change in my.cnf with a comment explaining why.
4. Do not copy configurations from blogs without understanding them — the "optimal" values depend heavily on hardware and workload.

Keywords: configuration, buffer pool, innodb_buffer_pool_size, max_connections, max_allowed_packet, query_cache, thread_pool, tmp_table_size, innodb_flush_log_at_trx_commit, io_capacity, my.cnf

Maximum database, table, and column sizes, identifier name lengths, and allowed characters in object names.

Applies to: MySQL 5.7+ MariaDB 10.5+ Aurora 2+

Knowing the engine's limits prevents surprises as you grow. These are the practical ceilings in modern MySQL and MariaDB.

Per database

- Total size: limited by the filesystem. With innodb_file_per_table = ON (default), each table is an .ibd file. On ext4 / XFS we are talking theoretical exabytes — the real limit comes from your storage.
- Tables per database: practically unlimited. The catalog (information_schema, mysql.tables) handles several hundred thousand without issue. Workloads with 10 000+ tables require tuning table_open_cache.

Per table

- Rows: 2⁶⁴ theoretical rows. Practical: hundreds of billions if the schema and indexes are good.
- Maximum table size: 64 TiB with the default INNODB_PAGE_SIZE (16 KiB).
- Columns: max 4 096 per table, but the real limit is set by the row size, not the count.
- Maximum row size: 65 535 bytes (excluding BLOB/TEXT, which are stored outside the row).
- Indexes per table: 64.
- Columns per index: 16 (InnoDB B-tree).
- Maximum index key length: 3072 bytes with DYNAMIC/COMPRESSED (the default format in MySQL 5.7+ / MariaDB 10.2+).

-- Inspect table sizes
SELECT table_schema, table_name,
       ROUND((data_length + index_length) / 1024 / 1024, 2) AS mb
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY (data_length + index_length) DESC
LIMIT 20;

Per column

Identifiers (object names)

- Databases, tables, columns, indexes, views: 64 characters.
- Column aliases: 256 characters.
- Functions, procedures, triggers, events: 64 characters.
- Constraints (FK, CHECK, UNIQUE): 64 characters.

Allowed characters in identifiers

- Without backticks: ASCII letters, digits, _ and $. Cannot start with a pure digit or be only digits.
- With backticks (`weird name`): any Unicode character except U+0000 (NUL).

Recommended convention: ASCII snake_case (order_customer_id). Avoid spaces, accents, and capitalization — some systems normalize them differently between Linux and macOS.

-- Valid but not recommended
CREATE TABLE `orders 2024 year` (`Order Number` INT);

-- Recommended
CREATE TABLE orders_2024 (order_number INT);

Case sensitivity

lower_case_table_names:

- 0 — names are stored as created and are case-sensitive. Default on Linux.
- 1 — names are stored in lowercase and comparisons ignore case. Default on macOS and Windows.
- 2 — names are stored as-is but comparisons ignore case. macOS/Windows only.

Changing this value on an existing install is destructive. Decide at server initialization.

Per query

- Nested subqueries: up to 64 levels.
- UNION: theoretically unlimited, but the optimizer degrades above several hundred.
- Parameters in a prepared statement: 65 535.
- Rows in an IN(...): practical up to a few thousand; above that, prefer a JOIN with a temporary table.

Per session

- Session variables (@@SESSION.xxx): can set almost any global runtime.
- User variables (@variable): up to 64 characters in the name.

Charset and collation

- Recommended charset: utf8mb4 (full UTF-8, 4 bytes). The utf8 alias is historical and limited to 3 bytes (no emoji).
- Recommended collation in MySQL 8.0+: utf8mb4_0900_ai_ci (case-insensitive, accent-insensitive, Unicode 9-based).
- In MariaDB: utf8mb4_unicode_ci or uca1400_ai_ci (10.10+).

ALTER DATABASE my_db
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

ALTER TABLE customers
    CONVERT TO CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

Recommendations

1. Design with headroom: if you expect 10 million rows, size indexes and partitions for 100 M.
2. Use BIGINT UNSIGNED for primary keys of tables that can grow. INT fills up at ~2 billion.
3. Define explicit charset and collation at the database, table, and column level. Relying on defaults can break migrations.
4. Document your model's limits (expected rows/year, max size per column). Useful for capacity planning and for spotting anomalous queries.

Keywords: limits, maximum, size, columns, rows, identifiers, charset, collation, utf8mb4, lower_case_table_names, index, row size

Schema design, naming conventions, backups, replication, user security, minimum GRANTs, and auditing.

Applies to: MySQL 5.7+ MariaDB 10.5+ Aurora 2+

Operational recommendations that separate an amateur database from one maintainable in production.

Schema design

1. Every table has a primary key. If a natural one is not obvious, add id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY.
2. Explicit types: declare NOT NULL and DEFAULT whenever the column allows it. NULL should mean "not applicable", not "not filled in".
3. Mandatory foreign keys between related tables. You lose microseconds on writes and gain unbreakable referential integrity.
4. InnoDB always. MyISAM does not support FKs or transactions; it survives only in legacy systems.

CREATE TABLE orders (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id  BIGINT UNSIGNED NOT NULL,
    status       TINYINT UNSIGNED NOT NULL DEFAULT 0,
    created_at   DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    updated_at   DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
                             ON UPDATE CURRENT_TIMESTAMP(6),
    FOREIGN KEY (customer_id) REFERENCES customers(id)
        ON DELETE RESTRICT ON UPDATE CASCADE,
    INDEX idx_orders_customer (customer_id),
    INDEX idx_orders_created (created_at)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_unicode_ci;

Naming conventions

- Tables: snake_case, plural if they represent collections (orders, customers).
- Columns: snake_case, without redundant prefix (name, not customer_name inside customers).
- Foreign keys: <table>_id (customer_id).
- Indexes: idx_<table>_<columns> or uq_<table>_<columns> for unique.
- Explicit foreign keys: fk_<table>_<target_table>.
- Procedures / functions: optional prefix sp_ / fn_, verb in infinitive (fn_calculate_discount).

Consistency > personal preference. Agree on the convention with your team and apply it universally.

Backups

1. 3-2-1 strategy: 3 copies, 2 different media, 1 off-site.
2. Types:
- mysqldump — logical, portable, slow restore (~5–10 MB/s).
- mariabackup / xtrabackup — physical, much faster, requires briefly pausing I/O.
- Filesystem snapshot (LVM, ZFS) — instant but tied to the filesystem.
3. Test the restore — a backup without a verified restore is not a backup.
4. Retention: daily 7 days + weekly 4 + monthly 12 is a reasonable starting point.

Calíope has an integrated backup module: Help › Backup.

-- Logical dump with transactional consistency
-- (from shell, not SQL):
-- mysqldump --single-transaction --routines --triggers --events \\
--           -u root -p my_db > my_db.sql

-- Physical backup (mariabackup):
-- mariabackup --backup --target-dir=/srv/backup/full \\
--             --user=root --password=...

Replication

- Asynchronous replication (default) — the primary does not wait for the replica. Risk: losing the latest transactions if the primary dies.
- Semi-synchronous replication — the primary waits for confirmation from at least one replica before acknowledging the client.
- Replication group (MySQL InnoDB Cluster, MariaDB Galera) — multi-primary with consensus.

Best practices:

1. GTID enabled (gtid_mode = ON) — required for automatic failover and modern tooling.
2. binlog_format = ROW — more robust than STATEMENT for non-deterministic functions.
3. Dedicated replication — a replica user with only REPLICATION SLAVE, restricted IP.
4. Lag monitoring — SHOW REPLICA STATUS (SHOW SLAVE STATUS on older versions), alert when Seconds_Behind_Source > 30.

Users and permissions

Principle of least privilege: every connection uses the most restricted user possible.

-- Create an application user with limited permissions
CREATE USER 'app_orders'@'10.0.%.%' IDENTIFIED BY 'strong_password';

GRANT SELECT, INSERT, UPDATE, DELETE
   ON my_db.orders          TO 'app_orders'@'10.0.%.%';
GRANT SELECT
   ON my_db.customers       TO 'app_orders'@'10.0.%.%';

-- NEVER in production
-- GRANT ALL PRIVILEGES ON *.* TO 'app'@'%';
FLUSH PRIVILEGES;

Rules:

1. One user per application / per function. Eases auditing.
2. No *.* privileges for application users. Grant per database or per table.
3. No application access to the root user. It is only for administrative tasks.
4. Rotate passwords and use strong authentication (caching_sha2_password in MySQL 8, ed25519 in MariaDB).
5. Restrict the host ('app'@'10.0.%.%'), do not use '%'.

Auditing

INSTALL SONAME 'server_audit';
SET GLOBAL server_audit_logging = ON;
SET GLOBAL server_audit_events = 'CONNECT,QUERY,TABLE';
SET GLOBAL server_audit_file_path = '/var/log/mysql/audit.log';

Calíope keeps a local log of executed queries (SQL Log) per connection session, independent of the server log.

Production minimum checklist

1. ✅ Automatic backups + monthly verified restores.
2. ✅ Replication with monitored lag.
3. ✅ Application users without excessive privileges.
4. ✅ Mandatory TLS for external connections.
5. ✅ Slow query log enabled (long_query_time = 1).
6. ✅ Disk space monitoring (alert at 80 %).
7. ✅ Security updates applied quarterly.

Keywords: best practices, design, conventions, backup, replication, GTID, binlog, GRANT, auditing, security, InnoDB, foreign key, TLS

Query analysis with EXPLAIN, slow query log, bottleneck identification, InnoDB cache, and using performance_schema.

Applies to: MySQL 5.7+ MariaDB 10.5+ Aurora 2+

Optimization starts with measuring. Without data, optimizing is guessing. These are the basic instruments.

EXPLAIN

Shows the plan the optimizer chose for a query. It does not execute it — safe to run in production.

EXPLAIN SELECT c.name, COUNT(o.id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE c.active = 1
GROUP BY c.id;

Key columns:

- type — access method. Best to worst: system → const → eq_ref → ref → range → index → ALL. ALL = full table scan = bad on large tables.
- key — chosen index. NULL = no index used.
- rows — estimated rows examined. If much greater than the rows returned, there is room to improve.
- Extra — useful hints:
- Using index — covering index (great).
- Using where — filter applied after reading the rows.
- Using temporary — needs a temporary table (expensive).
- Using filesort — sorting outside the index (expensive on large tables).

EXPLAIN ANALYZE (MySQL 8.0+ / MariaDB 10.1+)

Executes the query and shows actual times per node. More expensive than EXPLAIN, but much more informative.

EXPLAIN ANALYZE
SELECT c.name, COUNT(o.id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id;

Calíope has a built-in Visual Explain that renders the plan tree: Workspace › Analysis › Visual Explain.

Slow query log

Logs every query that takes longer than long_query_time seconds.

SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;     -- 1 second
SET GLOBAL log_queries_not_using_indexes = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

Log analysis:

- mysqldumpslow — classic tool included with MySQL.
- pt-query-digest (Percona Toolkit) — the de-facto standard, groups by fingerprint and shows statistics.

performance_schema

Schema with detailed server statistics.

-- Top 10 queries by total accumulated time
SELECT digest_text,
       count_star            AS exec,
       ROUND(sum_timer_wait/1e9, 2) AS total_ms,
       ROUND(avg_timer_wait/1e9, 2) AS avg_ms,
       sum_rows_examined     AS rows_exam
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC
LIMIT 10;

-- Tables with the most I/O
SELECT object_schema, object_name,
       count_read, count_write,
       ROUND(sum_timer_wait/1e9, 2) AS total_ms
FROM performance_schema.table_io_waits_summary_by_table
WHERE object_schema = DATABASE()
ORDER BY sum_timer_wait DESC
LIMIT 10;

InnoDB cache

- Buffer pool — data and indexes. Key metric: hit ratio (Innodb_buffer_pool_read_requests / (reads + reads_from_disk)). Target: >99 %.

SELECT ROUND(
    (1 - (
        VARIABLE_VALUE FROM performance_schema.global_status
        WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads'
    ) / (
        VARIABLE_VALUE FROM performance_schema.global_status
        WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests'
    )) * 100, 2) AS buffer_pool_hit_pct;

-- (Version that does parse, using two queries):
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';

- Adaptive hash index — automatic hash over hot pages of the buffer pool. On by default.
- Change buffer — buffers modifications to pages not present in the buffer pool.

Common bottlenecks

Query optimization — common patterns

1. Select only what you need. Avoid SELECT * in applications.
2. Avoid functions on indexed columns:
- Bad: WHERE YEAR(date) = 2024 → does not use the index.
- Good: WHERE date >= '2024-01-01' AND date < '2025-01-01'.
3. LIMIT with large offset is expensive — for deep pagination, use keyset pagination: WHERE id > :last_seen ORDER BY id LIMIT 50.
4. COUNT(*) on large tables — InnoDB does not maintain a counter. Consider summary columns or estimates (information_schema.tables.table_rows).
5. Non-correlated subqueries run once; correlated ones run once per outer row. Rewrite them as a JOIN when possible.

Recommendation

Create a basic monitoring dashboard (Calíope has one: Dashboard) with:

- Connections (Threads_connected, Threads_running).
- Buffer pool hit ratio.
- Slow queries per minute.
- Replication lag.
- Disk space per tablespace.

The sooner you spot a degradation, the easier it is to fix.

Keywords: performance, optimization, EXPLAIN, EXPLAIN ANALYZE, slow query log, performance_schema, buffer pool, filesort, covering index, filter, bottleneck, pt-query-digest

ACID, COMMIT and ROLLBACK, the four isolation levels and which anomaly each one allows.

Applies to: MySQL 5.7+ MariaDB 10.5+ Aurora 2+

A transaction groups several statements into a unit that either applies in full or not at all. In InnoDB every statement runs inside a transaction: if you don't open one, the server opens and commits one per statement (autocommit = 1).

ACID
- Atomicity — either every change applies, or none does.
- Consistency — the database moves from one valid state to another; constraints hold.
- Isolation — concurrent transactions never see each other half-done.
- Durability — what is committed survives a server crash.

Manual control
COMMIT confirms and ROLLBACK undoes everything done since START TRANSACTION.

START TRANSACTION;
UPDATE cuentas SET saldo = saldo - 100 WHERE id = 1;
UPDATE cuentas SET saldo = saldo + 100 WHERE id = 2;
COMMIT;

Savepoints
A SAVEPOINT undoes just one part without losing the rest of the transaction:

START TRANSACTION;
INSERT INTO pedidos (cliente_id) VALUES (42);
SAVEPOINT tras_pedido;
INSERT INTO lineas (pedido_id, sku) VALUES (LAST_INSERT_ID(), 'X-1');
ROLLBACK TO SAVEPOINT tras_pedido;
COMMIT;

The four levels

InnoDB's default is REPEATABLE READ. Thanks to MVCC and gap locks, InnoDB also prevents phantom reads at that level, which the SQL standard does not require.

Changing the level

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

SELECT @@transaction_isolation;

Watch out for DDL
CREATE, ALTER, DROP and TRUNCATE cause an implicit commit: they cannot be undone with ROLLBACK. A half-finished migration leaves the table as it ended up.

Long transactions
An open transaction forces InnoDB to keep the old versions of every row for consistent reads. A forgotten START TRANSACTION grows the undo log and degrades the whole server. Find them like this:

SELECT trx_id, trx_started, trx_mysql_thread_id, trx_query
FROM information_schema.INNODB_TRX
ORDER BY trx_started;

Recommendation
Keep transactions short, with the business logic outside and only the SQL inside. READ COMMITTED reduces locking and is what many web applications use; stay on REPEATABLE READ if you need two reads inside the same transaction to return the same thing.

Keywords: transaction, commit, rollback, savepoint, isolation, acid, mvcc, read committed, repeatable read, serializable, autocommit, innodb_trx

What InnoDB locks, why a deadlock happens and how to diagnose one without guessing.

Applies to: MySQL 5.7+ MariaDB 10.5+ Aurora 2+

InnoDB locks rows, not tables, and does so automatically on write. Almost every concurrency problem comes down to which rows a query ended up locking, and that depends on the index it used.

Kinds of lock
- Shared (S) — several transactions may read the same row at once.
- Exclusive (X) — taken by whoever writes; nobody else may lock-read or write it.
- Gap — locks the space between two index values to prevent inserts. Only in REPEATABLE READ and SERIALIZABLE.
- Next-key — the row plus the gap before it. This is InnoDB's normal mode when scanning an index.
- Intention (IS/IX) — marks at table level that row locks exist inside; stops a LOCK TABLES from slipping in.

The practical consequence: if the query uses no index, InnoDB scans the whole table and locks every row it examines, not just the matching ones. A good index doesn't only speed things up — it shrinks what gets locked.

Locking reads
A plain SELECT locks nothing (it reads a consistent version through MVCC). If you need to read and then write with nobody slipping in between, ask for the lock explicitly with FOR UPDATE or FOR SHARE:

START TRANSACTION;
SELECT saldo FROM cuentas WHERE id = 1 FOR UPDATE;
UPDATE cuentas SET saldo = saldo - 100 WHERE id = 1;
COMMIT;

What a deadlock is
Two transactions each waiting for a lock the other holds. Neither can move:

-- A
START TRANSACTION;
UPDATE cuentas SET saldo = saldo - 10 WHERE id = 1;
UPDATE cuentas SET saldo = saldo + 10 WHERE id = 2;

-- B
START TRANSACTION;
UPDATE cuentas SET saldo = saldo - 10 WHERE id = 2;
UPDATE cuentas SET saldo = saldo + 10 WHERE id = 1;

InnoDB detects it by itself and kills the transaction that is cheapest to roll back, which gets error 1213 Deadlock found when trying to get lock. It is not a server failure nor corruption: it is the correct behaviour, and the application must retry that transaction.

Different is error 1205 Lock wait timeout exceeded: there is no cycle there, just a wait that exceeded innodb_lock_wait_timeout (50 s by default).

Diagnosis
The LATEST DETECTED DEADLOCK section of SHOW ENGINE INNODB STATUS keeps the last deadlock with both transactions and the statements involved. To see the locks held right now, use performance_schema:

SHOW ENGINE INNODB STATUS;

SELECT * FROM performance_schema.data_locks;

SELECT * FROM performance_schema.data_lock_waits;

SELECT @@innodb_lock_wait_timeout;

How to avoid them
1. Always access in the same order — if all code touches tables and rows in the same order, no cycle can form.
2. Short transactions — less time holding locks, fewer chances to collide.
3. Index what you filter on — avoids locking rows that didn't even match.
4. Retry — the odd deadlock is normal in a concurrent system; wrap the transaction in a retry with backoff.
5. Avoid needless SELECT ... FOR UPDATE — if you are not going to write, don't ask for it.

Recommendation
When facing locks, look at the query plan first: most real deadlocks vanish once the missing index is added. Calíope's Process List shows you which session is waiting.

Keywords: lock, deadlock, gap lock, next-key, for update, for share, error 1213, innodb status, data_locks, lock wait timeout

RANGE, LIST, HASH and KEY, partition pruning and instant purging with DROP PARTITION.

Applies to: MySQL 5.7+ MariaDB 10.5+ Aurora 2+

Partitioning splits one table into several physical pieces that the server still sees as a single table. It does not magically make queries fast: what it gives you is partition pruning and, above all, the ability to delete millions of rows in an instant.

When it pays off
The clear case is a table that grows by date and from which old data is purged: logs, events, metrics, auditing. There DROP PARTITION replaces a DELETE that would take hours.

CREATE TABLE eventos (
    id        BIGINT NOT NULL AUTO_INCREMENT,
    ocurrido  DATE   NOT NULL,
    payload   JSON,
    PRIMARY KEY (id, ocurrido)
)
PARTITION BY RANGE (YEAR(ocurrido)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION pmax  VALUES LESS THAN MAXVALUE
);

The four kinds
- RANGE — by intervals of an orderable value, almost always a date. The most useful one.
- LIST — by membership of a discrete set of values.
- HASH — even spread by an integer expression; good for spreading writes, not for pruning.
- KEY — like HASH but with the server's internal function; accepts non-integer columns.

The RANGE COLUMNS and LIST COLUMNS variants take several columns and non-integer types without wrapping them in a function:

PARTITION BY LIST (region_id) (
    PARTITION europa VALUES IN (1, 2, 3),
    PARTITION asia   VALUES IN (4, 5)
);

PARTITION BY HASH (cliente_id) PARTITIONS 8;

PARTITION BY KEY (uuid) PARTITIONS 4;

PARTITION BY RANGE COLUMNS (pais, alta) (
    PARTITION p_es_2024 VALUES LESS THAN ('ES', '2025-01-01')
);

Partition pruning
The real benefit: if the WHERE filters on the partitioning column, the server only reads the partitions that could hold results. Check it in the partitions column of EXPLAIN — if all of them show up, you are pruning nothing and partitioning is only costing you.

EXPLAIN SELECT COUNT(*) FROM eventos
WHERE ocurrido BETWEEN '2025-03-01' AND '2025-03-31';

SELECT partition_name, table_rows
FROM information_schema.PARTITIONS
WHERE table_name = 'eventos';

Limits to know up front
1. The partitioning key must be part of every unique key, the primary one included. That is why the example carries PRIMARY KEY (id, ocurrido) and not just id.
2. No foreign keys: a partitioned table can neither have nor receive a FOREIGN KEY.
3. At most 8192 partitions per table, and each one consumes file descriptors.
4. Queries that don't filter on the key touch every partition and end up slower than without partitioning.
5. Indexes are local to each partition: there is no global index.

Maintenance
Adding next period's partition and dropping the oldest is the normal routine. DROP PARTITION is practically instant and genuinely frees the space, which a mass DELETE does not:

ALTER TABLE eventos DROP PARTITION p2023;

ALTER TABLE eventos REORGANIZE PARTITION pmax INTO (
    PARTITION p2026 VALUES LESS THAN (2027),
    PARTITION pmax  VALUES LESS THAN MAXVALUE
);

ALTER TABLE eventos REBUILD PARTITION p2025;

Recommendation
Partition by date only if you are going to purge by date, and create future partitions in advance (or with a scheduled event): if a row arrives that fits no range, the INSERT fails. Always keep a pmax as a safety net.

Keywords: partitioning, partition, range, list, hash, key, pruning, drop partition, reorganize, information_schema.partitions, purge

WITH, WITH RECURSIVE and OVER (): the modern SQL that spares you nested subqueries and temp tables.

Applies to: MySQL 8.0+ MariaDB 10.2+ Aurora 3+ PostgreSQL 13+ SQLite 3.35+

CTEs (WITH) and window functions (OVER ()) arrived in MySQL 8.0 and MariaDB 10.2; on PostgreSQL there is no supported version without them, and on SQLite both sit well below this handbook's floor: CTEs since 3.8.3 and windows since 3.25. They solve in one readable query what used to need nested subqueries, temporary tables or session variables.

CTE: naming an intermediate step
A CTE is a named result that lives only for the duration of the query. It lets you split a long query into steps and refer to the same sub-result twice without repeating it:

WITH ventas_mes AS (
    SELECT vendedor_id, DATE_FORMAT(fecha, '%Y-%m') AS mes, SUM(total) AS total
    FROM pedidos
    GROUP BY vendedor_id, mes
)
SELECT * FROM ventas_mes WHERE total > 10000;

WITH ventas_mes AS (
    SELECT vendedor_id, date_trunc('month', fecha) AS mes, SUM(total) AS total
    FROM pedidos
    GROUP BY vendedor_id, mes
)
SELECT * FROM ventas_mes WHERE total > 10000;

WITH ventas_mes AS (
    SELECT vendedor_id, strftime('%Y-%m', fecha) AS mes, SUM(total) AS total
    FROM pedidos
    GROUP BY vendedor_id, mes
)
SELECT * FROM ventas_mes WHERE total > 10000;

Recursive CTE: hierarchies
WITH RECURSIVE walks tree structures — org charts, nested categories, bills of materials — with no loops in the application. The first branch is the base case and the second repeats until it returns no rows:

WITH RECURSIVE arbol AS (
    SELECT id, nombre, jefe_id, 1 AS nivel
    FROM empleados
    WHERE jefe_id IS NULL

    UNION ALL

    SELECT e.id, e.nombre, e.jefe_id, a.nivel + 1
    FROM empleados e
    JOIN arbol a ON e.jefe_id = a.id
)
SELECT * FROM arbol ORDER BY nivel, nombre;

Window functions: computing without grouping
A GROUP BY collapses rows; a window function computes over a set of related rows and keeps every row. That is what makes a running total, a moving average or a rank within the group possible in a single pass:

SELECT
    vendedor_id,
    fecha,
    total,
    SUM(total)    OVER (PARTITION BY vendedor_id ORDER BY fecha) AS acumulado,
    AVG(total)    OVER (PARTITION BY vendedor_id
                        ORDER BY fecha
                        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS media_7,
    RANK()        OVER (PARTITION BY vendedor_id ORDER BY total DESC) AS puesto,
    LAG(total, 1) OVER (PARTITION BY vendedor_id ORDER BY fecha) AS anterior
FROM pedidos;

The ones you'll use most
- ROW_NUMBER() — running number within the partition, no ties.
- RANK() / DENSE_RANK() — position with ties; RANK leaves gaps, DENSE_RANK does not.
- LAG() / LEAD() — the previous or next row's value, with no self-join.
- FIRST_VALUE() / LAST_VALUE() — the ends of the window.
- NTILE(n) — splits the rows into n buckets, for quartiles and percentiles.
- Aggregates with OVER — SUM, AVG, COUNT, MIN, MAX without collapsing the rows.

The frame (ROWS BETWEEN ...) defines which rows go into each row's calculation. By default an aggregate with ORDER BY runs from the start of the partition to the current row, which is exactly what gives you the running total.

The pattern that pays off most: top N per group
Getting the three best-selling products of each category without windows needs a correlated subquery per row. With ROW_NUMBER() it is straightforward:

WITH ranking AS (
    SELECT
        p.*,
        ROW_NUMBER() OVER (PARTITION BY categoria_id ORDER BY ventas DESC) AS rn
    FROM productos p
)
SELECT * FROM ranking WHERE rn <= 3;

Performance
Neither is free: a window has to sort within each partition, so an index that already delivers rows in PARTITION BY plus ORDER BY order saves that sort. Check with EXPLAIN before settling for the pretty version.

Recommendation
Use CTEs so the query reads well, and windows so you don't do in the application what the server does in a single pass. If your server is MySQL 5.7 or MariaDB 10.1, neither is available: there, subqueries still rule.

Keywords: cte, with, with recursive, window function, over, partition by, row_number, rank, dense_rank, lag, lead, ntile, frame, hierarchy, top n per group

Why one error blocks the whole transaction, how a savepoint gets you out, what each level really does, and DDL that rolls back.

Applies to: PostgreSQL 13+

A transaction groups several statements into a unit that either applies in full or not at all. Without an explicit BEGIN, PostgreSQL commits each statement on its own.

The first surprise coming from MySQL
An error aborts the whole transaction. From then on every statement answers the same thing — current transaction is aborted, commands ignored until end of transaction block, SQLSTATE 25P02 — until you ROLLBACK. It is not an application bug: it is the design, and it keeps a transaction from carrying on over a state that is no longer the one you thought.

The way out is a savepoint
SAVEPOINT marks a point to come back to, and ROLLBACK TO SAVEPOINT rescues the transaction without losing what came before:

BEGIN;
INSERT INTO cuentas (id, saldo) VALUES (3, 0);
SAVEPOINT tras_alta;
INSERT INTO cuentas (id, saldo) VALUES (3, 0);  -- fails: 23505
ROLLBACK TO SAVEPOINT tras_alta;
COMMIT;

DDL does roll back
CREATE, ALTER and DROP run inside the transaction: there is no implicit commit here. A migration that fails halfway does not leave half a table.

BEGIN;
ALTER TABLE cuentas ADD COLUMN moneda text;
ROLLBACK;   -- the column never existed

The four levels

READ UNCOMMITTED is accepted and reported as such, but it behaves like READ COMMITTED: PostgreSQL has no dirty reads at any level. The default is READ COMMITTED.

REPEATABLE READ and SERIALIZABLE do not block: they abort
Instead of waiting, a transaction that cannot be serialized ends with SQLSTATE 40001 (could not serialize access…). That means the application has to retry: at these two levels a 40001 is normal operation, not a failure. SERIALIZABLE uses SSI, which detects read/write dependencies and takes no extra locks.

Locks and deadlocks
A deadlock is detected after deadlock_timeout — 1 s by default — and the server kills one of the two with SQLSTATE 40P01. To avoid waiting, or to spread work across consumers:

SELECT id FROM cuentas ORDER BY id FOR UPDATE SKIP LOCKED;

FOR UPDATE NOWAIT fails immediately with 55P03 instead of waiting. Careful: that failure also aborts the transaction.

Long transactions
Here an open transaction does not grow an undo log: it stops VACUUM from cleaning up dead row versions across the server, and the table grows without new rows. Find them like this:

SELECT pid, state, xact_start, now() - xact_start AS duracion, query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;

idle_in_transaction_session_timeout is 0 by default, meaning no limit; setting it is the safety net that keeps a forgotten session from degrading the whole database.

Recommendation
Keep transactions short, with the business logic outside. If you move up to REPEATABLE READ or SERIALIZABLE, write the retry before you move up, not after the first 40001 in production.

Keywords: transaction, commit, rollback, savepoint, isolation, mvcc, read committed, repeatable read, serializable, ssi, 25P02, 40001, 40P01, deadlock, skip locked, pg_stat_activity

Why a table grows without new rows, what each operation actually cleans, when the counters lie, and what transaction wraparound is.

Applies to: PostgreSQL 13+

In PostgreSQL, updating a row does not modify it: it writes a new version and leaves the old one dead. Deleting frees nothing right away either. That is how MVCC works here, and VACUUM is what collects afterwards. It has no equivalent in InnoDB, and it is behind almost every size surprise.

What it looks like
A 50 000-row table took 12 MB. A single UPDATE over all of them left it at 23 MB without adding one row: the 50 000 old versions are still in the file. Measured on PostgreSQL 17.6.

What each thing does
- VACUUM marks the dead space as reusable. It does not give the space back to the operating system: after the vacuum the example table was still 23 MB, only now the next writes fit inside it.
- VACUUM FULL rewrites the whole table and does give the space back — it dropped to 11 MB — but it takes an ACCESS EXCLUSIVE lock: nobody reads or writes while it runs, and it needs room for a full copy. It is not routine maintenance; it is the last resort.
- ANALYZE cleans nothing: it refreshes the planner statistics.

Autovacuum, which is already on
autovacuum ships on. A table is queued when its dead rows pass autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor × rows, that is 50 + 20 % with the default values. On a ten-million-row table that means two million dead rows before anything moves, so on big, heavily updated tables you lower the factor per table:

ALTER TABLE pedidos SET (autovacuum_vacuum_scale_factor = 0.02);

Checking that it is working

SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, autovacuum_count
FROM pg_stat_user_tables
WHERE n_dead_tup > 0
ORDER BY n_dead_tup DESC
LIMIT 10;

Be careful with that counter: it is an estimate and it is not instantaneous. Measured on 17.6, right after updating 50 000 rows it still said 0; only after an ANALYZE did it say 50 000, and after the VACUUM it went back to 0. If you have just written a lot and the number does not move, that does not mean there is no work pending.

A running vacuum is followed like this:

SELECT pid, relid::regclass AS tabla, phase, heap_blks_scanned, heap_blks_total
FROM pg_stat_progress_vacuum;

The enemy: the open transaction
VACUUM can only clean what nobody can still see. An open transaction — or a replica with hot_standby_feedback — freezes that horizon, and then the vacuum runs, reports success and frees nothing. That is why one session forgotten in idle in transaction grows tables it never touches.

Wraparound, which is a real emergency
Transaction ids are 32-bit and get recycled. So that no row ends up in the future, vacuum freezes the old ones. autovacuum_freeze_max_age is 200 000 000 by default: past that age the server launches an autovacuum that cannot be postponed, and if it still runs out, it stops accepting writes. Watch it like this:

SELECT datname, age(datfrozenxid) AS edad
FROM pg_database
ORDER BY edad DESC;

While that age stays far below two hundred million there is nothing to do.

Recommendation
Do not turn autovacuum off. If a table grows without new rows, suspect in this order: an open transaction, a scale_factor too high for its size, and only at the end VACUUM FULL — with a maintenance window, because it locks the whole table.

Keywords: vacuum, autovacuum, bloat, mvcc, n_dead_tup, vacuum full, freeze, wraparound, pg_stat_user_tables, pg_stat_progress_vacuum, datfrozenxid, hot_standby_feedback, idle in transaction

What each statement really locks, why an ALTER TABLE can stop your SELECTs, how to see who is waiting for whom and what to do with a 40P01.

Applies to: PostgreSQL 13+

In PostgreSQL locks live in two different places, and mixing them up is what makes a problem impossible to find. Table locks are in pg_locks; row locks live inside the row itself, in its header, so they take no memory, never escalate to a table lock and do not show up in pg_locks. A million locked rows cost no more than one.

And one rule has no exceptions: a plain SELECT never waits for a row. It reads its own version through MVCC. The only thing that can stop a SELECT is a table lock.

Who takes which table mode

The first three don't get in each other's way, which is why ordinary load never blocks. The last one conflicts with all of them, SELECT included.

The trap: a waiting ALTER TABLE queues everything behind it
That ACCESS EXCLUSIVE doesn't jump the queue: it joins it. And while it waits, everything that arrives later waits behind it, even a SELECT that would have had no problem with the statement ahead. One open transaction that only ran a SELECT is enough for an ALTER TABLE to freeze the table for everybody without having started any work. That is why DDL in production is issued with a cap and retried:

SET lock_timeout = '3s';
ALTER TABLE cuentas ADD COLUMN moneda text;

Four row modes, not two
Strongest to weakest: FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, FOR KEY SHARE. Only three pairs coexist —the two shared ones with each other, and FOR NO KEY UPDATE with FOR KEY SHARE—; FOR UPDATE conflicts with all four. That odd pair is the one that matters: an UPDATE that doesn't touch the key takes FOR NO KEY UPDATE, so it doesn't block the check of a foreign key pointing at that row, which is the one asking for FOR KEY SHARE.

BEGIN;
SELECT saldo FROM cuentas WHERE id = 1 FOR UPDATE;
UPDATE cuentas SET saldo = saldo - 100 WHERE id = 1;
COMMIT;

Here the wait is forever
lock_timeout is 0 by default, that is, no limit: there is no equivalent to MySQL's innodb_lock_wait_timeout, which cuts in at 50 s. Setting it —per session, before a risky statement, or in the configuration— is what turns an endless wait into an error the application can retry. When it fires, SQLSTATE 55P03.

Not waiting, on purpose

SELECT id FROM cuentas WHERE id = 3 FOR UPDATE NOWAIT;

SELECT id FROM cuentas ORDER BY id FOR UPDATE SKIP LOCKED;

NOWAIT fails immediately with 55P03 —and that failure, like any other, aborts the whole transaction—. SKIP LOCKED doesn't fail: it returns fewer rows. With row 3 locked by another session, the second query returned 1, 2, 4 and 5. This is how you split a work queue among several consumers without them colliding or waiting.

The deadlock

-- Session A
BEGIN;
UPDATE cuentas SET saldo = saldo - 10 WHERE id = 1;
UPDATE cuentas SET saldo = saldo + 10 WHERE id = 2;

-- Session B
BEGIN;
UPDATE cuentas SET saldo = saldo - 10 WHERE id = 2;
UPDATE cuentas SET saldo = saldo + 10 WHERE id = 1;

The server kills one of the two with SQLSTATE 40P01 («deadlock detected»), and the detail says which process and which row. But it doesn't detect it instantly: it only looks for the cycle once a wait exceeds deadlock_timeout, 1 s by default, and the victim takes that second to die. InnoDB detects it right away; here a deadlock costs a second of waiting. Lowering deadlock_timeout isn't free: that work is also spent on ordinary waits, which are the majority.

A 40P01 is not a server failure: it is the correct behaviour, and the application must retry that transaction.

Diagnosis
There is no SHOW ENGINE INNODB STATUS here. There are two queries, and both have to be run while the lock lasts:

SELECT pid, pg_blocking_pids(pid) AS bloqueado_por,
       wait_event_type, wait_event, left(query, 40) AS consulta
  FROM pg_stat_activity
 WHERE cardinality(pg_blocking_pids(pid)) > 0;

SELECT l.pid, c.relname, l.locktype, l.mode, l.granted
  FROM pg_locks l LEFT JOIN pg_class c ON c.oid = l.relation
 WHERE NOT l.granted;

pg_blocking_pids gives the list of processes holding what the other one is asking for, which is the question you actually have. What you will not see in pg_locks are the row locks: whoever waits for a row shows up waiting on a transactionid, the number of the transaction that holds it. And to leave a trace of what already happened, log_lock_waits writes to the server log every wait longer than deadlock_timeout.

Calíope's Process List shows that wait in the state column: a blocked session appears as Lock: transactionid.

Advisory locks
No table, no data: a number the server keeps for you so that two processes of your application don't do the same thing at once.

SELECT pg_try_advisory_lock(42);
SELECT pg_advisory_unlock(42);

While one session holds 42, pg_try_advisory_lock(42) from another returns false instead of waiting. Mind the scope: session-level ones survive COMMIT and are only released by releasing them or closing the connection; pg_advisory_xact_lock releases itself when the transaction ends, which is almost always what you want.

SERIALIZABLE doesn't block
At that level SIReadLock locks appear in pg_locks. They block nobody: they are the mark of what the transaction read, and the conflict arrives as 40001 on commit, not as a wait.

Recommendation
Always touch rows in the same order, and keep transactions short, as in any engine. What is specific here is two things: lock_timeout set before every DDL, because whoever waits queues everyone behind; and the retry written before going to production, which is the only thing that makes a 40P01 harmless.

Keywords: lock, deadlock, 40p01, 55p03, pg_locks, pg_blocking_pids, lock_timeout, deadlock_timeout, access exclusive, for update, for key share, skip locked, nowait, advisory lock

The six index methods and when each one fits, partial and expression indexes, why an Index Only Scan sometimes goes to the table anyway, and how to find the ones nobody uses.

Applies to: PostgreSQL 13+

An index speeds up lookups at the cost of space and of work on every write. What changes when you come from MySQL is not that idea, but that here there are six methods instead of one with exceptions, and that nearly everything that is an index option in MySQL —the prefix, the invisibility— is something else here.

The six methods

Sizes explain the choice better than theory does. On a 200 000-row, 22 MB table, with a timestamp that grows with insertion:

- B-tree on that column: 4,408 kB.
- BRIN on the very same column: 24 kB.

BRIN doesn't store the rows, it stores the minimum and maximum of each block, so it only helps when physical order resembles value order —and when it does, it costs almost nothing—. On the same table, a hash index on the customer column took 7,032 kB and the B-tree on that column 1,400 kB: smaller, and it also serves ranges and sorting. That is why B-tree is the default answer and hash a specific case.

Partial indexes: half the idea, a tenth of the size
An index may carry a WHERE, and then it only indexes the rows that match. If you query the pending queue and pending is 5 %, index the 5 %:

CREATE INDEX idx_pendientes ON pedidos (cliente_id) WHERE estado = 'pendiente';

Measured on that same table: the full index on the column took 1,400 kB and the partial one 88 kB. The query's WHERE has to imply the index's, or the planner won't use it.

No prefix indexes here: expression indexes instead
CREATE INDEX … ON paginas (url(64)) is not valid syntax; the server reads url(64) as a function call and answers 42883 function url(integer) does not exist. The equivalent is to index the expression:

CREATE INDEX idx_url ON paginas (left(url, 64));
CREATE INDEX idx_email ON usuarios (lower(email));

And the small print: the index only kicks in if the query writes the expression the same way. WHERE lower(email) = 'ana@ejemplo.com' uses it; WHERE email ILIKE 'Ana@%' doesn't, and eats the whole table.

Covering indexes, and why they sometimes don't cover
INCLUDE adds columns that are stored in the index but don't sort it:

CREATE INDEX idx_cobertura ON pedidos (cliente_id) INCLUDE (estado);

With that, EXPLAIN shows Index Only Scan. But "only" is half a promise: PostgreSQL cannot tell from the index whether a row is visible to your transaction, so it consults the visibility map, which VACUUM maintains. Measured: right after updating a thousand rows, the same plan said Heap Fetches: 2; after a VACUUM, Heap Fetches: 0. A covering index on a table that is written and not cleaned still goes to the table.

The leftmost-prefix rule is not strict
In MySQL, an index on (A, B) is useless for WHERE B = ?. Here it can help: measured, a query filtering only on the second column resolved with an Index Only Scan over the composite. It is neither magic nor a substitute for the right index —it walks the whole index instead of descending it—, but when the index is much smaller than the table it still pays off. Practical consequence: before creating the "missing" index, look at the plan; one may already be in use.

GIN for what lives inside a field

CREATE INDEX idx_datos ON eventos USING gin (datos);

SELECT count(*) FROM eventos WHERE datos @> '{"tags":["t7"]}';

Without the index that query is a sequential scan; with it, a Bitmap Index Scan. The test GIN took 864 kB over 200 000 rows. For jsonb, if you only query with @>, jsonb_path_ops takes less: 640 kB against the plain GIN's 864, over the same data.

Building without stopping the table
A plain CREATE INDEX takes a SHARE lock: it allows reads and stops writes. CREATE INDEX CONCURRENTLY takes SHARE UPDATE EXCLUSIVE, so it stops nothing, in exchange for two passes over the table and three rules:

CREATE INDEX CONCURRENTLY idx_pedidos_cliente ON pedidos (cliente_id);

-- Did any get left half-built?
SELECT indexrelid::regclass AS indice, indisvalid
  FROM pg_index WHERE NOT indisvalid;

1. It cannot run inside a transaction — 25001 CREATE INDEX CONCURRENTLY cannot run inside a transaction block.
2. If it fails, it leaves an invalid index: nobody uses it, but it is maintained on every write. You have to find it with the query above and drop it.
3. Since PostgreSQL 12 there is REINDEX INDEX CONCURRENTLY, which is how a bloated index is rebuilt without stopping the table.

The indexes nobody uses

SELECT relname AS tabla, indexrelname AS indice, idx_scan,
       pg_size_pretty(pg_relation_size(indexrelid)) AS tamano
  FROM pg_stat_user_indexes
 WHERE idx_scan = 0
 ORDER BY pg_relation_size(indexrelid) DESC;

Two warnings before dropping. First: the counter is not instant. In the test, three queries that used the index left it at 0 immediately and a second later; only after three seconds did it say 3. Second: it counts since the last pg_stat_reset(), and on a replica it counts the replica's own work, so an index used only by the month-end report looks dead the other 29 days.

Recommendation
Every extra index is paid for on every INSERT and on every UPDATE of its columns. The order that works is: look at the plan, create the index with CONCURRENTLY, look at the plan again, and review pg_stat_user_indexes a month later. An index nobody uses is not neutral: it costs writes, space and VACUUM time.

Keywords: index, btree, brin, gin, gist, spgist, hash, partial index, expression index, include, index only scan, heap fetches, concurrently, reindex, pg_stat_user_indexes, indisvalid, jsonb_path_ops, visibility map

Reading a plan and comparing the estimate with what was measured, extended statistics for columns that imply each other, why work_mem is per operation, and what the cache hit rate really measures.

Applies to: PostgreSQL 13+

Diagnosing here means reading a plan and comparing two numbers. Everything else —indexes, memory, statistics— follows from that comparison.

EXPLAIN doesn't execute; EXPLAIN ANALYZE does
The first form only asks for the plan. The second really runs the query in order to measure it, and that includes an UPDATE or a DELETE. If the statement writes, wrap it:

BEGIN;
EXPLAIN (ANALYZE) DELETE FROM pedidos WHERE creado < '2020-01-01';
ROLLBACK;

The two numbers that matter
Every node carries an estimate and a measurement: rows=… is what the planner believed, actual rows=… is what came out. When they drift far apart, the bad plan is a consequence, not the cause.

A measured example, with two columns that imply each other —city and province—:

- Unaided, the planner estimated 11,710 rows and 60,000 came out: it multiplied the two probabilities as if they were independent.
- With extended statistics, the estimate became 59,610.

CREATE STATISTICS st_ciudad_prov (dependencies, ndistinct)
    ON ciudad, provincia FROM pedidos;
ANALYZE pedidos;

That is the tool MySQL doesn't have, and it fixes the whole family of "the plan ignores my index": if the server thinks it will read 4 % of the table when it reads 20 %, it will choose badly for good reasons.

BUFFERS, which you have to ask for

EXPLAIN (ANALYZE, BUFFERS) SELECT … ;

shared hit are blocks that were in memory; shared read, the ones that had to be fetched. And temp read/written is the telling one: the query spilled to disk. Also, track_io_timing is off by default, so I/O times don't appear until you turn it on.

work_mem is per operation, not per connection
This is the setting that surprises the most, and the one most often got wrong. Every sort, every hash join and every hash aggregate may use up to work_mem, and a query with three of those operations —or with two parallel workers— uses a multiple. The factory value is 4 MB.

Measured over 300,000 rows, the same query with ORDER BY:

- With work_mem = 64kB: Sort Method: external merge Disk: 15680kB, and the sort node took ~144 ms.
- With work_mem = 64MB: Sort Method: quicksort Memory: 29627kB, and it took ~70 ms.

The figure that tells the truth is Sort Method. Raising work_mem globally multiplies by connections and by operations; the prudent move is to raise it in the session that needs it:

SET work_mem = '64MB';

Which query costs most: pg_stat_statements
It is the equivalent of the slow query log, but aggregated: one row per query shape, with calls, total time and rows.
In Calíope you can read the same thing without writing the query: the Slow Queries tool shows this summary, tells a missing extension apart from an unloaded library, and sends any row to the editor.

SELECT calls, round(total_exec_time::numeric, 1) AS ms_total,
       round(mean_exec_time::numeric, 2) AS ms_media, rows, query
  FROM pg_stat_statements
 ORDER BY total_exec_time DESC
 LIMIT 20;

The small print: CREATE EXTENSION is not enough. You have to add it to shared_preload_libraries and restart the server; if you only create the extension, the first query answers 55000 pg_stat_statements must be loaded via "shared_preload_libraries". Sort by total_exec_time, not by mean_exec_time: the query that eats your afternoon is usually a fast one executed a million times.

The cache hit rate

SELECT blks_hit, blks_read,
       round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS pct
  FROM pg_stat_database
 WHERE datname = current_database();

This is the number Calíope's Dashboard shows as "Data cache". It measures how many reads were served without going down to the filesystem since the last statistics reset —not how much memory is in use—, and on a small database it comes out very high by definition: in the test, 99.85 %. A sustained low value does mean shared_buffers is too small; a high one doesn't prove that everything is fine.

Parallelism
max_parallel_workers_per_gather is 2 by default, and when the planner uses it a Gather or Gather Merge node appears. Each worker has its own work_mem, which is the other half of the trap above.

Recommendation
The order that works: find the query with pg_stat_statements, look at it with EXPLAIN (ANALYZE, BUFFERS), compare rows with actual rows and only then decide whether an index, statistics or memory is what's missing. Touching shared_buffers before having read a plan is the long way round.

Keywords: performance, explain, analyze, buffers, plan, planner, estimate, create statistics, extended statistics, work_mem, sort method, external merge, pg_stat_statements, shared_preload_libraries, pg_stat_database, cache, track_io_timing, parallelism

Where each parameter is written and which one wins, what needs a restart, why effective_cache_size reserves no memory, and why max_connections is not the thing to raise.

Applies to: PostgreSQL 13+

PostgreSQL has 378 parameters —counted on this server—, and the good news is that you touch a handful. What you have to learn first is not which ones, but where they are written and when they take effect.

Four places, and the server tells you which one won
- postgresql.conf — the usual file, edited by hand.
- postgresql.auto.conf — written by ALTER SYSTEM and not edited by hand; it says so itself on its first line.
- Per database or per role — ALTER DATABASE … SET, ALTER ROLE … SET.
- Per session — SET, which lasts as long as the connection.

Who won is not a guess: the source column of pg_settings says it. Measured: after ALTER DATABASE demo SET work_mem = '32MB', a new connection read 32MB with source = database; after the RESET, 4MB with source = default.

SELECT name, setting, unit, context, source, pending_restart
  FROM pg_settings
 WHERE name IN ('shared_buffers','work_mem','max_connections','max_wal_size');

Three classes of parameter, and the one that hurts
The context column says what it takes to change it:
- user / superuser — a SET in the session is enough (work_mem, effective_cache_size).
- sighup — a reload is needed (checkpoint_timeout, max_wal_size, nearly all of autovacuum).
- postmaster — the server must be restarted. On this server they are 65 out of 378, and among them shared_buffers, max_connections, wal_level, shared_preload_libraries and autovacuum_max_workers.

ALTER SYSTEM SET max_wal_size = '4GB';
SELECT pg_reload_conf();

-- Is anything waiting for a restart?
SELECT name, setting FROM pg_settings WHERE pending_restart;

A measured detail that saves you a scare: pending_restart does not light up at the very instant of the reload. Right afterwards it was still false, and half a second later it said true. Ask for it later, not in the same breath.

shared_buffers and effective_cache_size are not the same, and one of them reserves nothing
- shared_buffers is real memory: the server's own cache. Out of the box it is 128 MB, which is little for any dedicated server; the usual rule is 25 % of RAM.
- effective_cache_size reserves nothing. It is what the planner assumes exists between PostgreSQL's cache and the operating system's, and it only serves to decide whether an index pays off. Changing it doesn't move a byte: it changes plans.

Mixing them up leads to raising effective_cache_size expecting more cache, or raising shared_buffers expecting a different plan.

max_connections is not raised: you put a pool in front
It is 100 out of the box, and here each connection is an operating-system process, not a thread. Raising it to a thousand is not a bigger number: it is a thousand processes, with their memory and their per-operation work_mem. The answer is a connection pooler (pgBouncer and the like). And it is one of those that require a restart.

WAL and checkpoints
max_wal_size (1 GB out of the box) and checkpoint_timeout (5 min) decide how often everything is flushed to disk. If checkpoints fire by size instead of by time, the server writes in bursts; you see it by turning on log_checkpoints and you fix it by raising max_wal_size. checkpoint_completion_target already comes at 0.9, which is what spreads that writing over time instead of concentrating it.

Autovacuum
Here they are autovacuum_naptime 60 s, autovacuum_max_workers 3 —this one requires a restart— and autovacuum_vacuum_scale_factor 0.2, that is, a table is cleaned when 20 % of its rows have changed. On a billion-row table that means waiting for two hundred million dead versions, so large tables carry their own setting:

ALTER TABLE eventos SET (autovacuum_vacuum_scale_factor = 0.01);

synchronous_commit, the only one that changes the promise
Turning it off makes COMMIT not wait for the WAL to reach disk: you gain latency and you risk the last transactions in a power cut —not the integrity of the database, only the most recent commits—. It is user, so it can be turned off only where that trade is accepted:

SET synchronous_commit = off;

Recommendation
Touch few, one at a time, measuring. ALTER SYSTEM instead of editing files —it is recorded and undone with ALTER SYSTEM RESET—, and per database or per role before global: a setting only the nightly report needs shouldn't be paid for by the rest of the day.

Keywords: configuration, postgresql.conf, postgresql.auto.conf, alter system, pg_settings, pending_restart, pg_reload_conf, shared_buffers, effective_cache_size, work_mem, max_connections, pool, wal, checkpoint, autovacuum, synchronous_commit

Why the account doesn't carry the host inside it, roles that are users and groups at once, the trap that granting doesn't reach tomorrow's tables, and why row-level security can be on and have no effect.

Applies to: PostgreSQL 13+

The underlying difference from MySQL is that here the account doesn't carry the host inside it. There is no ana@192.168.1.%: there is the role ana, and where it may connect from and how it authenticates is decided by a separate file, pg_hba.conf.

pg_hba.conf: the first line that matches wins
It is read top to bottom and stops there. And you don't need to open it to see it:

SELECT type, database, user_name, address, auth_method, error
  FROM pg_hba_file_rules
 ORDER BY rule_number;

The error column tells you whether a line is malformed —that is what saves you from the restart where the server doesn't come back—. Changes take effect with SELECT pg_reload_conf(), with no restart. On the test server there were seven rules, the 127.0.0.1 ones with trust and the last one, for everything else, with scram-sha-256: the order is the policy.

A role is a user and a group at the same time
There are not two concepts: CREATE USER is exactly CREATE ROLE … LOGIN. What tells a person from a group is the LOGIN attribute, and nothing else.

CREATE ROLE app_ro;                       -- no LOGIN: acts as a group
CREATE ROLE ana LOGIN PASSWORD 'secreta';
GRANT app_ro TO ana;                      -- ana inherits app_ro's rights

Roles inherit by default, so ana uses app_ro's privileges without doing anything. With NOINHERIT they have to be claimed with SET ROLE, which is what you use when you want the step to be explicit.

Passwords are stored with scram-sha-256, the default since PostgreSQL 14 —the test server confirms it—; md5 still exists and shouldn't be used.

The real trap: granting doesn't reach the future
GRANT … ON ALL TABLES IN SCHEMA grants on the tables that exist today. Measured: after the grant, app_ro could read the existing table and not the one created a minute later. What covers the future is another statement:

GRANT USAGE ON SCHEMA public TO app_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_ro;          -- today's
ALTER DEFAULT PRIVILEGES IN SCHEMA public
      GRANT SELECT ON TABLES TO app_ro;                          -- tomorrow's

And in small print: default privileges belong to whoever declares them, not to the schema, so if the tables are created by another role you have to declare them with FOR ROLE too. What has been declared shows up in pg_default_acl.

Granting also leaves a trail that has to be undone: after an ON ALL TABLES, dropping the role fails with DependentObjectsStillExist and the list of tables where a privilege remained —in the test, even the PostGIS ones showed up—. The counterpart is REVOKE, or DROP OWNED BY role before the DROP ROLE.

The public schema is no longer everybody's
Since PostgreSQL 15, PUBLIC keeps USAGE on schema public but no longer has CREATE. Measured on 17.6: a freshly created role gives USAGE = true and CREATE = false. Anyone bringing scripts from an earlier version will see the first CREATE TABLE of a user who used to be able to fail.

Predefined roles: monitoring without a superuser
The server ships fifteen ready-made roles. The ones that save you an extra superuser:
- pg_read_all_data, pg_write_all_data — read or write everything, with no further powers.
- pg_monitor — see the full statistics views; it includes pg_read_all_stats and pg_read_all_settings.
- pg_signal_backend — cancel queries and close other people's sessions.
- pg_maintain (PostgreSQL 16+) — VACUUM, ANALYZE, REINDEX without being the owner.

Row-level security
A policy filters which rows each role sees, within the same table:

ALTER TABLE pedidos ENABLE ROW LEVEL SECURITY;

CREATE POLICY solo_lo_mio ON pedidos
    FOR SELECT USING (dueno = current_user);

Here is what you have to measure before trusting it. With the same policy and the same table:

- The role the policy points at saw one row. Correct.
- The table's owner —not a superuser— saw both: the owner is not subject to their own policies until ALTER TABLE … FORCE ROW LEVEL SECURITY is declared. With FORCE, they saw one.
- The superuser saw both even with FORCE. Superusers and roles with BYPASSRLS always skip policies.

In other words: an application connecting as the owner of the tables —let alone as a superuser— has row-level security switched on and with no effect. The check is to connect with the real role and count rows.

Recommendation
One role per application, no LOGIN for groups, and none with SUPERUSER except the administration one. ALTER DEFAULT PRIVILEGES in the same commit as the GRANT, or the permission lasts until the next table. And write down what you grant in bulk, because the DROP ROLE a year from now will ask for it.

Keywords: security, role, user, group, pg_hba.conf, pg_hba_file_rules, scram-sha-256, grant, revoke, alter default privileges, pg_default_acl, public, pg_read_all_data, pg_monitor, rls, row level security, create policy, bypassrls, drop owned by

Logical versus physical and what each is for, what pg_dump leaves out and leaves nobody able to log in, why PITR doesn't work out of the box, and which slot can fill your disk.

Applies to: PostgreSQL 13+

There are two kinds of backup and they are not for the same thing. Choosing wrong is discovered on restore day.

Logical backup

-- on the command line, not in the SQL editor:
-- pg_dump -d demo -Fc -f demo.dump
-- pg_restore -d demo_nueva -j 4 demo.dump

The -Fc (custom) format is the one to default to: on the same database, the plain-text dump took 3.1 MB and the custom one 905 kB, and it also carries an index —pg_restore -l listed the thirty data blocks— so it lets you restore one table, and do it in parallel with -j.

What pg_dump doesn't carry, and it is what bites
Roles and cluster-wide settings are not inside. Measured: the database dump had not a single CREATE ROLE, whereas pg_dumpall --globals-only produced the two that existed. Restoring only the dump leaves a perfect database nobody can log into. A complete logical backup is two files:
- pg_dumpall --globals-only — roles, passwords and cluster privileges.
- pg_dump of each database.

pg_dump is consistent —it works on a snapshot— and doesn't block writers; but it takes an ACCESS SHARE lock, so an ALTER TABLE launched at the same time starts waiting, and everything queues behind it.

Physical backup
pg_basebackup copies the entire cluster. Measured on the test server: 84 MB in 1.3 s, with -X stream, which also brings the WAL generated during the copy —without it, the copy is not restorable—. It leaves a backup_label saying from which point in the WAL to replay:

START WAL LOCATION: 0/13000028 (file 000000010000000000000013)

PITR: recovering up to a point in time
It is the reason physical backups exist, and it does not work out of the box: archive_mode comes off, measured on this server. Without archiving, a physical backup restores exactly the moment it was taken and not one second more.

Three pieces are needed:
1. archive_mode = on and an archive_command that copies each WAL segment somewhere safe (or pg_receivewal from another machine).
2. A periodic pg_basebackup.
3. On restore: the backup's files, a restore_command that fetches the segments, recovery_target_time = '…' and an empty recovery.signal file in the data directory.

That last point is what throws people coming from old versions: since PostgreSQL 12 there is no recovery.conf; the parameters go in postgresql.conf and what declares "this is a recovery" is the signal file.

Replication slots are a double-edged knife
A slot guarantees the server does not delete WAL a consumer hasn't read. If the consumer disappears and the slot stays, WAL piles up until the disk fills —and a full disk is an outage, not a warning—. Watch them like this:

SELECT slot_name, active, wal_status,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retenido
  FROM pg_replication_slots;

max_slot_wal_keep_size sets the cap: past it, the server prefers to invalidate the slot rather than run out of disk.

Calíope's backup is logical
What the backup tool produces is SQL —CREATE and INSERT—, of the pg_dump family, not of pg_basebackup. It is good for migrating and for recovering data; recovering a whole server to a point in time needs the above, which is a matter for the operating system and not for a client.

Recommendation
The two files of the logical backup, always together —--globals-only and the dump— and test the restore, not the backup: a file that is generated without error may fail to restore, and the only way to know is to restore it on a real server.

Keywords: backup, pg_dump, pg_restore, pg_dumpall, globals, pg_basebackup, wal, archive_mode, archive_command, pitr, recovery_target_time, recovery.signal, replication slot, wal_status, restore

DDL is transactional, what costs is the lock and not the ALTER, which changes rewrite the whole table, and the NOT VALID + VALIDATE pattern that avoids stopping the database.

Applies to: PostgreSQL 13+

Here DDL is transactional. That changes how migrations are written and is the first thing to take in when coming from MySQL, where every ALTER commits on its own.

BEGIN;
ALTER TABLE pedidos ADD COLUMN moneda text;
CREATE INDEX idx_moneda ON pedidos (moneda);
CREATE TABLE monedas (codigo text PRIMARY KEY);
ROLLBACK;

Verified: after that ROLLBACK none of the three things remained. A migration that fails halfway leaves no half a database; and that is why the healthy pattern is to put the whole migration inside a transaction.

The exceptions are countable on one hand: CREATE INDEX CONCURRENTLY, VACUUM and ALTER SYSTEM cannot go inside a transaction.

What costs is not the ALTER: it is the lock
Almost every ALTER TABLE takes ACCESS EXCLUSIVE, which conflicts even with a SELECT. Even if the change takes one millisecond, waiting for the lock can take hours —and while it waits, everything arriving behind it queues up—. That is why DDL in production is always issued like this:

SET lock_timeout = '3s';
ALTER TABLE pedidos ADD COLUMN moneda text;

If it doesn't get the lock, it fails in three seconds and is retried. Without that, a one-millisecond migration can stop the whole application.

What rewrites the table and what doesn't
Rewriting means copying the whole table: it takes time proportional to size and needs twice the disk while it lasts. Measured over 500,000 rows and 32 MB:

The rule that sums up the table: widening is free and narrowing rewrites. And ADD COLUMN with a default stopped rewriting in PostgreSQL 11: the old detour of adding the column empty and filling it in batches is no longer needed.

Two warnings that the timings don't show:
- A DROP COLUMN is instant because it only marks the column as dropped: the space does not come back until the table is rewritten.
- SET NOT NULL and a plain CHECK don't rewrite, but they scan the whole table with the lock held. On a big table that is already an outage.

The pattern that avoids stopping the database: NOT VALID then VALIDATE
A constraint can be added in two steps: first it is declared without checking what is already there —instantly—, and then it is validated, which is the slow part but with a far weaker lock.

ALTER TABLE ddl_hija
    ADD CONSTRAINT fk_p FOREIGN KEY (padre) REFERENCES ddl_demo (id) NOT VALID;

ALTER TABLE ddl_hija VALIDATE CONSTRAINT fk_p;

Measured: the NOT VALID declaration took 0.7 ms with SHARE ROW EXCLUSIVE —which allows reads—, and the validation 67 ms with SHARE UPDATE EXCLUSIVE, which doesn't even block writers. Doing it in one go cost the same in time, but with the strong lock held throughout. On a real table, that difference is what separates a deployment from an outage.

From the moment the constraint is NOT VALID, the server enforces it on new rows; all that is pending is checking the old ones.

Indexes
A plain CREATE INDEX stops writes; CREATE INDEX CONCURRENTLY stops nothing, but it doesn't fit inside the migration's transaction, so it goes in its own step and is checked afterwards (pg_index.indisvalid).

Recommendation
lock_timeout always; the migration inside a transaction except for what can't be; NOT VALID + VALIDATE for constraints on big tables; and beware of type changes, which is where the rewrite hides. If a type has to be narrowed, it is almost always better to add the new column, copy in batches and rename.

Keywords: ddl, alter table, migration, transactional, rollback, lock_timeout, access exclusive, rewrite, relfilenode, add column, drop column, set not null, not valid, validate constraint, create index concurrently

What doesn't exist and raises a syntax error, why text is no worse than varchar, numeric versus floating point, what timestamptz really stores, and why jsonb doesn't save space.

Applies to: PostgreSQL 13+

Types are one of the few places where migrating from MySQL fails on the first try, and just as well: what doesn't exist raises a syntax error instead of being half-accepted.

What doesn't exist here
- UNSIGNED — 42601 syntax error at or near "unsigned". There are no unsigned integers; use the next type up or a CHECK (n >= 0).
- INT(11) — also 42601. MySQL's display width doesn't exist, and never meant what it looked like.
- TINYINT, DATETIME, DOUBLE with parentheses, and MySQL's SET/ENUM types. Their equivalents are smallint, timestamptz, double precision and a proper enum type.

Text: use text and be done
Measured, with the same value 'hola': text took 5 bytes, varchar(50) 5 and char(50) 51. All three are stored the same way; varchar(n) only adds a length check and char(n) pads with spaces. And those spaces change comparisons: 'x' = 'x ' is false for text and true for char.

Here text is not worse than varchar: there is no penalty. Use varchar(n) when the limit is a business rule, and char(n) almost never.

Numbers: numeric for money, and it isn't superstition

SELECT (0.1::float8 + 0.2::float8) = 0.3::float8,   -- false
       (0.1::numeric + 0.2::numeric) = 0.3::numeric; -- true

Measured: in floating point, 0.1 * 3 gave 0.30000000000000004; in numeric, an exact 0.3. numeric is exact and of arbitrary precision, and you pay for it in space and speed —10 bytes against float8's 8 for that value, and arithmetic in software—. For money and for any figure you add up in front of a customer, numeric.

Measured sizes: int 4, bigint 8, boolean 1, uuid 16 —against the 36 it would take as text—.

Dates: timestamptz nearly always
timestamp and timestamptz take the same 8 bytes. The difference is neither the size nor that one stores the zone: neither stores the zone. timestamptz stores an instant —it converts to UTC on the way in and to the session's zone on the way out—, and timestamp stores a clock reading and nothing else.

Measured, the same instant with two session zones:

It is the same moment said two ways. With timestamp there is no conversion at all: what went in is what comes out, and anyone who needs to know what time it really was cannot find out. date takes 4 bytes and interval 16.

json versus jsonb: nearly always jsonb, and not for size

SELECT '{"b":1,"a":2,"a":3}'::json::text,   -- {"b":1,"a":2,"a":3}
       '{"b":1,"a":2,"a":3}'::jsonb::text;  -- {"a": 3, "b": 1}

json stores the text as it came: it keeps the order, the whitespace and even duplicate keys. jsonb stores an already parsed tree: it sorts the keys, keeps the last duplicate and normalises whitespace. That is why jsonb is fast to query and can be indexed with GIN, and json is only right when you have to return the document byte for byte as it arrived.

What is not true is that jsonb saves space: measured over 200,000 identical documents, json took 14 MB and jsonb 16 MB. You choose jsonb for how it is queried, not for what it weighs.

Arrays
An array is a first-class type, with its operators —@> for containment, array_length— and its GIN index. It is convenient for tags and short lists; it stops being convenient as soon as the elements need attributes of their own or have to be joined to another table. An array is not a table you saved: it is a value.

serial or IDENTITY
serial is not a type: it is sugar that creates a sequence and sets its nextval as the default. GENERATED ALWAYS AS IDENTITY is the standard one and it also protects the column: trying to insert a value by hand answered 428C9 cannot insert a non-DEFAULT value into column. For new tables, IDENTITY.

Recommendation
text for text, numeric for money, timestamptz for instants, jsonb for documents you query and IDENTITY for keys. And when migrating from MySQL, let the syntax error do its job: it beats a type that is accepted and means something else.

Keywords: types, text, varchar, char, numeric, float, decimal, timestamptz, timestamp, time zone, json, jsonb, array, uuid, serial, identity, unsigned, enum

The codes you see daily, why you program against the class and not the code, what the DETAIL and HINT nobody shows you actually say, and where the code is when the connection never even opens.

Applies to: PostgreSQL 13+

There are no error numbers here. There is SQLSTATE: five characters, of which the first two are the class. And the class is what you program against: it says what to do without knowing exactly what failed.

The ones you see every day

The classes, which are what you should look at

The practical consequence: an application retries class 40 and does not retry class 42. And if the retry doesn't tell them apart, either a legitimate transaction is lost or a query that will never work is repeated a thousand times.

The message has three parts, and the third is the useful one
MESSAGE says what happened, DETAIL gives the row or the value, and HINT says what to do. Measured:

- 23505 — MESSAGE: duplicate key value violates unique constraint "er_d_pkey"; DETAIL: Key (id)=(1) already exists.
- 42883 — MESSAGE: operator does not exist: text = integer; HINT: No operator matches the given name and argument types. You might need to add explicit type casts.
- 42703 — MESSAGE: column "ids" does not exist; HINT: Perhaps you meant to reference the column "er_d.id".

A client that only shows the MESSAGE throws away half the information —and exactly the half that says how to get out—. Calíope composes all three.

Besides, the error carries separate fields: the table, the column and the constraint's name. With 23505 came constraint = er_d_pkey, which is what lets you turn it into "that email is already registered" without parsing the message text.

An error aborts the transaction
After any error inside a BEGIN, everything that follows answers 25P02 until you ROLLBACK. It is not a client bug: it is the design, and the graceful way out is savepoints.

Connection errors don't arrive in the response
If the role or the database doesn't exist, or the password is wrong, the connection never opens: the client only sees "connection failed". The code is in the server log, and only if you ask for it:

ALTER SYSTEM SET log_error_verbosity = 'verbose';
SELECT pg_reload_conf();

With that, the log went from FATAL: database "no_existe" does not exist to FATAL: 3D000: database "no_existe" does not exist —and 28000 for the non-existent role—. That is the difference between guessing and knowing when somebody reports they "can't connect".

Recommendation
In application code, branch on the class and use the full code only for the messages the user sees (23505 → "already exists"). Always store the SQLSTATE in your own log: the message text changes with the server's language, the code doesn't.

Keywords: error, sqlstate, code, class, 23505, 23503, 42p01, 42601, 42883, 42501, 25p02, 40001, 40p01, 55p03, 57014, 3d000, 28000, detail, hint, retry, log_error_verbosity

Declarative partitioning and what the planner really prunes, why there are no global indexes nor uniqueness on a single column, the CHECK that turns a 68 ms ATTACH into half a millisecond, and what the default partition costs.

Applies to: PostgreSQL 13+

Partitioning here is declarative: you declare the key and each partition is a real table. The parent holds not one row —measured: 0 bytes, with the data spread across the children—.

CREATE TABLE pt (id bigserial, creado date NOT NULL, importe numeric)
    PARTITION BY RANGE (creado);

CREATE TABLE pt_2024 PARTITION OF pt
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

CREATE TABLE pt_resto PARTITION OF pt DEFAULT;

There are three forms: RANGE (dates, amounts), LIST (country, status) and HASH (spreading for spreading's sake).

Pruning is the point
Measured over 300,000 rows across three years: a query with WHERE creado BETWEEN '2024-03-01' AND '2024-03-31' scanned only pt_2024. The same table, filtering on a column that is not the key, did a Parallel Append over all of them.

That is the rule that decides the design: the partition key is the column you almost always filter by. If the queries don't mention it, partitioning doesn't save reading: it spreads it out.

What doesn't exist: global indexes
An index created on the parent creates one per partition —measured: four partitions, four indexes—. There is no single index spanning the whole table, and from that comes the limitation you need to know before designing:

ALTER TABLE pt ADD CONSTRAINT pt_uni UNIQUE (id, creado);

A UNIQUE on (id) alone is rejected with 0A000 unique constraint on partitioned table must include all partitioning columns. Global uniqueness of an identifier cannot be guaranteed with declarative partitioning; you get it from a sequence, which is unique by construction, not by constraint.

ATTACH: the difference between 68 ms and half a millisecond
Attaching an existing table forces the server to check that all its rows fit the range. Measured over 300,000 rows:

In other words: if the table already carries a CHECK constraint implying the range, the server skips the scan. On a billion-row table that is the difference between an instant ACCESS EXCLUSIVE and half an hour of it.

The default partition isn't free
DEFAULT catches whatever falls outside every range, and avoids the error when an unexpected date is inserted. In exchange, measured: ALTER TABLE … DETACH PARTITION … CONCURRENTLY answered 55000 cannot detach partitions concurrently when a default partition exists. And besides, every new ATTACH has to scan the default partition to check it isn't hiding rows of the incoming range.

Why you really partition
Not for query speed —that's what indexes are for—, but for maintenance:
- Removing a whole period is a DROP TABLE of its partition: measured, 2.3 ms, and it returns the space to the filesystem. The equivalent DELETE took longer and, above all, leaves dead rows for VACUUM to clean and space that doesn't come back.
- VACUUM and ANALYZE work per partition, so maintenance work stops growing with the whole history.
- Old data can be detached and archived without touching the live table.

Recommendation
Partition by what you are going to delete, not by what you are going to query; and check that your queries carry the key in the WHERE by looking at the plan, not by assuming. Before partitioning an existing table, check whether what you actually need is an index: partitioning adds moving parts in exchange for cheaper maintenance, and that trade only pays off past a certain size.

Keywords: partitioning, partition, range, list, hash, pruning, attach, detach, default, global index, unique, 0a000, drop partition, maintenance

Why the utf8 trap doesn't exist here, how encoding and collation differ, how the ordering changes with each, and why a prefix LIKE doesn't use your index.

Applies to: PostgreSQL 13+

The trap that cost so many MySQL migrations doesn't exist here: there is no utf8 that wasn't UTF-8. The encoding is declared when the database is created, and UTF8 is the whole of UTF-8.

Measured, storing four strings in an ordinary text column:

Nothing special had to be declared. length() counts characters and octet_length() counts bytes, which is the distinction you had to chase type by type in MySQL.

Encoding and collation are two different things
- Encoding — how the bytes are stored. It belongs to the database, is fixed at creation and cannot be changed afterwards: changing it means dumping and recreating.
- Collation — how things are sorted and compared. It can be set per database, per column, per expression and even in an ORDER BY.

On the test server: server_encoding = UTF8, and the databases with en_US.utf8 from the libc provider. There are 815 collations available, from two providers: the system's (C, POSIX, en_US.utf8) and ICU's (es-ES-x-icu, unicode), which since PostgreSQL 15 can even be a database's default provider.

What changes with the collation

SELECT array(SELECT s FROM (VALUES ('a'),('B'),('á'),('b'),('A')) v(s) ORDER BY s COLLATE "C"),
       array(SELECT s FROM (VALUES ('a'),('B'),('á'),('b'),('A')) v(s) ORDER BY s COLLATE "es-ES-x-icu");

Measured, the results don't resemble each other:

- C — A, B, a, b, á. It sorts by the character's number: all uppercase before lowercase, and accents last.
- es-ES-x-icu — a, A, á, b, B. It sorts like a dictionary.

And comparison changes with it: 'a' < 'B' is false with C and true with the Spanish collation. A list that "comes out sorted wrong" is almost never an application bug: it's the column's collation.

The collation decides whether an index is any use for LIKE
This is the practical detail that is hardest to find on your own. With a linguistic collation —the database's—, an ordinary B-tree index is no use for prefix searches. Measured over 200,000 rows:

- WHERE s = 'usuario42' → Index Only Scan.
- WHERE s LIKE 'usuario42%' → Seq Scan, with the index sitting right there.
- After creating the index with the right operator class, the same query became a Bitmap Index Scan.

CREATE INDEX idx_prefijo ON ch_like (s text_pattern_ops);

text_pattern_ops compares byte by byte, which is exactly what LIKE 'something%' needs. With the C collation on the column it isn't needed, because that is how it already compares.

Collations have a version, and it matters
The database records the version of the collation its indexes were built with —measured: datcollversion = 2.36, the system library's—. If the operating system is upgraded and that version changes, the order can change, and an index built with the previous order stops being correct: searches that don't find rows that are there. PostgreSQL warns about the mismatch, and the answer is REINDEX.

That is why many people choose the C collation or ICU for databases that have to survive system upgrades: ICU carries its own version and doesn't depend on the system's.

Recommendation
UTF8 always. The collation is decided when the database is created, because changing it later is expensive: C for columns that are codes, identifiers or paths —it sorts fast and works with LIKE—, and a linguistic collation for what a person reads. And if a LIKE 'x%' query doesn't use the index, look at the collation before touching the query.

Keywords: encoding, utf8, collation, collate, icu, libc, text_pattern_ops, like, prefix, order by, datcollversion, reindex, octet_length

The ones that really bite —63-byte names, 1,600 columns, 32 per index—, why the identifier one raises a notice and not an error, and what TOAST does with a value that doesn't fit the page.

Applies to: PostgreSQL 13+

PostgreSQL's limits don't resemble InnoDB's, and the ones that bite daily are not the big ones.

The ones you actually hit

The first three are measured: 1,600 columns were created without trouble and 1,601 failed; a 32-column index was created and the 33-column one wasn't.

The identifier one is the only one that raises no error
A 72-character name was stored as a 63-character one, and the server said so with a notice:

identifier "t_aaa…" will be truncated to "t_aaa…"

A notice is not an error: the statement carried on. That is why two long names that only differ from character 64 onwards end up being the same object, and the failure shows up much later. Name generators —indexes, constraints, per-batch temporary tables— are what run into this, not anybody's hand.

The big ones, which are almost never the problem
They are not measured here —doing so would mean filling a disk— and come with their official figures:

- Maximum table size: 32 TB.
- Maximum field size: 1 GB.
- Maximum row size: 1.6 TB.
- Rows per table: no defined limit.
- Databases per cluster and tables per database: no practical limit.

What runs out long before any of these is maintenance: VACUUM, backups and index rebuilds over a table of terabytes.

TOAST: why a 1 GB text doesn't break the 8 kB page
A row has to fit in a page, and a page is 8 kB. Large values are compressed and moved out to a side table —what they call TOAST— automatically and with nothing to declare.

Measured, storing 100,000 bytes of text in a text column:

- octet_length — 100,000 bytes of data.
- pg_column_size — 1,156 bytes, so it was compressed.
- The table took 8,192 bytes and 16 kB counting its TOAST.

From which a practical consequence follows: a SELECT * on a table with large columns pays to read those columns even if nobody looks at them. Asking only for the columns you need isn't style, it's I/O.

The limits that are configured
These belong to the instance rather than the engine, which is why they show up in pg_settings: max_connections (100 out of the box), max_locks_per_transaction (64), max_wal_size, work_mem. Those are the ones that run out on a real server; the ones in the table above, almost never.

Recommendation
Watch only two: the 63 bytes one whenever something generates names, and columns per index whenever somebody proposes a composite index with half the schema in it. About the rest you find out from pg_settings, not from the documentation.

Keywords: limits, identifier, 63 bytes, truncated, 1600 columns, 32 columns, index, toast, page, 8 kB, 32 tb, 1 gb, pg_settings, max_connections

When to put the data inside the document and when to reference it: MongoDB's four patterns, the unbounded-array antipattern and what each one costs, measured.

Applies to: MongoDB 7.0+

In SQL the schema follows normalisation: each fact in one place, and queries put it back together with JOIN. In MongoDB it follows the access pattern: what is read together is stored together. The question is no longer "how do I avoid repeating a value?" but "what do I want a single read to give me?".

Embedding vs. referencing

An order can carry its lines inside it, or the lines can live in their own collection pointing back at the order.

// Embebido: el pedido lleva sus líneas dentro
db.p_emb.insertOne({ _id: 1, cliente: 7, fecha: new Date(),
  lineas: [ { sku: "A-7", cantidad: 2, precio: 19.9 } ] })
db.p_emb.findOne({ _id: 1 })

// Referencia: las líneas viven aparte y apuntan al pedido
db.p_ref.insertOne({ _id: 1, cliente: 7, fecha: new Date() })
db.l_ref.insertOne({ pedido: 1, sku: "A-7", cantidad: 2, precio: 19.9 })
db.l_ref.createIndex({ pedido: 1 })
db.p_ref.aggregate([ { $match: { _id: 1 } },
  { $lookup: { from: "l_ref", localField: "_id",
               foreignField: "pedido", as: "lineas" } } ])

Measured against MongoDB 8.2 with 100,000 three-line orders: reading one order with its lines costs 0.25 ms embedded and 0.30 ms with $lookup —but 67 ms if the index on l_ref.pedido is missing, because then every order walks all 300,000 lines—. On disk the embedded orders take 2.9 MB against 1.1 MB + 5.2 MB for the two separate collections: here embedding came out cheaper in space as well.

Embedding does not give up indexes: an index on a field inside the array is multikey and works just the same. Looking for {"lineas.sku": "A-7"} without one is a COLLSCAN over 100,000 documents in 42 ms; with it, 2,000 examined in 2 ms, for the same 2,000 rows. And a document is modified atomically without a transaction: the $inc of a counter and the $set of a status in a single updateOne either both land or neither does.

The antipattern: the array that never stops growing

db.sensor.insertOne({ _id: 1, lecturas: [] })
const tanda = Array.from({ length: 10000 }, (_, i) => ({ t: new Date(), v: i }))
db.sensor.updateOne({ _id: 1 }, { $push: { lecturas: { $each: tanda } } })
db.sensor.stats().avgObjSize   // 288 919 bytes tras 10 000 lecturas

The empty document is 29 bytes and each reading adds 28.9. At 540,000 it measures 16,628,919 bytes and the next batch fails with code 10334: "Resulting document after update is larger than 16777216". The 16 MB per-document ceiling is not negotiable. And it hurts before you reach it: a $set of a scalar field costs 3.15 ms on that document and 0.50 ms on a 228-byte one, and a $pop of the array 71.95 ms against the 0.15 ms of inserting a loose reading. An array that grows with no known ceiling is a reference in the wrong place.

Bucket: the readings are grouped into documents of N.

const lote = Array.from({ length: 200 }, (_, i) => ({ t: new Date(), v: i }))
db.cubos.insertOne({ sensor: 1, desde: new Date(), n: 200, lecturas: lote })
db.cubos.createIndex({ sensor: 1, desde: -1 })

The same 540,000 readings: loose they are 540,000 documents, 6.5 MB of data and 8.4 MB of index; in buckets of 200 they are 2,700 documents, 4.2 MB of data and 82 KB of index. Inserting them costs 203 ms in buckets against 901 ms loose. Reading the last one: 0.20 ms from the bucket, 0.30 ms loose and 46.80 ms from the embedded array, which has to be fetched whole.

Extended reference: copy into the child the handful of parent fields that are always on screen. Listing 100 orders with their customer's name costs 0.35 ms with the name duplicated inside and 1.00 ms with $lookup. You pay on write: renaming a customer means touching their 1,000 orders, 2 ms with an index on cliente. Duplicate what almost never changes.

Computed value: store the total already summed instead of recomputing it on every read. On a single order you cannot tell —0.35 ms read from the document, 0.30 ms summed on the fly—; aggregating all 100,000 you can: 14 ms reading the field against 129 ms recomputing.

Subset: inside the document only the few rows that are shown, the rest in their own collection. 500 products with 500 reviews each: with all of them embedded the average document is 80,738 bytes; with the last five and a counter, 889 bytes, and the collection drops from 6.1 MB to 60 KB. The product page goes from 0.55 ms to 0.25 ms, and the page of 20 reviews is fetched separately, from its own collection.

The rule, in one line: embed what is read with its parent, belongs to it alone and has a ceiling; reference what grows without limit, is shared between several parents or is queried on its own.

Keywords: modelling, embedding, reference, document, pattern, bucket, subset, extended reference, computed value, array, schema, 16 MB

Which type MongoDB stores with each value, how much it takes, how different types compare with one another and how accented text is sorted.

Applies to: MongoDB 7.0+

In SQL the table declares the type and every row obeys it. In MongoDB the type travels with each value: there is no CREATE TABLE, and two documents in the same collection can hold an integer and a string in the same field. The format is called BSON, a binary extension of JSON with the types JSON lacks: 32-bit and 64-bit integers, exact decimals, dates, binaries and ObjectId.

_id and ObjectId

Every document has an _id: unique, immutable and indexed from the moment the collection is born. If you don't write it yourself, the client puts an ObjectId there: 12 bytes, of which 4 are the time in seconds, 5 are random per process and 3 are a counter. It grows with the clock, so it works for date ranges without storing any date at all.

const oid = ObjectId()
oid.getTimestamp()            // 2026-09-12T01:28:05.000Z
oid.toString().slice(0, 8)    // "6aa4aaa5": los 4 bytes de tiempo

// Lo creado desde el 1 de septiembre, por el índice de _id
const desde = ObjectId.createFromTime(Date.parse("2026-09-01") / 1000)
db.pedidos.countDocuments({ _id: { $gte: desde } })

Storing it as a string costs more and buys nothing: {_id: ObjectId()} weighs 22 bytes, and the same value as a string, 39.

Integers, doubles and decimals

There are four numeric types, and the difference shows in the document. $bsonSize over {_id: 1, a: …} gives 21 bytes with int, 25 with long or with double, and 33 with decimal. Money goes in decimal for the same reason it goes in DECIMAL in SQL: adding 0.1 and 0.2 as doubles gives 0.30000000000000004, and as decimals it gives 0.3.

The trap is on the client side. mongosh picks the type by looking at the value, so a hand-written 7 is stored as an int, 3000000000 as a double, and 9007199254740993 is stored as 9007199254740992: past 2⁵³ you have to write NumberLong("…").

db.tipos.insertMany([
  { v: 7 }, { v: 7.5 }, { v: 3000000000 },
  { v: 9007199254740993 }, { v: NumberLong("9007199254740993") }
])
db.tipos.aggregate([ { $project: { v: 1, t: { $type: "$v" } } } ])

For querying, though, the four are a single number: with an int, a long, a double and a decimal all worth 7 in the collection, {v: 7} finds all four, and {v: "7"} finds none. $type does tell them apart, and "number" groups them again.

Dates

Date is 8 bytes of milliseconds since 1970, always in UTC and with no time zone. The DATETIME / TIMESTAMP pair does not exist here: there is a single type, the zone is applied by whoever reads, and microseconds are lost on the way in. BSON's Timestamp is not for your data — it is replication's internal clock.

db.eventos.insertOne({ _id: 1, d: new Date("2026-09-11T21:05:00.123Z") })
db.eventos.aggregate([ { $project: {
  utc: { $hour: "$d" },
  mx:  { $hour: { date: "$d", timezone: "America/Mexico_City" } }
} } ])                        // utc 21 · mx 15

Binaries and UUIDs

BinData stores bytes with a subtype, and UUID() is subtype 4. A UUID as BinData takes 29 bytes of document; the same one written as a hyphenated string, 49.

Ordering across different types

A sort over a field with mixed types does not fail: there is a total order between types, and measured over fourteen documents it is this one.

MinKey → missing field and null → numbers → strings → objects → binaries → ObjectId → booleans → dates → Timestamp → regular expressions → MaxKey.

Arrays are not on that list because an array compares by its smallest element: [9, 10] sorts among the numbers. And a missing field sorts exactly like null, to the point that {v: null} finds both; to tell them apart you use {v: {$type: "null"}} and {v: {$exists: false}}.

UTF-8 and collation

There is no character set to choose. BSON strings are UTF-8, full stop: "café ☕ 日本語 👩‍💻" is 14 characters and 31 bytes, and comes back exactly as it went in.

What you do choose is the collation, and by default it is binary: without collation, cafe and café are different values, and Árbol sorts after zorro. A collation with its locale and its strength — 1 ignores accents and case, 2 ignores case only, 3 tells everything apart — changes comparison and ordering at once.

db.clientes.find({ n: "cafe" }).collation({ locale: "es", strength: 1 })
db.clientes.find().sort({ n: 1 }).collation({ locale: "es" })

And here is the same trap as in SQL: the query's collation has to be the index's collation. With a plain index on n, the query {n: "cafe"} is an IXSCAN that examines 1 document; the same query with collation falls to a COLLSCAN and examines all 7. The fix is not writing collation on every query — it is giving it to the collection, so its indexes are born with it.

db.createCollection("clientes", { collation: { locale: "es", strength: 1 } })
db.clientes.createIndex({ n: 1 }, { unique: true })
db.clientes.insertOne({ n: "cafe" })
db.clientes.insertOne({ n: "CAFÉ" })   // E11000: aquí es el mismo valor

Two more warnings, both measured. $regex ignores collation: /^CAF/ does not find cafe even with strength: 1, while {n: "CAFE"} does find it. And numericOrdering: true makes the strings "1", "2" and "10" sort as numbers instead of "1", "10", "2".

Keywords: bson, types, objectid, decimal128, numberlong, date, uuid, bindata, collation, utf-8, accents, sorting

MongoDB's eleven kinds of index and when each one fits, the ESR rule for ordering a compound index, the covered query, plus the ceilings and the write cost, all measured with `explain`.

Applies to: MongoDB 7.0+

An index is a B-tree over the value of a field, and the number that tells you whether it helps comes from explain("executionStats"): totalDocsExamined against nReturned. If the first is much larger than the second, the server is reading documents only to throw them away.

const est = ["nuevo", "pagado", "enviado", "cerrado"]
const docs = []
for (let i = 0; i < 20000; i++) docs.push({
  cliente: i % 499, estado: est[i % 4], total: (i % 997) + 0.5,
  fecha: new Date(Date.UTC(2026, 0, 1 + (i % 240)))
})
db.pedidos.insertMany(docs)
const busca = { cliente: 42, estado: "pagado" }

db.pedidos.find(busca).explain("executionStats")   // COLLSCAN · 10 / 20000

db.pedidos.createIndex({ cliente: 1 })
db.pedidos.find(busca).explain("executionStats")   // IXSCAN · 10 / 40

db.pedidos.createIndex({ estado: 1, cliente: 1, fecha: -1 })
db.pedidos.find(busca).explain("executionStats")   // IXSCAN · 10 / 10

The ESR rule

A compound index is walked by prefixes, so the order of its fields is the decision. Equality fields first (E), then the sort field (S), and the range field (R) last. With the range before the sort, the index filters but does not order, and a SORT stage appears that sorts in memory: 1,826 documents in the query below.

const q = { estado: "pagado", fecha: { $gte: new Date("2026-06-01") } }

db.pedidos.createIndex({ estado: 1, fecha: 1, total: 1 })          // E-R-S
db.pedidos.find(q).sort({ total: 1 }).hint("estado_1_fecha_1_total_1").explain()
// FETCH <- SORT <- IXSCAN

db.pedidos.createIndex({ estado: 1, total: 1, fecha: 1 })          // E-S-R
db.pedidos.find(q).sort({ total: 1 }).hint("estado_1_total_1_fecha_1").explain()
// FETCH <- IXSCAN

That stage has a ceiling: internalQueryMaxBlockingSortMemoryUsageBytes is 104,857,600 bytes, and past it the query fails unless it is allowed to use disk.

Covered query

If the index carries every field the query reads, the server never touches the documents. Mind _id: it is in the projection by default, it is not in the index, and it has to be removed by hand.

db.pedidos.find({ estado: "pagado" }, { _id: 0, estado: 1, total: 1 })
  .hint("estado_1_total_1_fecha_1").explain("executionStats")
// PROJECTION_COVERED · nReturned 5000 · totalDocsExamined 0

db.pedidos.find({ estado: "pagado" }, { estado: 1, total: 1 })
  .hint("estado_1_total_1_fecha_1").explain("executionStats")
// totalDocsExamined 5000

The other kinds

The two that lose data silently are best seen together:

db.socios.insertMany([{ a: 1 }, { a: 2 }, { b: 9 }, { b: 8 }])
db.socios.createIndex({ a: 1 }, { sparse: true })
db.socios.find().sort({ a: 1 }).hint("a_1")   // 2 / 4

db.correos.createIndex({ correo: 1 }, { unique: true })
db.correos.insertOne({ otro: 1 })
db.correos.insertOne({ otro: 2 })   // E11000 · dup key: { correo: null }

Hidden is the opposite: it is still maintained, but the planner does not look at it. With hidden: true the same query was a COLLSCAN over 20,000 documents; back to visible with collMod, an IXSCAN over 1,940.

The ceilings

for (let i = 0; i < 70; i++) db.tope.createIndex({ ["c" + i]: 1 })
// 67 CannotCreateIndex · add index fails, too many indexes

const k = {}
for (let i = 0; i < 33; i++) k["g" + i] = 1
db.comp.createIndex(k)   // 13103 · too many compound keys

An index name and a key size have no practical ceiling: 400 characters and 2,000 bytes were accepted without complaint.

The price

Every index is paid for on every write. The same 20,000 inserts took 60 ms with no index, 101 ms with five and 160 ms with ten. And they take room: the six indexes on pedidos add up to 1.9 MB against 916 KB of data.

That is why you do not index everything. A low-cardinality field rarely helps: estado, with four distinct values, examines 5,000 documents to return 5,000. And a simple index is redundant when a compound one already starts with it: { cliente: 1 } and { cliente: 1, fecha: 1 } examine the same 40.

Keywords: index, indexes, ESR, compound, multikey, text, 2dsphere, hashed, wildcard, TTL, partial, sparse, unique, hidden, covered query, explain, IXSCAN, COLLSCAN, totalDocsExamined, MongoDB

The pipeline stages and the order to put them in, $lookup as the JOIN and what it costs without an index, $graphLookup, window functions, $facet and $unionWith, and $merge versus $out.

Applies to: MongoDB 7.0+

A pipeline is a list of stages, and each one receives the documents the previous one produced. The order is yours to write, and nearly all of the performance lives there.

const est = ["nuevo", "pagado", "enviado", "cerrado"]
const docs = []
for (let i = 0; i < 20000; i++) docs.push({
  cliente: i % 499, estado: est[i % 4], total: (i % 997) + 0.5,
  fecha: new Date(Date.UTC(2026, 0, 1 + (i % 240)))
})
db.pedidos.insertMany(docs)

db.pedidos.aggregate([
  { $match: { estado: "pagado" } },
  { $group: { _id: "$cliente", gastado: { $sum: "$total" }, pedidos: { $sum: 1 } } },
  { $match: { pedidos: { $gte: 11 } } },
  { $sort: { gastado: -1 } },
  { $limit: 3 }
])
// { _id: 37, gastado: 522.5, pedidos: 11 } y dos mas

Filter first, always

Only the first stage can use an index. With an index on {estado: 1} over those 20,000 orders, $match up front examines 5,000 keys and 5,000 documents; the same filter behind the $group, no keys at all and 20,000 documents.

$lookup is the JOIN, and without an index you pay for it

It joins another collection of the same database, and what it finds arrives as an array that is almost always opened with $unwind.

const cl = []
for (let i = 0; i < 499; i++)
  cl.push({ num: i, nombre: "Cliente " + i, pais: ["MX", "ES", "AR", "CO"][i % 4] })
db.clientes.insertMany(cl)

const pais = [
  { $match: { estado: "pagado" } },
  { $lookup: { from: "clientes", localField: "cliente", foreignField: "num", as: "c" } },
  { $unwind: "$c" },
  { $group: { _id: "$c.pais", gastado: { $sum: "$total" } } }
]
db.pedidos.explain("executionStats").aggregate(pais)
// collectionScans 5001 - totalDocsExamined 2495499 - indexesUsed []

db.clientes.createIndex({ num: 1 })
db.pedidos.explain("executionStats").aggregate(pais)
// collectionScans 0 - totalDocsExamined 5001 - indexesUsed num_1

The stage's own explain leaves no room for doubt: without an index on clientes.num it makes a full pass for every input document, and with the index it makes none.

The recursive one and the window one

$graphLookup follows a hierarchy as far as it goes, and depthField records how far out each step landed. The array it returns is not ordered: here it came back as Ana, Caro and Beto with levels 2, 0 and 1. $setWindowFields (5.0+) is the SQL window: partitionBy is PARTITION BY and sortBy is ORDER BY.

db.empleados.insertMany([{ _id: 1, nombre: "Ana", jefe: null },
  { _id: 2, nombre: "Beto", jefe: 1 }, { _id: 3, nombre: "Caro", jefe: 2 },
  { _id: 4, nombre: "Dora", jefe: 3 }])
db.empleados.aggregate([
  { $match: { nombre: "Dora" } },
  { $graphLookup: { from: "empleados", startWith: "$jefe", connectFromField: "jefe",
                    connectToField: "_id", as: "cadena", depthField: "nivel" } }
])   // cadena: Ana(2), Caro(0), Beto(1)

db.pedidos.aggregate([
  { $match: { cliente: 42 } },
  { $setWindowFields: { partitionBy: "$estado", sortBy: { fecha: 1 },
      output: { acumulado: { $sum: "$total", window: { documents: ["unbounded", "current"] } },
                puesto: { $rank: {} } } } },
  { $match: { estado: "pagado" } },
  { $limit: 3 }
])   // acumulado 559.5 - 1113 - 1660.5

Several answers in a single pass

$facet runs sub-pipelines over the same input and returns one single document with all of them; inside it no index counts any more. $unionWith is UNION ALL.

db.devoluciones.insertMany([{ cliente: 42, total: 9.5 }, { cliente: 7, total: 4.25 }])
db.pedidos.aggregate([
  { $match: { cliente: 42, estado: "pagado" } },
  { $project: { _id: 0, cliente: 1, importe: "$total" } },
  { $unionWith: { coll: "devoluciones", pipeline: [
      { $match: { cliente: 42 } },
      { $project: { _id: 0, cliente: 1, importe: { $multiply: ["$total", -1] } } } ] } },
  { $group: { _id: "$cliente", neto: { $sum: "$importe" }, filas: { $sum: 1 } } }
])   // { _id: 42, neto: 5495.5, filas: 11 }

Writing the result

$merge merges into the target collection and $out replaces it entirely.

db.pedidos.aggregate([
  { $match: { estado: "pagado" } },
  { $group: { _id: "$cliente", gastado: { $sum: "$total" } } },
  { $merge: { into: "resumen", whenMatched: "merge", whenNotMatched: "insert" } }
])
db.pedidos.aggregate([
  { $match: { estado: "enviado" } },
  { $group: { _id: "$cliente", enviado: { $sum: "$total" } } },
  { $merge: { into: "resumen", whenMatched: "merge", whenNotMatched: "insert" } }
])   // resumen: { _id: 42, gastado: 5505, enviado: 515 }

db.pedidos.aggregate([
  { $match: { estado: "nuevo" } },
  { $group: { _id: "$cliente", nuevo: { $sum: "$total" } } },
  { $out: "resumen" }
])   // resumen: { _id: 42, nuevo: 525 } - lo anterior ya no esta

The ceilings

Every blocking stage — $group, $sort, $facet, the intermediate of $lookup — gets 104,857,600 bytes, and since 6.0 the overflow spills to disk on its own. But an accumulator's array does not spill: a $push over large documents fails with 146 ExceededMemoryLimit, and allowDiskUse: true does not save it.

Keywords: aggregation, pipeline, stage, match, group, project, lookup, join, graphLookup, setWindowFields, window, facet, unionWith, merge, out, unwind, explain, allowDiskUse

One document is written whole without a transaction; for several you need one, and a replica set. The session, readConcern and writeConcern, WriteConflict 112 and the three measured ceilings.

Applies to: MongoDB 7.0+

In MongoDB a document is written whole or not at all, and that holds even when the updateOne touches ten fields and a nested array. Changing more than one document at a time takes a transaction, and a transaction requires a replica set: on a standalone node it is refused, and the message does not even mention transactions — it says the deployment does not support retryable writes.

db.cuentas.insertMany([{ _id: "A", saldo: 100 }, { _id: "B", saldo: 100 }])
db.cuentas.updateOne({ _id: "A" },
  { $inc: { saldo: -30 }, $set: { ultimo: new Date("2026-09-12") } })
db.cuentas.findOne({ _id: "A" })
// { _id: "A", saldo: 70, ultimo: 2026-09-12 } - los dos campos, o ninguno

A transaction runs on a session

Everything inside goes through the session object: the db.cuentas from outside is not in the transaction, however identical its name. Until commitTransaction(), what was written is visible only from inside.

const s = db.getMongo().startSession()
const c = s.getDatabase(db.getName()).cuentas
s.startTransaction({ readConcern: { level: "snapshot" },
                     writeConcern: { w: "majority" } })
c.updateOne({ _id: "A" }, { $inc: { saldo: -10 } })
c.updateOne({ _id: "B" }, { $inc: { saldo: 10 } })

c.find().toArray()           // dentro:  A 60, B 110
db.cuentas.find().toArray()  // fuera:   A 70, B 100

s.commitTransaction()
db.cuentas.find().toArray()  // fuera:   A 60, B 110
s.endSession()

abortTransaction() undoes all of it, and you do not have to ask: if the session goes away or the server restarts, the transaction dies aborted.

readConcern and writeConcern are two different questions

readConcern says what gets read: local is whatever is here, majority is what can no longer be lost, snapshot a coherent picture of one instant. writeConcern says when it counts as written: w: 1 is the primary, w: "majority" the majority of the set, and j: true adds the journal. The factory values come from getDefaultRWConcern: local for reads, majority for writes.

The write conflict

Two transactions on the same document do not wait: the second one fails on the spot with 112 WriteConflict, and the message says so plainly. Retrying is part of the deal, which is why the drivers ship withTransaction, which retries on its own.

const s1 = db.getMongo().startSession()
const s2 = db.getMongo().startSession()
s1.startTransaction(); s2.startTransaction()
s1.getDatabase(db.getName()).cuentas.updateOne({ _id: "A" }, { $inc: { saldo: 1 } })
s2.getDatabase(db.getName()).cuentas.updateOne({ _id: "A" }, { $inc: { saldo: 1 } })
// 112 WriteConflict - Write conflict during plan execution ... Please retry

s1.commitTransaction()   // la primera sí pasa
s2.abortTransaction()
s1.endSession(); s2.endSession()

A write from outside the transaction does not get the 112: it waits. In the measurement it waited 76 s, until the lifetime limit aborted the transaction that was holding the document.

The ceilings

A transaction lives 60 s, and past that line the commit returns 251 NoSuchTransaction with "has been aborted". Inside, each lock request waits only 5 ms: a transaction will not hang on a latch, it would rather fail. And a writeConcern the set cannot satisfy fails before even trying.

db.adminCommand({ getParameter: 1, transactionLifetimeLimitSeconds: 1 })
// 60
db.adminCommand({ getParameter: 1, maxTransactionLockRequestTimeoutMillis: 1 })
// 5
db.adminCommand({ getDefaultRWConcern: 1 })
// defaultReadConcern local - defaultWriteConcern { w: "majority", wtimeout: 0 }

db.cuentas.updateOne({ _id: "A" }, { $inc: { saldo: 0 } },
  { writeConcern: { w: 2, wtimeout: 1000 } })
// 100 UnsatisfiableWriteConcern - Not enough data-bearing nodes

The practical rule: if two documents have to change together many times a day, what is usually wrong is the model, and what you should have done is embed them. The transaction is the way out for what genuinely does not fit in one document.

Keywords: transaction, transactions, session, startTransaction, commit, abort, WriteConflict, 112, 251, readConcern, writeConcern, majority, snapshot, replica set, atomicity, lock

The three explain verbosities and what each one adds, the plan cache and when a plan is deactivated, the working set inside the WiredTiger cache, and the profiler's three levels.

Applies to: MongoDB 7.0+

Before touching anything you measure, and the tool is explain. It has three verbosities, each costing more than the last: queryPlanner only plans — it never executes — and shows the winning plan and the rejected ones; executionStats runs the winner and adds what it cost; allPlansExecution also adds what each candidate cost during the trial period, which is where you see why the winner won.

const est = ["nuevo", "pagado", "enviado", "cerrado"]
const docs = []
for (let i = 0; i < 20000; i++)
  docs.push({ cliente: i % 499, estado: est[i % 4], total: (i % 997) + 0.5 })
db.pedidos.insertMany(docs)
db.pedidos.createIndex({ cliente: 1 })
db.pedidos.createIndex({ cliente: 1, total: -1 })
const q = { cliente: 42, total: { $gt: 100 } }

db.pedidos.find(q).explain()
// queryPlanner: winningPlan FETCH y rejectedPlans con 1 candidato

db.pedidos.find(q).explain("executionStats")
// + executionStats: nReturned 20, docs 20, claves 20, 0 ms

db.pedidos.find(q).explain("allPlansExecution")
// + allPlansExecution: los 2 planes probados, FETCH 20 y FETCH 10

The three numbers that matter are nReturned, totalDocsExamined and totalKeysExamined. If the second is much larger than the first, the server is reading documents in order to throw them away.

The plan cache

The planner does not decide again on every query. The first time it tries the candidates, stores the winner under a planCacheKey and reuses it from then on; in the explain it shows up as isCached: true. The entry keeps works, the work it took, and if a later run spends ten times more, the plan is deactivated and the candidates compete again. Creating or dropping an index also clears the cache.

db.pedidos.getPlanCache().clear()
db.pedidos.getPlanCache().list()     // []

db.pedidos.find(q).toArray()
db.pedidos.find(q).toArray()
db.pedidos.getPlanCache().list()     // 1 entrada: works 21, isActive true

db.pedidos.find(q).explain().queryPlanner.winningPlan.isCached   // true

The working set and the WiredTiger cache

MongoDB does not keep results: what it keeps is pages, in the WiredTiger cache, and performance depends on the working set — the data and indexes actually touched — fitting in there. By default that cache takes half the RAM minus 1 GB. In the measurement, of 486,864 pages requested only 261 had to be brought in from disk: one in 1,865.

const w = db.serverStatus().wiredTiger.cache
w["maximum bytes configured"]        // 3621781504 - la mitad de la RAM menos 1 GB
w["bytes currently in the cache"]    // 18640438 - de todo el servidor
w["pages requested from the cache"]  // 486864
w["pages read into cache"]           // 261 - una de cada 1865

db.pedidos.stats().size              // 1385000 de datos
db.pedidos.stats().totalIndexSize    // 499712 de indices

The profiler

Three levels: 0 off, 1 only what goes past slowms, and 2 everything. Two measured things that catch people out: setProfilingLevel returns the level from before, not the one it just set — the new one has to be read back — and system.profile is a capped collection of 1 MiB, so it does not grow: it bites its own tail.

db.getProfilingStatus()      // { was: 1, slowms: 100, sampleRate: 1 }
db.setProfilingLevel(2)      // { was: 1, ... }  <- el nivel de ANTES

db.pedidos.find({ total: { $gt: 900 } }).toArray()
db.system.profile.find().sort({ ts: -1 }).limit(1)
// query - COLLSCAN - docsExamined 1901 - nreturned 101 - 0 ms

db.system.profile.stats().capped    // true, maxSize 1048576
db.setProfilingLevel(1, { slowms: 100 })

Every entry carries planSummary, docsExamined, nreturned and millis, which is exactly what you need in order to decide whether an index is worth it. Calíope reads that collection in its profiling tool. Level 2 is expensive in production: you turn it on for a while and turn it back down, you do not leave it there.

Keywords: performance, explain, queryPlanner, executionStats, allPlansExecution, plan cache, planCacheKey, isCached, WiredTiger, working set, profiler, system.profile, slowms

What is really running according to getCmdLineOpts, the five sections of mongod.conf that matter, and the three kinds of parameter: those that change hot, the startup-only ones, and those that are not parameters at all.

Applies to: MongoDB 7.0+

The first question about a server you do not know is not what its configuration file says, but what it is actually running with. getCmdLineOpts answers both at once: argv is what was handed to it on the command line, and parsed is that same thing translated into the file's vocabulary.

db.adminCommand({ getCmdLineOpts: 1 })
// argv:   ["mongod", "--profile", "1", "--slowms", "100",
//          "--auth", "--bind_ip_all"]
// parsed: { net: { bindIp: "*" },
//           operationProfiling: { mode: "slowOp", slowOpThresholdMs: 100 },
//           security: { authorization: "enabled" } }

The five sections that matter

storage says where the data is and how much memory the cache takes; net, which addresses it listens on; security, whether you have to authenticate; operationProfiling, what gets recorded of the slow traffic; and replication, which set it belongs to. The file is YAML, so the indentation is syntax.

# mongod.conf - lo mismo de arriba, escrito donde se queda
storage:
  dbPath: /data/db
  wiredTiger:
    engineConfig:
      cacheSizeGB: 3.37
net:
  bindIp: 127.0.0.1,10.0.0.5
  port: 27017
security:
  authorization: enabled
operationProfiling:
  mode: slowOp
  slowOpThresholdMs: 100
replication:
  replSetName: rs0
setParameter:
  cursorTimeoutMillis: 300000

Parameters come in three kinds

Those you change hot with setParameter, those read only at startup, and those that are not parameters at all however much they look like it. All three are told apart by what the server answers: the first returns was with the previous value — not the new one, so to know how it ended up you have to read it back; the second gives 20 IllegalOperation; and port, which is a startup option and not a parameter, gives 72 InvalidOptions with "unrecognized parameter".

db.adminCommand({ setParameter: 1, cursorTimeoutMillis: 300000 })
// { was: 600000, ok: 1 }   <- devuelve el valor de ANTES, no el nuevo

db.adminCommand({ setParameter: 1, wiredTigerEngineRuntimeConfig: "cache_size=512M" })
db.serverStatus().wiredTiger.cache["maximum bytes configured"]   // 536870912

db.adminCommand({ setParameter: 1, authenticationMechanisms: ["SCRAM-SHA-256"] })
// 20 IllegalOperation - not allowed to change [...] at runtime

db.adminCommand({ setParameter: 1, port: 27020 })
// 72 InvalidOptions - attempted to set unrecognized parameter [port]

setParameter does not stay

A setParameter lives until the next restart and not one second longer: with cursorTimeoutMillis set to 300,000, the server came back up at 600,000. For it to stay you have to write it in the setParameter: section of the file, the one at the bottom of the example.

The cache and the compatibility version

The WiredTiger cache is the first thing anyone reaches for, and almost always the thing not to touch: by default it takes half of what is left of the RAM after setting 1 GB aside. On the measured node, 7,933 MB of RAM gave 3,621,781,504 bytes of cache. And there is a sixth thing that is not in the file: the featureCompatibilityVersion, which decides which features of the binary are switched on — you raise it by hand after upgrading, and lower it before going back.

db.hostInfo().system.memSizeMB   // 7933
db.serverStatus().wiredTiger.cache["maximum bytes configured"]
// 3621781504 = la mitad de (7933 MB - 1 GB)

Object.keys(db.adminCommand({ getParameter: "*" })).length   // 780
db.adminCommand({ getParameter: 1, featureCompatibilityVersion: 1 })
// { version: "8.2" }

Keywords: configuration, mongod.conf, getCmdLineOpts, setParameter, cacheSizeGB, WiredTiger, bindIp, authorization, operationProfiling, replSetName, featureCompatibilityVersion, FCV

A user lives in a database and that is their surname, the built-in roles are not the same in admin as anywhere else, the 13 a write without permission gets, and the one door a freshly secured server leaves open.

Applies to: MongoDB 7.0+

Without security.authorization: enabled none of this exists: the server takes anyone who turns up, and with bindIp: "*" anyone can. Switched on, the first question is who am I.

db.adminCommand({ connectionStatus: 1 }).authInfo
// { authenticatedUsers:     [{ user: "caliope", db: "admin" }],
//   authenticatedUserRoles: [{ role: "root",    db: "admin" }] }

db.adminCommand({ getParameter: 1, authenticationMechanisms: 1 })
// ["MONGODB-X509", "SCRAM-SHA-1", "SCRAM-SHA-256"]

The default mechanism is SCRAM-SHA-256, and the server keeps both versions: the measured user's password carries 15,000 iterations under SHA-256 and 10,000 under SHA-1, with a 40-character salt. The password never travels, not even encrypted: SCRAM proves it is known without saying it. The third mechanism, MONGODB-X509, swaps the password for a client certificate, and then the user's name is the certificate's subject.

A user lives in a database, and that is their other half

lector is not a user: lector of ventas is. The database it was created in is its authentication database, and you have to name it when connecting (--authenticationDatabase). With the wrong one the server does not say the user exists elsewhere: it says "Authentication failed" and nothing more.

The roles are not the same in every database

A normal database has six built-in roles. admin has twenty-one, because that is where the ones reaching the whole server live — the …AnyDatabase ones, root, backup, restore, the cluster ones. A role is a list of actions: read is eleven of them, and find is only one.

db.getSiblingDB("admin").runCommand({ rolesInfo: 1, showBuiltinRoles: true })
// 21 roles: backup, clusterAdmin, clusterManager, clusterMonitor, dbAdmin,
// dbAdminAnyDatabase, dbOwner, read, readAnyDatabase, readWrite,
// readWriteAnyDatabase, restore, root, userAdmin, userAdminAnyDatabase ...

db.getSiblingDB("ventas").runCommand({ rolesInfo: 1, showBuiltinRoles: true })
// 6: dbAdmin, dbOwner, enableSharding, read, readWrite, userAdmin

What happens when a permission is missing

There is no empty answer and no missing row: there is a 13 Unauthorized, and the message names the database, the command and even the collection. It is an error you can read the missing rule out of.

db.getSiblingDB("ventas").createUser({
  user: "lector", pwd: "lectorpass",
  roles: [{ role: "read", db: "ventas" }]
})
// mechanisms: ["SCRAM-SHA-1", "SCRAM-SHA-256"]

// ya conectado como lector, con --authenticationDatabase ventas:
db.datos.findOne()              // { _id: 1, v: 1 }
db.datos.insertOne({ _id: 2 })
// 13 Unauthorized - not authorized on ventas to execute command { insert: ... }

The localhost exception

A server with --auth and not a single user lets whoever arrives from the machine itself do exactly one thing: create the first one. Reading, no longer; creating a second, no either. It is the ramp for getting started, and it closes on its own the moment a user exists.

// un nodo con --auth y sin un solo usuario, desde el propio nodo:
db.getSiblingDB("prueba").c.findOne()
// 13 Unauthorized

db.getSiblingDB("admin").createUser({ user: "primero", pwd: "x", roles: ["root"] })
// OK - este es el unico que deja

db.getSiblingDB("admin").createUser({ user: "segundo", pwd: "x", roles: ["root"] })
// 13 Unauthorized - Command createUser requires authentication

Keywords: security, authentication, SCRAM, SCRAM-SHA-256, x.509, TLS, user, role, built-in roles, read, readWrite, dbAdmin, userAdmin, root, 13, Unauthorized, localhost, authSource

What is and is not inside a mongodump, how long restoring takes and why, what --oplog is for, how long the oplog window really lasts, and the two things a dump does not guarantee.

Applies to: MongoDB 7.0+

mongodump is a logical backup: it connects like any other client, reads the documents and writes them as BSON. That has two consequences you can see in the numbers. The first is that the file is the size of the documents, not of what they take on disk: 2,420,000 bytes of .bson for a collection that is compressed on disk. The second is that it competes for the cache with the server's normal work, so a dump of a large database is felt.

mongodump -u caliope -p ... --authenticationDatabase admin \
  --db ventas --out /vol
// writing `ventas.pedidos` to `/vol/ventas/pedidos.bson`
// done dumping `ventas.pedidos` (20000 documents)      26 ms

ls -l /vol/ventas
// pedidos.bson           2420000   <- el tamano LOGICO de los documentos
// pedidos.metadata.json      255   <- los indices, sin sus datos

Of the indexes only the definition travels, in the .metadata.json. That is why restoring costs far more than dumping — 91 ms against 26 in this measurement: the time goes into rebuilding them, and on a real collection that is nearly all the wait.

mongorestore -u caliope -p ... --authenticationDatabase admin \
  --nsFrom "ventas.*" --nsTo "copia.*" /vol
// restoring `copia.pedidos` from `/vol/ventas/pedidos.bson`
// finished restoring `copia.pedidos` (20000 documents, 0 failures)
// restoring indexes for collection `copia.pedidos` from metadata
// 20000 document(s) restored successfully.             91 ms

db.getSiblingDB("copia").pedidos.getIndexes()   // _id_ y cliente_1

--archive leaves a single file instead of a tree of folders, and with --gzip it shrank from 2,420,000 to 133,572 bytes. Both can be sent down a pipe, which is how a database gets copied from one machine to another without ever touching an intermediate disk.

mongodump ... --db ventas --archive=/vol/ventas.gz --gzip
// 133572 bytes, frente a 2420000 del BSON suelto

The oplog is what turns a backup into a point in time

A dump takes a while, and meanwhile the database keeps changing: what was written to collection A before dumping it and to B afterwards does not line up. --oplog also saves the operations that happened during the dump, and mongorestore --oplogReplay applies them at the end, so what is restored is the state of one instant, the end of the dump. It only works against a replica set, because the oplog is the set's.

// esto sólo existe en un conjunto de réplicas:
db.getSiblingDB("local").oplog.rs.stats()
// capped true - maxSize 45822903296 - size 3708051130 - count 318107

rs.printReplicationInfo()
// oplog first event time   Sat Aug 08 2026 17:56:09
// oplog last event time    Sat Sep 12 2026 07:41:07

mongodump --port 27018 --oplog --out /vol
// writing captured oplog to `` - dumped 1 oplog entry

The oplog is a capped collection, so its window is not measured in bytes but in time, and that time depends on how much gets written. On the measured node, a 42 GiB cap gave a window from 8 August to 12 September; with ten times the load it would be three days. It is the number to look at before leaving for the weekend.

What a dump does not cover

Two things. A database of hundreds of gigabytes is not backed up by reading it document by document: that calls for filesystem snapshots, which have to be taken with the journal included or with the database locked by fsyncLock. And in a sharded cluster, a mongodump against the router does not give a point in time common to the shards: you have to stop the balancer and take one snapshot per shard plus one of the config servers.

Keywords: backup, mongodump, mongorestore, oplog, oplogReplay, archive, gzip, snapshot, fsyncLock, recovery, recovery window, BSON

The schema is whatever the documents carry, so changing it means writing them. The $jsonSchema validator, the 121 and its errInfo, the four combinations of validationLevel and validationAction, and migrating by document version.

Applies to: MongoDB 7.0+

There is no ALTER TABLE because there is no table: a collection's schema is, literally, whatever its documents carry. Adding a field to the new ones costs nothing and does not change the old ones, and that is the catch: whoever reads has to cope with both shapes until somebody levels the past.

The validator is a door, not a schema

What does exist is a validator with $jsonSchema: a condition checked on writing, never on reading and never backwards. It rejects with 121 DocumentValidationFailure, and the good part is errInfo.details, which names the rule that was broken instead of saying "not valid".

db.createCollection("socios", {
  validator: { $jsonSchema: {
    bsonType: "object",
    required: ["nombre", "correo"],
    properties: {
      nombre: { bsonType: "string", minLength: 2 },
      correo: { bsonType: "string", pattern: "^.+@.+$" },
      edad:   { bsonType: "int",    minimum: 18 }
    } } },
  validationLevel: "strict", validationAction: "error"
})

db.socios.insertOne({ nombre: "Ana", correo: "ana@ej.com", edad: 30 })   // entra
db.socios.insertOne({ nombre: "Caro" })
// 121 DocumentValidationFailure
// errInfo.details: required -> missingProperties: ["correo"]

Watch the types: mongosh stores a whole number as int, so edad: 30 passes a bsonType: "int" and edad: 30.5 breaks it, because that one really is a double.

validationLevel and validationAction are two different knobs

The level says which documents it reaches: strict all of them, moderate only those that were already valid — that is how you put a validator on a collection full of old junk without jamming its updates. The action says what happens on failure: error rejects, warn lets the write through and notes it in the log. And setting the validator with collMod touches nothing that was already there.

db.viejos.insertMany([{ _id: 1, nombre: "Uno" }, { _id: 2, nombre: "Dos" }])
db.runCommand({ collMod: "viejos",
  validator: { $jsonSchema: { bsonType: "object", required: ["nombre", "correo"] } },
  validationLevel: "moderate", validationAction: "error" })
db.viejos.countDocuments()                                  // 2 - no borra nada

db.viejos.updateOne({ _id: 1 }, { $set: { nombre: "Uno bis" } })   // moderate: pasa
db.viejos.insertOne({ _id: 3, nombre: "Tres" })                    // 121 igual

db.runCommand({ collMod: "viejos", validationLevel: "strict" })
db.viejos.updateOne({ _id: 2 }, { $set: { nombre: "Dos bis" } })   // 121

db.runCommand({ collMod: "viejos", validationAction: "warn" })
db.viejos.insertOne({ _id: 4, nombre: "Cuatro" })      // entra, y sólo avisa

Migrating means writing

With no ALTER, the equivalent of a new column is an updateMany with $set, and of dropping one, an $unset. They are cheap — 20,000 documents in 61 and 51 ms — but they are not atomic: they go document by document, so during the migration both shapes coexist.

db.pedidos.updateMany({ _v: 1 }, { $set: { moneda: "MXN", _v: 2 } })
// 20000 modificados en 61 ms

db.pedidos.updateMany({}, { $unset: { moneda: "" } })
// 20000 modificados en 51 ms

That is why the pattern that holds up is keeping the version inside each document (_v): the application knows how to read both, the migration advances in batches or as each document is touched, and the day {_v: 1} returns nothing, the old code goes. It is what a SQL migration does too, except that here the intermediate state is visible and has to be written down.

Keywords: schema, validation, validator, jsonSchema, 121, DocumentValidationFailure, validationLevel, validationAction, strict, moderate, warn, collMod, migration, document version

The three pieces of a sharded cluster, why a hashed key spreads and a monotonic one piles up, the measured difference between a targeted and a broadcast query, zones, and what changing the key costs.

Applies to: MongoDB 7.0+

Sharding means splitting a collection across several machines, and it takes three pieces: the shards, which hold the data and are replica sets; the config servers, which hold the map of which chunk is where; and mongos, the router, which holds nothing and is what the application connects to.

# tres piezas distintas, y el enrutador no guarda datos
mongod --configsvr --replSet cfg --port 27019
mongod --shardsvr  --replSet sh1 --port 27018
mongod --shardsvr  --replSet sh2 --port 27018
mongos --configdb cfg/qa-cfg:27019

# ya conectado al mongos:
sh.addShard("sh1/qa-sh1:27018")
sh.addShard("sh2/qa-sh2:27018")   // config.shards: ["sh1", "sh2"]

The shard key is the only decision that matters

Three things come out of it at once: how the data spreads, which queries can be aimed at a single shard, and whether there is a hot spot. It wants cardinality — many distinct values — even frequency, so no value takes half of everything, and not to be monotonic, because a key that always grows sends every new write to the same place.

That last one is not theory. On the same collection of 60,000 documents: a hashed key on the customer left 52.11 % on one shard and 47.88 % on the other; the ObjectId _id, which always grows, left 100 % on one, in a single chunk.

sh.enableSharding("ventas")

sh.shardCollection("ventas.pedidos", { cliente: "hashed" })
db.pedidos.getShardDistribution()
// 60000 documentos - sh1 52.11 %, sh2 47.88 % - 2 trozos

sh.shardCollection("ventas.eventos", { _id: 1 })
db.eventos.getShardDistribution()
// 60000 documentos - sh2 100 % - 1 trozo

Targeted or broadcast

A query carrying the key goes to one shard and that is that. One that does not is asked of all of them and the answers are merged: SINGLE_SHARD against SHARD_MERGE. The measured difference is 121 documents examined against 60,000.

db.pedidos.find({ cliente: 42 }).explain()
// SINGLE_SHARD - shards: ["sh2"]
// examinados 121, devueltos 121

db.pedidos.find({ estado: "pagado" }).explain()
// SHARD_MERGE  - shards: ["sh2", "sh1"]
// examinados 60000, devueltos 15000

Zones, and changing the key

A zone ties a range of the key to a shard, and it is good for two real things: keeping a country's data on machines in that country, and separating the hot from the cold. And since 5.0 the key can be changed with reshardCollection, but it copies the whole collection and it shows: on 60,000 documents it was still working after two minutes, with its temporary collection in plain sight.

sh.addShardToZone("sh1", "MX")
sh.addShardToZone("sh2", "EU")
// config.shards: sh1 tags ["MX"], sh2 tags ["EU"]

db.adminCommand({ reshardCollection: "ventas.eventos", key: { _id: "hashed" } })
// copia la coleccion entera: seguia en marcha a los dos minutos,
// con su system.resharding.<uuid> visible en config.collections

Three things that are no longer true

People still say that an updateOne without the key fails, that the key's value cannot be changed, and that the index has to exist before sharding. On 8.2 all three went through without a complaint.

Keywords: sharding, shard key, hashed, chunk, mongos, config servers, zone, balancer, reshardCollection, SINGLE_SHARD, SHARD_MERGE

The eight codes that come up daily, provoked one by one with the text the server returns, and the two that carry more inside than the number does: 11000 with its key and 121 with its errInfo.

Applies to: MongoDB 7.0+

A MongoDB error brings a number, nearly always a name, and sometimes something inside worth more than both. These are the eight that come up daily, provoked one by one against the server and copied as they came.

The two without a name carry something better

The 11000 and the 121 arrive with no codeName, and it does not matter: both come with the one fact needed to fix them. The 11000 names the collection, the index and the value that clashed, so there is no guessing which of three unique indexes went off.

db.correos.createIndex({ correo: 1 }, { unique: true })
db.correos.insertOne({ correo: "a@b.c" })
db.correos.insertOne({ correo: "a@b.c" })
// 11000 :: E11000 duplicate key error collection: ventas.correos
//          index: correo_1 dup key: { correo: "a@b.c" }

The 121 says "Document failed validation" and nothing else in the message, but e.errInfo.details carries the broken rule with its name and its expected value. That is the difference between "not valid" and "it is missing correo".

db.createCollection("socios", {
  validator: { $jsonSchema: { bsonType: "object", required: ["correo"] } } })
db.socios.insertOne({ nombre: "sin correo" })
// 121 :: Document failed validation
// e.errInfo.details:
// { operatorName: "$jsonSchema", schemaRulesNotSatisfied: [
//     { operatorName: "required", specifiedAs: { required: ["correo"] },
//       missingProperties: ["correo"] } ] }

The two permission ones are different

The 18 is "I do not know who you are": a bad password, or — most often — the wrong authentication database, because a MongoDB user is the name plus the database it was created in. The 13 is "I know who you are and you may not"; its message names the database, the command and even the collection, so the missing role reads straight off it.

And two syntax warnings

The 50 is not a server error but the ceiling you set yourself with maxTimeMS. And the 26 turns up where you least expect it: collMod and renameCollection on something that does not exist do fail, but drop() on a collection that does not exist returns false and that is all — it is not an error, so a script that takes it for granted never learns it had the name wrong.

db.gordo.find({ s: /x{100}/ }).maxTimeMS(1).toArray()
// 50 MaxTimeMSExpired :: operation exceeded time limit

db.runCommand({ collMod: "no_existe", validationLevel: "strict" })
// 26 NamespaceNotFound :: ns does not exist

db.no_existe.drop()   // false, y ningun error

Keywords: errors, codes, 11000, duplicate key, 13, Unauthorized, 18, AuthenticationFailed, 26, NamespaceNotFound, 50, MaxTimeMSExpired, 112, WriteConflict, 121, 251, NoSuchTransaction, errInfo

The eight ceilings you actually hit, provoked one by one against the server, with the code each returns and the three that are not where their reputation says.

Applies to: MongoDB 7.0+

All of these were provoked against the server, so the number on the left is the one that actually rejected, not the one the legend gives.

The 16 MB one is the only one you hit by accident

And nearly always for the same reason: an array growing unchecked inside a document. The message carries both numbers, the document's and the maximum, so you can see at a glance by how much it went over.

db.c.insertOne({ _id: 1, s: "x".repeat(16 * 1024 * 1024 - 200) })   // entra
db.c.insertOne({ _id: 2, s: "x".repeat(16 * 1024 * 1024 + 100) })
// 10334 :: object to insert too large.
//          size in bytes: 16777338, max size: 16777216

The two index ones get counted wrong

The 64 indexes per collection include _id_, so 63 of your own fit; and it is not a ceiling you reach in good health: with 64 indexes, every write maintains 64 trees. The 32 fields of a compound index are not a target either: past six or seven, what you almost certainly need is two indexes, not one wider one.

db.tope.insertOne({ a: 1 })
for (let i = 0; i < 70; i++) db.tope.createIndex({ ["c" + i]: 1 })
// crea 63, y el 64 falla:  el _id_ tambien cuenta
// 67 CannotCreateIndex :: add index fails, too many indexes

const k = {}
for (let i = 0; i < 33; i++) k["g" + i] = 1
db.comp.createIndex(k)          // con 32 pasa
// 13103 :: too many compound keys

Three that are not where their reputation says

Nesting is documented at 100 levels and what the server rejected was level 180, because the ceiling belongs to the BSON of the whole command and the insert wrapper takes a share. The long name does not fail on the collection but on the sum of database and collection, which is the namespace. And the ceiling on an index key's size, which in old versions was 1,024 bytes, no longer exists: an indexed value of 50,000 went in.

db.getSiblingDB("d".repeat(65)).c.insertOne({ a: 1 })
// 73 InvalidNamespace :: db name must be at most 63 characters, found: 65

db.createCollection("z".repeat(260))
// 73 InvalidNamespace :: Fully qualified namespace is too long

db.k.createIndex({ w: 1 })
db.k.insertOne({ w: "y".repeat(50000) })   // entra: no hay tope de clave

And what has no ceiling

Neither the number of collections, nor of databases, nor of documents in a collection. The one that runs out first is not in this table: it is the disk. And for what genuinely does not fit in 16 MB — a file — there is GridFS, which cuts it into 255 KB pieces and stores each piece as an ordinary document.

Keywords: limits, ceilings, 16 MB, document size, 10334, nesting, Overflow, indexes per collection, 67, CannotCreateIndex, 13103, namespace, 73, InvalidNamespace, index key

The summary of the MongoDB handbook: seven habits worth having, each with the measurement behind it, and the five lines that take the pulse of a server you have just inherited.

Applies to: MongoDB 7.0+

This is the end of the handbook, and it brings nothing new: it gathers what each topic demonstrated, in the shape that is useful day to day. Every habit comes with the number behind it, and all those numbers were measured against a real server, not copied.

1. Model for how you are going to read, not for how it resembles a table. What is read together is stored together. The limit of that rule is hard and it is measured: a document does not go past 16,777,216 bytes, so an array growing unchecked ends in a 10334 on some ordinary Tuesday.

2. One index per frequent query, and not one more. Every index is a tree to be maintained on every write: the same 20,000 inserts took 60 ms with no indexes, 101 with five and 160 with ten. And they take room: in the sample database, a collection of 1,148,000 bytes of data carried 622,592 of indexes.

3. Judge a query by totalDocsExamined against nReturned, not by the clock. The clock says what it took today, with a warm cache and a quiet machine; the ratio between those two numbers says what will happen when the collection is ten times bigger.

4. writeConcern: majority for what cannot be lost. On a replica set it is already the factory value, so the habit is not setting it: it is not removing it to go faster.

5. Never without authentication. It is one line in the file, and the only door a freshly started server leaves open — creating the first user from the machine itself — closes on its own the moment that user exists.

6. Back up with the oplog, and measure its window in time. mongodump --oplog is what turns a dump into an instant. And the oplog's size is not read in bytes but in days: the measured node gave 34, but that depends on how much gets written, so it is a number to look at again when the load changes.

7. The working set has to fit in the cache. It is the one performance rule with no tricks. In the sample database, 1,150,242 bytes of data against 3,621,781,504 of cache: room to spare three thousand times over. The day there is no room left, it shows in everything at once.

The pulse of a server you have just inherited

Five lines answer what you need to know before touching anything: whether it asks for a password, what counts as a write being written, whether anyone is watching the slow queries, how much memory it has to work with and how much data it has to move.

db.adminCommand({ getCmdLineOpts: 1 }).parsed.security
// { authorization: "enabled" }, o undefined - que es la respuesta mala

db.adminCommand({ getDefaultRWConcern: 1 }).defaultWriteConcern
// { w: "majority", wtimeout: 0 }
// en un nodo suelto ni existe: "not supported on standalone nodes"

db.getProfilingStatus()      // { was: 1, slowms: 100, sampleRate: 1 }

db.serverStatus().wiredTiger.cache["maximum bytes configured"]   // 3621781504
db.stats().dataSize                                              // 1150242

And one more, the one that shows where the disk is going and, along the way, which collection carries more index than data.

db.getCollectionInfos({ type: "collection" }).map(i => {
  const s = db.getCollection(i.name).stats()
  return { c: i.name, indices: s.nindexes, datos: s.size, indice: s.totalIndexSize }
})
// { c: "eventos", indices: 3, datos: 1148000, indice: 622592 }
// el filtro por type hace falta: stats() sobre una vista falla

Keywords: best practices, summary, access pattern, indexes, writeConcern, majority, authentication, oplog, working set, cache, explain, server health

The type lives in the value, not in the column: the five affinities and what each converts, the order between storage classes, what STRICT does prevent, and why NOCASE knows nothing about accents.

Applies to: SQLite 3.35+

In SQLite the type belongs to the value, not to the column. What a column declares is an affinity: a preference applied on storing, which converts when it can and lets things through when it cannot.

CREATE TABLE t (i INTEGER, r REAL, x TEXT, b BLOB, n NUMERIC);
INSERT INTO t VALUES ('42', '42', 42, 42, '42');
INSERT INTO t VALUES (7.0,  7,   '7', '7', '7.5');

SELECT typeof(i), typeof(r), typeof(x), typeof(b), typeof(n) FROM t;
-- integer | real | text | integer | integer
-- integer | real | text | text    | real

There are the five in one line. INTEGER and REAL convert text that looks like a number; TEXT converts the number to text; NUMERIC looks at the value and decides, so the same column holds an integer and a real; and BLOB is the one with no affinity: it stores what it is given, as it came.

There are five storage classes, and they are ordered

A column with no declared type is legal and accepts all five, so one column can hold an integer, a real, a text, a blob and a null. And they can be sorted, because there is a fixed order between classes: nulls first, then numbers, then text, and blobs last. Which means an ORDER BY over a dirty column does not fail: it groups by type without telling anyone.

CREATE TABLE libre (v);            -- sin tipo declarado: vale
INSERT INTO libre VALUES (1), (1.5), ('hola'), (x'0001'), (NULL);

SELECT typeof(v) FROM libre ORDER BY v;
-- null | integer | real | text | blob   <- y ese es el orden entre clases

STRICT prevents less than it looks

Since 3.37 a table can be declared STRICT, and then it only accepts a handful of types — INT, INTEGER, REAL, TEXT, BLOB and ANY — and rejects what it cannot store. But it still converts: a '42' goes into an INTEGER column because nothing is lost, and a 42 goes into a TEXT one and is stored as '42'. What it rejects is what has no conversion. And there is an unexpected gain: a made-up type, which a normal table accepts in silence, is rejected here at creation time.

CREATE TABLE s (i INTEGER, x TEXT) STRICT;

INSERT INTO s VALUES ('42', 'a');   -- entra: 42, convertible sin perder nada
INSERT INTO s VALUES (1, 42);       -- entra: el 42 se guarda como texto '42'
INSERT INTO s VALUES ('abc', 'a');
-- cannot store TEXT value in INTEGER column s.i

CREATE TABLE s2 (d DATETIME) STRICT;
-- unknown datatype for s2.d: "DATETIME"

No date, no boolean, and the collation knows nothing about accents

TRUE is an integer with value 1. A date is whatever you decide: date() returns text and julianday() returns real, and the one you pick is the one you will be sorting and comparing for the rest of its life. And there are only three collations — BINARY, NOCASE and RTRIM: NOCASE folds upper and lower case of ASCII and nothing else, so café and CAFÉ are different values and upper('café') returns CAFé. The text is UTF-8 all right: length counts characters, and over the blob it counts bytes.

SELECT typeof(TRUE), TRUE;                    -- integer | 1
SELECT typeof(date('2026-09-12'));            -- text
SELECT typeof(julianday('2026-09-12'));       -- real

CREATE TABLE n (v TEXT COLLATE NOCASE);
INSERT INTO n VALUES ('Cafe'), ('CAFE'), ('café');

SELECT v FROM n WHERE v = 'cafe';             -- Cafe, CAFE
SELECT v FROM n WHERE v = 'CAFÉ';             -- nada
SELECT upper('café'), length('café'), length(CAST('café' AS BLOB));
-- CAFé | 4 | 5

Keywords: types, affinity, typeof, INTEGER, REAL, TEXT, BLOB, NUMERIC, dynamic typing, STRICT, boolean, date, julianday, COLLATE, NOCASE, UTF-8, storage class

Isolation is serializable because only one writes: the three BEGIN modes, the SQLITE_BUSY 5 and why busy_timeout solves it, the 517 it does not solve, and what WAL really changes.

Applies to: SQLite 3.35+

SQLite has no isolation levels to choose from, and that is not a gap: isolation is serializable because across the whole database only one writes at a time. Everything else follows from that.

Two settings govern it, and both ship with the worst possible value.

PRAGMA journal_mode;    -- delete: el de fábrica, no WAL
PRAGMA busy_timeout;    -- 0: no espera nada

PRAGMA journal_mode = WAL;
PRAGMA busy_timeout = 3000;

The three BEGIN modes

DEFERRED — the default — takes nothing until it must: the first read takes a snapshot and the first write asks for the lock. IMMEDIATE asks for the write lock right away, on the BEGIN line itself. EXCLUSIVE also asks that nobody read, and in WAL mode it does almost nothing beyond IMMEDIATE. The practical rule: if the transaction is going to write, BEGIN IMMEDIATE; it costs a wait up front and avoids SQLite's most annoying error, the one further down.

SAVEPOINT is the intermediate mark, and ROLLBACK TO goes back to it without closing the transaction.

BEGIN;                          -- DEFERRED, el de por omision
UPDATE t SET v = 10 WHERE i = 1;
SAVEPOINT s1;
UPDATE t SET v = 20 WHERE i = 1;
ROLLBACK TO s1;                 -- deshace hasta aqui, NO cierra la transaccion
SELECT v FROM t WHERE i = 1;    -- 10
RELEASE s1;
COMMIT;

SQLITE_BUSY is 5, and it is nearly always yours

When someone else holds the write lock, the answer is SQLITE_BUSY with code 5 and the text "database is locked". It is not a fault: it is the queue for a single-lane resource. What turns it into a fault is that busy_timeout is 0 out of the box, so untouched the answer is instant and curt. With 800 ms set, the same call waited 895 before giving up.

# dos conexiones a la vez, con timeout=0 para que conteste en el acto
a = sqlite3.connect(db, isolation_level=None, timeout=0)
b = sqlite3.connect(db, isolation_level=None, timeout=0)

b.execute("BEGIN IMMEDIATE"); b.execute("UPDATE t SET v=1 WHERE i=2")

a.execute("SELECT v FROM t WHERE i=2")   # (0,)  <- lee el valor de antes
a.execute("BEGIN IMMEDIATE")             # SQLITE_BUSY (5) database is locked

a.execute("PRAGMA busy_timeout=800")
a.execute("BEGIN IMMEDIATE")             # espera 895 ms y vuelve a dar 5

The one busy_timeout does not fix: the 517

If a DEFERRED transaction reads and then wants to write, and somebody committed in between, the snapshot it took on reading is no longer good and SQLite returns SQLITE_BUSY_SNAPSHOT, the 517. Waiting is no use: nothing is going to give that snapshot back. The only way out is ROLLBACK and starting again — or, better, having opened with IMMEDIATE.

-- A:
BEGIN DEFERRED;
SELECT v FROM t WHERE i = 1;    -- aqui A se queda con una foto de la base

-- B, en otra conexion:
BEGIN IMMEDIATE; UPDATE t SET v = 5 WHERE i = 2; COMMIT;

-- A, que ahora quiere escribir:
UPDATE t SET v = 2 WHERE i = 1;
-- SQLITE_BUSY_SNAPSHOT (517), y busy_timeout no lo arregla
ROLLBACK;                       -- la unica salida: soltar y volver a empezar

What WAL changes, and what it does not

With the rollback journal, while one writes nobody reads. With journal_mode = WAL, readers go on reading the last committed version while the writer works: measured with two connections, the reader got the previous value without blocking for an instant. What does not change is the number of writers: still one, and the second still gets a 5.

Keywords: transaction, BEGIN, DEFERRED, IMMEDIATE, EXCLUSIVE, SAVEPOINT, ROLLBACK TO, SQLITE_BUSY, 5, 517, BUSY_SNAPSHOT, busy_timeout, WAL, journal_mode, lock, serializable

A pragma is not one single thing: some are written into the file, some last as long as the connection, and some are commands. Which is which, the measured factory values, and the one that is off and should not be.

Applies to: SQLite 3.35+

SQLite has no configuration file: it has pragmas. And the first confusion to clear is that they are not all the same kind of thing. Some are written inside the file and hold for whoever opens it later; some last as long as the connection and have to be repeated every time; and some are not settings at all but commands that do something and are done.

These are the factory values, read from a freshly created database.

PRAGMA journal_mode;   -- delete
PRAGMA synchronous;    -- 2 (FULL)
PRAGMA foreign_keys;   -- 0   <- apagadas
PRAGMA cache_size;     -- 2000 paginas
PRAGMA mmap_size;      -- 0
PRAGMA auto_vacuum;    -- 0
PRAGMA page_size;      -- 4096

The one that is off and almost nobody expects

foreign_keys is 0. Foreign keys get declared, get stored in the schema, show up in the CREATE TABLE… and are not checked. An orphan child goes in without a murmur. Turning it on is one line, but it belongs to the connection: it has to be set on every one, and turning it on does not look backwards — that is what foreign_key_check is for, which lists what already slipped through.

CREATE TABLE padre (id INTEGER PRIMARY KEY);
CREATE TABLE hijo  (id INTEGER PRIMARY KEY, p INTEGER REFERENCES padre(id));

INSERT INTO hijo VALUES (1, 999);   -- entra: no hay padre 999 y da igual

PRAGMA foreign_keys = ON;
INSERT INTO hijo VALUES (2, 999);   -- FOREIGN KEY constraint failed

PRAGMA foreign_key_check;           -- hijo | 1 | padre | 0

Which stays and which does not

journal_mode and user_version are written into the file header and survive closing and opening. page_size and auto_vacuum do too, but only if set before the first table is created: on a database that already has pages they are accepted without error and change nothing, and that was checked both ways. foreign_keys, cache_size, busy_timeout and mmap_size belong to the connection and return to their factory value as soon as another one is opened.

-- se quedan escritos en el archivo:
PRAGMA journal_mode = WAL;
PRAGMA user_version = 7;
PRAGMA page_size    = 8192;   -- solo en una base todavia VACIA
PRAGMA auto_vacuum  = FULL;   -- idem

-- son de la conexion, y hay que repetirlos en cada una:
PRAGMA foreign_keys = ON;
PRAGMA cache_size   = -8000;  -- en negativo son kibibytes, no paginas
PRAGMA busy_timeout = 3000;

One detail that throws people: after setting journal_mode = WAL, synchronous came back as 1 (NORMAL) with nobody touching it. It is deliberate — in WAL, NORMAL is enough not to lose anything committed — but it shows that reading a pragma does not tell you where that value came from.

And the ones that are commands

wal_checkpoint pours the journal into the database and, with TRUNCATE, leaves it at zero bytes: a -wal of 4,716,016 bytes was measured going to 0. ANALYZE fills sqlite_stat1 with what the planner will use to pick an index. And PRAGMA optimize is the one worth running when closing a long-lived connection: it looks at which tables have changed enough and fires the ANALYZE that is needed, saying nothing.

PRAGMA wal_autocheckpoint;         -- 1000 paginas
PRAGMA wal_checkpoint(TRUNCATE);   -- 0 | 0 | 0, y el -wal queda en 0 bytes

ANALYZE;
SELECT * FROM sqlite_stat1;        -- t | iv | 20000 20000
PRAGMA optimize;                   -- no devuelve nada

Keywords: pragma, configuration, journal_mode, WAL, synchronous, foreign_keys, cache_size, mmap_size, auto_vacuum, page_size, user_version, wal_checkpoint, optimize, ANALYZE, sqlite_stat1

SCAN, SEARCH and COVERING are the whole vocabulary of EXPLAIN QUERY PLAN. Partial and expression indexes and the condition you have to repeat for them to be used, what WITHOUT ROWID saves, and what ANALYZE writes.

Applies to: SQLite 3.35+

In SQLite there is only one kind of index: the B-tree. No hash, no bitmap, nothing to choose. Word search exists but is not an index: it is FTS5, a separate virtual table. That simplifies the whole topic, because the only decision left is on which columns and in what order.

And it is checked with EXPLAIN QUERY PLAN, whose vocabulary fits in three words: SCAN is reading the whole table, SEARCH is going in through an index, and COVERING means the row was never even touched.

-- pedidos(id, cliente, estado, total, correo) con 20 000 filas

EXPLAIN QUERY PLAN
  SELECT * FROM pedidos WHERE cliente = 42 AND estado = 'pagado';
-- SCAN pedidos

CREATE INDEX i_cli ON pedidos(cliente);
-- SEARCH pedidos USING INDEX i_cli (cliente=?)

CREATE INDEX i_cli_est ON pedidos(cliente, estado);
-- SEARCH pedidos USING INDEX i_cli_est (cliente=? AND estado=?)

The compound one is walked by prefixes, as in any engine: (cliente, estado) serves cliente alone and both together, but not estado alone.

Covering

If the index carries all the columns the query reads, the row is not touched. It is the same index as before: what changes is what is asked for.

EXPLAIN QUERY PLAN
  SELECT cliente, estado FROM pedidos WHERE cliente = 42;
-- SEARCH pedidos USING COVERING INDEX i_cli_est (cliente=?)

Partial and expression: both have to be repeated

A partial index indexes only the rows meeting a condition, which is why it takes so little room. The price is that the query has to repeat that condition, word for word, or the planner cannot use it: without it, the same query is back to SCAN. Same with the expression index: it indexes lower(correo), so lower(correo) has to be written in the WHERE; with plain correo it is no use at all.

CREATE INDEX i_parcial ON pedidos(total) WHERE estado = 'pagado';

EXPLAIN QUERY PLAN
  SELECT * FROM pedidos WHERE estado = 'pagado' AND total > 900;
-- SEARCH pedidos USING INDEX i_parcial (total>?)

EXPLAIN QUERY PLAN
  SELECT * FROM pedidos WHERE total > 900;
-- SCAN pedidos   <- sin repetir el filtro, el indice no existe

CREATE INDEX i_correo ON pedidos(lower(correo));

EXPLAIN QUERY PLAN
  SELECT * FROM pedidos WHERE lower(correo) = 'u42@ej.com';
-- SEARCH pedidos USING INDEX i_correo (<expr>=?)

WITHOUT ROWID removes one indirection

An ordinary table stores its rows by a hidden rowid, so its primary key is another index that then has to go and fetch the row. With WITHOUT ROWID the table is the tree of its primary key: the hop is saved and so is space — 1,335,296 bytes against 1,675,264 on the same 20,000-row table, 20 % less — and the plan gives it away by saying USING PRIMARY KEY instead of naming an automatic index.

ANALYZE gives numbers, not miracles

It fills sqlite_stat1 with the row count and how many there are per value of the index. That second number is the one that says whether an index is worth anything: 20,000 per value means it distinguishes nothing. But it does not always change the choice: in the measurement the planner was already choosing well before it ran, because without statistics it uses reasonable guesses. Running ANALYZE removes the guessing; it does not promise a different plan.

CREATE TABLE kv (k TEXT PRIMARY KEY, v TEXT) WITHOUT ROWID;
-- 20 000 filas: 1 335 296 bytes, frente a 1 675 264 con rowid

EXPLAIN QUERY PLAN SELECT v FROM kv WHERE k = 'clave-000042';
-- SEARCH kv USING PRIMARY KEY (k=?)
-- con rowid habria dicho:  USING INDEX sqlite_autoindex_kv_1 (k=?)

ANALYZE;
SELECT tbl, idx, stat FROM sqlite_stat1;
-- t | ia | 20000 20000    <- 20 000 filas, 20 000 por cada valor de a
-- t | ib | 20000 1        <- 20 000 filas, 1 por cada valor de b

Keywords: index, indexes, B-tree, EXPLAIN QUERY PLAN, SCAN, SEARCH, COVERING INDEX, partial index, expression index, WITHOUT ROWID, ANALYZE, sqlite_stat1, FTS5

The same 20,000 inserts measured six ways: the difference between the best and the worst is in no pragma at all, it is in whether there is a BEGIN. And what WAL, VACUUM and the page size do contribute.

Applies to: SQLite 3.35+

There is only one thing that matters, and it is not a pragma. The same 20,000 inserts, same schema, same machine:

440 times, and what explains it is that without BEGIN every INSERT is its own transaction: twenty thousand commits, each one waiting on the disk.

-- 20 000 INSERT, cada uno con su propia transaccion:  3963 ms
-- los mismos 20 000 aqui dentro:                          9 ms

BEGIN;
  INSERT INTO t (v) VALUES ('...');   -- x 20 000
COMMIT;

Watch out for a trap in the middle layer: a driver offering a "batch insert" call does not mean it opens a transaction. Python's executemany, with no explicit BEGIN, took 4,128 ms: exactly the same as the hand-written loop.

What the others contribute

Turning synchronous off saved 38 % and in exchange offers to lose committed data in a power cut: the worst deal on the list. WAL without a transaction came down to 258 ms — fifteen times — because committing stops rewriting the database, and that one is a change you can leave in place. But with both in play, the BEGIN takes nearly all of it: 9 ms without WAL and 10 with it. First you group, and only then you tune.

VACUUM is what gives the room back

Deleting does not shrink the file: the pages stay on a free list to be reused. A file of 10,813,440 bytes was measured, half its rows deleted, and it went on measuring exactly the same. VACUUM rewrites it whole and left it at 5,410,816. It costs a copy of the database and an exclusive lock, so it is not a nightly job: it is what you run when a big delete left the file twice the size it should be.

SELECT page_count * page_size
  FROM pragma_page_count(), pragma_page_size();   -- 10813440

DELETE FROM t WHERE i % 2 = 0;
PRAGMA freelist_count;   -- 1 pagina, y el archivo sigue igual de grande

VACUUM;                  -- 5410816 bytes, en 12 ms

The page size hardly ever needs touching

Three were measured. Dropping it to 512 cost 20 % more file and a measurable scan where the other two did not reach a millisecond; raising it to 65,536 gained nothing. The factory one — 4,096 — is the one to leave alone, and besides it can only be changed before the first table is created, or by going through a VACUUM afterwards.

PRAGMA page_size = 512;     -- 5215744 bytes, y el recorrido en 3 ms
PRAGMA page_size = 4096;    -- 4333568 bytes, y en 0 ms   <- el de fabrica
PRAGMA page_size = 65536;   -- 4390912 bytes, y en 0 ms

And two more things that measure themselves

Every index is paid for on every write: the same 20,000 rows took 7 ms with no indexes of their own, 12 with one, 16 with two and 20 with three, and the file went from 458,752 to 1,277,952 bytes. And a statement with a parameter gets reused: 5,000 queries with ? took 18 ms, and the same ones with the value glued into the SQL, 26 — besides being the door injection comes through.

Keywords: performance, transaction, BEGIN, COMMIT, batch, executemany, synchronous, WAL, VACUUM, freelist_count, page_size, fragmentation, prepared statement

What ALTER TABLE can do fits in four lines, and what it refuses when adding and dropping a column is measured with its message. The detour of create, copy, drop and rename, and why here it is safe.

Applies to: SQLite 3.35+

ALTER TABLE can do four things, and no more. Changing a column's type, taking a NOT NULL off it, adding a foreign key: none of that exists, and the attempt does not even get as far as a schema error, it is a syntax error.

ALTER TABLE t RENAME TO t2;            -- desde siempre
ALTER TABLE t RENAME COLUMN a TO a2;   -- 3.25
ALTER TABLE t ADD COLUMN g TEXT;       -- desde siempre
ALTER TABLE t DROP COLUMN b;           -- 3.35

ALTER TABLE t ALTER COLUMN g TYPE INTEGER;
-- near "ALTER": syntax error   <- no existe, y nunca ha existido

What ADD COLUMN refuses

The three refusals have the same cause: the new column is added without touching the rows already there, so the value they get has to be decidable without looking at them. A DEFAULT that changes, a UNIQUE that would have to be checked, and a NOT NULL with no value all fail that.

ALTER TABLE t ADD COLUMN i TEXT DEFAULT (datetime('now'));
-- Cannot add a column with non-constant default

ALTER TABLE t ADD COLUMN j TEXT UNIQUE;
-- Cannot add a UNIQUE column

ALTER TABLE t ADD COLUMN k TEXT NOT NULL;
-- Cannot add a NOT NULL column with default value NULL

What DROP COLUMN refuses, and worse: what it allows

Since 3.35 a column can be dropped, but not if it is part of the primary key, nor if it is UNIQUE, nor if an index or a generated column names it. So far, so good. The problem is the case it does let through: a column used by a view is dropped without a murmur, the view is left broken, and PRAGMA integrity_check still says ok because it does not look inside views. Nobody warns you until somebody queries.

ALTER TABLE t DROP COLUMN id;   -- cannot drop PRIMARY KEY column: "id"
ALTER TABLE t DROP COLUMN e;    -- cannot drop UNIQUE column: "e"
ALTER TABLE t DROP COLUMN c;    -- error in index i_c after drop column

CREATE VIEW v AS SELECT id, d FROM t;
ALTER TABLE t DROP COLUMN d;    -- PASA, sin una queja
SELECT * FROM v;                -- no such column: d
PRAGMA integrity_check;         -- ok

The usual detour

For everything else, the procedure is to create the new table, copy, drop the old one and rename. It sounds dangerous and here it is not, because of something MySQL does not have: SQLite's DDL is transactional. It was measured: a CREATE TABLE and an ADD COLUMN inside a BEGIN, with ROLLBACK at the end, left neither the table nor the column. So the whole detour fits in one transaction and, if something goes wrong halfway, nothing is left half done.

PRAGMA foreign_keys = OFF;
BEGIN;
  CREATE TABLE t_nueva (id INTEGER PRIMARY KEY, a INTEGER NOT NULL);
  INSERT INTO t_nueva (id, a) SELECT id, CAST(a AS INTEGER) FROM t;
  DROP TABLE t;
  ALTER TABLE t_nueva RENAME TO t;
  -- y aqui se vuelven a crear indices, disparadores y vistas
COMMIT;
PRAGMA foreign_key_check;
PRAGMA foreign_keys = ON;

Three cautions. The old table's indexes, triggers and views go with it and have to be created again, because the DROP TABLE takes them. Foreign keys are switched off during the detour and checked with foreign_key_check before switching them back on. And the type conversion is yours: a CAST('a' AS INTEGER) returns 0, without a word of warning.

Keywords: DDL, ALTER TABLE, RENAME TO, RENAME COLUMN, ADD COLUMN, DROP COLUMN, 3.25, 3.35, broken view, integrity_check, transactional DDL, twelve steps, CAST

A database in WAL mode is three files and the data is almost never in the first one: copying it leaves an empty database that claims to be healthy. The three ways that do work, measured, and what each integrity check finds.

Applies to: SQLite 3.35+

A SQLite database looks like a file, and that is where the trouble starts. In WAL mode there are three, and the one carrying the name may carry no data at all: after writing 30,000 rows, the .sqlite measured 4,096 bytes — the header and little else — and the -wal measured 3,366,072.

Copying only the first does not give a broken database. It gives something worse: one that opens without complaint, has not a single table, and that PRAGMA integrity_check calls ok. A backup like that passes every check and contains nothing.

PRAGMA journal_mode = WAL;
-- tras 30 000 filas, los tres archivos miden:
--   base.sqlite         4096   <- solo la cabecera
--   base.sqlite-shm    32768
--   base.sqlite-wal  3366072   <- aqui estan los datos

-- copiar solo base.sqlite da una base que abre, y esta VACIA:
SELECT count(*) FROM sqlite_schema;   -- 0
PRAGMA integrity_check;               -- ok

The three ways that do work

VACUUM INTO writes a clean, defragmented copy into another file, with the database in use: 3,338,240 bytes in 4 ms, with the 30,000 rows. The backup API — the command line's .backup, and Connection.backup in the drivers — does the same by copying pages and can go in instalments: 3,338,240 bytes in 3 ms. And .dump writes the SQL that rebuilds the database: 4,008,968 bytes of text and 30,003 statements, 20 % more than the binary, but it is the only one you can read with your eyes and the only one that survives a format change.

VACUUM INTO '/ruta/copia.sqlite';
-- 3338240 bytes en 4 ms, con las 30 000 filas dentro
-- y la copia sale en journal_mode delete, no en WAL

A welcome detail: the VACUUM INTO copy comes out in journal_mode delete, not WAL. It is a single file, which is exactly what you want from a backup.

Copying all three files at once with the database stopped does work. The trouble is "at once" and "stopped": while anybody is writing there is no instant at which the three agree, and no copying tool guarantees one.

Checking what you have

integrity_check walks the whole database and quick_check skips the cross-checks between indexes and tables. On a healthy database both said ok, and on the same database with a few hundred bytes deliberately smashed, both said exactly the same: Tree 2 page 4 cell 35: Rowid 0 out of order. The difference between them only shows on a large database, and neither fixes anything: they are for deciding whether to go back to the backup.

PRAGMA quick_check;       -- ok
PRAGMA integrity_check;   -- ok

-- con la misma base danada a proposito, los dos contestan igual:
-- *** in database main ***
-- Tree 2 page 4 cell 35: Rowid 0 out of order

And when it is already too late

Restoring a .dump is running its SQL against an empty database. Restoring a binary copy is putting it back in place, and there it helps to know that VACUUM INTO refuses to overwrite: onto a file that already exists it answers "output file already exists", so there is no way to tread on yesterday's backup by accident. And if what you have is a damaged database and no backup, there is the command line's .recover, which walks the pages that still make sense and writes the SQL to rebuild what it can: it does not promise everything, it promises what is left.

Keywords: backup, VACUUM INTO, backup API, dump, WAL, -wal, -shm, integrity_check, quick_check, corruption, recovery

The eight that really come up, provoked one by one with their text, and the arithmetic of the extended code: the constraint 19 turns into 275, 787, 1299, 1555 or 2067 depending on what was broken.

Applies to: SQLite 3.35+

SQLite has two sets of codes: a basic one, of one or two digits, and an extended one that says the same thing in more detail. And the relation between them is arithmetic: the extended one is the basic plus 256 times the subtype, so code & 255 always gives the basic one back. A driver that only shows you the basic one is hiding half of it.

The 19 is five different errors

The basic one says nothing useful, because a broken constraint could be any of five. The extended one does, and the message helps… with one exception: the primary key of an INTEGER PRIMARY KEY gives 1555, but its text says "UNIQUE constraint failed". So there the number is more precise than the sentence.

CREATE TABLE t (id INTEGER PRIMARY KEY, u TEXT UNIQUE, nn TEXT NOT NULL,
                ch INTEGER CHECK (ch > 0), p INTEGER REFERENCES padre(id));

INSERT INTO t VALUES (1,'b','x', 5,   1);   -- 1555  UNIQUE constraint failed: t.id
INSERT INTO t VALUES (2,'a','x', 5,   1);   -- 2067  UNIQUE constraint failed: t.u
INSERT INTO t VALUES (3,'c',NULL,5,   1);   -- 1299  NOT NULL constraint failed: t.nn
INSERT INTO t VALUES (4,'d','x',-1,   1);   --  275  CHECK constraint failed: ch > 0
INSERT INTO t VALUES (5,'e','x', 5, 999);   --  787  FOREIGN KEY constraint failed

The 5 and the 6 get confused and are not the same

The 5 comes from outside: another connection is writing, and it is fixed by waiting — that is where busy_timeout earns its keep. The 6 comes from inside: the same connection has a cursor open on the table it wants to change, and waiting is no use because the one blocking is you. It is fixed by closing the cursor.

PRAGMA max_page_count = 20;
INSERT INTO t ... ;      -- 13  SQLITE_FULL      database or disk is full

-- con la base abierta en modo solo lectura:
INSERT INTO t ... ;      --  8  SQLITE_READONLY  attempt to write a readonly database

-- con un cursor de SELECT todavia abierto, en la MISMA conexion:
DROP TABLE t;            --  6  SQLITE_LOCKED    database table is locked

The 13 is hardly ever the disk

SQLITE_FULL sounds like a full partition and is often the ceiling the database put on itself: max_page_count. With 20 pages it is provoked in one line, and the message is the same one a genuinely full disk would give: "database or disk is full".

The 11, the 26 and the one you never see

The two broken-file codes are told apart by where the damage is: if what makes no sense is a page, SQLITE_CORRUPT with "database disk image is malformed"; if what makes no sense is the header, it does not even try and says SQLITE_NOTADB. And the 21 is the odd one: calling the API in an impossible order. You hardly ever see it, because whichever driver you use catches it first and raises an error of its own; in Python, for instance, out comes a ProgrammingError that does not even carry a SQLite code.

-- con la pagina del esquema machacada:
SELECT count(*) FROM t;   -- 11  SQLITE_CORRUPT  database disk image is malformed

-- con el tamano de pagina de la cabecera machacado:
SELECT count(*) FROM t;   -- 26  SQLITE_NOTADB   file is not a database

Keywords: errors, codes, SQLITE_BUSY, 5, SQLITE_LOCKED, 6, SQLITE_READONLY, 8, SQLITE_CORRUPT, 11, SQLITE_FULL, 13, SQLITE_CONSTRAINT, 19, 275, 787, 1299, 1555, 2067, SQLITE_MISUSE, 21, SQLITE_NOTADB, 26

SQLite's ceilings are not the format's but the binary's, and they are read with PRAGMA compile_options. The eight you actually hit, provoked one by one, and the one that goes past without a word.

Applies to: SQLite 3.35+

SQLite's ceilings have a peculiarity no other engine has: they do not belong to the format, they belong to the binary you happen to be talking to. They are fixed at compile time, which is why the answer to "what is the maximum?" starts with PRAGMA compile_options, which shows them all. A program can also lower them at runtime with sqlite3_limit, never raise them.

These are the ones in the binary macOS ships, and the first five were provoked.

CREATE TABLE b (c0, c1, ... , c2000);
-- too many columns on b

SELECT 1 UNION ALL SELECT 1 UNION ALL ... ;   -- 501 veces
-- too many terms in compound SELECT

ATTACH DATABASE 'x11.sqlite' AS a11;
-- too many attached databases - max 10

The one that does not warn

PRAGMA page_size = 131072 gives no error, returns nothing odd and changes nothing: the page stays at 4,096. The maximum is 65,536 and anything asked for above it is silently discarded, so the only way to know whether it took is to read it back. It is the same failure mode page_size and auto_vacuum already have on a database with tables: accepted, and doing nothing.

PRAGMA page_size = 131072;   -- ni error ni aviso
PRAGMA page_size;            -- 4096   <- no lo cogio
PRAGMA page_size = 65536;    -- este si

How much actually fits

The maximum file size is not a constant: it is max_page_count times the page size. With the factory values — 1,073,741,823 pages of 4,096 bytes — that comes to 4 TiB, and raising the page to 65,536, 64 TiB. Long before getting near that, something else runs out: a text does not go past 1,000,000,000 bytes, and a SELECT with more than 250,000 parameters cannot even be prepared.

And a warning about the table above: it is this binary's. The one on a phone, the one in an embedded library, or one somebody compiled with their own flags may carry different numbers, which is why the useful answer is never the value: it is the command that asks for it.

PRAGMA compile_options;
-- MAX_COLUMN=2000          MAX_COMPOUND_SELECT=500   MAX_ATTACHED=10
-- MAX_LENGTH=1000000000    MAX_PAGE_SIZE=65536       MAX_EXPR_DEPTH=1000
-- MAX_VARIABLE_NUMBER=250000                         MAX_FUNCTION_ARG=1000

PRAGMA max_page_count;   -- 1073741823
-- x 4096 de pagina = 4 TiB de archivo; x 65536 = 64 TiB

Lowering them is a defence

That sqlite3_limit can only lower is not a shortcoming: it is what it exists for. An application that accepts SQL written by somebody else lowers LENGTH, COMPOUND_SELECT and EXPR_DEPTH to what it actually needs, and with that a hostile query can no longer ask for a gigabyte of memory. It is the same idea as the max_page_count in the errors topic: the ceiling you set yourself warns earlier than the system's, and warns about something you can fix.

Keywords: limits, ceilings, compile_options, MAX_COLUMN, MAX_COMPOUND_SELECT, MAX_ATTACHED, MAX_LENGTH, MAX_PAGE_SIZE, max_page_count, sqlite3_limit, maximum size, columns, ATTACH

The summary of the SQLite handbook: seven habits with the measurement behind each, four of them in the lines that go right after opening the connection, and the six that take the pulse of somebody else's file.

Applies to: SQLite 3.35+

This is the end of the handbook, and it brings nothing new: it gathers what each topic left measured. What stands out is where four of the seven fall: in the lines written right after opening the connection, which almost no program writes.

1. PRAGMA journal_mode = WAL. It stays written in the file, so once is enough. With it, a reader went on reading while another was writing, without blocking for an instant. What it does not fix is the number of writers: still one.

2. PRAGMA foreign_keys = ON, on every connection. It is the only thing on this list that changes what the database accepts, and it comes switched off: with it off, an orphan child goes in without a murmur. And it does not stay put, so it belongs in the same place as the busy_timeout.

3. PRAGMA busy_timeout, and set by you. The engine ships it at 0 — it answers SQLITE_BUSY on the spot — but many drivers change it on connecting: Python's leaves it at 5,000 without saying so. So the number that matters is not the one in the documentation: it is the one PRAGMA busy_timeout returns on your connection.

4. Group your writes into a transaction. It is, by a distance, what changes most: the same 20,000 inserts took 3,963 ms one by one and 9 ms inside a BEGIN. And mind the middle layer: a driver's "batch insert" call does not open a transaction on its own.

PRAGMA journal_mode;    -- wal, o delete si nadie lo ha tocado
PRAGMA foreign_keys;    -- 0 casi siempre, y casi siempre es un error
PRAGMA synchronous;     -- 2 con diario, 1 en WAL
PRAGMA busy_timeout;    -- el de TU conexion, no el del motor
PRAGMA page_count;      -- x page_size = lo que ocupa
PRAGMA quick_check;     -- ok

5. Back up with VACUUM INTO, never by copying the file. In WAL mode the data is in the -wal, so copying the .sqlite gives a database that opens, has not a single table, and that integrity_check calls ok. A backup that passes every check and is empty is worse than having none.

6. One index per frequent query, and look at what it weighs. dbstat says it per object, and it is surprising: in the measured database the index took 2,056,192 bytes against 1,826,816 for the table it indexed.

SELECT name, SUM(pgsize) AS bytes
  FROM dbstat
 GROUP BY name
 ORDER BY bytes DESC;
-- iv             2056192   <- el indice pesa mas que la tabla
-- t              1826816
-- sqlite_schema     4096

7. Security belongs to the file. There are no users, no roles, no GRANT: whoever can read the file can read all of it, and whoever can write it can delete it. The protection is the system's permissions, the disk's encryption and — on a phone — the data protection class. Everything else in this handbook is performance; this is the only part with no substitute.

And one that is not a habit but a boundary

SQLite takes far more than its reputation suggests, but it has one frontier no practice moves: one writer at a time. As long as the writes come from one process, or from several taking turns, the file stretches to limits hardly anybody reaches. The day two genuine simultaneous writers are needed, what has to change is not a pragma: it is the engine.

Keywords: best practices, summary, WAL, foreign_keys, busy_timeout, batched transaction, VACUUM INTO, backup, dbstat, indexes, permissions, encryption, security

Least privilege, roles, encrypted connections and the checklist before exposing a server.

Applies to: MySQL 5.7+ MariaDB 10.5+ Aurora 2+

In MySQL and MariaDB a user's identity is two things: the name and the host it connects from. 'app'@'10.0.%' and 'app'@'%' are different accounts, with different passwords and different permissions. Most security scares start by forgetting that.

Least privilege
Grant what the application uses, not one more, and to the narrowest host possible. An application account hardly ever needs DROP, and never SUPER, FILE or GRANT OPTION:

CREATE USER 'app'@'10.0.%' IDENTIFIED BY '...';

GRANT SELECT, INSERT, UPDATE, DELETE ON tienda.* TO 'app'@'10.0.%';

GRANT SELECT ON tienda.pedidos TO 'informes'@'%';

SHOW GRANTS FOR 'app'@'10.0.%';

Roles

CREATE ROLE 'lectura', 'escritura';

GRANT SELECT ON tienda.* TO 'lectura';
GRANT INSERT, UPDATE, DELETE ON tienda.* TO 'escritura';

GRANT 'lectura' TO 'informes'@'%';
GRANT 'lectura', 'escritura' TO 'app'@'10.0.%';

SET DEFAULT ROLE ALL TO 'app'@'10.0.%';

Encrypted connections
Without TLS the password and the data travel readable across the network. It can be required per account or server-wide with require_secure_transport. Calíope supports TLS in the connection profile, and SSH tunnelling too when the server is not exposed:

ALTER USER 'app'@'10.0.%' REQUIRE SSL;

SHOW VARIABLES LIKE 'require_secure_transport';

SELECT user, host, ssl_type FROM mysql.user;

Quick audit
Three queries worth running on any inherited server. Accounts with no password, accounts open to any host, and dangerous privileges handed out:

SELECT user, host FROM mysql.user WHERE authentication_string = '';

SELECT user, host FROM mysql.user WHERE host = '%';

SELECT * FROM information_schema.USER_PRIVILEGES
WHERE privilege_type IN ('SUPER', 'FILE', 'PROCESS', 'GRANT OPTION');

Before exposing a server
1. No anonymous or password-less accounts, and no sample test database.
2. root from localhost only, with a separate administrative account for everything else.
3. bind-address on the right interface — not 0.0.0.0 if nobody outside should reach it.
4. TLS required for any connection leaving the machine.
5. Passwords managed outside the code — Calíope keeps them in the Keychain, never in plain text.
6. Separate accounts per application, so one compromise doesn't drag the rest along.
7. Review the GRANTs regularly: permissions pile up and nobody removes them.

Recommendation
Start by revoking rather than granting: create the account with nothing and add privileges until the application works. Calíope's Users tool shows effective privileges per database and per table, which is where the surprises usually turn up.

Keywords: security, user, role, privilege, grant, revoke, least privilege, ssl, tls, require ssl, hardening, mysql.user, user_privileges, audit

Logical versus physical, what the binlog is for and how to get back to the minute before the DELETE.

Applies to: MySQL 5.7+ MariaDB 10.5+ Aurora 2+

A backup that has never been restored is not a backup: it is an intention. Two numbers rule here: the RPO (how much data you accept losing) and the RTO (how long you can be down). Everything else follows from those.

Logical versus physical
- Logical (mysqldump, Calíope's Backup) — produces SQL. Portable across versions and engines, lets you restore a single table, and is slow to restore at large volumes.
- Physical (volume snapshot, Percona XtraBackup, copying the directory with the server stopped) — copies the files. Extremely fast to restore, but tied to the server's version and architecture.

Rule of thumb: up to a few tens of gigabytes, logical; beyond that, physical for the full copy and logical for individual pieces.

The binlog is the missing half
The backup takes you back to the moment it was made. The binary log holds everything that happened afterwards, and it is what lets you move forward from there to one second before the disaster. Without log_bin enabled there is no point-in-time recovery, only a return to the last copy:

SHOW VARIABLES LIKE 'log_bin';
SHOW VARIABLES LIKE 'binlog_format';
SHOW VARIABLES LIKE 'binlog_expire_logs_seconds';

SHOW BINARY LOGS;

Recovering to a point in time
The procedure, always on a separate server and never on the one in production:
1. Restore the most recent full copy taken before the incident.
2. Find the exact moment of the mistake in the binlog: the statement that deleted too much, and its position or timestamp.
3. Replay the binlog from the position where the copy ended up to just before that statement, with mysqlbinlog and its --start-position and --stop-position options (or --start-datetime and --stop-datetime).
4. Check that the data is there, and only then decide whether to promote that server or export the missing pieces from it.

Calíope's Binlog Viewer is for step 2: it filters events by date, database and operation type, which is the part that is hard to do by hand.

Finding the position
SHOW MASTER STATUS gives the current file and position; the events of a given binlog are listed like this:

SHOW MASTER STATUS;

SHOW BINLOG EVENTS IN 'binlog.000042'
LIMIT 20;

Verifying the restore
Restoring without checking is the usual way to find the problem too late. A count per database and a CHECKSUM TABLE of the critical tables against the source are enough to sleep well:

SELECT table_schema, COUNT(*) AS tablas, SUM(table_rows) AS filas
FROM information_schema.TABLES
WHERE table_type = 'BASE TABLE'
GROUP BY table_schema;

CHECKSUM TABLE pedidos, lineas;

Recommendation
Schedule the backup (Calíope does it, with configurable retention), keep a copy off the machine, enable log_bin with a retention covering at least two backup cycles, and rehearse a full restore at least once. The day of the incident is not the day to learn the procedure.

Keywords: backup, restore, recovery, pitr, point in time, binlog, mysqldump, mysqlbinlog, rpo, rto, checksum table, snapshot

ALGORITHM, LOCK, metadata locks and when an external tool is needed.

Applies to: MySQL 5.7+ MariaDB 10.5+ Aurora 2+

An ALTER TABLE on a large table can take hours and leave the application waiting. Since MySQL 5.6 and MariaDB 10.0 you can state how the change must be made, and so know in advance whether it is going to hurt.

Ask for the algorithm, don't trust luck
If you declare the algorithm and the server cannot use it, the statement fails immediately instead of locking your table for three hours. That is the main reason to always write it:

ALTER TABLE pedidos
    ADD COLUMN nota VARCHAR(255) NULL,
    ALGORITHM=INSTANT;

ALTER TABLE pedidos
    ADD INDEX idx_fecha (fecha),
    ALGORITHM=INPLACE, LOCK=NONE;

ALTER TABLE pedidos
    MODIFY COLUMN total DECIMAL(12,2) NOT NULL,
    ALGORITHM=COPY, LOCK=SHARED;

The LOCK clause
- LOCK=NONE — reads and writes continue during the change. If that isn't possible, error.
- LOCK=SHARED — reads allowed, writes not.
- LOCK=EXCLUSIVE — nobody touches the table.

Declaring LOCK=NONE is how you guarantee the migration won't stop production: either it runs without blocking, or it doesn't run.

The metadata lock, the one that surprises people
Even an instant ALTER needs an exclusive metadata lock at the start and at the end. If an old transaction is open on that table, the ALTER waits — and every query arriving afterwards queues up behind it. A table freezes because of an ALTER that was going to take a millisecond. Before touching the schema, check there are no long transactions:

SELECT object_name, lock_type, lock_status, owner_thread_id
FROM performance_schema.metadata_locks
WHERE object_schema = DATABASE();

SELECT @@lock_wait_timeout;

Watching progress
An ALTER running for hours gives no sign of life by itself. performance_schema does:

SELECT stage, work_completed, work_estimated,
       ROUND(work_completed / work_estimated * 100, 1) AS pct
FROM performance_schema.events_stages_current;

SHOW PROCESSLIST;

When an external tool is needed
If the change forces ALGORITHM=COPY on a table of tens of gigabytes, no LOCK will save you. That is where pt-online-schema-change (Percona) and gh-ost (GitHub) come in: they create a new table, copy in batches, keep it in sync with triggers or by reading the binlog, and swap at the end in an instant. They don't ship with the server; they are installed separately and run from the command line.

Recommendation
Always write ALGORITHM= and LOCK= in your migrations, and try them first on a copy with real data so you know how long they will take. An ALTER that fails after a second is good news compared to one that locks the table halfway through the morning.

Keywords: online ddl, alter table, algorithm, instant, inplace, copy, lock=none, metadata lock, mdl, pt-online-schema-change, gh-ost, schema migration

Why utf8 is not UTF-8, what a collation decides and how to convert without breaking indexes.

Applies to: MySQL 5.7+ MariaDB 10.5+ Aurora 2+

Two concepts that get mixed up constantly: the character set says which characters can be stored, and the collation says how they compare and sort. The first affects what fits; the second, what a WHERE returns.

utf8 is not UTF-8
In MySQL, utf8 is a historical alias for utf8mb3: only three bytes per character, so it cannot store emoji nor much of modern Chinese, Japanese or Korean. Real UTF-8 is utf8mb4. It is the product's most repeated trap, and in MySQL 8.0 it is still alive for compatibility. Check where you stand:

SELECT default_character_set_name, default_collation_name
FROM information_schema.SCHEMATA
WHERE schema_name = DATABASE();

SELECT table_name, column_name, character_set_name, collation_name
FROM information_schema.COLUMNS
WHERE table_schema = DATABASE() AND character_set_name IS NOT NULL
  AND character_set_name <> 'utf8mb4';

SHOW VARIABLES LIKE 'character_set%';

What a collation decides
The name says it all once you can read it. In utf8mb4_0900_ai_ci: 0900 is the Unicode version, ai is accent-insensitive and ci is case-insensitive. Their opposites are as (accent-sensitive) and cs (case-sensitive). There is also utf8mb4_bin, which compares byte by byte and knows nothing about languages.

Defaults differ: MySQL 8.0 uses utf8mb4_0900_ai_ci and MariaDB utf8mb4_general_ci or utf8mb4_uca1400_ai_ci depending on the version. If you move data between the two, don't assume they sort alike.

What changes in practice
With an ai_ci collation, café and cafe are the same value: a UNIQUE will reject the second, and a WHERE will find both. That may be exactly what you want for searching names, and a disaster for storing identifiers:

SELECT 'cafe' = 'café' COLLATE utf8mb4_0900_ai_ci AS acentos_iguales,
       'Ana'  = 'ana'  COLLATE utf8mb4_0900_ai_ci AS mayusculas_iguales;

SELECT * FROM clientes
WHERE nombre = 'jose' COLLATE utf8mb4_0900_as_cs;

SHOW COLLATION WHERE charset = 'utf8mb4';

Mixing collations hurts
A JOIN between a utf8mb4_general_ci column and a utf8mb4_0900_ai_ci one gives error 1267 Illegal mix of collations. And if you patch it by wrapping the column in CONVERT() or a COLLATE, the query can no longer use that column's index. The real fix is not the COLLATE in the query: it is unifying the collation in the schema.

Converting without surprises
ALTER DATABASE only changes the default for future tables; existing ones must be converted one by one. And CONVERT TO CHARACTER SET rewrites the whole table, so it deserves the same caution as any heavy DDL:

ALTER DATABASE tienda
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_0900_ai_ci;

ALTER TABLE clientes
    CONVERT TO CHARACTER SET utf8mb4
    COLLATE utf8mb4_0900_ai_ci;

Recommendation
utf8mb4 everywhere — server, database, table, column and client connection — and a single collation across the schema. Before converting, look at indexes on long text columns: going from utf8mb3 to utf8mb4 each character may take one more byte, and an index that fitted may stop fitting.

Keywords: charset, character set, collation, utf8, utf8mb4, latin1, emoji, accents, case sensitivity, convert to character set, illegal mix of collations

The codes you meet most — 1045, 1062, 1213, 2006 — and what to do about each.

Applies to: MySQL 5.7+ MariaDB 10.5+ Aurora 2+

Codes below 2000 come from the server; those from 2000 up, from the client library. That distinction alone tells you where to look: if the number starts with 2, the problem is in the connection, not in the SQL.

1045 and 1040: the connection
The 1045 is hardly ever the password: it's that the account exists for a different host. Remember that 'app'@'localhost' and 'app'@'%' are different accounts. The 1040 means connections ran out, and the usual cause is not the pool size but connections nobody closes:

SHOW VARIABLES LIKE 'max_connections';
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';

SHOW VARIABLES LIKE 'wait_timeout';
SHOW VARIABLES LIKE 'max_allowed_packet';

1062: duplicate entry
The message names the key that clashed. If the duplicate is expected — a re-run import, an upsert — there is syntax to stop treating it as an error:

SELECT email, COUNT(*) AS repetidos
FROM clientes
GROUP BY email
HAVING repetidos > 1;

INSERT INTO clientes (email, nombre) VALUES ('a@b.c', 'Ana')
ON DUPLICATE KEY UPDATE nombre = VALUES(nombre);

1215: cannot create the foreign key
The message is famous for saying nothing. The real causes are always the same four: the two columns' types don't match exactly (sign and length included), their character sets don't match, an index is missing on the referenced column, or orphan rows already exist that the constraint wouldn't allow:

SELECT constraint_name, table_name, referenced_table_name
FROM information_schema.REFERENTIAL_CONSTRAINTS
WHERE constraint_schema = DATABASE();

SELECT l.* FROM lineas l
LEFT JOIN pedidos p ON p.id = l.pedido_id
WHERE p.id IS NULL;

Reading the error properly
Before searching the code online, read the whole thing: MySQL usually names the exact table, column and value. And when a statement returns a warning instead of an error, SHOW WARNINGS right afterwards reveals what the server decided on its own — a silent truncation, for instance — which is worse than a clean failure.

Recommendation
Calíope shows the server's code and message as they are, without wrapping them: that text is the best clue and is worth copying in full when you ask for help. The Query Log also keeps the statement that caused it, with its time and duration.

Keywords: error, error code, 1045, 1049, 1062, 1146, 1213, 1205, 1215, 1267, 1406, 2002, 2006, 2013, 1040, too many connections, gone away, access denied, duplicate entry