•
5 min read

WordPress Database Optimization with MySQL and PostgreSQL

Database performance tuning for WordPress including query optimization, indexing strategies, and migration considerations between MySQL and PostgreSQL

WordPress Database Optimization with MySQL and PostgreSQL

If your WordPress site has ever slowed to a crawl during peak traffic — or if you’ve watched a WooCommerce product page take six seconds to load — there’s a good chance your database is the bottleneck. WordPress ships with MySQL as its default database, and for most sites, it works beautifully out of the box. But “out of the box” only gets you so far.

In this post, we’ll dig into practical database optimization techniques for WordPress, explore indexing strategies that can dramatically speed up your queries, and talk about something increasingly on developers’ radar: whether migrating from MySQL to PostgreSQL makes sense for your project.

Let’s get into it.

Understanding WordPress Database Performance

WordPress relies heavily on two tables: wp_posts and wp_postmeta. Almost everything — pages, posts, custom post types, revisions, even WooCommerce products — lives in wp_posts. And all the extra data? That gets shoved into wp_postmeta as key-value pairs.

This design is flexible, but it comes at a cost. As your site grows, wp_postmeta can balloon to millions of rows, and query optimization becomes critical. A single product page on a WooCommerce store might trigger dozens of JOIN operations against wp_postmeta just to assemble the data it needs.

Here’s a common slow query you might see in your slow query log:

SELECT p.ID, p.post_title, pm1.meta_value AS price, pm2.meta_value AS stock
FROM wp_posts p
LEFT JOIN wp_postmeta pm1 ON p.ID = pm1.post_id AND pm1.meta_key = '_price'
LEFT JOIN wp_postmeta pm2 ON p.ID = pm2.post_id AND pm2.meta_key = '_stock_status'
WHERE p.post_type = 'product'
AND p.post_status = 'publish'
ORDER BY pm1.meta_value + 0 ASC
LIMIT 20;

On a store with 10,000 products and 500,000 postmeta rows, this query can take several seconds without proper optimization. Let’s fix that.

MySQL Optimization Strategies That Actually Work

1. Add Strategic Indexes

WordPress’s default indexing on wp_postmeta includes an index on post_id and meta_key, but it’s often not sufficient for complex queries. Adding a composite index can make a massive difference.

-- Check existing indexes on wp_postmeta
SHOW INDEX FROM wp_postmeta;

-- Add a composite index for common lookup patterns
ALTER TABLE wp_postmeta ADD INDEX idx_meta_lookup (meta_key, meta_value(191), post_id);

-- Add an index to speed up autoload queries on wp_options
ALTER TABLE wp_options ADD INDEX idx_autoload (autoload, option_name);

-- Clean up post revisions that bloat wp_posts
DELETE FROM wp_posts WHERE post_type = 'revision';

-- Remove orphaned postmeta entries
DELETE pm FROM wp_postmeta pm
LEFT JOIN wp_posts p ON pm.post_id = p.ID
WHERE p.ID IS NULL;

That composite index on wp_postmeta is a game-changer. It allows MySQL to satisfy many common WordPress queries using an index scan instead of a full table scan. On a real-world WooCommerce site I worked on, this single indexing change reduced average query time from 1.8 seconds to 0.05 seconds.

2. Tune Your MySQL Configuration

The default MySQL configuration is conservative. For a dedicated WordPress database server, these my.cnf adjustments can yield significant improvements:

[mysqld]
# InnoDB buffer pool — set to ~70-80% of available RAM on a dedicated DB server
innodb_buffer_pool_size = 2G

# Increase the query cache (MySQL 5.7; removed in 8.0)
query_cache_type = 1
query_cache_size = 128M
query_cache_limit = 2M

# Optimize for writes
innodb_flush_log_at_trx_commit = 2
innodb_log_file_size = 256M

# Connection handling
max_connections = 200
thread_cache_size = 16

# Temp tables
tmp_table_size = 64M
max_heap_table_size = 64M

3. Use EXPLAIN to Find Problems

Before you optimize blindly, use EXPLAIN to understand what MySQL is actually doing:

EXPLAIN SELECT p.ID, p.post_title
FROM wp_posts p
INNER JOIN wp_postmeta pm ON p.ID = pm.post_id
WHERE pm.meta_key = '_featured'
AND pm.meta_value = 'yes'
AND p.post_status = 'publish';

Look for type: ALL (full table scans), missing indexes in the key column, and high values in rows. These are your optimization targets.

