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+ SQL Server 2022+

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 customer_phones (
    customer_id  INTEGER NOT NULL,
    phone    TEXT NOT NULL,
    kind        TEXT NOT NULL CHECK (kind IN ('mobile', 'home', 'office')),
    PRIMARY KEY (customer_id, phone),
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
);

-- SQL Server: surrogate key and a named closed set
CREATE TABLE customer_phones (
    id          BIGINT IDENTITY(1, 1) PRIMARY KEY,
    customer_id  BIGINT NOT NULL,
    phone    VARCHAR(20) NOT NULL,
    kind        VARCHAR(10) NOT NULL
        CONSTRAINT ck_phone_kind CHECK (kind IN ('mobile', 'home', 'office')),
    UNIQUE (customer_id, phone),
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
);

-- Changing the list later
ALTER TABLE customer_phones DROP CONSTRAINT ck_phone_kind;
ALTER TABLE customer_phones ADD CONSTRAINT ck_phone_kind
    CHECK (kind IN ('mobile', 'home', 'office', 'fax'));

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 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;

ALTER TABLE orders
    ADD total DECIMAL(12, 2) NOT NULL DEFAULT 0;

CREATE TRIGGER order_line_after_insert
ON order_line
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE p
       SET total = (SELECT COALESCE(SUM(d.quantity * d.unit_price), 0)
                    FROM order_line d
                    WHERE d.order_id = p.id)
      FROM orders p
     WHERE p.id IN (SELECT order_id FROM inserted);
END;

CREATE VIEW dbo.orders_total WITH SCHEMABINDING AS
SELECT order_id, SUM(quantity * unit_price) AS total, COUNT_BIG(*) AS lines
FROM dbo.order_line
GROUP BY order_id;

CREATE UNIQUE CLUSTERED INDEX ux_orders_total ON dbo.orders_total (order_id);

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+ SQL Server 2022+

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.

The examples use these tables:

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.name, o.id
FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.id;

-- SQL Server native
SELECT c.name, o.id
FROM customers c
FULL OUTER JOIN orders o ON o.customer_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.name, o.id
FROM orders o
CROSS JOIN customers c ON o.customer_id = c.id;

-- SQL Server: the written order rules
SELECT c.name, o.id
FROM orders o
JOIN customers c ON o.customer_id = c.id
OPTION (FORCE ORDER);

-- Each customer's most expensive order
SELECT c.name, x.total
FROM customers c
CROSS APPLY (
    SELECT TOP (1) o.total
    FROM orders o
    WHERE o.customer_id = c.id
    ORDER BY o.total DESC
) x;

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, on SQLite 3.51 and on SQL Server 2025.

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.name
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_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, CROSS APPLY, OUTER APPLY, FORCE ORDER

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 the theoretical limit is in 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+ SQL Server 2022+

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

Schema design

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;

-- The schema first, in its own batch: CREATE SCHEMA sales;
CREATE TABLE sales.orders (
    id           BIGINT IDENTITY(1, 1) CONSTRAINT pk_orders PRIMARY KEY,
    customer_id  BIGINT       NOT NULL,
    status       TINYINT      NOT NULL CONSTRAINT df_orders_status DEFAULT 0,
    created_at   DATETIME2(3) NOT NULL CONSTRAINT df_orders_created_at DEFAULT SYSUTCDATETIME(),
    updated_at   DATETIME2(3) NOT NULL CONSTRAINT df_orders_updated_at DEFAULT SYSUTCDATETIME(),
    CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id)
        REFERENCES sales.customers (id) ON UPDATE CASCADE,
    CONSTRAINT ck_orders_status CHECK (status BETWEEN 0 AND 4)
);
CREATE INDEX idx_orders_customer_id ON sales.orders (customer_id);
CREATE INDEX idx_orders_created_at  ON sales.orders (created_at);

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

-- 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=...

ALTER DATABASE my_db SET RECOVERY FULL;
BACKUP DATABASE my_db TO DISK = N'/var/opt/mssql/data/my_db_full.bak'
    WITH CHECKSUM, COMPRESSION, INIT;
BACKUP LOG my_db TO DISK = N'/var/opt/mssql/data/my_db_log.trn'
    WITH CHECKSUM, INIT;
RESTORE VERIFYONLY FROM DISK = N'/var/opt/mssql/data/my_db_full.bak'
    WITH CHECKSUM;

Replication

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;

USE master;
CREATE LOGIN app_orders WITH PASSWORD = N'Strong-Password-2026',
    CHECK_POLICY = ON, DEFAULT_DATABASE = my_db;
USE my_db;
CREATE USER app_orders FOR LOGIN app_orders WITH DEFAULT_SCHEMA = sales;
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::sales TO app_orders;

-- NEVER in production
-- ALTER SERVER ROLE sysadmin ADD MEMBER app_orders;
-- ALTER ROLE db_owner ADD MEMBER app_orders;

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';

USE master;
CREATE SERVER AUDIT aud_sales TO FILE (FILEPATH = N'/var/opt/mssql/data/');
ALTER SERVER AUDIT aud_sales WITH (STATE = ON);
USE my_db;
CREATE DATABASE AUDIT SPECIFICATION das_sales FOR SERVER AUDIT aud_sales
    ADD (SELECT, INSERT, UPDATE, DELETE ON SCHEMA::sales BY public)
    WITH (STATE = ON);

SELECT event_time, action_id, server_principal_name, statement
FROM sys.fn_get_audit_file(N'/var/opt/mssql/data/aud_sales*.sqlaudit', DEFAULT, DEFAULT);

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

Production minimum checklist

Keywords: best practices, design, conventions, backup, replication, GTID, binlog, GRANT, auditing, security, InnoDB, foreign key, TLS, SQL Server, IDENTITY, SEQUENCE, schema, login, XACT_ABORT, SQL Server Audit

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+ SQL Server 2022+

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;

WITH ventas_mes AS (
    SELECT vendedor_id, DATETRUNC(month, fecha) AS mes, SUM(total) AS total
    FROM pedidos
    GROUP BY vendedor_id, DATETRUNC(month, fecha)
)
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

varchar versus nvarchar, the N prefix, _UTF8 collations, what each suffix does, collation conflicts, and why an N'…' parameter can leave your index unused.

Applies to: SQL Server 2022+

If you come from MySQL, there is no utf8mb4 and no SET NAMES here. In SQL Server the encoding of a piece of text is not declared separately: its collation decides it, and there are two families of text types.

varchar and nvarchar are two encodings
- nvarchar (and nchar) stores UTF-16: any Unicode character, whatever the collation.
- varchar (and char) stores the bytes of its collation's code page. With a classic collation such as Latin1_General_CI_AS that is Windows-1252, and anything outside it is stored as ?.
- With a collation ending in _UTF8 (since SQL Server 2019), varchar stores UTF-8.

Measured on the test server (SQL Server 2025), with the database in Latin1_General_100_CI_AS_SC_UTF8:

LEN() counts characters and DATALENGTH() counts bytes. The n in varchar(n) is bytes, and in nvarchar(n) byte pairs, not characters. Measured: a UTF-8 varchar(10) holds five ñ, and ten give Msg 2628 ("String or binary data would be truncated"); an nvarchar(10) holds ten ñ but not six emojis.

The N prefix
A literal without an N in front is varchar, and it is converted to the database's code page before it reaches any column. Measured in a Latin1_General_CI_AS database:

- 'Zúrich ✓' → Zúrich ?
- N'Zúrich ✓' → Zúrich ✓

And in a varchar column of that database, even with the N on the literal, Zúrich ✓ 日本 was stored as Zúrich ? ??. The data is lost on write, with no error and no warning, and changing the column's collation afterwards does not bring it back: what was stored is already question marks.

What each suffix says

SELECT CASE WHEN 'a' = 'A' COLLATE Latin1_General_100_CI_AS THEN 1 ELSE 0 END AS ci,
       CASE WHEN 'a' = 'A' COLLATE Latin1_General_100_CS_AS THEN 1 ELSE 0 END AS cs,
       CASE WHEN 'e' = 'é' COLLATE Latin1_General_100_CI_AI THEN 1 ELSE 0 END AS ai,
       CASE WHEN 'e' = 'é' COLLATE Latin1_General_100_CI_AS THEN 1 ELSE 0 END AS acc;

Measured: 1, 0, 1, 0.

- _CI / _CS — case-insensitive / case-sensitive.
- _AI / _AS — accent-insensitive / accent-sensitive.
- _SC — counts characters outside the basic plane, such as an emoji, as one. Measured: LEN(N'😀') is 1 with _SC and 2 without it, and without _SC a LIKE N'_' does not match it.
- _UTF8 — varchar in UTF-8.
- _BIN2 — compares by code point: it sorted A, B, a, b, n, o, á, ñ, where Latin1_General_100_CI_AS and Modern_Spanish_100_CI_AS gave A, a, á, b, B, n, ñ, o.
- The 100 is the version of the sort tables; the ones without it (Latin1_General_CI_AS) are older, and the ones starting with SQL_ are the legacy ones from SQL Server 2000.

The test server offers 5540 collations, 1585 of them _UTF8 (sys.fn_helpcollations()).

Server, database, column and expression

SELECT SERVERPROPERTY('Collation'), DATABASEPROPERTYEX('demo', 'Collation');

The collation is set at four levels, and each one takes the level above's when it is created:

- Server — the one for master and tempdb, chosen at install time.
- Database — CREATE DATABASE … COLLATE ….
- Column — name varchar(40) COLLATE Latin1_General_100_CS_AS.
- Expression — WHERE a = b COLLATE Latin1_General_100_CI_AS, or inside an ORDER BY.

ALTER DATABASE … COLLATE does not touch the columns that already exist: measured, the database moved to _UTF8 and its columns stayed in Latin1_General_CI_AS. Each column is changed with its own ALTER TABLE … ALTER COLUMN.

Collation conflicts
Two texts with different collations do not compare on their own:

- WHERE a = b → Msg 468, "Cannot resolve the collation conflict".
- A UNION ALL of both columns → Msg 457; with catalog text (sys.* is in Latin1_General_CI_AS) → Msg 451.
- It is solved by putting COLLATE on one side.

The most frequent case is temporary tables: #tmp lives in tempdb and is born with the server's collation, not your database's. Measured in a Latin1_General_100_CI_AS database on a _UTF8 server: a JOIN with #tmp gave Msg 468. The way out is to declare its columns with COLLATE DATABASE_DEFAULT.

Collation and indexes
On 100,000 rows with an index on the column:

- WHERE s = 'usuario42' → Index Seek. With COLLATE on the column → Index Scan: the whole index is read.
- WHERE s LIKE 'usuario42%' → Index Seek, unlike PostgreSQL.
- An N'…' parameter against a varchar column: with a Windows collation (Latin1_General_100_CI_AS) the index was still used, with 7 logical reads; with a SQL_ collation (SQL_Latin1_General_CP1_CI_AS) the whole column is converted to nvarchar and the plan becomes an Index Scan: 334 reads against 3. If an equality search is slow and the parameter arrives as nvarchar, check the column's collation.

Recommendation
For a new database, a _100_…_SC_UTF8 collation, which makes varchar full UTF-8. Declare temporary table columns with COLLATE DATABASE_DEFAULT. And decide the collation when you create the database: changing it later is column by column.

Keywords: encoding, collation, collate, varchar, nvarchar, utf8, utf-16, n prefix, unicode, _sc, _bin2, 468, 451, 457, 2628, tempdb, database_default, datalength

datetime versus datetime2, bit instead of boolean, money versus decimal, GUIDs that don't fragment, rowversion that isn't a date, the 8000-byte limit and the 2025 json type.

Applies to: SQL Server 2022+

If you come from MySQL, several types have similar names and behave differently, and two names mislead: timestamp is not a date and datetime rounds. What doesn't exist raises an error, which is a good thing: there is no UNSIGNED, tinyint goes only from 0 to 255 (with -1 you get Msg 220, arithmetic overflow) and SELECT TRUE gives Msg 207: TRUE is not a word of the language.

Dates: datetime2, not datetime
datetime is the old type and rounds to 1/300 of a second. Measured:

The last row is the one that breaks reports: a WHERE fecha <= '2026-09-30 23:59:59.999' on datetime includes midnight of the next day. For a whole day, with any type, use fecha >= '2026-09-30' AND fecha < '2026-10-01'.

