WordPress Database Structure: A Deep Dive for Developers

Every WordPress operation — publishing a post, saving a setting, processing an order, checking user capabilities — ultimately reads from or writes to a MySQL database. Understanding this database structure is fundamental to advanced WordPress development.

When you're debugging a migration, optimising a slow query, building a WP-CLI script that manipulates data directly, or designing a plugin that stores complex data efficiently, the database structure isn't an abstraction — it's the ground truth.

This guide covers every table in WordPress's default database schema, what each one stores, how they relate to each other, and how to query them effectively.

The WordPress Database at a Glance

A fresh WordPress installation creates 12 tables. All table names use the table prefix defined in wp-config.php (default wp_). In a Multisite network, each site gets its own set of tables with a numeric prefix (wp_2_posts, wp_3_posts, etc.).

Default WordPress Tables
wp_posts          — All content (posts, pages, revisions, attachments, etc.)
wp_postmeta       — Custom fields and metadata for posts
wp_terms          — Categories, tags, and custom taxonomy terms
wp_term_taxonomy  — Taxonomy context for terms
wp_term_relationships — Associations between posts and terms
wp_termmeta       — Metadata for terms
wp_options        — Site settings and configuration
wp_users          — User accounts
wp_usermeta       — User metadata and capabilities
wp_comments       — Post comments
wp_commentmeta    — Metadata for comments
wp_links          — Blogroll links (legacy, rarely used)
01
wp_posts: The Heart of WordPress

wp_posts is the most important table in WordPress. It stores not just blog posts, but every type of "content object" — pages, media attachments, revisions, navigation menus, and any custom post type.

Schema

SQL — wp_posts Table Schema
wp_posts
├── ID                   BIGINT     Primary key, auto-increment
├── post_author          BIGINT     User ID (FK to wp_users.ID)
├── post_date            DATETIME   Publication date/time (local timezone)
├── post_date_gmt        DATETIME   Publication date/time (UTC)
├── post_content         LONGTEXT   Full content of the post
├── post_title           TEXT       Post title
├── post_excerpt         TEXT       Excerpt
├── post_status          VARCHAR    publish, draft, private, trash, auto-draft, inherit
├── comment_status       VARCHAR    open, closed
├── ping_status          VARCHAR    open, closed
├── post_password        VARCHAR    Password for password-protected posts
├── post_name            VARCHAR    URL slug
├── to_ping              TEXT       URLs to ping
├── pinged               TEXT       URLs already pinged
├── post_modified        DATETIME   Last modification (local timezone)
├── post_modified_gmt    DATETIME   Last modification (UTC)
├── post_content_filtered LONGTEXT  Filtered content (used by some plugins)
├── post_parent          BIGINT     Parent post ID (for pages, attachments, revisions)
├── guid                 VARCHAR    Globally unique identifier (permanent URL)
├── menu_order           INT        Order for hierarchical post types and nav menus
├── post_type            VARCHAR    post, page, attachment, revision, nav_menu_item, custom
├── post_mime_type       VARCHAR    MIME type for attachments (image/jpeg, etc.)
└── comment_count        BIGINT     Cached comment count

Key Queries

SQL — Common wp_posts Queries
-- Get all published posts
SELECT ID, post_title, post_date, post_name
FROM wp_posts
WHERE post_type = 'post'
  AND post_status = 'publish'
ORDER BY post_date DESC;

-- Count posts by type and status
SELECT post_type, post_status, COUNT(*) as count
FROM wp_posts
GROUP BY post_type, post_status
ORDER BY post_type, post_status;

-- Find all attachments (uploaded media)
SELECT ID, post_title, post_mime_type, guid
FROM wp_posts
WHERE post_type = 'attachment';

-- Find revisions for a specific post
SELECT ID, post_title
FROM wp_posts
WHERE post_parent = 42
  AND post_type = 'revision';

Understanding Post Statuses

