Postgres vs. MySQL: Indexing Options

For the impatience, jump to the comparison table

PostgreSQL and MySQL stand as two of the most popular choices. Both systems have evolved significantly over the years, with PostgreSQL 17 and MySQL 8 representing their latest General Availability (GA) versions. One of the most critical aspects of database performance tuning is the effective use of indexes. This article compares the indexing capabilities of PostgreSQL 17 and MySQL 8, providing insights for experienced database administrators and developers.

B-tree Indexes: The Foundation#

B-tree indexes are the default and most commonly used index type in both PostgreSQL and MySQL. They are well-suited for equality and range queries on data that can be sorted.

PostgreSQL B-tree Indexes#

-- Basic B-tree index
CREATE INDEX idx_customer_name ON customers(name);

-- Multi-column B-tree index
CREATE INDEX idx_customer_location ON customers(city, state, country);

-- B-tree index with sort options
CREATE INDEX idx_product_price_desc ON products(price DESC NULLS LAST);

PostgreSQL B-trees can handle equality and range queries, as well as BETWEEN, IN, IS NULL, and IS NOT NULL conditions. They can also be used with pattern matching operators like LIKE and ~ when the pattern is anchored to the beginning of the string.

MySQL B-tree Indexes#

-- Basic B-tree index
CREATE INDEX idx_customer_name ON customers(name);

-- Multi-column B-tree index
CREATE INDEX idx_customer_location ON customers(city, state, country);

-- B-tree index with descending order (MySQL 8.0+)
CREATE INDEX idx_product_price_desc ON products(price DESC);

MySQL B-trees support equality and range queries, as well as BETWEEN and IN conditions. They can also be used with LIKE when the pattern is anchored to the beginning of the string.

Key Differences#

While both systems use B-tree as their default index type, PostgreSQL offers more control over NULL sorting with NULLS FIRST/NULLS LAST options and supports regex pattern matching with the ~ operator. MySQL added support for descending indexes in version 8.0, catching up with a feature PostgreSQL has had for many years.

Hash Indexes: Equality-Only Optimization#

Hash indexes are optimized for equality comparisons and generally provide faster lookups than B-trees for simple equality operations.

PostgreSQL Hash Indexes#

-- Hash index
CREATE INDEX idx_user_id_hash ON users USING HASH (user_id);

PostgreSQL's hash indexes are transaction-safe and crash-recoverable, making them suitable for production use.

MySQL Hash Indexes#

-- Hash index for MEMORY table
CREATE TABLE lookup (
    id INT NOT NULL,
    name VARCHAR(100),
    INDEX USING HASH (name)
) ENGINE = MEMORY;

In MySQL, explicit hash indexes are primarily available for MEMORY tables. InnoDB uses an adaptive hash index internally, but this is managed automatically by the storage engine.

Key Differences#

PostgreSQL's hash indexes are more versatile and can be used with any storage engine, while MySQL's explicit hash indexes are limited to MEMORY tables. PostgreSQL's implementation is also transaction-safe and crash-recoverable, making it more suitable for production environments.

Specialized Index Types in PostgreSQL#

PostgreSQL offers several specialized index types that have no direct equivalent in MySQL.

GiST (Generalized Search Tree) Indexes#

GiST indexes provide an infrastructure for various indexing strategies, particularly useful for complex data types:

-- GiST index for geometric data
CREATE INDEX idx_location ON cities USING GIST (location);

-- GiST index for text with trigram matching
CREATE EXTENSION pg_trgm;
CREATE INDEX idx_document_trigram ON documents USING GIST (content gin_trgm_ops);

SP-GiST (Space-Partitioned GiST) Indexes#

SP-GiST indexes support non-balanced disk-based data structures like quadtrees and k-d trees:

-- SP-GiST index for points
CREATE INDEX idx_points ON points USING SPGIST (point);

-- SP-GiST index for text pattern matching
CREATE INDEX idx_text_prefix ON documents USING SPGIST (content text_ops);