datetime2 stores down to a hundred nanoseconds (in datetime2(7), .1234567 stayed exactly as written), starts in year 1 —'1700-01-01' in datetime gives Msg 242, out of range— and takes the same space or less: measured, datetime 8 bytes, datetime2(7) 8, datetime2(3) 7 and datetime2(0) 6. And don't mix them without thinking: the same 12:00:00.001 in datetime2(3) and in datetime is not equal, because datetime had already stored it as .000.

Other measured sizes: date 3 bytes, time 5, smalldatetime 4 (to the minute) and datetimeoffset 10.

datetimeoffset stores the offset
Unlike PostgreSQL's timestamptz, it does store the offset it was written with, and compares by instant: '2026-09-30T12:00:00+05:30' and '2026-09-30T06:30:00+00:00' came out equal. SWITCHOFFSET(value, '+00:00') takes it to UTC, and AT TIME ZONE 'Central European Standard Time' returned 08:30 +02:00, with daylight saving time applied. Calíope shows it in the value's own time, with its offset next to it.

There is no boolean: there is bit
bit is a number that is 0 or 1, not a truth value. Measured: assigning 5, -1 or the string 'true' stores 1; a bare WHERE @b gives Msg 4145 —you have to write WHERE @b = 1— and bit + bit gives Msg 402. Calíope shows it as 1 or 0.

Money: decimal, and money with care

DECLARE @m money = 100, @m3 money = 3;
DECLARE @d decimal(19,4) = 100, @d3 decimal(19,4) = 3;
SELECT @m / @m3 * @m3 AS con_money,     -- 99.9999
       @d / @d3 * @d3 AS con_decimal;   -- 100.000000

money has four fixed decimals, in intermediate results too: 1 / 3 gave .3333, and multiplied by 3 it no longer gets back to the integer. decimal widens the result's scale (it gave .3333333333333333333). For calculations, decimal; money takes 8 bytes and smallmoney 4. float is binary as everywhere else: 0.1 + 0.2 gave 0.30000000000000004, and equality with 0.3 was false; in decimal, true.

GUIDs: uniqueidentifier, and order matters
A uniqueidentifier takes 16 bytes. As a clustered primary key, NEWID() scatters rows at random across the index. Measured with 20,000 rows inserted one by one:

NEWSEQUENTIALID() only works as a column's DEFAULT: SELECT NEWSEQUENTIALID() gives Msg 302. Calíope shows the GUID in uppercase, as SQL Server does.

rowversion is not a date
rowversion —the type formerly called timestamp, a name still accepted when creating a column— is an 8-byte database counter that changes on every write to the row. Measured: two rows were born with 0x…07D4 and 0x…07D5, and updating the first one moved it to 0x…07D6. It stores no time at all. It is for optimistic concurrency: you read the version and update with WHERE ver = @read. Calíope shows it as binary, [8 bytes].

Text: 8000 bytes, or max
Outside max, varchar(n) goes up to 8000 and nvarchar(n) up to 4000: varchar(8001) gives Msg 131 and nvarchar(4001), Msg 2717. Above that come varchar(max) and nvarchar(max).

The row has a limit too: two char(5000) columns in one table give Msg 1701 —the minimum row would be 10,007 bytes and the maximum is 8060—. Two full varchar(8000) do fit (measured: 16,000 bytes in one row), because variable data moves off the page when it doesn't fit.

And a trap that gives no warning: REPLICATE('x', 10000) assigned to a varchar(max) gave 8000 characters, because the function works in its argument's type. With REPLICATE(CAST('x' AS varchar(max)), 10000), 10,000.

JSON: the json type is from SQL Server 2025
In SQL Server 2022, JSON is stored in nvarchar(max) and validated with CHECK (ISJSON(col) = 1), which rejects malformed text with Msg 547. Since 2025 there is a json type, which rejects it on write with Msg 13609 and stores it already parsed. Measured over 20,000 copies of a 142-character order, json took 5.4 MB and nvarchar(max) 6.0 MB; for a tiny document, {"b":1,"a":2}, the other way round: 60 bytes versus 26.

And one visible difference: with a repeated key, ISJSON accepts {"b":1,"a":2,"a":3}, and the json type returned it as {"b":1,"a":2}: it kept the first one, silently. It preserves key order. Calíope receives json as text and shows it as is.

The special types
- geography and geometry — types with their own binary format, which is not WKB. geography::Point(40.4168, -3.7038, 4326) takes 22 bytes and its STAsText() gives POINT (-3.7038 40.4168): the constructor wants latitude, longitude and the text comes out as longitude, latitude. Calíope shows the cell as [22 bytes]; to read it, col.STAsText() in the query.
- hierarchyid — a path in a tree: '/1/3/' was stored in 2 bytes, with level 2 and parent /1/. Calíope doesn't decode it and the cell says unsupported type: hierarchyid; with col.ToString() in the query, it can be read.
- sql_variant — a column that holds values of several types, each with its own: SQL_VARIANT_PROPERTY(@v, 'BaseType') gave decimal. Calíope shows it by its base type. It makes sense in the catalog (sys.extended_properties); in a table of your own it is usually a badly designed column.

IDENTITY or SEQUENCE
IDENTITY is a property of the column, and it protects it: inserting a value by hand gives Msg 544, unless you use SET IDENTITY_INSERT tabla ON, and afterwards the counter continues from the highest (after inserting 100, the next row was 101). And it leaves gaps: an INSERT undone with ROLLBACK used up 3, and the next row was 4. A gap is not lost data.

SEQUENCE is a separate object that several tables can share: with DEFAULT NEXT VALUE FOR sq on two tables, the values were split 1 and 3 in one and 2 in the other. Use it when you need the number before inserting, or shared; for a table's key, IDENTITY.

Recommendation
datetime2 for clock readings and datetimeoffset for instants, decimal for money, bit knowing it is a number, NEWSEQUENTIALID() or an integer if the clustered key is a GUID, and the json type only if your oldest server is 2025. For text, see “Encoding and collations (SQL Server)”.

Keywords: types, datetime, datetime2, datetimeoffset, date, time, bit, boolean, money, decimal, float, uniqueidentifier, guid, newsequentialid, rowversion, timestamp, varchar, nvarchar, max, json, isjson, hierarchyid, geography, sql_variant, identity, sequence, unsigned

How to read a Msg, what its level tells you, the numbers you see every day, which errors abort the transaction and which don't, TRY…CATCH with THROW, and where the reason for an 18456 lives.

Applies to: SQL Server 2022+

If you come from MySQL, there are numbers here too —no SQLSTATE as in PostgreSQL—, but every error carries four pieces of data and all of them matter. This is how Calíope shows it, on one line; sqlcmd splits it in two:

Msg 2627, Level 14, State 1, Line 1: Violation of PRIMARY KEY constraint 'PK__padre__3213E83F9B0ABF57'. Cannot insert duplicate key in object 'dbo.padre'. The duplicate key value is (1).

- Msg — the number. It's what you look up and what you code against.
- Level — the severity, from 0 to 25. It says whose fault it is and what happens to the connection.
- State — a sub-code the server uses to tell apart causes that share a number. For most errors it doesn't matter; for 18456, it's the only thing that says why.
- Line — the line within the batch (what sits between two GOs), or within the procedure: an error inside one comes out as Procedure p_falla, Line 3.

The level, which is the first thing to look at

A level of 20 or more can't be raised by hand without being sysadmin and without WITH LOG: it gives Msg 2754. Calíope treats level 20 or more as a lost connection.

The ones you see every day
Triggered one by one against SQL Server 2025:

To see the text of any number: SELECT text FROM sys.messages WHERE message_id = 1205 AND language_id = 1033;. There are 16,785 messages in English, in 22 languages.

What each error undoes: the trap for anyone coming from PostgreSQL
In PostgreSQL an error aborts the whole transaction. Here it depends on the error, and most of them only abort the statement. Measured, with nothing switched on:

BEGIN TRAN;
INSERT padre VALUES (10, 'd@x.es', 1);
INSERT padre VALUES (10, 'e@x.es', 1);  -- Msg 2627
INSERT padre VALUES (11, 'f@x.es', 1);
COMMIT;
-- rows 10 and 11 remained

The transaction stayed open, the COMMIT confirmed what did work, and the batch carried on as if nothing had happened. What each measured error did:

- Only the statement: 2627, 2601, 547, 515, 2628, 8134, and also 1222: after the wait ran out, @@TRANCOUNT was still 1.
- The batch and the transaction: 245 (conversion) rolled back the transaction and cut the batch with nothing switched on; 1205 too, in the victim; a ROLLBACK inside a trigger gives Msg 3609 and cuts the batch.
- The connection: level 20 or more.

