WordPress Multisite Architecture: Understanding the Database Schema

I remember the first time I opened a database export from a client’s network and saw tables I didn't recognize. I was looking for wp_options, but instead, I found wp_2_options, wp_3_options, and a confusing mix of global tables. If you’re used to the clean, singular structure of a standard install, WordPress Multisite can feel like a labyrinth at first glance.

Understanding the underlying database schema is non-negotiable if you want to perform manual migrations, troubleshoot plugin conflicts, or build custom reporting tools for your network.

The Core Difference: Global vs. Local Tables

In a standard WordPress installation, tables like wp_posts or wp_users are straightforward. In a multisite environment, WordPress uses a prefix system to separate data based on the site ID.

When you enable the network feature, WordPress classifies your tables into two categories:

1. Global Tables: These contain data shared across the entire network. They don't include a site-specific ID in their name. Examples include wp_users, wp_usermeta, wp_site, and wp_blogs.
2. Site-Specific Tables: These are prefixed with the site ID (e.g., wp_10_posts). Every time you provision a new site, WordPress clones these tables.

If you’re planning to scale your infrastructure, you might eventually need to look into WordPress Database Scaling: Strategies for Horizontal Sharding to handle the bloat that comes with hundreds of these prefixed tables.

The WP_NETWORK and Global Metadata

The wp_site and wp_sitemeta tables are the heart of the WP_NETWORK structure. While wp_site defines which domains belong to your network, wp_sitemeta stores the global settings.

When you change a setting in your Network Admin dashboard, you aren't updating wp_options for a single site. You’re modifying rows inside wp_sitemeta. This is why site switching works the way it does. When you switch sites, WordPress isn't just changing the theme; it’s dynamically swapping the global $wpdb prefix to point to the correct set of tables for that specific blog_id.

I once tried to write a custom query to pull user activity across all sites. I initially tried to join wp_1_posts with wp_2_posts manually, which was a disaster. I learned the hard way that you should always use switch_to_blog() and restore_current_blog() to let the core functions handle the prefix switching for you. If you need to sync data between these sites, check out WordPress Headless Content Synchronization: Architecting Custom Sync Engines for a more robust approach than raw SQL joins.

Why the Schema Matters for Admin

If you're doing network administration, you have to be careful with how you interact with the database. Because wp_users is global, deleting a user from one site removes them from the entire network.

Here is a quick breakdown of the critical network-only tables:

• wp_blogs: Lists every site in your network.
• wp_site: Defines the main network domain.
• wp_sitemeta: Stores network-wide settings (like registration settings).
• wp_registration_log: Tracks when new sites are created.

Common Pitfalls and Trade-offs

We initially tried to move the wp_users table to a separate database to improve performance, but it broke almost every authentication hook we had. The WordPress core expects the global tables to reside in the same database instance as the site-specific ones.

If you are dealing with massive scale, you might be tempted to move things around. Before you do, make sure you understand the persistence layers. We’ve covered some of the storage challenges in WordPress Kubernetes Multisite: Solving Storage and Database Persistence, which is a better place to start than hacking the schema manually.

FAQ

Can I rename my table prefix after setting up multisite? Technically, yes, but it’s a nightmare. You have to update the prefix in wp-config.php, wp_blogs, and potentially dozens of entries in wp_sitemeta. I wouldn't recommend it unless you have about two days of downtime and a very strong coffee habit.

How does site switching affect performance? It's surprisingly cheap. switch_to_blog() simply updates the $wpdb->prefix property. It doesn't actually "switch" databases; it just tells WordPress to look at a different set of table names in the same database.

Why does my wp_users table get so large? Because it's global. Every user registered on any site in your network lives there. If you have 50 sites and each has 1,000 users, your wp_users table isn't 50,000 rows—it's only 1,000 if those users are shared. This is a feature, not a bug, but it can create contention on high-traffic sites.

Final Thoughts

The WordPress Multisite database schema is essentially a way to keep things isolated while sharing the user base. It’s not elegant, and it certainly isn't normalized by modern standards, but it works. Next time you're poking around in phpMyAdmin, pay close attention to which tables have that numeric prefix. If you find yourself needing to audit changes across these tables, looking into WordPress Event Sourcing: A Guide to DDD-Driven Auditability might offer a cleaner path than constant manual database queries.

I’m still not entirely convinced that running a massive network on a single database is the best path for every use case, but for most, it’s the reality we have to work with.