GIN (Generalized Inverted Index) Indexes#

GIN indexes are "inverted indexes" suitable for composite values like arrays and full-text search:

-- GIN index for array containment
CREATE INDEX idx_tags ON posts USING GIN (tags);

-- GIN index for full-text search
CREATE INDEX idx_document_search ON documents USING GIN (to_tsvector('english', content));

BRIN (Block Range INdex) Indexes#

BRIN indexes store summaries about values in consecutive physical block ranges, making them efficient for large tables with natural ordering:

-- BRIN index for timestamp data
CREATE INDEX idx_logs_timestamp ON logs USING BRIN (created_at);

-- BRIN index with larger block range
CREATE INDEX idx_temperature ON weather_data USING BRIN (recorded_at) WITH (pages_per_range = 32);

Specialized Index Types in MySQL#

MySQL also offers some specialized index types that have no direct equivalent in PostgreSQL.

SPATIAL Indexes (R-tree)#

MySQL uses R-tree indexes for spatial data:

-- Spatial index
CREATE TABLE locations (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    location POINT NOT NULL SRID 4326,
    SPATIAL INDEX idx_location (location)
) ENGINE = InnoDB;

-- Query using spatial index
SELECT name, ST_AsText(location)
FROM locations
WHERE ST_Contains(
    ST_GeomFromText('POLYGON((-74 40.7, -74 40.9, -73.9 40.9, -73.9 40.7, -74 40.7))', 4326),
    location
);

FULLTEXT Indexes#

MySQL has built-in support for full-text search:

-- Full-text index
CREATE TABLE articles (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200),
    body TEXT,
    FULLTEXT INDEX idx_content (title, body)
) ENGINE = InnoDB;

-- Natural language mode search
SELECT id, title
FROM articles
WHERE MATCH(title, body) AGAINST('database optimization');

-- Boolean mode search
SELECT id, title
FROM articles
WHERE MATCH(title, body) AGAINST('+MySQL -PostgreSQL' IN BOOLEAN MODE);

Multi-Valued Indexes#

MySQL 8.0.17 introduced multi-valued indexes for JSON arrays:

-- Table with JSON column and multi-valued index
CREATE TABLE products (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    tags JSON,
    INDEX idx_tags ((CAST(tags AS ARRAY)))
) ENGINE = InnoDB;

-- Query using multi-valued index
SELECT * FROM products
WHERE JSON_CONTAINS(tags, '"electronics"');

Advanced Indexing Features#

Both PostgreSQL and MySQL offer advanced indexing features that can significantly improve query performance in specific scenarios.

Functional Indexes#

Both systems support indexing expressions rather than just columns:

PostgreSQL:

-- Expression index
CREATE INDEX idx_email_domain ON users ((split_part(email, '@', 2)));

MySQL:

-- Functional index
CREATE INDEX idx_upper_name ON customers ((UPPER(name)));

Covering Indexes#

Both systems support covering indexes, which can satisfy a query without accessing the table:

PostgreSQL:

-- Covering index with included columns
CREATE INDEX idx_orders_customer ON orders (customer_id) INCLUDE (order_date, status);

MySQL:

-- Index that covers a query
CREATE INDEX idx_covering ON customers (id, name, email);

Partial Indexes (PostgreSQL Only)#

PostgreSQL supports partial indexes that only index a subset of rows:

-- Partial index for active users only
CREATE INDEX idx_active_users ON users (username) WHERE active = true;

Invisible Indexes (MySQL Only)#

MySQL 8.0 introduced invisible indexes for testing index removal without actually dropping the index:

-- Create invisible index
CREATE INDEX idx_test ON orders (status) INVISIBLE;

-- Make existing index invisible
ALTER TABLE orders ALTER INDEX idx_status INVISIBLE;

-- Temporarily use invisible indexes for a query
SET SESSION optimizer_switch = 'use_invisible_indexes=on';

Index Maintenance#