SET XACT_ABORT ON changes it: with it, the same 2627 rolled back the whole transaction and cut the batch (rows 20 and 21 weren't kept). It's what you put at the top of every procedure that opens a transaction.

TRY…CATCH, and what it doesn't catch

SET XACT_ABORT ON;
BEGIN TRY
  BEGIN TRAN;
  -- …
  COMMIT;
END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0 ROLLBACK;
  THROW;
END CATCH;

Inside the CATCH, ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_LINE(), ERROR_PROCEDURE() and ERROR_MESSAGE() return the error's details (measured: 2627, 14, 1, 4, NULL). And XACT_STATE() says what you can do with the transaction: it gave 1 without XACT_ABORT (it can be committed) and -1 with it (it can only be rolled back).

What a TRY does not catch: an object that doesn't exist in its own scope. SELECT * FROM tabla_que_no_existe inside a TRY came out as Msg 208 without going through the CATCH, because it fails at compile time; inside an EXEC sp_executesql, it was caught. Nor does it catch anything of level 10 or lower.

THROW versus RAISERROR
- THROW; with no arguments, inside a CATCH, rethrows the original error: the number that reached the outside was still 8134. Rethrowing it with RAISERROR(ERROR_MESSAGE(), 16, 1) turned it into 50000, and the number was lost.
- RAISERROR doesn't stop the batch: what came after it ran. THROW does.
- THROW 50001, 'text', 1 raises your own error, always at level 16 and with a number from 50000 up. RAISERROR also accepts a number registered with sp_addmessage and fills in its arguments: RAISERROR(50100, 16, 1, 42) gave "El pedido 42 no existe".

The text changes with the language; the number doesn't
With SET LANGUAGE Spanish, 8134 arrived as "Error de división entre cero.": the same number with different text. The language is the session's, and Calíope connects with the login's default language. That's why, in code and in your own logs, you compare the number, never the text.

18456: the reason is in the server log
A failed login always reaches the client the same way: Msg 18456, Level 14, State 1, "Login failed for user '…'". Measured with three different causes, the client saw State 1 all three times. The real state is only in the server log:

EXEC xp_readerrorlog 0, 1, N'Login failed';

When the database requested at connect time doesn't exist, a 4060 arrives before the 18456 ("Cannot open database … requested by the login"), and that one does give the reason. If someone "can't get in", the server log tells apart in a second what looks like the same sentence from outside.

What Calíope shows
When a statement fails, Calíope shows the server's message in full in the results tab, in the Msg, Level, State, Line form above, and all the messages, one per line: a CREATE TABLE with a repeated constraint brings 2714 and then 1750, and both say something. Those errors stay in their tab. The ones that prevent connecting —18456, 4060— and those of level 20 or more, which cut the connection, also reach Calíope's error log. Informational messages —a PRINT, a level-10 RAISERROR— aren't shown.

Recommendation
SET XACT_ABORT ON and TRY…CATCH with IF @@TRANCOUNT > 0 ROLLBACK; THROW; in every procedure that opens a transaction. In the application, retry 1205 —the whole transaction, which the server has already rolled back— and don't retry levels 14 to 16, which are about the data or the program. Always store the number, never the text. And faced with an 18456, read the server log before touching the password.

Keywords: error, msg, level, severity, state, 18456, 1205, 2627, 2601, 547, 208, 102, 2628, 8152, 515, 229, 916, 4060, 1222, try, catch, throw, raiserror, xact_abort, xact_state, error_number, sys.messages, xp_readerrorlog, retry

The clustered index that is the table, the Key Lookup and when INCLUDE removes it, filtered indexes, computed columns instead of expression indexes, columnstore, FILLFACTOR and fragmentation, and the indexes nobody uses.

Applies to: SQL Server 2022+

If you come from MySQL, the closest thing is InnoDB: here too the table is an index. What changes is that you choose that index —the clustered one— and it doesn't have to be the primary key, that a table may not have one at all, and that instead of prefix or invisible indexes there are filtered indexes, computed columns and columnstore.

Everything below is measured on a table of 200,000 rows and about 32 MB.

The table is the clustered index
PRIMARY KEY creates a clustered index if the table doesn't have one yet: the rows are stored ordered by that key. A table without a clustered index is a heap (HEAP in sys.indexes): rows go wherever they fit.

What you don't see from MySQL is the price: the clustered key travels inside every nonclustered index, because that is how the index finds the row. The same index on cliente_id took 2,952 kB with an int clustered key and 5,416 kB with a uniqueidentifier one. A narrow, ever-increasing clustered key makes every other index cheaper; why NEWID() is a poor clustered key is covered in the data types topic.

Key Lookup, and what INCLUDE fixes
A nonclustered index only carries its own columns and the clustered key. If the query asks for another one, every row found forces a trip back to the table: that is the Key Lookup.

CREATE INDEX ix_cliente ON dbo.pedidos (cliente_id);
SELECT cliente_id, total FROM dbo.pedidos WHERE cliente_id = 4242;
-- Index Seek (ix_cliente) + Clustered Index Seek … LOOKUP: 29 logical reads

CREATE INDEX ix_cliente_inc ON dbo.pedidos (cliente_id) INCLUDE (total);
-- Index Seek (ix_cliente_inc) and nothing else: 3 logical reads

INCLUDE stores the column in the leaves of the index without sorting by it. Here the index grew from 2,952 kB to 4,752 kB.

The Key Lookup is paid row by row, so it stops paying off very early. Measured: with 992 rows (0.5% of the table) the plan still used the index and the lookup; with 1,483 (0.7%) it was already scanning the whole table. An index that "isn't used" is often an index missing an INCLUDE.

The leftmost-prefix rule
With an index on (fecha, cliente_id) and WHERE cliente_id = 77, SQL Server can't seek down the index. Like PostgreSQL, it can scan it whole if that is cheaper than the table: Index Scan and 809 logical reads. It isn't an Index Seek, and column order still matters.

Filtered indexes
An index can carry a WHERE, and then it only indexes what satisfies it:

CREATE INDEX ix_pend ON dbo.pedidos (cliente_id) WHERE estado = 'pendiente';

With pending orders at 5%, it took 160 kB, against 2,952 kB for the full index on the same column. The catch is that the plan has to work for any value: with the literal WHERE estado = 'pendiente' it used it; with a variable (WHERE estado = @e), it didn't, and forcing it with WITH (INDEX(ix_pend)) gives Msg 8622.

And one use that surprises MySQL users: in SQL Server a unique index allows a single NULL. CREATE UNIQUE INDEX ux_email ON dbo.pedidos (email) with two emails set to NULL gives Msg 1505 ("The duplicate key value is (<NULL>)"). The way out is a filtered index:

CREATE UNIQUE INDEX ux_email ON dbo.pedidos (email) WHERE email IS NOT NULL;

No expression indexes: computed columns instead
A function on the WHERE column prevents seeking down the index. On the same 14,400 rows of one month:

- WHERE YEAR(fecha) = 2023 AND MONTH(fecha) = 6: Index Scan, 510 reads.
- WHERE fecha >= '2023-06-01' AND fecha < '2023-07-01': Index Seek, 40 reads.

When the expression can't be rewritten as a range, you index a computed column:

ALTER TABLE dbo.pedidos ADD email_lower AS LOWER(email);
CREATE INDEX ix_email_lower ON dbo.pedidos (email_lower);

SELECT id FROM dbo.pedidos WHERE LOWER(email) = 'u77@ejemplo.com';
-- Index Seek (ix_email_lower), without naming the computed column

To create that index, and afterwards to write to the table, the session needs QUOTED_IDENTIFIER and ANSI_NULLS on; sqlcmd turns the first one off unless you pass -I, and then it fails with Msg 1934.

Columnstore, for aggregation
A columnstore index stores each column separately and compressed. The same table took 35,520 kB with its regular clustered index and 7,104 kB as a CLUSTERED COLUMNSTORE. A SELECT estado, SUM(total) … GROUP BY estado read 4,440 pages and used 19 ms of CPU on the first, and 147 pages and 2 ms on the second. To look up one row by its key a regular index is still better; the usual move on a working table is to add a NONCLUSTERED COLUMNSTORE with the columns you aggregate (the four-column one took 3,128 kB).

Foreign keys aren't indexed on their own
InnoDB creates an index for each foreign key; SQL Server doesn't. Measured: after cliente_id int REFERENCES dbo.clientes(id), the child table only had its primary key index. Without that index, every DELETE on the parent table scans the whole child table to check the constraint.

Fragmentation and FILLFACTOR

SELECT i.name, p.avg_fragmentation_in_percent, p.page_count,
       p.avg_page_space_used_in_percent
  FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('dbo.pedidos'), NULL, NULL, 'DETAILED') p
  JOIN sys.indexes i ON i.object_id = p.object_id AND i.index_id = p.index_id
 WHERE p.index_level = 0;

The index on cliente_id, with random values, measured step by step:

FILLFACTOR leaves room in each page so inserts fit without splitting it: it costs space from day one and saves the fragmentation later. It stays stored in the index (sys.indexes.fill_factor), so later REBUILDs repeat it. REORGANIZE compacts without blocking and can be interrupted; REBUILD redoes the whole index and, without ONLINE, locks the table while it runs.

Rebuilding without stopping the table

ALTER INDEX ix_cliente ON dbo.pedidos REBUILD WITH (ONLINE = ON, RESUMABLE = ON);

ONLINE = ON belongs to the Enterprise edition (and to Developer, which is the same, and to Azure SQL): in Standard it doesn't exist, and a REBUILD stops writes while it runs —and reads too, if it's the clustered index—. RESUMABLE = ON lets you pause it and continue later, and requires ONLINE = ON (without it, Msg 11438). And two cases where ONLINE = ON fails: with a spatial index on the table and with ALTER INDEX ALL.

No invisible indexes: DISABLE instead
ALTER INDEX … DISABLE is the closest thing, but it isn't the same: it deletes the index data and keeps only its definition, so going back is a full REBUILD. And disabling the clustered index leaves the table unreadable: SELECT COUNT(*) gives Msg 8655, and on the way it disables every nonclustered index, with one warning each.

The ones nobody uses, and the missing ones

SELECT OBJECT_NAME(i.object_id) AS tabla, i.name AS indice,
       u.user_seeks, u.user_scans, u.user_lookups, u.user_updates
  FROM sys.indexes i
  LEFT JOIN sys.dm_db_index_usage_stats u
    ON u.object_id = i.object_id AND u.index_id = i.index_id
   AND u.database_id = DB_ID()
 WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1 AND i.index_id > 1
 ORDER BY u.user_seeks + u.user_scans + u.user_lookups;

The caution is bigger than in PostgreSQL: this view lives in memory and is emptied when the server restarts. Measured: I restarted the container and the view came back without a single row for the table. Disabling the index also resets it; a REBUILD doesn't. An index showing 0 reads on a Monday may be one nobody has had time to use.

The other half is the missing ones: the optimizer records in sys.dm_db_missing_index_details the index it would have wanted. After a query on cliente_id and estado, it proposed [cliente_id], [estado] with an estimated impact of 99.6%. It's a hint, not an order: it doesn't look at the indexes you already have, doesn't weigh writes, and lives in the same memory that gets emptied.

Recommendation
A narrow, ever-increasing clustered key; an index for every foreign key used to search or delete; INCLUDE before another index when the plan shows a Key Lookup; and before dropping an "unused" index, check how long ago the server started (sqlserver_start_time in sys.dm_os_sys_info).

Keywords: index, clustered, nonclustered, heap, key lookup, include, filtered index, computed column, columnstore, fillfactor, fragmentation, reorganize, rebuild, online, resumable, disable, dm_db_index_usage_stats, dm_db_missing_index_details, dm_db_index_physical_stats

The limits you actually hit —8,060 bytes per row, 900 and 1,700 bytes of index key, 1,024 columns, 128-character names, 2,100 parameters—, which ones only warn when you create the table and fail later, and the ones set by Express edition.

Applies to: SQL Server 2022+

SQL Server's limits look little like InnoDB's, and the ones that bite day to day are not the big ones. I triggered them one by one against SQL Server 2025.

The ones you actually hit

The row fits in a page, except what is variable
A page is 8 KB and a row has to fit in it: 8,060 bytes. With fixed-width columns the server checks this when the table is created: two char(5000) columns give Msg 1701, which says the minimum row size would be 10,007 bytes.

With variable columns it doesn't. I stored two full varchar(8000) values in the same row —16,000 bytes— without any error: what doesn't fit is moved to separate pages, ROW_OVERFLOW_DATA. sys.allocation_units showed 2 in-row pages and 2 overflow pages. It works, but reading that row costs one extra read per overflowed column, and the design gives no sign that it is happening.

SELECT au.type_desc, SUM(au.used_pages) AS pages
FROM sys.allocation_units AS au
JOIN sys.partitions AS p ON au.container_id = p.partition_id
WHERE p.object_id = OBJECT_ID('dbo.my_table')
GROUP BY au.type_desc;

varchar(max), nvarchar(max) and varbinary(max) go up to 2 GB and leave the row the same way. Why varchar(8001) doesn't exist and you have to jump to max is covered in the data types topic.

The limit that only warns: the index key
This is the most surprising one. An index on a column wider than the limit is created, with a warning:

Warning! The maximum key length for a clustered index is 900 bytes. The index 'cx' has maximum length of 1000 bytes.

A warning doesn't stop a deployment. The table works until the day someone stores a long value: 900 bytes went in and 901 gave Msg 1946. On a nonclustered index the limit is 1,700 bytes, and with nvarchar that is 850 characters, not 1,700, because each one takes two bytes. A PRIMARY KEY on varchar(1000) is a clustered index and behaves the same way.

If a long column needs equality lookups, what works is indexing a digest: a computed column with CHECKSUM or HASHBYTES, indexed, and comparing the full value as well.

Names: an error, not truncation
A 129-character name gives Msg 103 and the statement doesn't run. PostgreSQL truncates to 63 bytes and carries on; SQL Server doesn't let the name through. A local temporary table's name is shorter, 116, because the server appends a suffix to tell sessions apart.

Parameters: 2,100, and 2,098 in practice
A procedure accepts 2,100 parameters and a call can't pass more. But drivers and ORMs send parameterized queries through sp_executesql, and there the statement and the declaration count too: 2,098 parameters worked and 2,099 gave Msg 180. It is the typical error of a WHERE id IN (@p1, @p2, …) generated from a list that grows one day. The way out isn't splitting the list: it is passing it as a table (a table-valued parameter, or a temporary table) and doing a JOIN. An IN with 60,000 literals written into the statement did work.

More columns: SPARSE with a column set
1,025 columns give Msg 1702. With SPARSE columns and a COLUMN_SET FOR ALL_SPARSE_COLUMNS I created a table with 2,002 columns; the same columns without the set gave the same Msg 1702. The documentation calls this a "wide table" and raises it to 30,000 columns, but the row still has to fit in 8,060 bytes.

Nesting: 32 levels
A procedure that calls itself reached @@NESTLEVEL 32 and the next level gave Msg 217. Procedures, functions, triggers and views count together: a trigger that updates another table with a trigger uses up levels without anyone seeing it.

The big ones, which are almost never the problem
I didn't measure them —the testbed is Developer edition and measuring them would mean filling a disk—; they come with the figure from Microsoft's documentation:

- Size of a database: 524,272 TB. Of a data file: 16 TB.
- Rows per table: no limit other than storage.
- Size of a batch: 65,536 times the network packet size (4,096 bytes by default, so 256 MB).
- Connections: 32,767 (@@MAX_CONNECTIONS), and user connections at 0 means there is no other cap.

The ones set by the editions
Here there are limits you do hit, and they come from the license, not the engine. I didn't measure these either: the testbed is Developer, which has none.

- Express: 10 GB per database in SQL Server 2022 and 50 GB in 2025; about 1.4 GB of memory for the buffer pool; the lesser of one socket or four cores. When it reaches the size, the database stops growing and writes fail.
- Standard: no database size cap, but a ceiling on memory and cores, and without some Enterprise features, such as REBUILD with ONLINE = ON.

SELECT SERVERPROPERTY('Edition') tells you which one the server you're connected to is.

Recommendation
Watch three: the index key when you index a text column, because it only warns; the 2,098 parameters when something generates IN lists; and the database size if the server is Express. You learn about the rest from the error number, which always says what the limit is.

Keywords: limits, maximum, 8060, row, page, row_overflow_data, overflow, index key, 900 bytes, 1700 bytes, 1024 columns, sparse, column_set, 128 characters, identifier, temporary table, 2100 parameters, sp_executesql, nestlevel, nesting, express, 10 gb, 50 gb, edition, msg 1946, msg 1702, msg 180

