Optimizing the WordPress posts table

The WordPress posts table is one of the most important tables in a WordPress database.

It is also frequently misunderstood.

Despite its name, the table does not contain only blog posts. WordPress stores many different types of content in the same table, including:

• posts;
• pages;
• attachments;
• revisions;
• navigation-related content;
• custom post types;
• other Core or plugin-created post types.

On a small website, this architecture usually requires little attention. On a large editorial site, WooCommerce installation, membership platform or application with many custom post types, however, the posts table can grow substantially.

A large table is not automatically a slow table.

The real questions are:

• what is stored in it;
• how much unnecessary data has accumulated;
• which queries access it;
• whether those queries match useful database indexes;
• whether related metadata and taxonomy queries are creating additional work;
• whether maintenance is being performed safely.

This guide explains how the WordPress posts table works, what normally makes it grow, how WordPress indexes it, which query patterns matter, how revisions and Trash affect it and how to optimize it without treating direct SQL deletion as a maintenance strategy.

What is the WordPress posts table?

On a default WordPress installation, the table is commonly called:

wp_posts

However, wp_ is only the default database prefix.

A site can use another prefix, such as:

client_posts

abc_posts

site1_posts

In WordPress code, developers should normally refer to the table through:

$wpdb->posts

rather than assuming the prefix is always wp_.

The official WordPress database schema defines the current structure of the posts table.

What does wp_posts contain?

WordPress uses a unified content model.

A blog post and a Page are not stored in completely separate tables.

Instead, both are records inside the posts table and are distinguished primarily by:

post_type

The official WordPress Post Types documentation explains that different post types are stored in the same wp_posts table.

For example:

post_type = post

post_type = page

post_type = attachment

post_type = product

post_type = revision

The last two examples depend on the site’s installed functionality and context. A plugin can register its own custom post type while revisions are managed by WordPress itself.

For a broader explanation of the content model, read WordPress post types vs. custom post types.

The main columns in the WordPress posts table

The current Core schema contains fields including:

• ID;
• post_author;
• post_date;
• post_date_gmt;
• post_content;
• post_title;
• post_excerpt;
• post_status;
• comment_status;
• ping_status;
• post_password;
• post_name;
• post_modified;
• post_modified_gmt;
• post_parent;
• guid;
• menu_order;
• post_type;
• post_mime_type;
• comment_count.

Understanding these fields helps explain why common WordPress queries filter and sort the table in particular ways.

Why the posts table becomes large

Growth is not necessarily a problem.

A site with:

250,000 legitimate products

has a fundamentally different database from a site with:

2,000 useful posts
+
200,000 obsolete revisions

Both may have a large posts table, but only the second obviously contains a major cleanup opportunity.

Before optimizing, identify what the rows represent.

Revisions can significantly increase row count

WordPress revisions are stored as posts with:

post_type = revision

That means revisions contribute directly to the size of the posts table.

For example, imagine:

5,000 published posts
×
20 revisions each
=
100,000 revision rows

before considering the original posts, pages, attachments and other content types.

Revision history can be valuable, so the goal is not automatically to eliminate it.

The goal is to retain an amount appropriate for the site’s editorial workflow.

Read How WordPress post revisions work before changing revision retention.

WordPress keeps unlimited revisions by default

The current wp_revisions_to_keep() documentation states that WordPress keeps an unlimited number of revisions by default when revisions are supported.

The effective number can be controlled through:

WP_POST_REVISIONS

and through revision-related filters.

For example, a site may decide that keeping:

10 revisions per post

provides sufficient editorial recovery without preserving every historical version indefinitely.

Limiting revisions does not necessarily remove existing history immediately

This distinction is important.

Changing the revision limit controls retention behavior as WordPress processes future revisions.

It should not be confused with a guaranteed bulk cleanup of every old revision already present in the database at the moment the setting changes.

Core’s wp_save_post_revision() function evaluates the revisions to retain when a revision is saved and can remove older revisions beyond the configured limit.

Autosaves also live in the WordPress content system

Autosaves protect work while users edit content.

They are closely related to revisions, although they have their own behavior.

Aggressively deleting every record that looks revision-related without understanding autosaves can interfere with content recovery workflows.

Autosave frequency, revision retention and post locking should therefore be treated as separate but related editing controls.

For concurrent editing behavior, see WordPress post locking, explained.

Attachments also use the posts table

An image in the WordPress Media Library is not represented only by a physical JPEG, WebP or AVIF file.

WordPress also creates an attachment record.

That record is stored in the posts table with:

post_type = attachment

Its related information can then be stored elsewhere, particularly in post meta.