Proper index maintenance is crucial for maintaining optimal database performance.

PostgreSQL#

PostgreSQL provides several tools for index maintenance:

-- Rebuild an index
REINDEX INDEX idx_name;

-- Rebuild all indexes on a table
REINDEX TABLE table_name;

-- Update statistics
ANALYZE table_name;

-- Monitor index usage
SELECT * FROM pg_stat_user_indexes WHERE relname = 'table_name';

MySQL#

MySQL integrates index maintenance with table operations:

-- Update statistics
ANALYZE TABLE customers;

-- Rebuild table and indexes
OPTIMIZE TABLE customers;

-- View index information
SHOW INDEX FROM customers;

-- Query index metadata
SELECT * FROM information_schema.STATISTICS
WHERE table_schema = 'mydb' AND table_name = 'customers';

Best Practices for Index Creation and Management#

PostgreSQL#

1. Use CONCURRENTLY for production environments:

   CREATE INDEX CONCURRENTLY idx_new_index ON large_table(column);

   This avoids locking the table for writes during index creation.

2. Monitor index usage:

   SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
   FROM pg_stat_user_indexes
   WHERE schemaname = 'public'
   ORDER BY idx_scan DESC;

   Regularly check which indexes are being used and which are not.

3. Consider partial indexes for large tables with filtered queries:

   CREATE INDEX idx_recent_orders ON orders(order_date)
   WHERE order_date > CURRENT_DATE - INTERVAL '3 months';

   This creates smaller, more efficient indexes.

4. Use expression indexes for queries with functions:

   CREATE INDEX idx_lower_email ON users(lower(email));

   This allows the optimizer to use the index for queries with lower(email).

MySQL#

1. Use ALGORITHM=INPLACE for online operations:

   ALTER TABLE large_table ADD INDEX idx_new_index (column), ALGORITHM=INPLACE;

   This minimizes locking during index creation.

2. Monitor index usage:

   SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage
   WHERE object_schema = 'your_database'
   ORDER BY count_star DESC;

   Check which indexes are being used and which are not.

3. Use invisible indexes for testing:

   CREATE INDEX idx_test ON orders(status) INVISIBLE;

   Test the impact of an index before making it visible to the optimizer.

4. Consider prefix indexes for string columns:

   CREATE INDEX idx_description ON products(description(50));

   This creates smaller indexes while maintaining good selectivity.

Comparison Table: PostgreSQL 17 vs MySQL 8#

Conclusion and Recommendations#

Both PostgreSQL 17 and MySQL 8 offer robust indexing capabilities that can significantly improve database performance when properly utilized. Choose PostgreSQL if you need specialized index types, complex data types, partial indexes, or concurrent index creation in production environments. Choose MySQL if you prefer built-in full-text search, invisible indexes for testing, multi-valued indexes for JSON data, or simpler configuration and management.

For most general-purpose applications, either system can provide excellent performance with proper index design. The key is understanding the specific query patterns of your application and selecting the appropriate index types and configurations to optimize those patterns.

Remember that indexes come with maintenance overhead and storage costs, so it's important to balance the benefits of faster queries against these costs. Regularly monitor index usage and performance to ensure your indexing strategy remains optimal as your application evolves.

References#

PostgreSQL Documentation

1. PostgreSQL 17 Index Types
2. PostgreSQL CREATE INDEX
3. PostgreSQL Index-Only Scans
4. PostgreSQL Partial Indexes
5. PostgreSQL GiST and GIN Index Types
6. PostgreSQL BRIN Indexes

MySQL Documentation

1. MySQL 8.0 CREATE INDEX Statement
2. MySQL 8.0 How MySQL Uses Indexes
3. MySQL 8.0 SPATIAL Index Optimization
4. MySQL 8.0 Full-Text Search Functions
5. MySQL 8.0 Invisible Indexes
6. MySQL 8.0 Descending Indexes
7. MySQL 8.0 Multi-Valued Indexes