Why a SELECT waits for an UPDATE here, what READ_COMMITTED_SNAPSHOT changes, how to see who blocks whom, where the graph of a 1205 is kept, and when row locks turn into a table lock.

Applies to: SQL Server 2022+

If you come from MySQL or PostgreSQL, this is the first thing that catches you out: in SQL Server, by default, a SELECT waits for an UPDATE. InnoDB and PostgreSQL read the last committed version of the row and move on; SQL Server, under plain READ COMMITTED, asks for a shared lock and waits for the other side to commit or roll back. Everything I describe here I measured against SQL Server 2025, with two sessions open at once.

A SELECT that waits

-- Session A
BEGIN TRAN;
UPDATE dbo.cuentas SET saldo = 50 WHERE id = 1;

-- Session B
SELECT saldo FROM dbo.cuentas WHERE id = 1;

Session B sat still for 7.5 s, exactly as long as A took to ROLLBACK, waiting on LCK_M_S for that row's key. And A doesn't need to be doing anything: a session that opened a transaction, wrote and then went quiet —status sleeping, open_transaction_count 1— blocks just the same. That's the everyday case: an application that forgot its COMMIT.

READ_COMMITTED_SNAPSHOT: reading like the other two

ALTER DATABASE my_db SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;

With that option, READ COMMITTED reads the last committed version of the row, and the same read came back instantly with the old value (100, not the uncommitted 50). Three things before you turn it on:
- It belongs to the database, not the session, and it comes off: in a freshly created database, in model and in my lab's demo, is_read_committed_snapshot_on is 0.
- Without WITH ROLLBACK IMMEDIATE, the ALTER waits until no other session is left in the database: with one connected, it waited 14.5 s, as long as that session took to leave. With it, it throws the others out and rolls back their transactions.
- It only changes who reads. Two writes to the same row still wait for each other: session B's UPDATE waited just as before.

Who blocks whom
Ask while the block lasts:

SELECT r.session_id, r.blocking_session_id, r.wait_type,
       r.wait_time, r.wait_resource
FROM sys.dm_exec_requests AS r
WHERE r.blocking_session_id <> 0;

SELECT request_session_id, resource_type, resource_description,
       request_mode, request_status
FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID();

The first answers the question you actually have: blocking_session_id is the session holding what this one asks for. The second shows the whole ladder: whoever updates a row holds X on the key and IX on the page and on the table, and whoever waits shows up with request_status WAIT. Unlike PostgreSQL, row locks here do appear in the list, one per row.

Calíope's Processes list shows the wait in the State column —a blocked session appears with LCK_M_S, LCK_M_U or LCK_M_X—, but not who blocks it: the first query gives you that. And Kill Connection is the only way here to release someone else's lock: KILL closes the whole session and rolls back its transaction, because SQL Server doesn't let you cancel just another session's query.

UPDLOCK, the local FOR UPDATE
There's no SELECT … FOR UPDATE. A hint does that job:

BEGIN TRAN;
SELECT saldo FROM dbo.cuentas WITH (UPDLOCK, ROWLOCK) WHERE id = 1;
UPDATE dbo.cuentas SET saldo = saldo - 100 WHERE id = 1;
COMMIT;

It takes a U lock: a normal read from another session went through without waiting, and another read with UPDLOCK was left waiting. And it prevents the silliest deadlock of all: two sessions in REPEATABLE READ that read the same row and then update it ended in a 1205; with UPDLOCK on the read, both finished, one after the other.

Here, too, the wait is forever
@@LOCK_TIMEOUT is -1, meaning no limit: like PostgreSQL's lock_timeout, not like InnoDB's 50 s. You set it per session, in milliseconds:

SET LOCK_TIMEOUT 3000;

When it fires you get Msg 1222, and it rolls back nothing: the transaction stays open and whoever receives it has to roll it back. What each error rolls back is covered in the “Errors (SQL Server)” topic.

Not waiting, on purpose

SELECT saldo FROM dbo.cuentas WITH (NOWAIT) WHERE id = 3;

SELECT id FROM dbo.cuentas WITH (READPAST) WHERE id BETWEEN 1 AND 5;

NOWAIT fails instantly with the same 1222, and the batch carries on. READPAST is the local SKIP LOCKED: with row 3 locked by another session, it returned 1, 2, 4 and 5. It's how you share a work queue between several consumers without them waiting on each other.

NOLOCK reads what never existed

SELECT saldo FROM dbo.cuentas WITH (NOLOCK) WHERE id = 3;

With another session halfway through an UPDATE it later rolled back, that read returned 50: a balance that was never committed. NOLOCK is READ UNCOMMITTED, and it isn't “reading faster”: it's reading what another transaction may still throw away. The documentation adds that if pages split while it reads, it can skip rows or read them twice; I didn't reproduce that. If what you want is reads that don't wait, the answer is READ_COMMITTED_SNAPSHOT, which doesn't wait and only sees committed data.

The deadly embrace

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

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

One of the two gets Msg 1205 —level 13, “… has been chosen as the deadlock victim. Rerun the transaction.”— and loses its transaction and the rest of the batch; the other commits. It isn't detected instantly: over six runs, the victim took between 0.1 and 4.8 s to fall after the cycle closed. The documentation explains that range: a monitor looks for cycles every 5 s, and more often right after finding one.

You can decide which one dies. With SET DEADLOCK_PRIORITY HIGH in session A, B was the victim all three times; LOW suits a batch job that can be repeated. A 1205 isn't a fault: the application has to retry that transaction.

The graph is already saved
There's nothing to turn on: the system_health Extended Events session, which runs out of the box, stores every deadlock with its graph —who died, which statement each one was running and which key of which index it was waiting for.

SELECT CAST(event_data AS xml) AS grafo
FROM sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL)
WHERE object_name = 'xml_deadlock_report';

Two things I measured. The file lags: right after provoking six, it had two; a few seconds later, all of them. And the session's ring_buffer, which has them immediately, lives in memory: after restarting the server it had the new ones and had lost the earlier one, which the file still kept.

From row to table: escalation
Every lock takes memory, and when a statement piles up too many on one table, SQL Server swaps them for a single one on the whole table. I updated the first N rows of a 20,000-row table and counted:
- Up to 6,200 rows, 6,200 X key locks.
- From 6,300, one: X on the table, without going through the page. And another session that went for row 20,000, which nobody had touched, waited until its LOCK_TIMEOUT.

The documentation's figure is 5,000; here it jumped a little later. A SELECT of 8,000 rows under REPEATABLE READ ended the same way, with an S on the table. It's controlled per table:

ALTER TABLE dbo.cuentas SET (LOCK_ESCALATION = DISABLE);

With that, the same UPDATE of all 20,000 rows held 20,000 key locks and none on the table. TABLE is the default; AUTO, according to the documentation, escalates to the partition on a partitioned table. Turning it off isn't free —every lock is server memory—, and the usual way out is different: write in batches of fewer than 5,000 rows.

Application locks
The equivalent of PostgreSQL's advisory locks: a name the server keeps so that two processes of your application don't do the same thing at once.

DECLARE @r int;
EXEC @r = sp_getapplock @Resource = 'cierre-diario', @LockMode = 'Exclusive',
     @LockOwner = 'Session', @LockTimeout = 0;
-- …
EXEC sp_releaseapplock @Resource = 'cierre-diario', @LockOwner = 'Session';

With the resource held by another session it returned -1 instead of waiting; 0 means “granted”. It raises no error: you have to check the number. With @LockOwner = 'Transaction', the default, it's released on its own when the transaction ends, and asking for it outside one returned -999.

Recommendation
Always touch rows in the same order and keep transactions short, as with any engine. What's specific here is three things: turn on READ_COMMITTED_SNAPSHOT as soon as the application tolerates it, which is what stops reads from waiting on writes and removes the temptation of NOLOCK; UPDLOCK on the read that will end in an UPDATE; and the 1205 retry written before going to production.

Keywords: lock, locking, deadlock, blocking, 1205, 1222, lck_m_s, blocking_session_id, dm_tran_locks, dm_exec_requests, read_committed_snapshot, rcsi, nolock, readpast, updlock, nowait, lock_timeout, deadlock_priority, escalation, lock_escalation, system_health, sp_getapplock

DDL is transactional and the cost is the Sch-M lock and the queue it builds, which ALTERs rewrite the table, ONLINE, RESUMABLE and WAIT_AT_LOW_PRIORITY, and why WITH NOCHECK doesn't lighten the lock here.

Applies to: SQL Server 2022+

If you come from MySQL, the first thing: DDL here is transactional, as in PostgreSQL. If you come from PostgreSQL, what changes is the name of the lock and a couple of tools you don't have there. Everything I describe here I measured against SQL Server 2025, on a 200,000-row table.

BEGIN TRAN;
ALTER TABLE dbo.pedidos ADD moneda char(3) NULL;
CREATE INDEX ix_cliente ON dbo.pedidos (cliente);
CREATE TABLE dbo.monedas (codigo char(3) PRIMARY KEY);
EXEC sp_rename 'dbo.pedidos.nota', 'comentario', 'COLUMN';
ROLLBACK;

After that ROLLBACK nothing was left: not the column, not the index, not the table, and the column was called nota again. A whole migration fits in one transaction. The exceptions I found: CREATE DATABASE and ALTER DATABASE (Msg 226) and a RESUMABLE index (Msg 574) can't go inside one.

The cost is Sch-M, and the queue it builds
Every ALTER TABLE asks for a schema modification lock, Sch-M, which conflicts with everything, a SELECT included. The danger isn't the ALTER, which may last milliseconds: it's the wait. With another session halfway through a transaction that had touched one row:
- The ALTER TABLE … ADD waited 7 s, with wait_type LCK_M_SCH_M.
- A SELECT of another row, started behind it, waited 6 s with LCK_M_SCH_S, and its blocking_session_id wasn't the transaction: it was the ALTER.

That's how a one-millisecond change stops a whole application. The cure is the same as in PostgreSQL:

SET LOCK_TIMEOUT 3000;
ALTER TABLE dbo.pedidos ADD moneda char(3) NULL;

At 3 s the ALTER got Msg 1222, and the SELECT behind it went through at that same moment. You retry later.

What rewrites the table and what doesn't
Measured on 200,000 rows (1,364 pages). The witness is the transaction log each statement wrote:

How to read it:
- A default that is the same for every row —a constant, or SYSDATETIME(), which is evaluated once— is stored in the metadata and doesn't touch the rows. NEWID() gives each row a different one, and that forces writing them all.
- Widening a varchar is free. Narrowing it, changing its family (nvarchar, max), growing an integer, going to NOT NULL or changing a decimal's size on disk all rewrite. decimal(10,2) and decimal(12,2) take the same space, 9 bytes, which is why that one didn't rewrite.
- Whatever rewrites leaves the table twice the size. After each of those statements the table went from 1,364 to 2,723 pages and stayed there: the old column still takes its place in every row. After int → bigint, an ALTER TABLE dbo.pedidos REBUILD brought it to 1,495. And a DROP COLUMN is instant because it frees nothing: 1,364 pages before and after, and 1,075 after the REBUILD.

Two obstacles PostgreSQL doesn't put in your way:
- An indexed column doesn't change type. With an index on estado, going to varchar(100) worked, because it doesn't rewrite; varchar(30) and nvarchar(100) gave Msg 5074 ("The index 'ix_estado' is dependent on column 'estado'"). You drop the index, change the column and create the index again.
- A DEFAULT is a named constraint, and while it exists the column can't be dropped: the same Msg 5074, naming a DF__pedidos__moneda__… constraint SQL Server named on its own. You drop it first with ALTER TABLE … DROP CONSTRAINT, which is why it pays to name it when you create it.

The documentation adds an edition condition: an ADD COLUMN … NOT NULL DEFAULT that only touches metadata is an Enterprise feature; in Standard it rewrites the table. My lab is Developer, which has everything Enterprise has, so I couldn't measure that.

Online indexes: ONLINE = ON

ALTER INDEX ALL ON dbo.grande REBUILD WITH (ONLINE = ON);
CREATE INDEX ix_g ON dbo.grande (g) WITH (ONLINE = ON);

On 3,000,000 rows, the online rebuild took 47 s, and an UPDATE started halfway through finished in half a second. Without ONLINE, the table stays locked the whole time. An ALTER COLUMN accepts it too (ALTER TABLE … ALTER COLUMN n bigint NOT NULL WITH (ONLINE = ON)): it took 395 ms instead of 228, but left the table at 1,483 pages instead of doubling it.