Therefore a media-heavy website may have a large number of legitimate wp_posts rows even if it publishes relatively few blog posts.

Custom post types can dominate wp_posts

Many plugins build entire applications on the WordPress post-type system.

A site might contain:

2,000 posts
500 pages
25,000 products
40,000 orders or legacy records
18,000 courses
120,000 revisions

depending on the software architecture and versions involved.

When diagnosing table growth, grouping rows by post_type is therefore more useful than simply asking how many “posts” the site has.

Start optimization with an inventory

Before deleting anything, establish what is actually stored.

A database administrator might inspect counts conceptually like:

post
page
attachment
revision
product
custom post types
other plugin-created types

The objective is to distinguish:

legitimate scale

from:

avoidable accumulation

This principle is covered more broadly in WordPress database bloat, explained.

Do not assume row count alone determines performance

Modern relational databases are designed to work with large tables.

A properly indexed query over hundreds of thousands of rows can outperform a poorly designed query over a much smaller dataset.

Performance depends on factors including:

• query structure;
• indexes;
• selectivity;
• sorting;
• joins;
• database memory;
• storage performance;
• cache behavior;
• concurrent load.

Therefore:

large wp_posts
≠
automatically slow WordPress

WordPress already indexes the posts table

The Core schema does not leave wp_posts completely unindexed.

The current schema includes a primary key on:

ID

and secondary indexes including:

post_name

post_parent

post_author

as well as compound indexes.

The type_status_date index is particularly important

WordPress currently defines an index covering:

post_type
post_status
post_date
ID

This matches an extremely common WordPress pattern:

find published posts
of a particular post type
ordered or filtered by date

For example, a typical archive request might effectively need:

post_type = post
post_status = publish
ORDER BY post_date DESC

The structure of the Core index exists for a reason.

WordPress also has a type_status_author index

Current Core includes another compound index involving:

post_type
post_status
post_author

This can support queries that combine content type, status and author.

The exact indexes should always be checked against the WordPress version actually running on the site rather than copied from an old database diagram found somewhere online.

Do not add database indexes blindly

Adding an index can accelerate some reads.

But an index also has costs.

It consumes storage and must be maintained when records are inserted or changed.

An index that no important query uses can therefore create overhead without solving a real bottleneck.

Before adding a custom index:

• identify a slow real-world query;
• inspect its execution plan;
• check existing indexes;
• measure before and after;
• test writes as well as reads;
• verify compatibility with future WordPress or plugin updates.

Query design often matters more than table size

A common performance problem is not:

too many rows

but:

an expensive query repeated constantly

WordPress’s main query abstraction is:

WP_Query

The official WP_Query documentation exposes numerous parameters that can materially change the amount of database work performed.

Request only the data you need

By default, a normal WP_Query returns full post objects.

If the code needs only IDs, WordPress supports:

'fields' => 'ids'

For example:

$query = new WP_Query(
    array(
        'post_type'      => 'post',
        'posts_per_page' => 100,
        'fields'         => 'ids',
    )
);

If the application genuinely needs only post IDs, requesting complete post rows creates unnecessary work.

Use no_found_rows when pagination totals are unnecessary

A query that displays:

the latest 5 articles

may not need to calculate:

how many total matching articles exist

or:

how many pagination pages exist

In that situation, a query can use:

'no_found_rows' => true

This avoids work associated with calculating the total result count for pagination.

Do not use it when you actually need:

• found_posts;
• max_num_pages;
• accurate pagination totals.

Avoid loading post meta when it will not be used

WP_Query can prime post-meta caches for returned posts.

When a specific query will never use metadata, WordPress supports:

'update_post_meta_cache' => false

The official WP_Query reference documents this option together with other caching parameters.

This is an optimization for specific query patterns, not a setting that should automatically be disabled everywhere.

Avoid loading taxonomy data when it will not be used

Similarly:

'update_post_term_cache' => false

can avoid taxonomy-cache priming when the result will not use terms.

A background process collecting only IDs may benefit.

A template immediately displaying categories and tags probably will not.

Do not disable caching reflexively

WP_Query also supports:

'cache_results' => false

but the WordPress documentation explicitly notes that in general usage you normally do not need to disable result caching.

Optimization means avoiding unnecessary work, not disabling every mechanism that has the word “cache” attached to it.

Meta queries are often the real bottleneck

One of the most common diagnostic mistakes is blaming:

wp_posts

when the expensive work actually involves:

wp_postmeta

A query might start from posts but join post meta to filter on arbitrary custom fields.

For example:

find products
where custom price metadata
is between two values
and another metadata field
equals a specific value

