Aniketh Deshpande

Posted on Sep 29

The Database Playground: Everything You Can Plug In and Tune in PostgreSQL and MySQL (Including AI Workloads)

#ai #database #mysql #postgres

TL;DR: PostgreSQL and MySQL are not black boxes. Both let you swap storage engines, index types, durability
guarantees, where data physically lives, and even add vector search and ML, often per table and sometimes
per transaction. This article maps those options by the storage or retrieval problem each one solves,
with links to the official docs for every fact.

🤖 Here for AI workloads? Jump straight to Part 3: AI and ML workloads
for vector types, HNSW/IVFFlat/DiskANN tuning, hybrid search, in-database ML, and a full RAG query in SQL.

How to read this article
Part 1: PostgreSQL
Part 2: MySQL (plus MariaDB and Percona)
Part 3: AI and ML workloads 🤖
Cheat sheet: problem to engine
Further reading

How to read this article

Every database, however it's marketed, has the same layers. Each layer has something you can plug in (swap the component) or tune (turn a knob):

            ┌───────────────────────────────┐
 clients →  │ Connection pooler             │  PgBouncer, thread pool
            ├───────────────────────────────┤
            │ Parser / planner / executor   │  hooks, JIT, parallelism, hash joins
            ├───────────────────────────────┤
            │ Access methods                │  ← storage engines + index types
            │  (tables and indexes)         │     heap, columnar, InnoDB, MyRocks, HNSW...
            ├───────────────────────────────┤
            │ Buffer cache                  │  shared_buffers, innodb_buffer_pool_size
            ├───────────────────────────────┤
            │ Write-ahead log / redo log    │  durability vs speed knobs
            ├───────────────────────────────┤
            │ Files, disks, object storage  │  tablespaces, SSD tuning, S3, Iceberg
            └───────────────────────────────┘

The two main parts follow those layers from the top down to the disk. Part 3 then covers AI workloads across both databases.

Conventions

Versions matter. "PG15+" means PostgreSQL 15 or later. MySQL facts use the 8.4 LTS manual unless stated otherwise. The VECTOR type needs MySQL 9.x; 9.7 (April 2026) is the newest LTS.
Restart marks a setting that needs a server restart. Everything else can be changed with a reload, or per session.
To see your current values, run SELECT name, setting, unit, context FROM pg_settings; in PostgreSQL or SHOW VARIABLES LIKE 'innodb%'; in MySQL.

⚠️ Every knob here is a trade-off, not a free speed-up. Change one thing at a time, benchmark with your own
workload, and read the linked doc before touching anything marked unsafe.

Part 1: PostgreSQL

1.1 How PostgreSQL plugs things in

PostgreSQL's superpower is its extension system. A lot of what feels like "core" features ships as extensions that use documented plug points:

