WordPress Database: Custom Post Types or Custom Tables?

Every WordPress plugin that stores structured data has to decide where that data lives in the WordPress database. You can reuse the core tables through custom post types and post meta, or you can create your own tables. This guide explains how each option works, where postmeta queries get slow, how to build a custom table with dbDelta() and $wpdb, and how KanbanWP lays out its own tables.

How the WordPress database is organized

A single-site WordPress install creates 12 core tables, defined in wp-admin/includes/schema.php:

• wp_posts and wp_postmeta for posts, pages, attachments and every custom post type
• wp_comments and wp_commentmeta
• wp_terms, wp_termmeta, wp_term_taxonomy and wp_term_relationships for categories, tags and custom taxonomies
• wp_users and wp_usermeta
• wp_options for site settings
• wp_links, a legacy table for the old blogroll

The wp_ part is the table prefix set in wp-config.php, and it can be different on your site. In code you read it from $wpdb->prefix, which the wpdb reference describes as “the assigned WordPress table prefix for the site.”

The meta tables use a key-value layout. Each attribute of a post is one row in wp_postmeta, with a meta_key (varchar 255) and a meta_value (longtext). That layout is flexible, and it explains most of the performance trade-offs below.

Option 1: a WordPress custom post type with postmeta

A custom post type is a new kind of content stored in wp_posts, next to posts and pages. You register it with register_post_type(), and the key must be 20 characters or fewer. Extra fields go into wp_postmeta.

Custom post types give you a lot of core features without extra code:

• Set show_in_rest to true and the type appears in the REST API and the block editor.
• Add revisions to supports and WordPress stores revisions.
• Capabilities, the admin list screen, trash, and the WordPress export tool all work.
• Other plugins can read your data with WP_Query, get_posts() and get_post_meta().

For events, products, portfolio items, recipes and other content that people publish and browse, custom post types are usually the right choice.

Where postmeta gets slow

Look at the wp_postmeta definition in core’s schema file. It has a primary key on meta_id, an index on post_id, and an index on the first 191 characters of meta_key. The meta_value column has no index. The WordPress VIP documentation says the same, and advises you to “avoid queries that only use the meta_value field.”

Consider a task manager built on a custom post type. A query such as “open tasks on board 12, assigned to user 5, sorted by due date” turns into a WP_Query with a meta_query of three conditions. WordPress joins wp_postmeta once per condition, and none of the conditions can use an index on meta_value. On a few hundred posts you will not notice. On a site with hundreds of thousands of meta rows, these are the queries that show up as slow WordPress database queries.

Other costs of the postmeta layout:

• Weak types. Every value is stored as text, so numbers and dates need casting in queries and in PHP.
• Shared tables. Your rows sit next to every other plugin’s rows, and if the post type is public, sitemaps and site search can pick them up.
• Relationships by convention. A board with stages, tasks, comments and members is relational data. In postmeta you store IDs inside meta values and keep them consistent yourself.

A real migration: WooCommerce orders

WooCommerce stored orders as posts and postmeta for years. Since version 8.2, new stores keep them in custom tables by default through High-Performance Order Storage (HPOS). In WooCommerce’s own benchmark from March 2023, on a test site with about 400,000 orders:

• Creating 1,000 orders took 15.18 seconds with HPOS and 78.12 seconds with posts storage.
• Filtering orders by customer ID took 0.016 seconds with HPOS and 0.599 seconds with posts storage.
• A metadata search took 0.053 seconds with HPOS and 0.639 seconds with posts storage.

The test ran on a development build with one worker, so treat the numbers as a direction, not a promise for your site.

Option 2: custom tables with dbDelta and wpdb

A custom table gives you typed columns, a schema you design, and indexes that match your queries. WordPress gives you two tools for it: dbDelta() to create and update tables, and the wpdb class to query them.

Creating the table with dbDelta

dbDelta() compares your CREATE TABLE statement with the table in the database, then creates the table or alters it to match. It lives in wp-admin/includes/upgrade.php, which is not loaded on normal requests, so you require it first. The plugin handbook page on creating tables lists strict formatting rules:

• Put each field on its own line.
• Write two spaces between PRIMARY KEY and the column list.
• Use the keyword KEY, not INDEX, and include at least one KEY.
• Do not wrap field names in backticks or apostrophes.
• Give a length to every field type that takes one, such as int(11).

A minimal example:

global $wpdb;
require_once ABSPATH . 'wp-admin/includes/upgrade.php';