That can be much more expensive than a simple indexed post_type and post_status query.

wp_posts and wp_postmeta solve different problems

Core fields such as:

• post type;
• status;
• date;
• author;
• parent;

exist directly in the posts table.

Arbitrary custom attributes are commonly stored in:

wp_postmeta

This flexible key-value design is convenient but can become expensive when plugins attempt to use metadata as though it were a purpose-built analytical or transactional schema.

Do not move Core fields into post meta

If WordPress already has a native indexed field for a concept, storing another copy in metadata and querying that instead can create avoidable complexity.

For example, do not invent:

custom_post_status = published

when the relevant state can appropriately use:

post_status

Architecture matters long before the database reaches millions of rows.

Be careful with broad searches

Searching text fields across large content sets is fundamentally different from an exact indexed lookup.

Operations involving:

• titles;
• content;
• excerpts;
• multiple joined metadata conditions;
• complex taxonomy combinations;

can require much more work than retrieving a record by ID.

If a large site requires sophisticated search, dedicated search infrastructure may eventually be more appropriate than trying to force every search workload through standard relational queries.

Pagination strategy matters on very large datasets

Deep pagination can become expensive.

A request conceptually equivalent to:

skip hundreds of thousands of rows
then return the next 20

can be less efficient than designs based on known IDs, dates or cursors.

This matters especially for:

• large imports;
• exports;
• batch processing;
• administrative tools;
• background jobs.

Do not automatically process a million-row content set as though it were a ten-page blog archive.

Use bounded batches for maintenance jobs

Large cleanup jobs should generally operate in manageable batches.

For example:

retrieve 500 IDs
↓
process
↓
release memory
↓
continue

is often safer than loading every matching WP_Post object and all related metadata into PHP at once.

The WordPress Trash contributes rows until content is deleted

Moving a post to Trash does not immediately mean its record disappears from the posts table.

WordPress changes its state so the item can be restored.

The official wp_trash_post() documentation describes this behavior.

WordPress’s default Trash retention period is:

30 days

through:

EMPTY_TRASH_DAYS

according to the official wp-config.php documentation.

Emptying Trash is different from limiting revisions

These mechanisms address different rows:

Trash
→ content intentionally deleted but retained temporarily

Revisions
→ historical versions of editable content

Do not combine them into one generic “old posts” cleanup operation.

Use WordPress deletion APIs when relationships matter

Deleting a post is more complicated than:

DELETE FROM wp_posts
WHERE ID = 123;

WordPress may need to clean up:

• post metadata;
• taxonomy relationships;
• comments or related data depending on context;
• child relationships;
• caches;
• plugin hooks;
• attachment-specific resources.

The official wp_delete_post() function performs WordPress-aware deletion behavior.

Direct SQL deletion bypasses that application logic.

Direct SQL can create orphaned data

Suppose somebody runs:

DELETE FROM wp_posts
WHERE post_type = 'revision';

without considering all related data and plugin behavior.

The posts disappear, but related records elsewhere may remain if the appropriate cleanup routines are not invoked.

The database may become smaller in one table while becoming less internally consistent overall.

Back up before destructive cleanup

Before deleting large quantities of database data:

• create a current database backup;
• confirm that it can be restored;
• test the cleanup on staging;
• record what will be deleted;
• measure row counts before and after.

A cleanup script that runs successfully is not evidence that it deleted the correct data.

Database optimization and data cleanup are different operations

This distinction is fundamental.

Deleting obsolete records is:

logical cleanup

Running database-engine maintenance is:

physical/table optimization

They may be performed together, but they solve different problems.

What does OPTIMIZE TABLE actually mean?

Database engines can accumulate internal storage characteristics after large quantities of data are inserted, updated or deleted.

Depending on the database engine and version, an optimization operation can reorganize table storage and perform related maintenance.

It does not rewrite inefficient WordPress PHP.

It does not fix an expensive meta_query.

It does not replace missing application-level caching.

It does not automatically redesign indexes.

WordPress CLI can optimize the database

WP-CLI provides:

wp db optimize

The official wp db optimize documentation states that the command uses the database’s optimization facilities through mysqlcheck.

This can be useful as a maintenance operation when appropriate.

It should not be presented as a universal performance fix.

WordPress also has a database repair interface

WordPress supports:

WP_ALLOW_REPAIR

which exposes database repair and optimization functionality.

However, the official documentation includes an important security warning: when this functionality is enabled, the repair page does not require the normal logged-in state because it is intended to remain accessible when database corruption may prevent authentication.

Therefore it should be enabled only when necessary and disabled afterwards.

Do not leave:

define( 'WP_ALLOW_REPAIR', true );