The PostgreSQL Question: Should You Migrate?

Here’s where things get interesting. PostgreSQL — or Postgres as most of us call it — has been gaining traction in the WordPress ecosystem, though it’s still far from mainstream. Projects like PG4WP provide a compatibility layer, and some developers have built custom solutions for headless WordPress setups.

Why Developers Consider Postgres

Postgres offers several advantages that matter for large-scale WordPress deployments:

  • Better query planner: Postgres’s query optimizer is generally more sophisticated than MySQL’s, especially for complex joins across the postmeta table.
  • Advanced indexing: Postgres supports GIN, GiST, and BRIN indexes that can handle full-text search and JSON data far more efficiently.
  • Native JSON support: If you’re storing structured data in postmeta, Postgres’s jsonb type with indexing is significantly faster than MySQL’s text-based approach.
  • Concurrent operations: Postgres handles concurrent reads and writes more gracefully thanks to its MVCC implementation.

Here’s what a WordPress-style query looks like with Postgres-specific optimizations:

-- Create a GIN index on JSONB data for product attributes
CREATE INDEX idx_product_attributes ON wp_postmeta
USING GIN ((meta_value::jsonb))
WHERE meta_key = '_product_attributes';

-- Use a CTE for cleaner, often faster complex WordPress queries
WITH published_products AS (
    SELECT ID, post_title
    FROM wp_posts
    WHERE post_type = 'product'
    AND post_status = 'publish'
),
product_prices AS (
    SELECT post_id, meta_value::numeric AS price
    FROM wp_postmeta
    WHERE meta_key = '_price'
)
SELECT pp.ID, pp.post_title, pr.price
FROM published_products pp
JOIN product_prices pr ON pp.ID = pr.post_id
WHERE pr.price BETWEEN 10 AND 100
ORDER BY pr.price ASC
LIMIT 20;

The Reality Check

Before you rush to migrate, here’s what you need to know:

Compatibility is the hard part. WordPress core, plugins, and themes are written for MySQL. While compatibility layers exist, you’ll inevitably encounter plugins that use MySQL-specific syntax (FOUND_ROWS(), SQL_CALC_FOUND_ROWS, backtick quoting) that breaks on Postgres.

Headless setups are the sweet spot. If you’re using WordPress as a headless CMS with a separate frontend (Next.js, Gatsby, etc.), migrating the data layer to Postgres becomes far more practical. You can use WordPress for content management and serve the frontend from a Postgres-backed API.

Managed hosting doesn’t support it. WP Engine, Kinsta, Flywheel — none of the major managed WordPress hosts offer Postgres. You’ll need to self-host or use a platform like AWS/GCP with custom infrastructure.

My recommendation: stick with MySQL for traditional WordPress sites and invest in proper query optimization and indexing. Consider Postgres if you’re building a custom headless architecture where you control the data layer entirely.

A Practical Optimization Checklist

Before we wrap up, here’s a quick-reference checklist you can apply today:

# 1. Identify slow queries
mysqldumpslow -s t /var/log/mysql/mysql-slow.log | head -20

# 2. Check table sizes to find bloat
mysql -e "
SELECT table_name, 
       ROUND(data_length/1024/1024, 2) AS data_mb,
       ROUND(index_length/1024/1024, 2) AS index_mb,
       table_rows
FROM information_schema.tables 
WHERE table_schema = 'wordpress'
ORDER BY data_length DESC;
"

# 3. Optimize tables after cleanup
mysqlcheck -o wordpress wp_postmeta wp_posts wp_options

Conclusion and Next Steps

Database optimization isn’t a one-time task — it’s an ongoing practice. Start with the highest-impact changes: add composite indexes to wp_postmeta, clean up revisions and orphaned data, and tune your MySQL configuration for your server’s resources. Use EXPLAIN religiously to understand your slow queries before throwing hardware at the problem.

If you’re evaluating Postgres for a WordPress project, be honest about your use case. For a headless architecture where you own the entire stack, it’s a compelling option with real performance benefits. For a traditional WordPress site with dozens of plugins, MySQL with proper optimization will serve you better.

Your immediate action items:

  1. Enable the MySQL slow query log and identify your top 10 slowest queries.
  2. Run the indexing commands from this post on a staging environment and benchmark the difference.
  3. Schedule a monthly database cleanup (revisions, transients, orphaned meta) — or automate it with WP-CLI.

Got questions about a specific optimization challenge? Drop a comment below or find me on Twitter. Happy optimizing!

Back to all posts