Three limits:
- With a spatial index on the table, ALTER INDEX ALL … WITH (ONLINE = ON) fails with Msg 153, and the spatial index itself can't be rebuilt online either. The primary key and the ordinary indexes, one by one, can.
- ONLINE doesn't mean lock-free: it asks for an S lock on the table when it starts and, according to the documentation, a brief Sch-M when it ends. With a transaction open on the table, the online REBUILD sat waiting for its S, and an UPDATE that arrived behind it waited 6.6 s in the queue (LCK_M_IX). A SELECT did get through.
- According to the documentation, ONLINE = ON is Enterprise: in Standard it's an error.

WAIT_AT_LOW_PRIORITY: waiting without building a queue
PostgreSQL doesn't have this one. The rebuild waits for its lock in a separate queue, without getting in the way of whoever arrives behind it:

ALTER INDEX ALL ON dbo.pedidos REBUILD WITH (ONLINE = ON (
    WAIT_AT_LOW_PRIORITY (MAX_DURATION = 1 MINUTES, ABORT_AFTER_WAIT = SELF)));

With the same transaction open, the UPDATE behind it went through in 0.5 s, and the REBUILD showed as waiting on LCK_M_S_LOW_PRIORITY. Once MAX_DURATION passes, ABORT_AFTER_WAIT decides:
- SELF: the REBUILD gives up with Msg 1222 and the transaction carries on. That's the prudent one.
- BLOCKERS: after the minute, the blocking session was disconnected ("… disconnected because of a high priority DDL operation") and its transaction rolled back; the REBUILD finished.
- NONE: it waits at low priority with no limit.

ALTER INDEX … REBUILD accepts it and, on my 2025, so does CREATE INDEX … WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY …)). ALTER TABLE doesn't: with ADD, ALTER COLUMN or ADD CONSTRAINT it's a syntax error (Msg 155). For those, only LOCK_TIMEOUT is left.

Resumable indexes: RESUMABLE = ON

CREATE INDEX ix_relleno ON dbo.grande (relleno, g) WITH (ONLINE = ON, RESUMABLE = ON);
-- from another session:
ALTER INDEX ix_relleno ON dbo.grande PAUSE;
SELECT name, state_desc, percent_complete FROM sys.index_resumable_operations;
ALTER INDEX ix_relleno ON dbo.grande RESUME;

I paused the build at 4 s: the session that had started it was disconnected (Msg 1219), sys.index_resumable_operations said PAUSED and 5 %, and the index didn't exist yet —it wasn't in sys.indexes—. RESUME finished the rest in 45 s. It lets you spread a huge index across several maintenance windows. It requires ONLINE = ON (without it, Msg 11438), it can't go inside a transaction (Msg 574) and, according to the documentation, since SQL Server 2022 it also works for ALTER TABLE … ADD CONSTRAINT of a primary or unique key; on 2025 I checked it with a unique one.

WITH NOCHECK: it doesn't lighten the lock here
PostgreSQL's NOT VALID + VALIDATE pattern has its twin here, but it doesn't do the same. On 3,000,000 rows:

ALTER TABLE dbo.grande WITH NOCHECK ADD CONSTRAINT ck_id CHECK (id > 0);  -- 13 ms
ALTER TABLE dbo.grande WITH CHECK CHECK CONSTRAINT ck_id;                 -- 346 ms

Adding it in one go took 375 ms. And all three took Sch-M on the table, validation included: splitting it in two spreads out the scan, but there's no weaker lock for the second half, as there is in PostgreSQL.

And it leaves a trap: a constraint added WITH NOCHECK is not trusted (is_not_trusted = 1), and the optimizer doesn't rely on it. With a trusted CHECK (importe >= 0), WHERE importe < 0 didn't even read the table; untrusted, 1,364 logical reads. The same happens when you disable it and re-enable it without WITH CHECK —ALTER TABLE … CHECK CONSTRAINT ck_importe left it untrusted—, which is what bulk loads usually do. To find them:

SELECT name FROM sys.check_constraints WHERE is_not_trusted = 1;
SELECT name FROM sys.foreign_keys WHERE is_not_trusted = 1;

In Calíope
The OPTIMIZE operation in Table Maintenance is, on SQL Server, ALTER INDEX ALL … REBUILD without ONLINE: it locks the table while it rebuilds. On a large production table, you're better off writing the REBUILD WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY …)) in the editor.

Recommendation
SET LOCK_TIMEOUT before every ALTER TABLE; the migration inside a transaction; ONLINE = ON with WAIT_AT_LOW_PRIORITY … SELF for indexes; a proper name for each DEFAULT; and care with type changes, which rewrite the table and leave it twice the size until the next REBUILD. If you need to narrow a large column or change its family, it usually works better to add the new one, copy in batches and rename with sp_rename.

Keywords: ddl, alter table, migration, transactional, rollback, sch-m, lck_m_sch_m, lock_timeout, online, resumable, wait_at_low_priority, abort_after_wait, rebuild, rewrite, add column, alter column, drop column, with nocheck, is_not_trusted, sp_rename

Partition function and scheme, what the optimizer really eliminates, why a unique index must include the key, SWITCH and TRUNCATE to purge in milliseconds, the trusted CHECK that loading data requires, and SPLIT and MERGE, which are only free on an empty partition.

Applies to: SQL Server 2022+

If you come from MySQL or PostgreSQL, what changes is the shape: here a partition is not a table but a piece of one, and the layout is described by two objects created before the table. Everything I describe here I measured against SQL Server 2025, on 300,000 sales spread over three years.

CREATE PARTITION FUNCTION pf_anio (date)
    AS RANGE RIGHT FOR VALUES ('2024-01-01', '2025-01-01', '2026-01-01');

CREATE PARTITION SCHEME ps_anio
    AS PARTITION pf_anio ALL TO ([PRIMARY]);

CREATE TABLE dbo.ventas (
    id bigint IDENTITY NOT NULL,
    creado date NOT NULL,
    cliente int NOT NULL,
    importe decimal(10,2) NOT NULL,
    CONSTRAINT pk_ventas PRIMARY KEY CLUSTERED (creado, id)
) ON ps_anio (creado);

The function sets the boundaries and the scheme says which filegroup each piece lives in. Three boundaries give four partitions: everything before 2024, one year in each of the next two, and everything from 2026 on. There is no LIST or HASH: only ranges. And you don't have to foresee the future: whatever falls before the first boundary or after the last always has a place.

RANGE RIGHT or RANGE LEFT: which side the boundary falls on
With RIGHT, the boundary belongs to the partition on its right, which is the natural choice for dates: $PARTITION.pf_anio('2023-12-31') returned 1, and '2024-01-01', 2. With LEFT and the same boundaries, January 1 landed in the 2023 partition. Nothing fails: one day's data just ends up in the wrong year. $PARTITION is also handy for counting:

SELECT $PARTITION.pf_anio(creado) AS particion, COUNT(*) AS filas
FROM dbo.ventas GROUP BY $PARTITION.pf_anio(creado);

Partition elimination is the point
The actual plan (SET STATISTICS XML ON, under RunTimePartitionSummary) tells how many partitions each query read:

Two things to read there:
- Variables and parameters are eliminated too, because it is decided at execution, not at compile time. That's why the estimated plan doesn't give a number: it shows [PtnId1000] >= RangePartitionNew(…) when the key is in the filter, and [PtnId1000] >= (1) AND [PtnId1000] <= (4) when it isn't.
- A function on the column hides it, just as it hides it from an index: YEAR(creado) read all four. The range written by hand (>= '2024-01-01' AND < '2025-01-01') reads one.

The design rule is the same as in the other engines: the partition key is the column you filter by almost every time. If queries don't mention it, partitioning doesn't save reads: it spreads them.

Aligned indexes, and uniqueness
An index created without ON inherits the table's scheme: it is aligned, with one piece per partition. The consequence is the same as in PostgreSQL:

CREATE UNIQUE INDEX ux_id ON dbo.ventas (id);

returned Msg 1908: the partitioning column must be part of a unique index's key. Here there is a way out that PostgreSQL doesn't have —creating it unaligned, ON [PRIMARY]—, but it has a price: with such an index, the SWITCH below stops working (Msg 7733). That's why the primary key of dbo.ventas is (creado, id) and not (id).

SWITCH and TRUNCATE: purging a year in milliseconds
Deleting all of 2023, 100,009 rows, measured inside a transaction:

SWITCH moves no data: it hands a whole piece over to another owner. That's why it asks a lot of the target table, and each requirement has its error:
- Empty: with one row, Msg 4905.
- Same columns and same indexes: with one extra column, Msg 4943.
- And, according to the documentation, on the same filegroup as the partition.

Loading data takes a trusted CHECK
This is the difference from PostgreSQL, where the CHECK only saves a scan. Switching the table back into its partition without one doesn't take longer: it is refused, with Msg 4982. With the constraint, it went in in 9.5 ms:

ALTER TABLE dbo.ventas_2023 WITH CHECK
    ADD CONSTRAINT ck_2023 CHECK (creado < '2024-01-01');
ALTER TABLE dbo.ventas_2023 SWITCH TO dbo.ventas PARTITION 1;

And it has to be trusted: disabled and re-enabled without WITH CHECK —what many bulk loads do—, the SWITCH returned Msg 4972. After WITH CHECK CHECK CONSTRAINT, it went in. That's how you load a new period without touching the live table: fill a separate table, give it its CHECK, and SWITCH it in.

SPLIT and MERGE: free only on an empty partition
Boundaries are moved through the function:

ALTER PARTITION SCHEME ps_anio NEXT USED [PRIMARY];
ALTER PARTITION FUNCTION pf_anio() SPLIT RANGE ('2027-01-01');

Splitting a full partition moves the rows on one side of the boundary, and each one writes log: the cost grows with what moves. The healthy habit is to always keep an empty partition at the end and split it before the next period's data arrives. And each SPLIT uses up the NEXT USED: without declaring it again, the next one returned Msg 7710 and changed nothing.

Maintenance per partition
Rebuild only the partition that changes:

ALTER INDEX pk_ventas ON dbo.ventas REBUILD PARTITION = 2 WITH (ONLINE = ON);

33 ms for one partition (148 ms online) versus 214 ms for all four. A closed year no longer receives writes: there's no reason to rebuild it every night.

Lock escalation, per partition
By default (LOCK_ESCALATION = TABLE), an UPDATE of 16,440 rows from 2024 escalated to an X lock on the whole table, and updating a 2025 row from another session returned Msg 1222. With

ALTER TABLE dbo.ventas SET (LOCK_ESCALATION = AUTO);

escalation stayed at the partition (an X on HOBT), and the 2025 row was updated in 9 ms. The documentation warns that AUTO can bring deadlocks between sessions escalating on different partitions; the rest of escalation is in “Locks and deadlocks (SQL Server)”.

SWITCH takes Sch-M too
With an open transaction that had touched a single row, the SWITCH sat waiting for its Sch-M, and a SELECT of another row, launched after it, waited in the queue behind the SWITCH. The cure is the same as for indexes:

ALTER TABLE dbo.ventas SWITCH PARTITION 1 TO dbo.ventas_2023
    WITH (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 1 MINUTES, ABORT_AFTER_WAIT = SELF));

The SELECT behind finished in half a second, and the SWITCH gave up after 61 s with Msg 1222, leaving the transaction alone. The rest of that queue is in “Online schema changes (SQL Server)”.

Limits and editions
A function allows 15,000 partitions: with 15,000 boundaries, Msg 7719. According to the documentation, partitioning is in every edition since SQL Server 2016 SP1, Express included.

In Calíope
The rows and size of a partitioned table add up all its partitions. The CREATE TABLE Calíope writes —the one it shows as the table's DDL and the one Backup saves— is preceded by the partition function and scheme, each with IF NOT EXISTS, and ends in ON ps_anio (creado); an index that isn't aligned, like a nonclustered primary key on [PRIMARY], keeps its own ON. Restoring that backup into an empty database leaves it partitioned the same way, with each row in its partition; if the target already has a function with that name, the existing one is used, with its boundaries. Schema Diff does look at partitioning —where the table lives and each scheme's boundaries—, but its script doesn't partition or unpartition a table that already exists: that means rebuilding its clustered index with the data inside, and it leaves it written as a note for you to do. The plan Calíope shows is the estimated one, so for partitions it gives the PtnId1000 form above; how many were really read is only in the actual plan, SET STATISTICS XML ON.