permanently enabled as a maintenance convenience.

Measure the posts table before optimizing it

Useful baseline measurements include:

• total rows;
• table data size;
• index size;
• rows grouped by post type;
• rows grouped by post status;
• revision count;
• Trash count;
• attachment count;
• old or abandoned custom post types;
• slow queries involving the table.

This turns optimization into diagnosis rather than guesswork.

Look for abandoned custom post types

Plugins can create custom post types and later be removed.

Deactivating or uninstalling a plugin does not guarantee that every content record it created is deleted.

You may therefore discover post types belonging to:

• an old page builder;
• an abandoned event plugin;
• a previous e-commerce system;
• a discontinued forms plugin;
• a migration tool;
• a custom feature no longer used.

Do not delete them merely because the post type is unfamiliar.

First identify their source and dependencies.

Unknown post types are not automatically orphaned data

A temporarily disabled plugin can make its content type appear unfamiliar.

The data may still be required when the plugin is reactivated.

Before deleting records:

identify owner
↓
confirm feature is retired
↓
back up
↓
test cleanup
↓
delete through appropriate mechanism

Post status distribution can reveal unnecessary accumulation

Useful statuses may include:

publish
draft
pending
private
future
trash
inherit

Plugins can also introduce additional statuses.

A site with thousands of legitimate published items is different from one with years of abandoned drafts or expired workflow records.

Do not delete all drafts because they are old

Age alone does not determine whether content is disposable.

Old drafts may contain:

• future campaigns;
• legal material;
• unfinished documentation;
• editorial research;
• scheduled migration content.

Cleanup policies should be based on business rules, not SQL enthusiasm.

Check wp_postmeta alongside wp_posts

A posts-table investigation should almost always include its related metadata table.

It is possible to have:

50,000 wp_posts rows

but:

5,000,000 wp_postmeta rows

because plugins attach many metadata records to each post.

In that situation, shrinking wp_posts by 10% may have little impact on the expensive workload.

Deleting posts can reduce related metadata when done correctly

WordPress-aware deletion routines clean associated data as part of the deletion process.

This is one reason application APIs are preferable to manually removing only one table’s rows.

Object caching can change the performance picture

WordPress caches post objects and query results.

With persistent object caching, frequently requested information can survive across page requests instead of requiring the same database work repeatedly.

A database optimization investigation should therefore include:

• query frequency;
• cache hit behavior;
• persistent object caching;
• invalidations;
• uncacheable query patterns.

The fastest database query is often the one the application does not need to repeat.

Do not confuse page caching with database optimization

Full-page caching can dramatically reduce requests reaching WordPress for anonymous traffic.

But it does not necessarily solve expensive queries in:

• wp-admin;
• logged-in dashboards;
• REST endpoints;
• AJAX requests;
• background jobs;
• uncached personalized pages.

If the administration area is the problem, see Reducing WordPress admin server load.

Admin list tables can expose expensive content queries

Pages such as:

Posts → All Posts

must retrieve, count, filter and sometimes enrich content records.

Plugins that add custom columns can introduce additional queries for every row.

Twenty posts on screen combined with several poorly implemented custom columns can create far more work than the base posts query itself.

Read WordPress admin list tables, explained for the interface layer involved.

Watch for N+1 query patterns

An N+1 pattern occurs when code performs:

1 query to retrieve posts
+
1 additional query for every returned post

For 100 posts, that can become:

101 queries

instead of using efficient cache priming or batched retrieval.

This is one reason WordPress normally primes related caches rather than expecting developers to disable all cache behavior indiscriminately.

Do not optimize a query you have not measured

Useful diagnostic tools can include:

• slow query logs;
• database monitoring;
• application performance monitoring;
• Query Monitor during development;
• WP-CLI;
• database execution plans;
• staging benchmarks.

The objective is to answer:

Which query is slow?

How often does it run?

How many rows does it examine?

Which indexes can it use?

What part of the request triggers it?

A practical optimization workflow

1. Create a backup

Make sure the database can be restored before destructive changes.

2. Measure the table

Record row count and size.

3. Group records by post type

Identify where the volume originates.

4. Group records by status

Look for unusual amounts of Trash, drafts or plugin-specific states.

5. Inspect revisions

Determine whether revision volume matches the editorial needs of the site.

6. Inspect attachments

Do not mistake legitimate Media Library records for unnecessary posts.

7. Identify abandoned post types

Confirm ownership before deleting anything.

8. Profile slow requests

Determine whether wp_posts is actually the bottleneck.

9. Review WP_Query usage

Look for unnecessary full objects, total counts, meta loading or term loading.