$table = $wpdb->prefix . 'myplugin_items';
$sql   = "CREATE TABLE {$table} (
	id bigint(20) NOT NULL AUTO_INCREMENT,
	group_id bigint(20) NOT NULL DEFAULT 0,
	title varchar(255) NOT NULL DEFAULT '',
	due_date date NULL DEFAULT NULL,
	PRIMARY KEY  (id),
	KEY group_id (group_id)
) " . $wpdb->get_charset_collate() . ';';

dbDelta( $sql );
update_option( 'myplugin_db_version', '1.0.0' );

The handbook also notes that the activation hook does not run when a plugin is updated. Store your schema version in an option, compare it on a later hook such as plugins_loaded, and call your install routine again when the stored version is older.

Querying with $wpdb

The wpdb class has methods for each kind of query:

• get_results() returns many rows, get_row() returns one row, and get_var() returns one value.
• insert(), update() and delete() take arrays of columns and values.
• query() runs any SQL statement.
• prepare() builds a query from placeholders: %d for integers, %f for floats and %s for strings.

Pass every value that comes from a request through prepare(). The wpdb reference says that “all data in SQL queries must be SQL-escaped before the SQL query is executed to prevent against SQL injection attacks.” A typical read looks like this:

$rows = $wpdb->get_results(
	$wpdb->prepare(
		"SELECT id, title, due_date FROM {$table} WHERE group_id = %d ORDER BY due_date ASC",
		$group_id
	)
);

Indexes and performance

An index helps a query only when the query filters or sorts on the indexed columns. Start from the queries your plugin runs most often, and index the column each one filters on first, usually a foreign key such as board_id. Avoid indexing every column. Each index slows inserts and updates a little and takes disk space.

To find slow WordPress database queries, set SAVEQUERIES to true in wp-config.php. WordPress then records each query, its time and its caller in $wpdb->queries. The debugging handbook describes this and warns that the setting slows the site. The Query Monitor plugin shows the same data in the admin bar and groups queries by the plugin or theme that ran them.

What custom tables cost you

• You write and maintain the schema, including changes in later versions.
• You lose revisions, the REST endpoints, the admin list screens and the export tool that a post type would give you, so you build what you need.
• Other plugins cannot read your data with WP_Query. You provide a REST API or hooks instead.
• Backups that copy the whole database include your tables, but the WordPress export tool does not.
• On uninstall, you decide whether to drop the tables, and you document that choice.

How to choose

Ask three questions about the data:

• Do people publish, browse or search it like posts? Use a custom post type.
• Is it a few settings or a few fields on existing posts? Use options or post meta.
• Is it relational, filtered and sorted in many ways, or private to logged-in users? Use custom tables.

Many plugins use both. Content stays in wp_posts, and the high-volume or relational data goes into custom tables that reference post IDs.

How KanbanWP stores its data

KanbanWP uses custom tables. The helper KanbanWP_DB::table() builds each name from $wpdb->prefix followed by kwp_, so the tasks table on a default install is wp_kwp_tasks. On activation, the plugin runs dbDelta() for 11 tables and stores the schema version in the kanbanwp_db_version option.

The tasks table splits its fields into two kinds. Fields you filter and sort on are real columns: board, stage, parent task, title, priority, position, swimlane, start and due dates, the linked post ID, the internal and archived flags, and the created, updated and completed times. A subtask is a row whose parent_id points to another task. List-shaped fields are stored as JSON in longtext columns: assignees, labels, checklist, attachments, custom fields, dependencies, the recurrence rule and watchers.

The JSON storage has a cost. Filtering by assignee or label uses a LIKE match on the JSON text, and that match cannot use an index. The task list query always filters on board_id, which is indexed, so the LIKE only scans the tasks of one board.

The post_id column links a card to a WordPress post or page, so editorial work stays connected to real content. The task itself is stored only in the KanbanWP tables, not in wp_posts. See linking tasks to WordPress posts.

What happens on deactivation and uninstall

Deactivating KanbanWP leaves every table and setting in place. Deleting the plugin also leaves the data, unless you tick Delete data on uninstall on the Data tab of the KanbanWP settings. With that box ticked, deleting the plugin drops all 11 tables, deletes the kanbanwp_settings and kanbanwp_db_version options, and removes the kanbanwp_manage capability from every role. The same Data tab has a Download all data (JSON) button for a full export first. See advanced settings.

Extending KanbanWP without touching its tables

If you build on KanbanWP, use the REST API, the hooks and filters or the WP-CLI commands rather than writing to the tables directly. The REST API checks the user’s role on the board and fires actions such as kanbanwp_task_created, which the webhooks and notifications listen to. A direct $wpdb write skips those checks and actions.