StatusDescription
publishPublicly visible
draftUnpublished, visible to editors/admins
privateOnly visible to logged-in users with appropriate role
trashTrashed, not visible
auto-draftAutomatically saved draft
pendingAwaiting review/approval
futureScheduled for future publication
inheritRevision or attachment (inherits parent's status)
02
wp_postmeta: Custom Fields and Metadata

wp_postmeta stores arbitrary key-value data attached to any post — custom fields, theme settings, plugin data, featured image IDs, WooCommerce product data, and more.

SQL — wp_postmeta Schema
wp_postmeta
├── meta_id     BIGINT     Primary key, auto-increment
├── post_id     BIGINT     FK to wp_posts.ID
├── meta_key    VARCHAR    The field name (e.g., '_thumbnail_id', 'client_name')
└── meta_value  LONGTEXT   The field value (can be serialised PHP arrays/objects)

Understanding the Underscore Prefix Convention

Meta keys beginning with _ are "private" — they're hidden from the Custom Fields UI in the post editor. WordPress core and plugins use this convention for their own meta data.

  • _thumbnail_id — Featured image attachment ID
  • _wp_attachment_image_alt — Image alt text
  • _edit_lock — Who's currently editing the post
  • _edit_last — User who last edited

Custom fields visible to users have keys without the underscore prefix.

Key Queries

SQL — wp_postmeta Queries
-- Get all meta for a specific post
SELECT meta_key, meta_value
FROM wp_postmeta
WHERE post_id = 42;

-- Get specific meta value
SELECT meta_value
FROM wp_postmeta
WHERE post_id = 42
  AND meta_key = '_thumbnail_id';

-- Find all posts with a specific meta value
SELECT p.ID, p.post_title, pm.meta_value
FROM wp_posts p
JOIN wp_postmeta pm ON p.ID = pm.post_id
WHERE pm.meta_key = 'is_featured'
  AND pm.meta_value = '1'
  AND p.post_status = 'publish';

-- Find posts with no featured image
SELECT p.ID, p.post_title
FROM wp_posts p
WHERE p.post_type = 'post'
  AND p.post_status = 'publish'
  AND p.ID NOT IN (
    SELECT post_id FROM wp_postmeta WHERE meta_key = '_thumbnail_id'
  );
03
wp_terms, wp_term_taxonomy, wp_term_relationships

The taxonomy system uses three tables working together:

wp_terms — The actual terms (e.g., "WordPress", "PHP", "JavaScript" as tags):

SQL — wp_terms Schema
wp_terms
├── term_id     BIGINT     Primary key
├── name        VARCHAR    Display name ("WordPress Development")
├── slug        VARCHAR    URL slug ("wordpress-development")
└── term_group  BIGINT     For grouping synonymous terms (rarely used)

wp_term_taxonomy — Gives context to a term (which taxonomy it belongs to):

SQL — wp_term_taxonomy Schema
wp_term_taxonomy
├── term_taxonomy_id  BIGINT    Primary key
├── term_id           BIGINT    FK to wp_terms.term_id
├── taxonomy          VARCHAR   "category", "post_tag", or custom taxonomy name
├── description       LONGTEXT  Term description
├── parent            BIGINT    Parent term (for hierarchical taxonomies)
└── count             BIGINT    Cached count of posts with this term

wp_term_relationships — Connects posts to taxonomy terms:

SQL — wp_term_relationships Schema
wp_term_relationships
├── object_id         BIGINT    FK to wp_posts.ID
├── term_taxonomy_id  BIGINT    FK to wp_term_taxonomy.term_taxonomy_id
└── term_order        INT       Order when multiple terms (rarely used)

Joining All Three Tables

SQL — Taxonomy Join Queries
-- Get all categories for a specific post
SELECT t.name, t.slug, tt.taxonomy
FROM wp_terms t
JOIN wp_term_taxonomy tt ON t.term_id = tt.term_id
JOIN wp_term_relationships tr ON tt.term_taxonomy_id = tr.term_taxonomy_id
WHERE tr.object_id = 42
  AND tt.taxonomy = 'category';

-- Get all posts in a category by slug
SELECT p.ID, p.post_title
FROM wp_posts p
JOIN wp_term_relationships tr ON p.ID = tr.object_id
JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
JOIN wp_terms t ON tt.term_id = t.term_id
WHERE t.slug = 'wordpress-tips'
  AND tt.taxonomy = 'category'
  AND p.post_status = 'publish';
Why three tables? A single term (like "News") can exist in multiple taxonomies (category, tag, custom taxonomy). Separating terms from their taxonomy context allows this flexibility without duplicating term data.
04
wp_options: Site Configuration

wp_options stores all WordPress settings and plugin configuration — a massive key-value store:

SQL — wp_options Schema
wp_options
├── option_id     BIGINT     Primary key
├── option_name   VARCHAR    Setting key (e.g., "siteurl", "blogname")
├── option_value  LONGTEXT   Setting value (can be serialised)
└── autoload      VARCHAR    "yes" = loaded on every page, "no" = loaded on demand

Critical Options

option_nameDescription
siteurlWordPress installation URL
blognameSite title
blogdescriptionSite tagline
homeSite address (URL)
admin_emailAdmin email
blogpublicSearch engine visibility
active_pluginsArray of active plugin file paths
templateActive theme folder name
stylesheetActive theme (child theme) folder name
Performance note: Every option with autoload = 'yes' is loaded on every page request. Plugin developers who store large data in autoloaded options add to every page's load time. Use autoload = 'no' for infrequently accessed data.

Key Queries

SQL — wp_options Queries
-- Check active plugins
SELECT option_value
FROM wp_options
WHERE option_name = 'active_plugins';

-- Find all autoloaded options and their sizes (debug slow sites)
SELECT option_name, LENGTH(option_value) as size
FROM wp_options
WHERE autoload = 'yes'
ORDER BY size DESC
LIMIT 20;

-- Count total autoloaded data
SELECT 
  COUNT(*) as count,
  SUM(LENGTH(option_value)) as total_bytes,
  ROUND(SUM(LENGTH(option_value)) / 1024) as total_kb
FROM wp_options
WHERE autoload = 'yes';

-- Find all options from a specific plugin (by prefix)
SELECT option_name, option_value
FROM wp_options
WHERE option_name LIKE 'woocommerce_%';
05
wp_users and wp_usermeta
SQL — wp_users Schema
wp_users
├── ID             BIGINT     Primary key
├── user_login     VARCHAR    Username
├── user_pass      VARCHAR    Hashed password
├── user_nicename  VARCHAR    URL-friendly name
├── user_email     VARCHAR    Email address
├── user_url       VARCHAR    Website URL
├── user_registered DATETIME  Registration date
├── user_activation_key VARCHAR  For password reset
├── user_status    INT        0 = active
└── display_name   VARCHAR    Display name

wp_usermeta
├── umeta_id   BIGINT    Primary key
├── user_id    BIGINT    FK to wp_users.ID
├── meta_key   VARCHAR   Key (e.g., "wp_capabilities", "wp_user_level")
└── meta_value LONGTEXT  Value (capabilities stored as serialised array)

Critical usermeta keys:

  • wp_capabilities — Serialised PHP array: a:1:{s:13:"administrator";b:1;}
  • wp_user_level — User level integer (0–10, legacy)
  • session_tokens — Active login sessions

Querying User Roles

SQL — User Queries
-- Find all administrators
SELECT u.user_login, u.user_email
FROM wp_users u
JOIN wp_usermeta um ON u.ID = um.user_id
WHERE um.meta_key = 'wp_capabilities'
  AND um.meta_value LIKE '%administrator%';

-- Find users who haven't logged in recently
SELECT u.user_login, u.user_email, u.user_registered
FROM wp_users u
LEFT JOIN wp_usermeta um ON u.ID = um.user_id 
  AND um.meta_key = 'session_tokens'
WHERE um.meta_value IS NULL OR um.meta_value = 'a:0:{}';

wp_comments and wp_commentmeta

SQL — wp_comments Schema
wp_comments
├── comment_ID           BIGINT     Primary key
├── comment_post_ID      BIGINT     FK to wp_posts.ID
├── comment_author       TINYTEXT   Commenter name
├── comment_author_email VARCHAR    Commenter email
├── comment_author_url   VARCHAR    Commenter website
├── comment_author_IP    VARCHAR    IP address
├── comment_date         DATETIME   Comment date (local)
├── comment_date_gmt     DATETIME   Comment date (UTC)
├── comment_content      TEXT       Comment text
├── comment_karma        INT        Rating/karma
├── comment_approved     VARCHAR    1 = approved, 0 = pending, spam = spam, trash = trash
├── comment_agent        VARCHAR    User-agent string
├── comment_type         VARCHAR    "comment", "pingback", "trackback"
├── comment_parent       BIGINT     Parent comment (for threaded comments)
└── user_id              BIGINT     FK to wp_users.ID (0 if not logged in)
06
Interacting Safely with the Database

WordPress provides $wpdb for database interactions. Always use it rather than raw MySQLi:

PHP — Safe Database Queries with $wpdb
global $wpdb;

// Prepared statement (safe against SQL injection)
 $post_id = 42;
 $results = $wpdb->get_results(
    $wpdb->prepare(
        "SELECT * FROM {$wpdb->posts} WHERE ID = %d AND post_status = %s",
        $post_id,
        'publish'
    )
);

// Always use table name constants, not hardcoded table names
// $wpdb->posts         = wp_posts
// $wpdb->postmeta      = wp_postmeta
// $wpdb->options       = wp_options
// $wpdb->users         = wp_users
// $wpdb->usermeta      = wp_usermeta
// $wpdb->terms         = wp_terms
// $wpdb->term_taxonomy = wp_term_taxonomy
// $wpdb->comments      = wp_comments

Using $wpdb->prefix and the table property aliases ensures your code works regardless of the configured table prefix.

Security: Always use $wpdb->prepare() for queries containing user input or variables. Never concatenate values directly into SQL strings. This prevents SQL injection — the most critical database security vulnerability.

How the Tables Relate to Each Other

Table Connects To Relationship
wp_postswp_userspost_author → ID
wp_postswp_postspost_parent → ID (self-referential)
wp_postmetawp_postspost_id → ID
wp_term_relationshipswp_postsobject_id → ID
wp_term_relationshipswp_term_taxonomyterm_taxonomy_id → term_taxonomy_id
wp_term_taxonomywp_termsterm_id → term_id
wp_termmetawp_termsterm_id → term_id
wp_commentswp_postscomment_post_ID → ID
wp_commentswp_usersuser_id → ID
wp_commentmetawp_commentscomment_id → comment_ID
wp_usermetawp_usersuser_id → ID

Data Storage Patterns by Table

Table Storage Pattern Typical Data
wp_postsRow per content itemPosts, pages, attachments, revisions, menus
wp_postmetaKey-value pairs per postCustom fields, ACF data, WooCommerce fields
wp_optionsGlobal key-value storeSite settings, plugin config, theme mods
wp_usersRow per userUser accounts
wp_usermetaKey-value pairs per userCapabilities, preferences, session tokens
wp_termsRow per termCategory/tag names and slugs
wp_commentsRow per commentComment text, author, approval status

Frequently Asked Questions

Q: Can I add columns to existing WordPress tables?
A: Technically yes, but it's strongly discouraged. Modifying core tables creates upgrade headaches and compatibility issues. Instead, use wp_postmeta, wp_usermeta, or wp_options for additional data, or create new custom tables for complex data.
Q: Why do some meta_value fields contain `a:1:{s:13:` type data?
A: That's PHP serialised data — arrays and objects stored as strings in the database. WordPress serialises complex data for storage. Use maybe_unserialize() to decode it in PHP. Never search-replace raw serialised data in the database without using WP-CLI's search-replace command, which handles serialised data correctly.
Q: How do I find which plugin created a specific option in wp_options?
A: Search the plugin's code for add_option() or update_option() calls with that option name. Or grep the entire wp-content/plugins/ directory: grep -r "your_option_name" /wp-content/plugins/
Q: How large can a WordPress database get?
A: It depends on the content. A typical small business site is 50–200MB. A WooCommerce store with thousands of orders can reach several gigabytes. High-traffic publishing sites with extensive postmeta can exceed 10GB. The database size itself isn't a problem — unoptimised queries against large databases are.
Q: Can I use a different database engine (PostgreSQL, SQLite) with WordPress?
A: WordPress is tightly coupled to MySQL/MariaDB. While experimental SQLite and PostgreSQL compatibility layers exist, they're not production-ready. For serious WordPress projects, use MySQL 8.0+ or MariaDB 10.6+ for best compatibility and performance.
Q: What's the difference between post_date and post_date_gmt?
A: post_date stores the publication date in the site's configured timezone. post_date_gmt stores the same moment in UTC. WordPress uses GMT internally for consistency and converts to the local timezone for display. Always use GMT for programmatic comparisons to avoid timezone-related bugs.

Final Thoughts

Understanding WordPress's database structure transforms how you debug, optimise, and develop for the platform. When a migration fails, you know to check wp_options for old URLs. When a post's taxonomy isn't showing, you understand the three-table join. When a plugin is mysteriously slow, you can identify the bloated wp_postmeta query.

The WordPress database is simple, well-designed, and thoroughly documented. Time invested understanding it pays dividends on every complex WordPress project.

Need help with database optimisation, complex queries, or data migration? Our development team handles advanced WordPress database operations daily.

Contact us today and we'll sort it out.
Views: 227