Recommendation
Partition by what you are going to delete: the real gain is purging a period with SWITCH or TRUNCATE … WITH (PARTITIONS) instead of a DELETE that fills the log. Primary key and unique indexes with the partition key inside, so everything stays aligned; RANGE RIGHT for dates; always an empty partition at the end, to split it for free; and check in the actual plan that your queries eliminate partitions, instead of assuming it.

Keywords: partitioning, partition, partition function, partition scheme, range right, range left, $partition, partition elimination, aligned, switch, truncate, split range, merge range, next used, lock_escalation, 1908, 4982, 7733

Logical reads instead of timings, the estimated plan versus the actual one, parameter sniffing and its measured cures, the plan cache that Slow Queries reads, Query Store switched on out of the box, and the waits that actually tell you something.

Applies to: SQL Server 2022+

If you come from MySQL or PostgreSQL, three things change here: the figure you compare is logical reads, not time; the plan is cached and reused with other values, and that is the cause of half of all “it was fine yesterday”; and the equivalent of pg_stat_statements is already there without switching anything on, but it is lost on restart. Everything I describe here I measured against SQL Server 2025, on 200,000 orders.

Measuring a query: logical reads

SET STATISTICS IO, TIME ON;
SELECT COUNT(*), SUM(importe) FROM dbo.pedidos WHERE cliente_id = 4243;
SET STATISTICS IO, TIME OFF;

Each logical read is one 8 kB page read from memory. It is the figure to compare, because time changes with load and with the cache, and it does not. For one customer's 20 orders:
- Without an index: 6,085 logical reads —the whole table— and 12 ms.
- With an index on cliente_id: 62 reads.

In Calíope those lines don't appear: the server sends them as informational messages, and the editor shows rows and errors, not messages. The same figure can be read from the plan cache, right after running the query:

SELECT TOP (5) qs.last_logical_reads, qs.last_elapsed_time AS micros, st.text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
WHERE st.text LIKE N'%cliente_id = 4243%'
ORDER BY qs.last_execution_time DESC;

It gave me 62, the same as STATISTICS IO.

The estimated plan and the actual one
SET SHOWPLAN_XML ON asks for the plan without running the query. SET STATISTICS XML ON runs it and adds what happened: ActualRows next to EstimateRows on every operator. If the statement writes, wrap it in BEGIN TRAN … ROLLBACK, because it really runs.

Visual EXPLAIN, in Calíope, asks for the estimated one: the operator tree with estimated rows, no actual rows, no timings and no cost. It shows the shape —an Index Scan where you expected a Seek—; to compare estimate with reality you need the actual plan.

Parameter sniffing
The first time a procedure or a parameterized query runs, the optimizer looks at the value it brings and compiles the plan for it. That plan stays in the cache, and later executions reuse it even when they bring another value. I reproduced it with a procedure and two customers: one with 20 orders and one with 100,000.

CREATE OR ALTER PROCEDURE dbo.pedidos_de @cliente int AS
  SELECT SUM(importe) AS total, COUNT(*) AS n
  FROM dbo.pedidos WHERE cliente_id = @cliente;

Compiled for the small customer, the plan seeks in the index and goes to the table row by row: perfect for 20 rows, and 300,000 reads for 100,000, fifty times more than scanning the whole table. Compiled the other way round, the small one scans the whole table. Arrival order decides, and a restart or a statistics change reverses it: hence “it was fine yesterday”.

The actual plan gives it away without guessing: in the bad case it said EstimateRows="20" and ActualRows="100000", and among its parameters, ParameterCompiledValue="(4243)" next to ParameterRuntimeValue="(1)".

What I tried to cure it:
- OPTION (RECOMPILE) at the end of the query: 62 and 6,085, each value with its own plan. The price is compiling on every call (here, 2-3 ms), which is not free for a query that runs thousands of times a second.
- OPTION (OPTIMIZE FOR UNKNOWN): compiles for the average value, about 20 rows, and that gave 70 and 306,429. When there is one huge value, it doesn't help.
- EXEC sp_recompile 'dbo.pedidos_de' throws away the procedure's plan: it is the fix for the moment, not the cure.
- Since SQL Server 2022, with compatibility level 160 or higher, the server can keep several plans for the same query depending on the value. It is on by default, but with 100,000 rows against 20 it did not kick in. With 200,000 against 1, it did: two plans. Don't count on it.

Which query costs the most: the plan cache
The server keeps count of every plan it has cached: executions, time, reads and rows. query_hash groups by the shape of the query: five SELECTs that only changed the customer number, each on its own, left five plans in the cache (80 kB) and a single query_hash.

SELECT TOP (20) CONVERT(varchar(20), qs.query_hash, 1) AS forma,
       SUM(qs.execution_count) AS ejecuciones,
       SUM(qs.total_elapsed_time) / 1000 AS ms_total,
       SUM(qs.total_logical_reads) AS lecturas
FROM sys.dm_exec_query_stats AS qs
GROUP BY qs.query_hash
ORDER BY SUM(qs.total_elapsed_time) DESC;

That is what Calíope shows in Slow Queries, By statement view: one row per shape, with the text of its most expensive plan, from every database on the server. It needs the VIEW SERVER STATE permission. And it keeps no history: I restarted the server and the view went from 23 rows to 0. Nor does it survive the plan leaving the cache.

Query Store, which is already on
Query Store is the version that persists: it keeps text, plans and figures by interval inside the database itself. On SQL Server 2025 I found it on (READ_WRITE) in demo and in a freshly created database, without anyone asking for it. According to the documentation, that has been the case for new databases since SQL Server 2022.

SELECT TOP (20) q.query_id, SUM(rs.count_executions) AS ejecuciones,
       SUM(rs.avg_duration * rs.count_executions) / 1000 AS ms_total,
       COUNT(DISTINCT p.plan_id) AS planes, qt.query_sql_text
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan AS p ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id = p.plan_id
GROUP BY q.query_id, qt.query_sql_text
ORDER BY ms_total DESC;

A planes column greater than 1 is the footprint of parameter sniffing. Two measured caveats: after the restart the texts of its 11 queries were still there, but not the figures from before, because it writes them to disk every 900 seconds; and it is per database, not per server.

Calíope doesn't use it: Slow Queries reads the plan cache, which is already there for the whole server. It neither switches it on nor off, because that is an administrative ALTER DATABASE.

Waits: where the time goes
sys.dm_os_wait_stats adds up, since the last restart, how long the server waited and why. It has 1,547 types, and the first ones on the list, unfiltered, are threads waiting for work: DISPATCHER_QUEUE_SEMAPHORE, BROKER_TASK_STOP, the SLEEP_… ones. They have to be discarded. The ones that do tell you something:
- LCK_M_…: locks. I cover them in “Locks and deadlocks (SQL Server)”.
- PAGEIOLATCH_…: waiting for pages from disk. Memory or indexes are missing.
- WRITELOG: waiting for the transaction log on every commit.
- CXPACKET and CXCONSUMER: parallelism.
- SOS_SCHEDULER_YIELD: CPU.
- RESOURCE_SEMAPHORE: queries waiting for memory to sort or to hash.

They are cumulative, so a single snapshot is of little use: take two and subtract. After my restart, the total dropped from 12.1 to 0.7 million ms.

Parallelism
Out of the box, cost threshold for parallelism is 5 and max degree of parallelism is 0 (all cores). The threshold is so low that a medium-sized scan can already go parallel: on another 200,000-row table, one used 16 threads, 2,094 ms of CPU for 152 ms of wall clock. A high CXPACKET is not an error; it is the sign that those two values were left as they came.

What is already in other topics
- An nvarchar parameter against a varchar column with a SQL_ collation goes from 3 reads to 334: “Encoding and collations (SQL Server)”.
- Key Lookup, when the plan drops the index for the table, YEAR(fecha) versus the range, and the indexes the server misses: “Indexes (SQL Server)”.
- An untrusted CHECK constraint is not used by the optimizer (1,364 reads against none): “Online schema changes (SQL Server)”.
- How many partitions a query read is only told by the actual plan: “Partitioning (SQL Server)”.

Recommendation
The order that works: find the query in Slow Queries (or in Query Store, if you need history), ask for its actual plan with SET STATISTICS XML ON and compare EstimateRows with ActualRows. If they diverge and the compiled value is not the current one, it is parameter sniffing; if they diverge with the same value, it is statistics; if they match and it still reads a lot, an index is missing. Touching memory or parallelism before reading a plan is the long way round.

Keywords: performance, statistics io, statistics time, logical reads, showplan_xml, statistics xml, execution plan, actual plan, parameter sniffing, recompile, optimize for unknown, sp_recompile, dm_exec_query_stats, query_hash, plan cache, query store, dm_os_wait_stats, waits, parallelism

The three recovery models and which one lets the log grow, BACKUP DATABASE and BACKUP LOG on the server's disk, the log chain, going back to the minute before the DELETE with STOPAT, COPY_ONLY, and how Calíope's backup differs.

Applies to: SQL Server 2022+

If you come from MySQL, there is no mysqldump and no separate binlog here: the physical backup is a T-SQL statement, and the history that lets you move forward from it is each database's transaction log. If you come from PostgreSQL, the difference is the unit: you back up and restore one database, not the whole server. Everything I describe here I measured on SQL Server 2025.

The recovery model decides almost everything
It is a property of each database, and there are three:

A new database copies the model of model, which on this server is FULL:

SELECT name, recovery_model_desc, log_reuse_wait_desc
FROM sys.databases;

FULL without log backups fills the disk
This is the trap. Until the database has its first full backup, FULL behaves like SIMPLE: I inserted 20,000 rows of 2 kB, the log reached 42.2 MB used and a CHECKPOINT brought it down to 8.1 MB. After the first BACKUP DATABASE, the same thing freed nothing: 55.4 MB, then 102.8 MB, and the file grew from 72 to 136 MB. log_reuse_wait_desc says why it can't be reused:

- LOG_BACKUP — waiting for a BACKUP LOG. It's the reason behind almost every full disk.
- ACTIVE_TRANSACTION — an open transaction that nobody closes.
- CHECKPOINT — nothing to fear, it frees at the next one.

BACKUP LOG brought the used space down to 15.1 MB, but the file stayed at 136 MB: backing up the log frees space inside it, it doesn't give it back to the disk. If a database is in FULL and nobody schedules BACKUP LOG, either schedule it or switch to SIMPLE.

Backing up

BACKUP DATABASE tienda TO DISK = '/var/opt/mssql/backup/tienda_full.bak'
    WITH CHECKSUM, INIT;

BACKUP DATABASE tienda TO DISK = '/var/opt/mssql/backup/tienda_dif.bak'
    WITH DIFFERENTIAL, CHECKSUM, INIT;

BACKUP LOG tienda TO DISK = '/var/opt/mssql/backup/tienda_0930_1200.trn'
    WITH CHECKSUM, INIT;

The path is on the server's disk, not yours: the SQL Server process writes the file. On Linux the usual folder is /var/opt/mssql/backup/; in the test container it didn't exist, and the first BACKUP created it.

- The full copies the whole database: 150 MB in my test.
- The differential copies what changed since the last full: 3.1 MB after changing 2,000 rows.
- The log backup copies the log since the previous one, and it's what lets you reach a point in time.

CHECKSUM is not on by default (backup checksum default is 0), and you notice when verifying: RESTORE VERIFYONLY … WITH CHECKSUM on a backup taken without it gave Msg 3187. Always use it, and verify:

RESTORE VERIFYONLY FROM DISK = '/var/opt/mssql/backup/tienda_full.bak'
    WITH CHECKSUM;

The log chain
Each log backup starts where the previous one ended, and they are restored all of them, in order. Skipping one gave Msg 4305, with the missing LSN. Two things break it without warning:

- Switching to SIMPLE and back to FULL: afterwards, BACKUP LOG gave Msg 4214 until the next full, even though there was one from before.
- Someone else's BACKUP LOG, to a file you don't know about. The history is in msdb:

SELECT b.type, b.is_copy_only, b.first_lsn, b.last_lsn,
       b.backup_finish_date, m.physical_device_name
FROM msdb.dbo.backupset b
JOIN msdb.dbo.backupmediafamily m ON m.media_set_id = b.media_set_id
WHERE b.database_name = 'tienda'
ORDER BY b.backup_start_date;

