PostgreSQL vs MySQL Indexing

PostgreSQL is strong on complex queries thanks to partial, expression, and GIN indexes, while MySQL is optimized for straightforward B-Tree query processing.

A database index is a data structure for finding rows quickly without scanning the whole table. PostgreSQL and MySQL support different index types and implement them differently, and that difference directly affects query performance and indexing strategy.

Supported index types

PostgreSQL

MySQL (InnoDB)

PostgreSQL ships specialized index types such as GIN, GiST, and BRIN in addition to B-Tree. MySQL with InnoDB is effectively B-Tree plus FULLTEXT for text search and R-Tree for spatial search, so the gap in available options widens as the workload gets more specialized.

Partial and expression indexes

PostgreSQL partial index

Indexing only part of the data reduces disk usage and maintenance cost:

-- Index only rows where status='ACTIVE'
CREATE INDEX idx_active_users ON users(email)
WHERE status = 'ACTIVE';

PostgreSQL expression index

Build an index on the result of a function or expression:

-- Case-insensitive search
CREATE INDEX idx_lower_name ON users((LOWER(name)));
 
-- Index a single key inside JSON
CREATE INDEX idx_json_email ON users((data->>'email'));

MySQL limitations

• Partial index: condition-based partial indexes are not supported (only prefix indexes)
• Expression index: functional indexes supported since 8.0.13

-- MySQL prefix index
CREATE INDEX idx_name ON users(name(10));
 
-- MySQL 8.0+ functional index
CREATE INDEX idx_lower ON users((LOWER(name)));

MySQL has supported functional indexes since 8.0.13, but they are implemented internally as hidden virtual generated columns. Subqueries and non-deterministic functions cannot appear in the index expression. PostgreSQL expression indexes accept arbitrary function results far more freely.

Full text search and JSON indexing

PostgreSQL

GIN indexes provide advanced text search and JSONB indexing:

-- Full text search
CREATE INDEX idx_fts ON articles USING GIN(to_tsvector('english', content));
 
-- JSONB indexing
CREATE INDEX idx_jsonb ON users USING GIN(metadata jsonb_path_ops);

• Per-language tokenization and stop word handling built in
• Korean-language search is not adequately served by the default configuration and needs a morphological analysis extension or a custom configuration
• The full JSONB structure can be inverted-indexed

MySQL

-- FULLTEXT index
CREATE FULLTEXT INDEX idx_content ON articles(content);
 
-- JSON path index (8.0+)
CREATE INDEX idx_json ON users((CAST(data->>'$.email' AS CHAR(255))));

• Supports natural language search and boolean mode
• Indexing the full JSON structure is limited

Concurrency and index maintenance

PostgreSQL

-- Build an index without downtime
CREATE INDEX CONCURRENTLY idx_name ON users(name);
 
-- Rebuild an index
REINDEX INDEX idx_name;

CONCURRENTLY builds an index without taking a write lock, so it can be applied to tables in active use. In exchange it scans the table twice and is slower, and if it fails midway it leaves an unusable index behind that has to be dropped manually.

MySQL

-- Online DDL (limited for some operations)
ALTER TABLE users ADD INDEX idx_name(name), ALGORITHM=INPLACE, LOCK=NONE;

ALGORITHM=INPLACE avoids copying the whole table, and LOCK=NONE permits reads and writes while the index is being built. Depending on the operation, however, LOCK=NONE may be rejected and fall back to a stronger lock.

Performance considerations

PostgreSQL offers a wider set of options for partial and expression indexes and for text and JSON search. MySQL delivers simple, predictable performance on B-Tree range and equality queries. The deciding factor is therefore not index variety in itself but which way the workload leans.

Summary

PostgreSQL ships partial, expression, and GIN indexes out of the box, which helps with complex data models such as text search and JSONB. MySQL is B-Tree centered and handles range and equality queries simply and fast, but it is constrained on condition-based partial indexes and on broad expression indexes. Zero-downtime indexing is supported differently on each side, through PostgreSQL's CONCURRENTLY and MySQL's online DDL. Verify in advance whether it can be applied to a live table. Choose the DBMS and the indexing strategy based on data model complexity, how much of the workload is text and JSON search, and whether zero-downtime indexing is required.