10. Review meta queries

Determine whether expensive joins against wp_postmeta are responsible for the observed delay.

11. Clean data through WordPress-aware routines

Avoid deleting isolated rows with unreviewed SQL.

12. Run table maintenance if appropriate

After a large cleanup, database-engine optimization may be reasonable.

13. Measure again

Compare:

• table size;
• row count;
• query duration;
• request duration;
• database CPU;
• admin responsiveness.

If nothing important improved, do not declare success merely because a table contains fewer megabytes.

What not to do when optimizing wp_posts

Do not delete every revision without considering recovery

Revision history exists for a reason.

Do not delete attachment rows because they are not normal posts

They represent Media Library items.

Do not delete unknown custom post types without identifying them

A disabled plugin may still own that content.

Do not add indexes simply because a generic tutorial recommends them

Your WordPress version, database engine and workload matter.

Do not run raw DELETE statements against production without a backup

WordPress data has relationships beyond one table.

Do not assume OPTIMIZE TABLE fixes slow application queries

Storage maintenance and query optimization are different tasks.

Do not disable useful caching because one query seems stale

Fix the actual invalidation or data issue.

Do not optimize only for database size

A smaller database is useful when it removes unnecessary data, but response time and operational reliability are the real outcomes to measure.

How TheOneWP can help manage database growth

TheOneWP includes several modules relevant to the environment around the posts table.

Post Revisions

Post Revisions controls how many WordPress revisions are retained per post.

This can limit future revision growth while preserving a deliberate amount of editorial history.

Database Optimizer

Database Optimizer provides database-cleanup functionality for broader WordPress database maintenance.

Cleanup should still be performed with an understanding of what each data type represents.

Database Manager

Database Manager provides direct visibility into WordPress database tables, their structure and stored records.

This can help inspect the posts table before performing maintenance rather than treating the database as an opaque component.

Autosave Interval

Autosave Interval controls how frequently WordPress automatically saves editor work.

Autosave frequency should be tuned separately from revision retention.

Heartbeat Frequency

Heartbeat Frequency addresses recurring WordPress Heartbeat activity.

This is more directly related to admin request frequency than posts-table size, but both can contribute to the overall performance of large editorial environments.

WordPress posts table optimization checklist

• Confirm the actual database table prefix.
• Back up the database before destructive changes.
• Record current table size and row count.
• Group rows by post_type.
• Group rows by post_status.
• Measure revision volume.
• Review Trash accumulation.
• Identify legitimate attachment records.
• Identify custom post types and their owners.
• Do not delete unknown post types blindly.
• Inspect wp_postmeta alongside wp_posts.
• Profile real slow queries.
• Check existing Core indexes before adding new ones.
• Use fields => 'ids' where only IDs are needed.
• Use no_found_rows when pagination totals are unnecessary.
• Disable meta-cache priming only when metadata is not needed.
• Disable term-cache priming only when taxonomy information is not needed.
• Keep normal WordPress caching where it provides value.
• Look for expensive meta queries.
• Look for N+1 database patterns.
• Process large maintenance workloads in batches.
• Use WordPress deletion APIs where application relationships matter.
• Limit future revision growth if appropriate.
• Test cleanup procedures on staging.
• Consider database-engine optimization after major cleanup.
• Measure performance again after every significant change.

Related WordPress database and content guides

• WordPress database bloat, explained
• How WordPress post revisions work
• WordPress post locking, explained
• Reducing WordPress admin server load
• The WordPress Heartbeat API, explained
• WordPress post types vs. custom post types
• WordPress admin list tables, explained
• Why serialized data breaks naive WordPress migrations

Final thoughts

Optimizing the WordPress posts table is not about making wp_posts as small as possible.

It is about making sure the table contains legitimate data and that WordPress accesses that data efficiently.

A healthy optimization process looks like:

measure
↓
identify row types
↓
profile queries
↓
remove genuinely obsolete data
↓
improve inefficient query patterns
↓
perform appropriate table maintenance
↓
measure again

Revision growth may be the problem on one website.

An abandoned custom post type may be the problem on another.

A third site may have a perfectly reasonable posts table but suffer from expensive post-meta queries or inefficient custom admin columns.

That is why the number of rows alone is not a useful diagnosis.

WordPress already provides a structured posts table, Core indexes and a query API designed around its content model. Work with those mechanisms before attempting to replace them with custom SQL or speculative indexes.

Control revision growth where appropriate, remove genuinely obsolete records through safe workflows, keep related tables in mind and optimize the queries that actually appear in performance traces.

That produces a database that is not merely smaller, but more predictable, maintainable and efficient.