COPY_ONLY: the copy that doesn't get in the way
A normal full becomes the base of the following differentials. If you take a one-off —to move the database to another machine—, tonight's differential no longer matches Sunday's full. With WITH COPY_ONLY that doesn't happen: the next differential kept pointing at the previous full, and restoring it on top of the COPY_ONLY gave Msg 3136. Every copy outside the routine, with COPY_ONLY.

Going back to the minute before the DELETE
I reproduced it end to end: 500 orders, a DELETE without WHERE four seconds later, and the recovery into a new database, without touching the original:

RESTORE DATABASE tienda_rec FROM DISK = '/var/opt/mssql/backup/tienda_full.bak'
    WITH NORECOVERY,
    MOVE 'tienda' TO '/var/opt/mssql/data/tienda_rec.mdf',
    MOVE 'tienda_log' TO '/var/opt/mssql/data/tienda_rec_log.ldf';

RESTORE LOG tienda_rec FROM DISK = '/var/opt/mssql/backup/tienda_log1.trn'
    WITH NORECOVERY;

RESTORE LOG tienda_rec FROM DISK = '/var/opt/mssql/backup/tienda_log2.trn'
    WITH STOPAT = '2026-09-30T17:50:09.926', RECOVERY;

All 500 came back. What you need to know:

- NORECOVERY leaves the database in RESTORING, waiting for more: reading it gives Msg 927. The last step carries RECOVERY.
- MOVE is mandatory when restoring under another name, or it collides with the original's files. Their logical names come from RESTORE FILELISTONLY.
- STOPAT is server time, with three decimals: with seven it gave Msg 3217.
- Restoring over the live database without first backing up the tail of the log gave Msg 3159. The tail is what happened since the last BACKUP LOG; you back it up with BACKUP LOG … WITH NORECOVERY, which also leaves the database in RESTORING.

BULK_LOGGED and its price
A SELECT … INTO of a 21 MB table wrote 22.1 MB of log in FULL and 1.0 MB in BULK_LOGGED. But the next log backup weighed the same, 22.1 MB, because it carries the loaded pages; and restoring that backup with STOPAT gave Msg 4341: inside an interval with a bulk load you can only reach the end, not a point in time. Switch to BULK_LOGGED for the load and back to FULL right after, with a BACKUP LOG on each side.

Restoring on another server
A database backup carries its users, but not the server logins, which live in master. Restored on another machine, each user is left orphaned from its login; how to find them and join them back is in the SQL Server security topic.

What Calíope does
Backup writes a text script, not a .bak: statements with GO between batches, rows in INSERTs of a thousand, dates in ISO 8601 and binaries as 0x…, so it comes back the same on another server, another version or with the session in another language. With Include user passwords, logins go as CREATE LOGIN … WITH PASSWORD = 0x… HASHED, and each database's user with CREATE USER … FOR LOGIN: when you restore the script, nobody is left orphaned. A partitioned table comes back partitioned: the script first creates its partition function and scheme, if they don't exist.

From the SQL Editor you can run a BACKUP DATABASE —it's just another statement—, but the file stays on the server's disk: Calíope doesn't bring it over.

Recommendation
Decide the model per database: SIMPLE where losing today's work is acceptable, and FULL with a scheduled BACKUP LOG where it isn't. Weekly full, daily differential and log every few minutes is the usual routine, always WITH CHECKSUM. Watch log_reuse_wait_desc, make every one-off copy COPY_ONLY, and actually restore, under another name, from time to time: a backup that has never been restored is an intention. Calíope's backup is for taking a database, or part of it, to another machine; it doesn't replace the server's backups.

Keywords: backup, restore, recovery, recovery model, full, simple, bulk_logged, transaction log, log_reuse_wait_desc, backup log, log chain, norecovery, stopat, copy_only, differential, checksum, verifyonly, point in time, 3159, 4214, 4341

Server login and database user, why a user is orphaned after restoring on another server and how to reconnect it, granting by schema, DENY and who bypasses it, ownership chaining, contained users, row-level security and what to do with sa.

Applies to: SQL Server 2022+

If you come from MySQL, here the account does not carry the host inside and, on top of that, it is two things: the login, which belongs to the server and is what you connect with, and the user, which belongs to each database and is what receives the permissions. If you come from PostgreSQL, it is as if every role with LOGIN had to exist again inside each database to be able to use it. Everything I describe here I measured against SQL Server 2025.

Login and user

CREATE LOGIN ana WITH PASSWORD = 'Xq7#lunes_pw9';   -- server: get in
USE tienda;
CREATE USER ana FOR LOGIN ana;                    -- database: be inside

With the login and without the user, you get into the server but not into the database: connecting while naming it gave "Cannot open database … The login failed" (4060), and a USE from master gave Msg 916. The user is linked to its login by the sid, not by the name: that is where orphans come from.

The password goes through the policy (CHECK_POLICY, on by default): eight characters from three classes, and without the login name inside. 'Xq7#ana_pw9' for the login ana gave Msg 33064, even though it meets the rest.

Orphaned users: restoring on another server
I reproduced it: I backed up a database with the user ana, dropped the login and created it again with the same name —as it would be on another machine—, and restored the database. ana could no longer get into the copy: its user kept the old sid, and the new login had another one. Creating it again did not work either: Msg 15023, the user already exists. Finding them:

SELECT dp.name, dp.type_desc
FROM sys.database_principals dp
LEFT JOIN sys.server_principals sp ON sp.sid = dp.sid
WHERE dp.type IN ('S', 'U', 'G')
  AND dp.authentication_type_desc = 'INSTANCE'
  AND sp.sid IS NULL;

And reconnecting them, without losing their permissions:

ALTER USER ana WITH LOGIN = ana;

Two more things. DROP LOGIN does not check whether the login has users: it dropped it without a warning and left the user orphaned in the original database, on the same server. And so that it does not happen when you take a database to another machine, the login is created there with the same sid (CREATE LOGIN ana WITH PASSWORD = …, SID = 0x1C2F…): with it, the user of the restored database worked again without touching it.

Roles
There are 18 fixed server roles: the usual eight (sysadmin, serveradmin, securityadmin, processadmin, setupadmin, bulkadmin, diskadmin, dbcreator) and ten ##MS_…## since 2022, finer-grained (##MS_ServerStateReader## to see the state without touching anything). In each database there are nine fixed ones: db_owner, db_datareader, db_datawriter, db_ddladmin, db_securityadmin, db_accessadmin, db_backupoperator and the two that deny, db_denydatareader and db_denydatawriter. Your own roles are created with CREATE ROLE and filled with ALTER ROLE … ADD MEMBER.

And public, which everybody belongs to. On the server it has VIEW ANY DATABASE: ana saw the names of the ten databases on the server, although she could only get into four. If the names are information, take it away from public.

Granting by schema also covers tomorrow's tables

GRANT SELECT ON SCHEMA::rrhh TO ana;

With that, ana read the tables in rrhh, and also the one I created afterwards. It is the opposite of PostgreSQL, where ON ALL TABLES only reaches today's. And whatever is outside the schema stays closed: dbo.pedidos gave Msg 229.

GRANT, DENY and REVOKE: three states, not two
- GRANT grants.
- DENY forbids, and wins over any GRANT: with DENY SELECT on rrhh.nominas, ana got Msg 229 while having the schema granted, and also while being a member of db_datareader.
- REVOKE removes whatever is there, whether a GRANT or a DENY. After the REVOKE, ana could read again, through the role.

Who bypasses a DENY, I measured one by one:
- A member of db_owner: does not bypass it, got Msg 229.
- A login with CONTROL SERVER: neither, Msg 229.
- A member of sysadmin: yes. It enters every database as dbo (USER_NAME() says so) and no permission is checked for it, so it read the denied table.

What can I do, asked to the server

EXECUTE AS USER = 'ana';
SELECT permission_name, subentity_name
FROM fn_my_permissions('rrhh.nominas', 'OBJECT');
SELECT HAS_PERMS_BY_NAME('rrhh.nominas', 'OBJECT', 'UPDATE');
REVERT;

fn_my_permissions returned SELECT four times: once for the table and once for each of its three columns. It is the way to check an effective permission —with roles, schemas and DENY already resolved— without asking anyone for their password.

Ownership chaining
If a view or a procedure and the table it reads have the same owner, whoever can use the view needs no permission on the table. ana, with nothing on dbo.pedidos, read through the view dbo.v_totales and through the procedure dbo.p_total. It is the good way to give access: only to what the object shows. But dynamic SQL breaks the chain: the same procedure with an EXEC('SELECT …') inside gave Msg 229.

Contained users
In a contained database the user carries its password and needs no login, so it travels with the database and is never orphaned:

EXEC sp_configure 'contained database authentication', 1;
RECONFIGURE;
CREATE DATABASE tienda CONTAINMENT = PARTIAL;
-- inside tienda:
CREATE USER carla WITH PASSWORD = 'Rt5%jueves_z2';

The option is off by default. With it, carla got in by naming the database when connecting; without naming it, "Login failed". In Calíope, that means putting the database in the connection's Database field.

Row-level security
A policy with a filter predicate:

CREATE FUNCTION seg.fn_lo_mio(@dueno sysname)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN SELECT 1 AS ok WHERE @dueno = USER_NAME();

CREATE SECURITY POLICY seg.solo_lo_mio
ADD FILTER PREDICATE seg.fn_lo_mio(dueno) ON dbo.pedidos
WITH (STATE = ON);

ana saw her row. And unlike PostgreSQL, here nobody bypasses it: sa and a sysadmin saw zero rows, because for them USER_NAME() is dbo. The predicate decides, and if the administrator has to see everything, the predicate has to say so. Also, a filter only filters what is read: ana inserted a row belonging to another owner without an error. Preventing that takes a BLOCK PREDICATE.

sa
sa is the login with sid 0x01, a member of sysadmin, and its name is known to everyone who tries to get in. A wrong password gives 18456 on the client, always with state 1; the reason is only in the server log, where the same attempt shows up with state 8, wrong password. The usual move is to create an administration login with its own name and then ALTER LOGIN sa DISABLE.

What Calíope does not do
Calíope only speaks SQL authentication: neither Windows authentication nor Microsoft Entra ID.

What Calíope does
Users lists the server's logins, with public as one more account, and sa apart, marked as System user. Permissions are shown by level —server, database, schema, table, routine—, and a DENY is drawn as a third state: with its glyph, struck through and with the word DENY, not just with another color. It cannot be unticked, because removing it is not unticking but a REVOKE. Account locked is ALTER LOGIN … DISABLE. A database user without a login, orphaned or contained, does not appear: for those, the queries above in the SQL Editor.

Recommendation
One login per person or application, and permissions by schema to roles, not to people. sysadmin only for administering, because no DENY reaches it. Whatever can be given through a view or a procedure should go that way, and not with dynamic SQL inside. When moving a database to another machine, the logins with their sid, or the orphan query right after restoring. And sa, disabled.

Keywords: security, login, user, role, sysadmin, db_owner, db_datareader, public, grant, deny, revoke, schema, fn_my_permissions, has_perms_by_name, orphaned user, alter user with login, sid, contained user, contained database, ownership chaining, row-level security, security policy, sa, check_policy, 229, 916, 4060, 15023, 18456, 33064

sp_configure and RECONFIGURE, why value and value_in_use differ, the options that need a restart, the memory ceiling that isn't set, where each MAXDOP wins, per-database configuration, tempdb and mssql-conf.

Applies to: SQL Server 2022+

If you come from MySQL, there's no my.cnf and no SET GLOBAL here: server settings are written with sp_configure and take effect with RECONFIGURE, and database settings with ALTER DATABASE SCOPED CONFIGURATION. If you come from PostgreSQL, sys.configurations is your pg_settings, and the column that matters isn't source: it's the difference between value and value_in_use. Everything I describe here I measured against SQL Server 2025.

107 options, and 74 hidden
sys.configurations has 107 options on this server, and plain sp_configure shows 33. The other 74 are advanced: until you turn on show advanced options they are neither listed nor changeable. sp_configure 'max degree of parallelism', 4 gave Msg 15123, “does not exist, or it may be an advanced option”.

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;

Written is not applied: value and value_in_use
sp_configure only writes the value, and the server keeps the old one until RECONFIGURE. After sp_configure 'cost threshold for parallelism', 50, sys.configurations said value 50 and value_in_use 5, and the server itself warned: “Run the RECONFIGURE statement to install”.

SELECT name, value, value_in_use, is_dynamic, is_advanced
FROM sys.configurations
WHERE value <> value_in_use;