Plug point	What it lets an extension do	Since
`CREATE EXTENSION`	Package types, functions, operators and index types	9.1
Table access methods	Replace how rows are stored (the storage engine)	12
Index access methods + `CREATE ACCESS METHOD`	Add new index types (for example, pgvector's HNSW)	9.6
Custom scan providers	Take over query execution (pg_duckdb, Citus and TimescaleDB use this)	9.5
Foreign data wrappers	Query external data as if it were a table	9.1
Background workers	Run jobs inside the server (schedulers, partition maintenance)	9.3
Custom WAL resource managers	Let extension storage be crash-safe and replicated	15
Archive modules (`archive_library`)	Replace shell-based WAL archiving with a loadable library	15
Logical decoding output plugins	Stream row changes out (`pgoutput`, `wal2json`)	9.4

Extensions that hook into the server at startup must be listed in shared_preload_libraries, which needs a restart. List what's installed with \dx, and what's available with SELECT * FROM pg_available_extensions;.

1.2 Storage engines (table access methods)

PostgreSQL has one built-in storage engine, heap. Others plug in through the table access method API:

CREATE TABLE orders (...) USING heap;              -- default
CREATE TABLE events (...) USING columnar;          -- from an extension
ALTER  TABLE events SET ACCESS METHOD heap;        -- PG15+, rewrites the table
SET default_table_access_method = 'columnar';      -- default for new tables

ALTER TABLE ... SET ACCESS METHOD arrived in PG15. It rewrites the whole table under an exclusive lock, so plan it like a migration. The default comes from default_table_access_method.

Engine	What it is	Best for	Watch out for
`heap` (core)	Row store: 8 KB slotted pages, MVCC, TOAST for big values	General OLTP. The safe default	Bloat from dead row versions, which needs VACUUM
Citus columnar (`USING columnar`)	Compressed column store in the `citus_columnar` extension	Append-only analytics, event history	Designed as append-only: `UPDATE`/`DELETE` are unsupported, and several index types aren't available
OrioleDB (`USING orioledb`)	Undo-log MVCC, row-level WAL, no buffer-mapping table	Write-heavy OLTP without VACUUM bloat	Beta, and needs a patched PostgreSQL. The project says it's not for production yet
pg_duckdb	Embeds DuckDB's vectorised engine; mainly a query accelerator	Analytics over heap tables and data-lake files	A different execution engine, so SQL semantics can differ in edge cases
pg_lake (`USING iceberg`)	Transactional Apache Iceberg tables on object storage	Lakehouse tables writable from Postgres	Needs the companion `pgduck_server` process
TimescaleDB hypertables	Automatic time partitioning, plus a row-to-column store ("hypercore")	Time series, metrics, IoT	Columnstore features are under the Timescale License, not Apache 2

💡 In my own tests (see my Database Engines 101 article's lab), the same 5 M rows took 868 MB in heap and 127 MB in
columnar, and an analytics query read 35x fewer pages. Columnar also flatly refused UPDATE.

1.3 Index types (index access methods)

This is where most performance is won or lost. PostgreSQL ships six index types (overview), and extensions add more:

Index	Syntax	Use it for	Key knobs
B-tree (default)	`USING btree`	Equality, ranges, sorting, uniqueness	`fillfactor`, `deduplicate_items`
Hash	`USING hash`	Equality only, on long keys	Crash-safe only since PG10
GIN	`USING gin`	JSONB, arrays, full-text, trigrams	`fastupdate`, `gin_pending_list_limit`
GiST	`USING gist`	Geometry, ranges, nearest-neighbour	Operator-class specific
SP-GiST	`USING spgist`	Quadtrees, k-d trees, radix trees (IPs, phone prefixes)	Operator-class specific
BRIN	`USING brin`	Huge tables naturally ordered by a column (time)	`pages_per_range`, `autosummarize`
bloom (contrib)	`USING bloom`	Equality on any combination of many columns	`length`, `col1..colN`
RUM (ext)	`USING rum`	Full-text search ranked inside the index	Slower to build than GIN
HNSW / IVFFlat (pgvector)	`USING hnsw`	Vector similarity search	See Part 3

B-tree features worth knowing

Deduplication (PG13): duplicate keys are stored once, so indexes on low-cardinality columns shrink a lot. It's on by default. Indexes carried over by pg_upgrade need a REINDEX to benefit. Docs
Covering indexes with INCLUDE (PG11) enable index-only scans without making the extra columns part of the key: CREATE INDEX ON orders (customer_id) INCLUDE (total); Docs
Skip scan (PG18): a multicolumn index on (region, created_at) can now serve WHERE created_at > ... even with no condition on region. PG18 release notes
uuidv7() (PG18): time-ordered UUIDs land at the end of the B-tree instead of at random pages. That means fewer page splits than random gen_random_uuid() values. PG18 release notes

Index features that apply to every type

Partial indexes index only the rows you query: CREATE INDEX ON orders (created_at) WHERE status = 'open'; Docs
Expression indexes index a computed value: CREATE INDEX ON users (lower(email)); Docs
CREATE INDEX CONCURRENTLY builds an index without blocking writes. It can't run inside a transaction, and if it fails it leaves an INVALID index you must drop. Docs
Operator classes change what an index can answer:
- text_pattern_ops makes LIKE 'abc%' indexable under non-C collations. Docs
- jsonb_path_ops gives a smaller, faster GIN index that supports only containment (@>) and jsonpath queries. Docs

GIN pending list. With fastupdate on (the default), new entries go to a pending list that is merged later. This makes writes cheaper, but a reader that triggers the merge pays the cost. For steady read latency, shrink gin_pending_list_limit (default 4 MB) or turn fastupdate off. Docs

BRIN stores just the min and max for each block range (default pages_per_range = 128), so it is kilobytes where a B-tree would be gigabytes. PG14 added minmax_multi operator classes, which tolerate outliers, and bloom operator classes for unordered equality lookups. Docs

CREATE INDEX ON logs USING brin (ts timestamptz_minmax_multi_ops) WITH (pages_per_range = 32);

PG18 can also build GIN indexes in parallel. B-tree (PG11) and BRIN (PG17) builds were already parallel. PG18 release notes

1.4 Row and page layout knobs

These are set per table or per column, and they change how bytes are laid out on disk.

fillfactor and HOT updates

Default 100 for tables. Setting it to 80–90 leaves free space on each page.
An update can then write the new row version on the same page, and if no indexed column changed, no index is touched. That's a heap-only tuple (HOT) update.
ALTER TABLE accounts SET (fillfactor = 85);
HOT docs · storage parameters

TOAST (how big values are stored) (docs)

Values larger than about 2 KB are compressed and/or moved out of line into a TOAST table.
toast_tuple_target (default 2032 bytes) controls when that kicks in.
Per-column storage modes:
- PLAIN: inline only, no compression.
- MAIN: compress, and move out of line only as a last resort.
- EXTERNAL: out of line, no compression. Fast substring() on large text.
- EXTENDED: compress, then move out of line. The default for most types.

ALTER TABLE docs ALTER COLUMN body SET STORAGE EXTERNAL;

Compression algorithm (PG14)

Choose pglz (the default) or lz4 per column, or server-wide with default_toast_compression.
lz4 is much faster to compress and decompress. It requires a server built with lz4 support.
Changing it only affects newly written values.
Docs

ALTER TABLE docs ALTER COLUMN body SET COMPRESSION lz4;

Virtual generated columns (PG18): computed when read, so they take no disk space. Before PG18, generated columns always took space (STORED). PG18 release notes

1.5 WAL and durability knobs

The write-ahead log is what makes commits survive crashes. It's also the biggest lever for write speed. All of these are on the WAL settings page unless linked otherwise.

Knob	Default	What it trades	Safe?
`synchronous_commit`	`on`	`off` returns before the WAL flush. A crash can lose the last few hundred ms of commits, but never corrupts data	✅ Per transaction
`wal_compression`	`off`	Compresses full-page images with `pglz`, `lz4` or `zstd` (PG15+). Costs CPU, saves WAL volume and replication bandwidth	✅
`checkpoint_timeout` / `max_wal_size`	`5min` / `1GB`	Fewer checkpoints mean fewer full-page writes, at the cost of longer crash recovery	✅
`wal_level`	`replica`	`logical` is needed for CDC and logical replication. `minimal` suits standalone bulk loads	✅ Restart
`commit_delay`	`0`	Groups commits together. Helps only at high concurrency on slow fsync	✅
`full_page_writes`	`on`	Protects against torn pages. Turn off only if storage guarantees atomic 8 KB writes	⚠️
`fsync`	`on`	`off` risks unrecoverable corruption on a crash	❌ Throwaway loads only

Per-transaction durability is the most underused trick here:

BEGIN;
SET LOCAL synchronous_commit = off;   -- only this transaction
INSERT INTO page_views ...;           -- losing this on a crash is acceptable
COMMIT;

(Asynchronous commit docs)

Unlogged tables skip WAL entirely. That makes them much faster to write, but they are emptied after a crash and not replicated. They suit staging and cache tables. Switch between the two modes with ALTER TABLE t SET LOGGED | UNLOGGED. Docs

Backups, archiving and replication

archive_library (PG15) archives WAL through a loadable module instead of running a shell command for every file. Docs
Incremental backups (PG17): set summarize_wal = on, then run pg_basebackup --incremental and merge with pg_combinebackup.
Quorum synchronous replication: synchronous_standby_names = 'ANY 1 (replica_a, replica_b)' waits for any one of the replicas, so one slow standby doesn't stall commits. Docs

The official Non-Durable Settings page lists every knob that trades safety for speed, in one place.

1.6 Memory and cache

All on the resource consumption page, except where linked otherwise.

Knob	Default	Guidance
`shared_buffers`	128 MB	Start around 25% of RAM on a dedicated server. The OS page cache holds the rest. Restart
`effective_cache_size`	4 GB	A planner hint only; it allocates nothing. Set to ~50–75% of RAM
`work_mem`	4 MB	Applies per sort or hash operation, per worker, so one query can use it many times over. Raise it per session for analytics
`hash_mem_multiplier`	2.0 (PG15+)	Lets hash joins and aggregates use `work_mem × this` before spilling to disk
`maintenance_work_mem`	64 MB	Used by VACUUM, `CREATE INDEX` and adding foreign keys. 1–2 GB speeds up index builds (it matters a lot for vector indexes)
`huge_pages`	`try`	Use `on` with preallocated Linux huge pages when shared_buffers is ≥ 8 GB. Restart

Keeping hot data hot: pg_prewarm can load a table into cache on demand. Its autoprewarm worker saves the list of cached blocks and reloads it after a restart, so you don't start with a cold cache. pg_buffercache shows what's in the cache right now.

1.7 Disks, SSDs and asynchronous I/O

Tell the planner you're on SSD.

random_page_cost defaults to 4.0, which assumes spinning disks.
On NVMe or cloud SSDs, 1.1–1.5 makes the planner choose index scans when it should.
It can also be set per tablespace (see 1.8).

Asynchronous I/O (PG18). This is one of the biggest storage changes in years.

The new io_method setting lets backends queue several reads at once for sequential scans, bitmap heap scans and VACUUM. Restart.
Options: sync (the old behaviour), worker (background I/O workers) and io_uring (Linux, needs a build with liburing).
The new pg_aios view shows I/O in flight.
PG18 release notes

io_method = io_uring            # or 'worker' (default)
effective_io_concurrency = 256  # PG18 default is 16
io_combine_limit = 256kB        # PG17+: largest combined read

PG18 also raised the defaults of effective_io_concurrency and maintenance_io_concurrency to 16. PG18 release notes

Data checksums are now on by default (PG18 initdb), so silent disk corruption is detected when pages are read. Use --no-data-checksums to opt out. pg_upgrade needs matching settings in the old and new clusters. PG18 release notes · pg_checksums

1.8 Hot and cold data: tablespaces and partitioning

Tablespaces map tables to disks. Put the hot tables on NVMe and the archive on cheap disks, and tell the planner each disk's real cost:

CREATE TABLESPACE fast LOCATION '/nvme/pg'
  WITH (random_page_cost = 1.1, effective_io_concurrency = 256);
CREATE TABLESPACE slow LOCATION '/hdd/pg'
  WITH (random_page_cost = 4.0);

ALTER TABLE orders_2019 SET TABLESPACE slow;   -- locks and copies the table

(CREATE TABLESPACE · managing tablespaces)

Declarative partitioning makes hot and cold data a matter of metadata (docs):

RANGE, LIST and HASH partitions. Partition pruning skips irrelevant partitions both at plan time and at run time.
Each partition can live in its own tablespace.
DETACH PARTITION ... CONCURRENTLY (PG14) removes an old partition without blocking queries. You can then archive it, move it, or drop it.

CREATE TABLE events (id bigint, ts timestamptz, payload jsonb) PARTITION BY RANGE (ts);
CREATE TABLE events_2026_09 PARTITION OF events
  FOR VALUES FROM ('2026-09-01') TO ('2026-10-01') TABLESPACE fast;
CREATE TABLE events_2025 PARTITION OF events
  FOR VALUES FROM ('2025-01-01') TO ('2026-01-01') TABLESPACE slow;

Automating it

pg_partman creates future partitions and applies retention rules: drop old partitions, or detach them into an archive schema. It runs as a background worker or via CALL partman.run_maintenance_proc().
TimescaleDB compresses old chunks into its columnstore automatically, e.g. CALL add_columnstore_policy('metrics', after => INTERVAL '7 days'); (docs). On its managed cloud, it can also tier old chunks to S3. (The company is now called TigerData; the extension is still TimescaleDB.)

1.9 Lakehouse, object storage and external data

This is where PostgreSQL is changing fastest. Postgres is turning into a front door to the data lake.

Tool	What it does
postgres_fdw	Query another Postgres. Pushes down filters, joins, aggregates and sorts. `async_capable` (PG14) scans remote partitions in parallel
file_fdw	Read server-side CSV or text files as tables
mysql_fdw	Read and write MySQL tables from Postgres
pg_duckdb	Read and write Parquet, CSV, JSON, Iceberg and Delta on S3, GCS, Azure and R2, and join them with regular tables. `SET duckdb.force_execution = true;` also runs regular queries on DuckDB's engine
pg_lake	Transactional Iceberg tables (`USING iceberg`) with Postgres as the catalog. Query and `COPY` Parquet, CSV and JSON on S3. Open-sourced by Snowflake from its Crunchy Data acquisition
pg_mooncake	Keeps a columnstore mirror of a Postgres table in Iceberg format, synced via logical replication
Neon	A modified Postgres with separate storage and compute: WAL goes to safekeepers, and pageservers keep pages on object storage. This makes instant branching and scale-to-zero possible. Databricks acquired Neon in 2025

-- pg_duckdb: join a Parquet file on S3 with a local table
SELECT c.name, count(*)
FROM read_parquet('s3://my-bucket/reviews/*.parquet') r
JOIN customers c ON c.id = r['customer_id']
GROUP BY c.name;

💡 Pattern: keep hot data in heap, send cold data to the lake. Keep recent rows in regular partitions.
Periodically copy old partitions to Parquet or Iceberg on object storage (pg_duckdb or pg_lake), then detach
and drop them locally. You can still query all of it.

1.10 Data types that change how data is stored and found

Choosing the right type changes both storage size and which indexes can help.

Type	Why it matters	Index with
`jsonb` vs `json`	`jsonb` is a parsed binary format that's fast to query and indexable. `json` keeps the exact input text and is reparsed every time	GIN (`jsonb_ops` or `jsonb_path_ops`)
Arrays	Many values in one column; containment and overlap queries	GIN
`hstore`	Flat key/value pairs. Older than jsonb, still compact	GIN / GiST
Range and multirange types (multirange PG14)	Time periods and numeric ranges, plus exclusion constraints ("no overlapping bookings")	GiST
PostGIS	Geometry and geography, spatial joins, distance queries	GiST, SP-GiST, BRIN
`pg_trgm`	Makes `LIKE '%foo%'`, `ILIKE`, regex and fuzzy `similarity()` indexable	GIN / GiST (`gin_trgm_ops`)
`tsvector`	Built-in full-text search with stemming and ranking	GIN
`citext`	Case-insensitive text (emails, usernames)	B-tree
`ltree`	Tree paths (`europe.germany.berlin`) for category hierarchies	GiST

-- "no two bookings of the same room may overlap", enforced by the database
CREATE EXTENSION btree_gist;
CREATE TABLE bookings (
  room int,
  during tstzrange,
  EXCLUDE USING gist (room WITH =, during WITH &&)
);

1.11 VACUUM and MVCC knobs

PostgreSQL keeps old row versions for MVCC, and autovacuum cleans them up. Autovacuum that falls behind is the most common cause of table bloat. All settings are on the vacuuming settings page.

Trigger formula: a table is vacuumed when dead rows exceed autovacuum_vacuum_threshold (50) + autovacuum_vacuum_scale_factor (0.2) × table rows. On a 1-billion-row table, that's 200 million dead rows. Lower the scale factor per table:

  ALTER TABLE big_events SET (autovacuum_vacuum_scale_factor = 0.01);

Insert-only tables (PG13+) now get vacuumed too, via autovacuum_vacuum_insert_threshold. That keeps the visibility map current, which index-only scans depend on.
PG18 adds autovacuum_vacuum_max_threshold, a cap on the trigger for huge tables. It also adds autovacuum_worker_slots, so you can raise autovacuum_max_workers without a restart. PG18 release notes
Speed: on SSD, raise autovacuum_vacuum_cost_limit (it effectively defaults to 200) so vacuum keeps up.
Reclaiming space: VACUUM FULL rewrites the table but blocks all access while it runs. pg_repack does the same online, with only brief locks.

1.12 Query execution and connections

Parallel query (docs)

max_parallel_workers_per_gather (default 2) controls how many CPU cores one query can use.
Raise it for analytics. Keep it low for high-concurrency OLTP.

JIT compilation (docs)

On by default, and kicks in above jit_above_cost (100000).
Compiling can take longer than a medium-sized query itself, so many OLTP systems set jit = off.

Statistics (docs)

For badly estimated skewed columns, raise statistics per column: ALTER TABLE t ALTER COLUMN c SET STATISTICS 1000;
CREATE STATISTICS teaches the planner about correlated columns such as city and zip code.

Connection poolers. Every PostgreSQL connection is a separate process, so thousands of clients need a pooler in front:

PgBouncer: pool_mode = transaction is the usual choice.
PgCat: pooling plus load balancing and sharding.
Supavisor: cloud-native and multi-tenant.

1.13 Observability plug-ins

You can't tune what you can't see:

pg_stat_statements: the top queries by total time. The first extension to install.
auto_explain: logs the execution plan of any query slower than N ms.
pg_stat_io (PG16): I/O broken down by backend type and context. Docs
track_io_timing = on: adds I/O time to EXPLAIN (ANALYZE, BUFFERS) and the stats views.

1.14 PostgreSQL starter profiles

These are starting points for a 64 GB RAM, 16 vCPU, NVMe server on PG18. Always benchmark. PGTune and the official tuning wiki are good sanity checks.

# (a) OLTP on NVMe
shared_buffers = 16GB
effective_cache_size = 48GB
work_mem = 16MB
maintenance_work_mem = 2GB
huge_pages = on
wal_compression = lz4
checkpoint_timeout = 15min
max_wal_size = 16GB
random_page_cost = 1.1
effective_io_concurrency = 256
io_method = io_uring              # PG18 + liburing build; else 'worker'
autovacuum_vacuum_cost_limit = 2000
autovacuum_vacuum_scale_factor = 0.05
jit = off
shared_preload_libraries = 'pg_stat_statements,auto_explain,pg_prewarm'

# (b) Bulk load / ETL (trades some durability, never corrupts)
synchronous_commit = off
wal_level = minimal               # no replicas / PITR on this box
max_wal_senders = 0
archive_mode = off
max_wal_size = 64GB
checkpoint_timeout = 30min
maintenance_work_mem = 4GB
max_parallel_maintenance_workers = 8
# + UNLOGGED staging tables + COPY, then ALTER TABLE ... SET LOGGED

# (c) Analytics / reporting
work_mem = 256MB                  # per operation, per worker!
hash_mem_multiplier = 4.0
max_parallel_workers_per_gather = 8
max_parallel_workers = 16
max_worker_processes = 16
random_page_cost = 1.1
effective_io_concurrency = 256
default_statistics_target = 500
enable_partitionwise_join = on
enable_partitionwise_aggregate = on

Part 2: MySQL (plus MariaDB and Percona)

2.1 How MySQL plugs things in

MySQL made "pluggable storage engines" famous. The server layer (parser, optimizer, replication) talks to storage engines through a handler API. On top of that sits a general plugin system, plus the newer component system:

SHOW ENGINES;                                          -- storage engines
SHOW PLUGINS;                                          -- everything plugged in
INSTALL PLUGIN clone SONAME 'mysql_clone.so';          -- classic plugin
INSTALL COMPONENT 'file://component_validate_password'; -- newer component model

(Plugin loading · plugin types · components)

💡 The big difference from PostgreSQL: in MySQL a storage engine is the whole package. It brings its own
index format, locking, crash recovery and caching. PostgreSQL splits table engines and index types into separate
layers that you can mix freely.

2.2 Storage engines

CREATE TABLE t (...) ENGINE=InnoDB;
ALTER TABLE t ENGINE=MyISAM;          -- full table copy

The default comes from default_storage_engine. disabled_storage_engines blocks the ones you never want used.

Engines in stock MySQL (overview)

Engine	What it is	Use it for	Watch out for
InnoDB (default)	ACID, row locks, MVCC, foreign keys, rows clustered by primary key	Almost everything	Primary key choice matters (see 2.3)
MyISAM	No transactions, table-level locks	Legacy read-only data	Not crash-safe
MEMORY	RAM only, hash indexes by default	Temporary lookup tables	Lost on restart. Size capped by `max_heap_table_size`
ARCHIVE	zlib-compressed rows. `INSERT` and `SELECT` only	Cold audit and log data	No `UPDATE`/`DELETE`; almost no indexes
CSV	Plain CSV files on disk	Swapping files with other tools	No indexes
BLACKHOLE	Throws data away but still writes it to the binlog	Replication relays and filters	Stores nothing, by design
FEDERATED	A proxy to a table on another MySQL server	Occasional remote reads	Disabled by default; slow joins
NDB	Distributed, shared-nothing, in-memory	Telecom-style high availability	Ships as the separate NDB Cluster product

Extra engines in MariaDB and Percona Server

Engine	Where	What it's for
MyRocks (RocksDB LSM)	Percona, MariaDB	Write-heavy workloads and data much bigger than RAM. Much better compression and less flash wear than InnoDB
ColumnStore	MariaDB	Columnar analytics (OLAP), with optional object storage
S3	MariaDB 10.5+	Read-only archive tables stored on S3. Move a table there with `ALTER TABLE old_orders ENGINE=S3;`
Spider	MariaDB	Shards one logical table across many servers
CONNECT	MariaDB	Treats CSV, JSON, XML, ODBC and remote tables as MariaDB tables
Aria	MariaDB	Crash-safe replacement for MyISAM; used for internal temp tables

⚠️ MyRocks trade-offs: no foreign keys, no FULLTEXT or SPATIAL indexes, and it requires binlog_format=ROW.
Limitations.
TokuDB, the other write-optimised engine, has been removed from both
Percona and
MariaDB (removed in 10.6).

2.3 InnoDB layout: clustered keys, page size and row formats

The clustered primary key (docs)

InnoDB stores rows inside the primary-key B-tree, and every secondary index stores the primary key as its row pointer. So:
- Keep primary keys short. A 36-byte UUID string gets copied into every secondary index.
- Keep inserts sequential. Random UUIDs split pages all over the tree. UUID_TO_BIN(UUID(), 1) reorders the time bits so new values sort roughly in insert order.
- With no primary key, InnoDB invents a hidden 6-byte one. sql_generate_invisible_primary_key (8.0.30) makes it visible and explicit.

Page size

innodb_page_size: 4 KB to 64 KB, default 16 KB.
It can only be set when the data directory is initialised. Smaller pages suit point lookups on SSD; larger pages suit scans.

Row formats (docs)

DYNAMIC is the default. It stores long BLOB, TEXT and VARCHAR values fully off-page, keeping only a 20-byte pointer in the row.
COMPRESSED adds zlib compression (see 2.8).

Instant DDL

ALTER TABLE t ADD COLUMN c INT, ALGORITHM=INSTANT; changes only metadata. Since 8.0.29 it works for adding a column at any position, and for dropping columns.
Online DDL docs

2.4 Indexes

Index	Syntax	Use it for
B-tree	default	Everything; the clustered primary key plus secondary indexes
Hash	`USING HASH`	MEMORY and NDB only. InnoDB silently uses a B-tree instead
Adaptive hash index	automatic	InnoDB builds an in-memory hash over hot pages. Off by default in 8.4
FULLTEXT	`FULLTEXT(body)`	Natural-language search. Add `WITH PARSER ngram` for Chinese, Japanese and Korean
SPATIAL (R-tree)	`SPATIAL INDEX(g)`	Geo queries. The column needs `NOT NULL` and an `SRID`
Functional (8.0.13)	`INDEX ((LOWER(email)))`	Indexing expressions
Multi-valued (8.0.17)	`INDEX ((CAST(doc->'$.tags' AS CHAR(32) ARRAY)))`	Arrays inside JSON (`MEMBER OF`, `JSON_OVERLAPS`)
Descending (8.0)	`INDEX (a ASC, b DESC)`	Mixed-direction `ORDER BY`
Invisible (8.0)	`ALTER TABLE t ALTER INDEX i INVISIBLE`	Test dropping an index safely. It's still maintained, but the optimizer ignores it

Tools the optimizer uses instead of, or alongside, indexes

Histograms (8.0): ANALYZE TABLE t UPDATE HISTOGRAM ON col WITH 64 BUCKETS; gives the optimizer data distributions without the write cost of an index. 8.4 adds AUTO UPDATE.
Hash join (8.0.18): replaced block nested-loop for joins without usable indexes. Memory is bounded by join_buffer_size.

2.5 Durability: redo log, doublewrite and binlog

Variables are on the InnoDB parameters and binary log options pages.

Knob	Default	What it trades	Safe?
`innodb_flush_log_at_trx_commit`	`1`	`2` writes at commit but flushes about once per second, so an OS crash can lose ~1 s. `0` means a mysqld crash can lose ~1 s	⚠️
`sync_binlog`	`1`	`0` leaves binlog flushing to the OS	⚠️
`innodb_redo_log_capacity` (8.0.30)	100 MB	Bigger means fewer forced flushes but longer recovery. Resizable at runtime	✅
`innodb_doublewrite`	`ON`	Protects against torn pages. `DETECT_ONLY` (8.0.30) detects them but can't repair them	⚠️
`binlog_transaction_compression` (8.0.20)	`OFF`	zstd-compresses the binlog, and it stays compressed on replicas	✅
`binlog_row_image`	`FULL`	`MINIMAL` makes the binlog much smaller, but breaks CDC tools like Debezium	⚠️
`binlog_group_commit_sync_delay`	`0` µs	Waits so more commits share one fsync	✅

The nuclear option for bulk loads: ALTER INSTANCE DISABLE INNODB REDO_LOG (8.0.21) turns off redo logging for the whole instance.

ALTER INSTANCE DISABLE INNODB REDO_LOG;   -- ONLY on a fresh instance you can rebuild
LOAD DATA INFILE '/data/orders.csv' INTO TABLE orders FIELDS TERMINATED BY ',';
ALTER INSTANCE ENABLE INNODB REDO_LOG;

❌ A crash while redo logging is disabled can leave the entire instance unrecoverable. It's meant only for
loading data into a new instance.

2.6 Memory and the buffer pool

innodb_buffer_pool_size: the single most important knob. The default is 128 MB; aim for about 70–75% of RAM on a dedicated server. It can be resized online.
innodb_dedicated_server: sizes the buffer pool and redo log from the machine's RAM and CPU count. It's a good default on dedicated hosts.
Buffer pool warm-up: innodb_buffer_pool_dump_at_shutdown and innodb_buffer_pool_load_at_startup (both on by default) save the hottest pages at shutdown and reload them at startup. That's MySQL's equivalent of pg_prewarm. Docs

MySQL 8.4 changed many InnoDB defaults. If you upgraded from 8.0 and performance shifted, this is why. (What's new in 8.4)

Variable	8.0 default	8.4 default
`innodb_adaptive_hash_index`	ON	OFF
`innodb_change_buffering`	all	none
`innodb_flush_method` (Linux)	fsync	O_DIRECT
`innodb_io_capacity`	200	10000
`innodb_log_buffer_size`	16 MB	64 MB
`innodb_numa_interleave`	OFF	ON

2.7 Disks and SSDs

innodb_flush_method = O_DIRECT (the 8.4 default on Linux) bypasses the OS page cache, so data isn't cached twice.
innodb_io_capacity / innodb_io_capacity_max tell InnoDB how many IOPS it may use for background flushing. Raise them on fast NVMe.
innodb_flush_neighbors = 0 (the default since 8.0) stops InnoDB flushing neighbouring pages together. That trick only helps spinning disks.
innodb_read_io_threads / innodb_write_io_threads: background I/O threads. Restart.
innodb_tmpdir puts the temporary sort files from online ALTER TABLE on a bigger or faster disk.

2.8 Compression

Method	How	Trade-off
Table compression	`ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8`	zlib. The buffer pool holds compressed and uncompressed copies, which costs CPU and RAM
Transparent page compression	`COMPRESSION='lz4'` or `'zlib'`	Compresses each page, then punches holes in the file. Needs a filesystem with hole-punching (ext4, XFS)
ARCHIVE engine	`ENGINE=ARCHIVE`	Heavy compression, but insert and select only
MyRocks	`rocksdb_default_cf_options="compression=kLZ4Compression;bottommost_compression=kZSTD"`	Compression per LSM level. Usually the best ratio of the lot
Binlog	`binlog_transaction_compression=ON`	Smaller binlogs and less replication bandwidth

2.9 Hot and cold data placement

Put a table on a different disk

With innodb_file_per_table (the default), each table is its own .ibd file, and DATA DIRECTORY can place it on another disk.
The directory must be listed in innodb_directories.

CREATE TABLE logs_archive (...) DATA DIRECTORY = '/mnt/hdd/mysql';

General tablespaces group several tables into one file on a chosen disk (docs):

CREATE TABLESPACE ts_cold ADD DATAFILE '/mnt/hdd/ts_cold.ibd' ENGINE=InnoDB;
ALTER TABLE logs_2019 TABLESPACE ts_cold;

Partitioning (docs)

Types: RANGE, LIST, HASH and KEY, plus the COLUMNS variants.
Partition pruning is visible in the partitions column of EXPLAIN.
In 8.0+, only InnoDB and NDB support partitioning.

The classic archiving pattern uses EXCHANGE PARTITION, which swaps a partition with a table instantly:

CREATE TABLE orders_2019 LIKE orders;
ALTER TABLE orders_2019 REMOVE PARTITIONING;
ALTER TABLE orders EXCHANGE PARTITION p2019 WITH TABLE orders_2019;  -- instant swap
ALTER TABLE orders DROP PARTITION p2019;
ALTER TABLE orders_2019 ENGINE=ARCHIVE;     -- or ENGINE=S3 on MariaDB → cold storage on S3

2.10 Lakehouse and analytics bridges

MySQL HeatWave: Oracle's in-memory, columnar, scale-out accelerator that sits next to InnoDB.
- HeatWave Lakehouse queries CSV, Parquet, Avro and JSON files in object storage and joins them with InnoDB tables.
- It's a managed cloud service (Oracle Cloud, AWS and others), not part of MySQL Community.
MariaDB ColumnStore: open-source columnar analytics inside MariaDB.
MariaDB S3 engine: cold, read-only tables on object storage.
CDC to the lake: stream the binlog through Debezium and Kafka into Iceberg, Delta or a warehouse. This needs binlog_format=ROW and binlog_row_image=FULL, and GTIDs are recommended.

2.11 Data types that change storage and retrieval

JSON is stored in an optimised binary format.
- Partial in-place updates (8.0) apply to JSON_SET, JSON_REPLACE and JSON_REMOVE.
- Pair them with binlog_row_value_options=PARTIAL_JSON so only the diff is written to the binlog.
- Index JSON with generated columns, functional indexes or multi-valued indexes.
Spatial types support SRIDs, with geographic calculations since 8.0.
ENUM stores a string as a 1–2 byte integer.
BLOB/TEXT columns are stored off-page and need prefix indexes. They're one more reason to avoid SELECT *.
VECTOR (9.0+) is covered in Part 3.

2.12 Plugins worth knowing

Plugin / component	What it adds
Clone plugin	`CLONE INSTANCE FROM ...` makes a full physical copy for provisioning replicas
Thread pool	Handles thousands of connections efficiently. Enterprise-only in MySQL; free in Percona and MariaDB (`thread_handling=pool-of-threads`)
Group Replication	Multi-node, consensus-based high availability
Query Rewrite	Rewrites bad queries server-side without changing application code
Keyring components	Encryption at rest (`ENCRYPTION='Y'`). The old keyring plugins are gone in 8.4
validate_password	Password policy, now implemented as a component

Online schema changes without downtime:

gh-ost: triggerless; reads the binlog.
pt-online-schema-change: uses triggers.
MySQLTuner gives quick configuration recommendations.

2.13 MySQL starter profiles

# (a) OLTP on NVMe (64 GB RAM, 16 vCPU)
[mysqld]
innodb_buffer_pool_size        = 48G
innodb_redo_log_capacity       = 8G
innodb_flush_log_at_trx_commit = 1
sync_binlog                    = 1
innodb_flush_method            = O_DIRECT
innodb_io_capacity             = 10000
innodb_io_capacity_max         = 20000
innodb_flush_neighbors         = 0
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup  = ON
binlog_format                  = ROW
binlog_transaction_compression = ON

# (b) Bulk load into a disposable instance (revert afterwards!)
[mysqld]
disable-log-bin
innodb_flush_log_at_trx_commit = 2
sync_binlog                    = 0
innodb_doublewrite             = OFF     # restart; unsafe on crash
innodb_redo_log_capacity       = 16G
innodb_log_buffer_size         = 256M
# + ALTER INSTANCE DISABLE INNODB REDO_LOG for the load itself
# + load in primary-key order, add secondary indexes afterwards

# (c) Write-heavy with MyRocks (Percona Server)
[mysqld]
plugin-load-add             = rocksdb=ha_rocksdb.so
default-storage-engine      = ROCKSDB
binlog_format               = ROW
transaction_isolation       = READ-COMMITTED
rocksdb_block_cache_size    = 32G
innodb_buffer_pool_size     = 1G
rocksdb_max_background_jobs = 8
rocksdb_default_cf_options  = "compression=kLZ4Compression;bottommost_compression=kZSTD"

Part 3: AI and ML workloads

🤖🤖🤖 This is the section most people don't know exists. You don't necessarily need a separate vector
database. Both PostgreSQL and MySQL-family databases can store embeddings, run approximate nearest-neighbour
search, combine it with keyword search, and even call models, inside the database, next to the rows you
already have. Here's what to plug in and which knobs matter.

3.1 The five AI workload shapes

Shape	What the database must do	Key components
Store embeddings	Hold 384–3,072-dim float vectors per row, cheaply	Vector types, half precision, TOAST/off-page storage
Similarity search (ANN)	"Top-k closest vectors" in milliseconds	HNSW, IVFFlat, DiskANN, ScaNN indexes
Filtered search	"Closest vectors where tenant = 42"	Partial indexes, partitions, iterative scans, label filters
Hybrid search	Combine semantic and keyword relevance	Full-text/BM25 plus rank fusion
In-database ML	Generate embeddings, predict, train	Cloud ML extensions, PL/Python, HeatWave AutoML

3.2 PostgreSQL + pgvector: the core

pgvector is the standard. It's available on practically every managed Postgres service, and at the time of writing the latest release is 0.8.6 (changelog).

CREATE EXTENSION vector;
CREATE TABLE items (id bigserial PRIMARY KEY, embedding vector(1536));
SELECT * FROM items ORDER BY embedding <=> '[...]' LIMIT 5;   -- cosine distance

Types, and how many dimensions each type can index (README):

Type	Bytes per dim	Max dims in an HNSW/IVFFlat index	Use when
`vector`	4 (float32)	2,000	Default
`halfvec` (0.7.0)	2 (float16)	4,000	Halves storage and index size, usually with little recall loss. Needed to index 3,072-dim models
`bit`	1/8	64,000	Binary quantization for very fast first-pass candidates
`sparsevec` (0.7.0)	per non-zero	1,000 non-zero	Sparse models (SPLADE, BM25-style vectors)

Distance operators:

<-> L2
<#> negative inner product
<=> cosine
<+> L1
<~> Hamming
<%> Jaccard

The operator in your query must match the index's operator class (vector_cosine_ops for <=>, for example), or the index won't be used.

HNSW vs IVFFlat: the two index types, and their knobs

	HNSW	IVFFlat
How	Multi-layer proximity graph	k-means clusters ("lists")
Build	Slower, more memory. Can be built on an empty table	Faster. Build after loading data
Speed/recall	Better	Good if tuned
Build knobs	`m` (default 16), `ef_construction` (default 64)	`lists` (start with rows/1000 up to 1 M rows, then √rows)
Query knob	`hnsw.ef_search` (default 40)	`ivfflat.probes` (default 1; start at √lists)

CREATE INDEX ON items USING hnsw (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 64);

BEGIN;
SET LOCAL hnsw.ef_search = 100;    -- higher = better recall, slower
SELECT id FROM items ORDER BY embedding <=> $1 LIMIT 10;
COMMIT;

Build-time knobs (these matter enormously)

maintenance_work_mem: HNSW builds are much faster when the graph fits in memory. If you see NOTICE: hnsw graph no longer fits into maintenance_work_mem, the build has fallen back to a slow path. Raise it, but not so far that the server runs out of memory. README
max_parallel_maintenance_workers: parallel index builds.
Load with COPY, then create the index. Watch progress in pg_stat_progress_create_index.

SET maintenance_work_mem = '8GB';
SET max_parallel_maintenance_workers = 7;   -- plus the leader process
CREATE INDEX CONCURRENTLY ON items USING hnsw (embedding vector_cosine_ops);

⚠️ Keep pgvector patched. Version 0.8.2 fixed a buffer overflow in parallel HNSW builds, and 0.8.3 fixed
possible index corruption during HNSW vacuuming (changelog).
Check yours with SELECT extversion FROM pg_extension WHERE extname = 'vector';

Filtered search: the #1 production gotcha

Approximate indexes find the top-k first and apply your WHERE afterwards. With a selective filter, you can get back fewer rows than your LIMIT. You have four options:

Iterative index scans (0.8.0+): keep scanning the index until enough rows pass the filter.

   SET hnsw.iterative_scan = relaxed_order;   -- or strict_order; default off
   SET hnsw.max_scan_tuples = 20000;          -- safety cap

Partial indexes per hot filter value: CREATE INDEX ... USING hnsw (embedding vector_l2_ops) WHERE (category_id = 123);
Partition by tenant, so each tenant gets its own small index.
pgvectorscale label filtering (below), which filters inside the graph search.

(README: filtering · iterative scans in 0.8.0)

Shrinking vectors: half precision and binary quantization plus re-ranking

-- index at half precision: half the index size
CREATE INDEX ON items USING hnsw ((embedding::halfvec(1536)) halfvec_cosine_ops);

-- binary quantization: tiny index; re-rank the candidates at full precision
CREATE INDEX ON items USING hnsw ((binary_quantize(embedding)::bit(1536)) bit_hamming_ops);

SELECT * FROM (
  SELECT * FROM items
  ORDER BY binary_quantize(embedding)::bit(1536) <~> binary_quantize($1)
  LIMIT 40                                   -- over-fetch cheap candidates
) candidates
ORDER BY embedding <=> $1                    -- re-rank with full precision
LIMIT 10;

3.3 More PostgreSQL vector engines

Extension	Index	Why you'd pick it	Status
pgvectorscale (TigerData)	`USING diskann` (StreamingDiskANN)	Disk-friendly graph index with Statistical Binary Quantization. Filters on `smallint[]` labels inside the search	Active; uses pgvector's types
VectorChord (TensorChord)	`USING vchordrq`	IVF plus RaBitQ quantization and re-ranking; fast builds at large scale	Active; successor to the deprecated pgvecto.rs
AlloyDB ScaNN (Google)	`USING scann`	Google's ScaNN algorithm inside AlloyDB	Cloud-only (AlloyDB)
pg_diskann (Azure)	`USING diskann`	Microsoft's DiskANN on Azure Postgres	Cloud-only (Azure)

-- pgvectorscale: filter by labels inside the ANN search
CREATE EXTENSION vectorscale CASCADE;
CREATE INDEX ON documents USING diskann (embedding vector_cosine_ops, labels);
SELECT * FROM documents
WHERE labels && ARRAY[1, 3]::smallint[]
ORDER BY embedding <=> $1 LIMIT 10;

3.4 Hybrid search: semantic plus keyword

Pure vector search misses exact matches such as product codes, names and error messages. Hybrid search runs both kinds of search and fuses the rankings:

Built-in full-text search: a tsvector column with a GIN index, queried with websearch_to_tsquery and ranked with ts_rank_cd. Docs
pg_trgm: typo-tolerant fuzzy matching.
ParadeDB pg_search: true BM25 ranking (built on Tantivy) through a bm25 index. Licensed AGPL.
Reciprocal Rank Fusion (RRF): score = Σ 1 / (60 + rank) across the result lists. pgvector's authors publish a reference example.

3.5 End-to-end RAG retrieval in one SQL query (PostgreSQL)

This combines half-precision storage, an HNSW index, a tenant filter kept full by iterative scans, keyword search and RRF:

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE chunks (
  id          bigserial PRIMARY KEY,
  document_id bigint NOT NULL,
  tenant_id   int    NOT NULL,
  content     text   NOT NULL,
  content_tsv tsvector GENERATED ALWAYS AS (to_tsvector('english', content)) STORED,
  embedding   halfvec(1536) NOT NULL          -- 2 bytes per dimension
);

-- after bulk-loading with COPY:
SET maintenance_work_mem = '8GB';
CREATE INDEX ON chunks USING hnsw (embedding halfvec_cosine_ops);
CREATE INDEX ON chunks USING gin (content_tsv);
CREATE INDEX ON chunks (tenant_id);

-- $1 = query embedding, $2 = tenant id, $3 = user question
BEGIN;
SET LOCAL hnsw.ef_search = 100;
SET LOCAL hnsw.iterative_scan = relaxed_order;
WITH semantic AS MATERIALIZED (
  SELECT id, embedding <=> $1::halfvec(1536) AS distance
  FROM chunks WHERE tenant_id = $2
  ORDER BY distance LIMIT 40
),
semantic_ranked AS (
  SELECT id, RANK() OVER (ORDER BY distance) AS rank FROM semantic
),
keyword AS (
  SELECT c.id, RANK() OVER (ORDER BY ts_rank_cd(c.content_tsv, q) DESC) AS rank
  FROM chunks c, websearch_to_tsquery('english', $3) q
  WHERE c.tenant_id = $2 AND c.content_tsv @@ q
  ORDER BY ts_rank_cd(c.content_tsv, q) DESC LIMIT 40
)
SELECT c.id, c.content,
       COALESCE(1.0 / (60 + s.rank), 0) + COALESCE(1.0 / (60 + k.rank), 0) AS rrf_score
FROM semantic_ranked s
FULL OUTER JOIN keyword k ON s.id = k.id
JOIN chunks c ON c.id = COALESCE(s.id, k.id)
ORDER BY rrf_score DESC
LIMIT 8;          -- these 8 chunks go into the LLM prompt
COMMIT;

3.6 In-database ML and model calls (PostgreSQL)

Option	What it does	Status
Google `google_ml_integration`	`embedding('text-embedding-005', text)` and `ml_predict_row()` from SQL	AlloyDB and Cloud SQL
Azure `azure_ai`	`azure_openai.create_embeddings()`, Azure ML and language services	Azure Postgres
AWS `aws_ml`	Calls SageMaker, Comprehend and Bedrock from SQL	Aurora PostgreSQL
PL/Python (`plpython3u`)	Run scikit-learn and similar libraries inside the server	Self-managed only. It's untrusted, so superuser only
pg_duckdb	Fast feature engineering and aggregations over tables and Parquet	Active
Apache AGE	Graph queries (openCypher) inside Postgres, useful for GraphRAG	Active

⚠️ Check a project's status before adopting it. Timescale's pgai (automatic
embedding "vectorizers" and LLM calls from SQL) is now an archived repository.
PostgresML and Apache MADlib were
once the go-to options for in-database ML. Check their recent activity before depending on them. For
auto-embedding today, a common pattern is a trigger that writes to a queue, plus an external worker that calls
the embedding API. Don't call an LLM synchronously inside a trigger.

3.7 MySQL-family AI features

MySQL 9.x: VECTOR type (added in 9.0; up to 16,383 dimensions) (docs)

CREATE TABLE docs (id INT PRIMARY KEY, v VECTOR(768));
INSERT INTO docs VALUES (1, STRING_TO_VECTOR('[0.1, 0.2, ...]'));
SELECT VECTOR_DIM(v), VECTOR_TO_STRING(v) FROM docs;

⚠️ Know what Community MySQL does and doesn't give you. It has the VECTOR type and its conversion
functions. According to the vector functions docs,
the DISTANCE() function is available only in Oracle's HeatWave service (and the Enterprise "MySQL AI"
offering), and Community MySQL has no vector index. For open-source vector search in the MySQL family, look
at MariaDB.

MySQL HeatWave (managed service) (docs)

GenAI: sys.ML_GENERATE (LLM calls), sys.ML_EMBED_ROW (embeddings), sys.ML_RAG (retrieval-augmented generation over a built-in vector store).
AutoML: sys.ML_TRAIN, sys.ML_PREDICT_ROW and sys.ML_EXPLAIN. You can train and serve models on your tables with SQL alone.

CALL sys.ML_TRAIN('shop.churn', 'churned', JSON_OBJECT('task', 'classification'), @model);
CALL sys.ML_MODEL_LOAD(@model, NULL);
SELECT sys.ML_PREDICT_ROW(JSON_OBJECT('tenure', 3, 'plan', 'basic'), @model, NULL);

MariaDB Vector (open source, 11.7+; 11.8 is the first LTS with it) (docs)

Type VECTOR(N), with a VECTOR INDEX based on a modified HNSW ("mHNSW") that works with InnoDB.
Distance functions: VEC_DISTANCE_EUCLIDEAN and VEC_DISTANCE_COSINE.
Knobs: mhnsw_default_m (graph connectivity), mhnsw_ef_search (recall vs speed), mhnsw_default_distance, and mhnsw_max_cache_size (memory for the index).

CREATE TABLE chunks (
  id        BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  content   TEXT NOT NULL,
  embedding VECTOR(4) NOT NULL,
  VECTOR INDEX (embedding) M=16 DISTANCE=cosine
);

INSERT INTO chunks (content, embedding) VALUES
  ('Tuning InnoDB buffer pool', VEC_FromText('[0.12,0.45,0.33,0.91]')),
  ('Vector search in MariaDB',  VEC_FromText('[0.10,0.40,0.35,0.88]'));

SET SESSION mhnsw_ef_search = 40;   -- higher = better recall
SELECT id, content
FROM chunks
ORDER BY VEC_DISTANCE_COSINE(embedding, VEC_FromText('[0.11,0.42,0.34,0.90]'))
LIMIT 5;

The distance function in ORDER BY must match the index's DISTANCE setting, or the index isn't used.

Managed MySQL with AI features

Google Cloud SQL for MySQL: vector columns, ANN vector indexes and approx_distance() searches. Docs
Amazon Aurora MySQL: calls SageMaker, Comprehend (for example aws_comprehend_detect_sentiment()) and Bedrock from SQL. Docs
PlanetScale: MySQL-compatible vector columns and indexes. Docs
MindsDB: an external SQL layer that connects to both MySQL and Postgres to add models and knowledge bases.

3.8 AI workload tuning checklist

#	Do this	Why
1	Size `maintenance_work_mem` so the HNSW graph fits, and use parallel builds	Builds that spill are many times slower
2	Keep the vector index in RAM (shared_buffers plus OS cache, or `mhnsw_max_cache_size` in MariaDB)	Graph search is random access; disk reads kill latency
3	Use `halfvec` (or quantization) for storage and indexes	Half the memory, and 3,072-dim models become indexable
4	Over-fetch, then re-rank	Cheap approximate candidates, then exact or cross-encoder precision
5	Plan for filters: partial indexes, partitions, iterative scans or label filtering	Otherwise filtered queries return too few rows
6	Tune recall per query with `SET LOCAL hnsw.ef_search` / `ivfflat.probes`	Trade recall for latency where it matters
7	Measure recall against exact search (`SET enable_indexscan = off`)	Otherwise you're tuning blind
8	Load with `COPY`, build indexes afterwards; generate embeddings asynchronously	Faster loads, and no LLM calls inside transactions
9	Keep embeddings in a separate `chunks` table	The main table stays small, and re-embedding with a new model doesn't rewrite business rows
10	Add hybrid search (FTS or BM25 plus RRF)	Catches exact terms that embeddings miss

Cheat sheet: problem to engine

Problem	PostgreSQL	MySQL / MariaDB
General OLTP	`heap` + B-tree	InnoDB
Write-heavy, larger than RAM	Tune WAL and checkpoints; OrioleDB (beta)	MyRocks
Append-only analytics	Citus `columnar`, TimescaleDB columnstore, pg_duckdb	MariaDB ColumnStore, HeatWave (cloud)
Faster writes, some data loss acceptable	`SET LOCAL synchronous_commit = off`	`innodb_flush_log_at_trx_commit = 2`
Staging or scratch tables	`UNLOGGED` tables	`sql_log_bin=0`, MEMORY/TempTable
One-off bulk load	`wal_level = minimal`, `COPY`, build indexes afterwards	`ALTER INSTANCE DISABLE INNODB REDO_LOG`
Hot data on NVMe, cold on HDD	Tablespaces + partitions	`DATA DIRECTORY`, general tablespaces
Cold data on S3	pg_lake / pg_duckdb (Parquet, Iceberg)	MariaDB S3 engine, HeatWave Lakehouse
Rolling archive	`DETACH PARTITION CONCURRENTLY` + pg_partman	`EXCHANGE PARTITION` → ARCHIVE
Huge time-ordered table	BRIN index	Partition by range
JSON documents	`jsonb` + GIN (`jsonb_path_ops`)	JSON + multi-valued / functional indexes
Fuzzy text search	`pg_trgm`	FULLTEXT (+ ngram parser)
Geo	PostGIS + GiST	SPATIAL (R-tree) + SRID
Vector search	pgvector HNSW / pgvectorscale / VectorChord	MariaDB Vector, HeatWave, Cloud SQL
Hybrid search	FTS or pg_search + RRF	FULLTEXT + vector + RRF in SQL
In-DB ML	Cloud ML extensions, PL/Python	HeatWave AutoML, Aurora ML
Remote data	FDWs (postgres_fdw, mysql_fdw)	FEDERATED, MariaDB CONNECT/Spider
Thousands of connections	PgBouncer	Thread pool (Percona/MariaDB)

Table of Contents