PostgreSQL vs MySQL Indexing

PostgreSQL handles complex queries well with partial, expression, and GIN indexes. 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. These differences directly affect query performance and indexing strategy.

flowchart TD
    Q[Query predicate] --> E{Equality or range lookup}
    E -->|yes| B[Evaluate a B-Tree]
    E -->|no| T{Text, arrays, or JSON}
    T -->|PostgreSQL| G[GIN, GiST, or expression index]
    T -->|MySQL| F[FULLTEXT or functional index]

Supported index types

PostgreSQL

MySQL (InnoDB)

PostgreSQL provides specialized index types such as GIN, GiST, and BRIN in addition to B-Tree. MySQL with InnoDB mainly uses B-Tree, with FULLTEXT for text search and R-Tree for spatial search. The difference in available options increases with more specialized workloads.

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. It implements them internally as hidden virtual generated columns. Subqueries and non-deterministic functions cannot appear in the index expression. PostgreSQL expression indexes accept arbitrary function results 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. It scans the table twice, which makes index creation slower. If it fails midway, it leaves an unusable index that must 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. 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 more options for partial and expression indexes and for text and JSON search. MySQL delivers simple, predictable performance on B-Tree range and equality queries. Choose based on the operations that the workload requires.

Summary

PostgreSQL provides partial, expression, and GIN indexes. These indexes support complex data models for uses such as text search and JSONB. MySQL mainly uses B-Tree and handles range and equality queries simply and quickly. It has limits on condition-based partial indexes and on expression indexes.

PostgreSQL's CONCURRENTLY and MySQL's online DDL support zero-downtime indexing differently. Verify in advance whether the chosen method can be applied to a live table. Consider data model complexity and how much of the workload involves text and JSON search. Also consider whether zero-downtime indexing is required when choosing the DBMS and indexing strategy.