Run that query before every RECONFIGURE, for two things I measured:
- RECONFIGURE installs everything pending, not just yours: if someone left a value written, it goes in with yours.
- A RECONFIGURE that fails leaves what was written pending. With min server memory (MB) at 1,024 and max server memory (MB) at 128 it gave Msg 5831, and the 1,024 stayed in value, waiting for the next one.

There's one case with a pass: recovery interval (min) at 120 gave Msg 5807 (“not recommended”), and RECONFIGURE WITH OVERRIDE applied it anyway. WITH OVERRIDE skips exactly the check that just warned you, so it isn't used out of habit.

The ones that need a restart
Options with is_dynamic = 0 don't take effect with RECONFIGURE: here that's 23 of the 107, among them user connections, fill factor (%), locks and priority boost. With sp_configure 'user connections', 500 and RECONFIGURE, value became 500 and value_in_use stayed at 0 until the service restarts.

Every change is recorded in the server log, with time and session: EXEC xp_readerrorlog 0, 1, N'Configuration option' lists them with their “changed from 0 to 4”.

Memory: max server memory comes without a ceiling
max server memory (MB) is 2,147,483,647 out of the box, that is, none: SQL Server takes memory for its cache until the system asks it to let go. On a dedicated server you set a ceiling that leaves room for the operating system. The lowest it accepts is 128; 100 gave Msg 15129.

EXEC sp_configure 'max server memory (MB)', 12288;   -- 12 GB of 16
RECONFIGURE;

On Linux there's another ceiling below that, memory.memorylimitmb in mssql-conf, which according to the documentation defaults to 80% of physical memory. This container sees 6,347 MB, and with max server memory untouched its target (committed_target_kb in sys.dm_os_sys_info) was 5,139 MB.

Parallelism: three levels, and the closest one wins
Out of the box, max degree of parallelism is 0 (all processors) and cost threshold for parallelism is 5. I ran the same query over 300,000 rows and read DegreeOfParallelism from the actual plan:
- With the server at 0: 15 threads, every processor it sees.
- With the server at 4: 4.
- With the database at ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 2: 2, even though the server says 4.
- With OPTION (MAXDOP 8) on the query: 8, above both the database and the server.
- With cost threshold for parallelism at 50: serial. The threshold is compared with the cost of the serial plan (7.7 here), not the parallel one.

How parallelism shows up in the waits I cover in “Performance and diagnosis (SQL Server)”.

Per-database configuration
sys.database_scoped_configurations has 41 options in demo. Besides MAXDOP there are LEGACY_CARDINALITY_ESTIMATION (0), PARAMETER_SNIFFING (1) and QUERY_OPTIMIZER_HOTFIXES (0), among others. They're the way to change how one application behaves without touching the rest of the server.

SELECT name, value
FROM sys.database_scoped_configurations
WHERE is_value_default = 0;

tempdb
This server sees 15 processors, and tempdb has 8 data files of 8 MB, plus the log file, growing 64 MB at a time: setup already creates one per processor, up to eight. It's the database behind #temporary tables, sorts that don't fit in memory and the row versions of READ_COMMITTED_SNAPSHOT.

What isn't sp_configure: mssql-conf, on Linux
The default paths for data, log and backups, the TLS certificate, the memory ceiling or the language live in /var/opt/mssql/mssql.conf, and are changed with /opt/mssql/bin/mssql-conf set … and a restart. This container's file has only two lines: the TLS certificate and key, which is what SQL Server 2022 and later need for strict TLS (TDS 8.0). On Windows, the same is done in SQL Server Configuration Manager.

What Calíope shows
Server Information, under Global variables, lists sys.configurations with its value_in_use —what's running, not what's written and pending— and the SERVERPROPERTY.* properties: version, edition, collation. Session variables are the SET options of your connection. Calíope reads them and doesn't change them: an sp_configure is written in the SQL Editor, and after RECONFIGURE it shows up in Server Information on refresh. Slow Queries shows optimize for ad hoc workloads and max server memory (MB) in its settings. And contained database authentication, the option that allows users with their own password, I cover in “Logins, users and permissions (SQL Server)”.

Recommendation
Few, and measuring. max server memory always, leaving room for the system; cost threshold for parallelism above 5; and MAXDOP per database when an application asks for it, not for the whole server. Before every RECONFIGURE, the value <> value_in_use query: that way you apply what you want and not what someone else left half done.

Keywords: configuration, sp_configure, reconfigure, with override, sys.configurations, value_in_use, show advanced options, is_dynamic, max server memory, min server memory, memorylimitmb, mssql-conf, maxdop, max degree of parallelism, cost threshold for parallelism, database scoped configuration, tempdb, user connections, xp_readerrorlog, 15123, 15129, 5807, 5831

Why BEGIN TRAN inside another one does not nest, how to roll back part of it with SAVE TRANSACTION, what each level locks, the SNAPSHOT 3960 and what a forgotten transaction does to the log.

Applies to: SQL Server 2022+

A transaction groups several statements into a unit that is applied whole or not at all. As in MySQL and PostgreSQL, without an explicit transaction SQL Server commits each statement on its own (autocommit). What changes is in the details, and everything I describe here I measured against SQL Server 2025.

BEGIN TRAN, not BEGIN
A bare BEGIN opens a T-SQL block, not a transaction: the server answered Msg 102, “Incorrect syntax near 'BEGIN'”. You write BEGIN TRAN (or BEGIN TRANSACTION). A COMMIT with no open transaction gives Msg 3902, and a ROLLBACK, Msg 3903.

Implicit mode
With SET IMPLICIT_TRANSACTIONS ON, the first statement that touches data opens a transaction that stays open until the COMMIT. I measured it: @@TRANCOUNT was 0, a plain SELECT raised it to 1, and only the COMMIT brought it back to 0. A session that forgets to commit in this mode keeps its locks and holds the log, as I explain below.

Nesting does not nest
A BEGIN TRAN inside another one only raises the @@TRANCOUNT counter. The outer one decides:
- The inner COMMIT commits nothing: it lowered the counter from 2 to 1, and the outer ROLLBACK undid both UPDATEs, the inner one too.
- The inner ROLLBACK undoes everything: it left @@TRANCOUNT at 0, and the outer COMMIT gave Msg 3902 because there was no transaction left.
- Naming the inner one does not help: ROLLBACK TRAN interior gave Msg 6401 and the counter stayed at 2. You can only roll back as far as a savepoint.

BEGIN TRAN;                                  -- @@TRANCOUNT = 1
  UPDATE cuentas SET saldo = saldo - 10 WHERE id = 1;
  BEGIN TRAN;                                -- @@TRANCOUNT = 2
    UPDATE cuentas SET saldo = saldo + 10 WHERE id = 2;
  COMMIT;                                    -- @@TRANCOUNT = 1, nothing committed
ROLLBACK;                                    -- undoes both UPDATEs

A procedure that ends with a different @@TRANCOUNT from the one it started with gives Msg 266: I cover it in “Errors (SQL Server)”.

Savepoints: SAVE TRANSACTION
To undo part of the work without losing the transaction, you mark a point and go back to it. Here the duplicate INSERT failed with 2627, I went back to the point, and the COMMIT committed the row before and the row after:

BEGIN TRAN;
  INSERT INTO cuentas (id, saldo) VALUES (3, 0);
  SAVE TRANSACTION antes;
  BEGIN TRY
    INSERT INTO cuentas (id, saldo) VALUES (1, 0);   -- fails: 2627
  END TRY
  BEGIN CATCH
    ROLLBACK TRANSACTION antes;                      -- @@TRANCOUNT stays at 1
  END CATCH
  INSERT INTO cuentas (id, saldo) VALUES (5, 5);
COMMIT;

Two limits I measured: SAVE TRANSACTION with no open transaction gives Msg 628, and with XACT_ABORT ON the same error left the transaction doomed —XACT_STATE() -1— and going back to the point gave Msg 3931: the only way out was to roll it all back.

An error almost never aborts the transaction
Unlike PostgreSQL, a statement error —a duplicate key, a constraint— aborts only that statement, and the transaction stays alive with what it already did. Without XACT_ABORT, a COMMIT at the end commits what came before and after the error. With SET XACT_ABORT ON, the same error undoes everything and ends the batch. Which errors roll back the transaction on their own I cover in “Errors (SQL Server)”.

Isolation levels
The default is READ COMMITTED. I measured with two sessions: the first reads four rows of a range, waits four seconds and reads them again; the second, meanwhile, inserts a row into that range and updates one of the rows read.
- READ UNCOMMITTED reads what others have not committed: it is the NOLOCK of “Locks and deadlocks (SQL Server)”.
- READ COMMITTED does not keep locks on what it reads. Without READ_COMMITTED_SNAPSHOT, which ships off, a read waits for the writer; I cover it in “Locks and deadlocks (SQL Server)”.
- REPEATABLE READ kept five shared locks (KEY S) until the end: the UPDATE of the row read waited 3 s. But the INSERT went in at once, and the second read counted 5 rows instead of 4: a phantom row.
- SERIALIZABLE locked the range (KEY RangeS-S): the INSERT waited 3 s and the second read still counted 4.
- SNAPSHOT reads a snapshot from the start of the transaction and blocks nobody, but it has to be allowed in each database, and it ships off: snapshot_isolation_state_desc was OFF in demo, in model and in a new database. Without it, SET TRANSACTION ISOLATION LEVEL SNAPSHOT and BEGIN TRAN went through, and it was the first SELECT that gave Msg 3952.

ALTER DATABASE mi_base SET ALLOW_SNAPSHOT_ISOLATION ON;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

SNAPSHOT does not wait: it aborts with 3960
With it allowed, the second session updated the row without waiting for the first one, which had only read it. The first one read it again and still saw the value from its snapshot, 100, with the other's 101 already committed. When it tried to update it, the server aborted it with Msg 3960 (“update conflict”), and XACT_STATE() was left at -1. Under SNAPSHOT, a 3960 is normal operation: the application has to retry the whole transaction. READ_COMMITTED_SNAPSHOT is something else: it does not change the level, it changes how READ COMMITTED reads, and that is what I cover in “Locks and deadlocks (SQL Server)”.

A forgotten transaction holds the log
A session sleeping inside a transaction (status = sleeping, open_transaction_count = 1) prevents the log from being reused. I inserted and deleted 5,000 rows and ran a CHECKPOINT, in a database where the CHECKPOINT empties the log. Without that session 6.5 MB stayed in use; with it, 73.8 MB, and log_reuse_wait_desc said ACTIVE_TRANSACTION. After closing it, 5.1 MB. The rest of the log cycle I cover in “Backup and recovery (SQL Server)”. To find it:

DBCC OPENTRAN;   -- the oldest one: SPID and start time

SELECT session_id, status, open_transaction_count, last_request_end_time
FROM sys.dm_exec_sessions
WHERE open_transaction_count > 0;

DDL is rolled back too
ALTER TABLE, CREATE INDEX and CREATE TABLE go inside the transaction, with their exceptions (CREATE DATABASE gives Msg 226): I cover it in “Online schema changes (SQL Server)”.

What Calíope does
The SQL Editor runs what you give it on a single connection, statement by statement, and stops at the first one that fails. Without a transaction from the menu, when it finishes it returns the connection to the pool, and if a transaction was left open it rolls it back (IF @@TRANCOUNT > 0 ROLLBACK): a BEGIN TRAN and its COMMIT written by hand go in the same run, and what came before an error is rolled back when the connection is returned. In sqlcmd or in an application, it would have been committed.
For a transaction that spans several runs there is the Transaction menu in the toolbar: BEGIN sends BEGIN TRANSACTION and reserves a connection for the tab, and whatever you run there stays inside until COMMIT or ROLLBACK. Closing the tab rolls it back. If the server rolls it back on its own —a 245, a 1205 or any error under XACT_ABORT— or a COMMIT in your SQL ends it, the Active transaction label goes out and the status bar says so: from then on, every statement is committed immediately.

Recommendation
SET XACT_ABORT ON in every batch or procedure that writes, and a TRY…CATCH that ends in IF @@TRANCOUNT > 0 ROLLBACK; and THROW;. Do not nest BEGIN TRAN expecting the inner one to be independent: that is what savepoints are for. If you move up to SNAPSHOT, write the 3960 retry before moving up. And every now and then, DBCC OPENTRAN: a forgotten session gives no warning until the disk fills up.

Keywords: transaction, commit, rollback, begin tran, @@trancount, save transaction, savepoint, xact_abort, xact_state, implicit_transactions, isolation, repeatable read, serializable, snapshot, allow_snapshot_isolation, 3960, 3952, 3902, 6401, 3931, active_transaction, dbcc opentran

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