Database cleanup: why your PrestaShop store gets slower over time

Here is a frustration we hear constantly: your PrestaShop store was quick when you launched it, and a year later the same pages crawl. Same hosting, same theme, same modules. Nothing you can point to changed. So what happened? In a large share of these cases the culprit is the one thing that did change, silently, every single day you were open for business: your database. PrestaShop logs a remarkable amount of activity by default, and the tables that held a few hundred rows at launch now hold millions. Queries that were instant against a small table become a measurable drag against a huge one, and because your product pages, category pages and back office all touch those tables, the slowdown spreads everywhere at once.

This guide is specifically about database bloat that accumulates over time. Which tables grow, why they grow, exactly what is safe to clean, and how to clean it without breaking your store. It is not a general "why is PrestaShop slow" piece (modules, hosting and front-end weight are separate causes. We cover those in what actually makes PrestaShop slow), and it is not about caching your way around slow queries (Redis and full-page cache have their own posts). This is about the rot that builds up inside the tables themselves.

Reviewed June 2026. The bloat tables and safe-cleanup policy below hold across PrestaShop 1.7, 8.x and 9.x. Exactly which stats tables exist varies by version and installed modules, so the audit query comes first, always measure your own schema before targeting anything.

Why PrestaShop databases grow silently

PrestaShop ships with a stats and logging system that records far more than most merchants realise. Connections, guest sessions, internal searches, 404s, sent-mail logs, abandoned carts, when the relevant stats and logging features are enabled, all of it lands in the database, and retention is left almost entirely to you. PrestaShop has no built-in "keep 90 days" policy, and pruning depends on the modules and configuration in play, so for most stores the data simply accumulates.

For a modest store doing 500 visitors a day, the arithmetic is unforgiving:

• ps_connections, one row per visitor connection. 500/day is ~15,000/month, ~182,000/year, and it never stops.
• ps_connections_source, referrer/HTTP-referer tracking for each connection; frequently larger than ps_connections itself.
• ps_guest, one row per unique guest (browser/OS/screen fingerprint), accumulating for every non-logged-in visitor.
• ps_log, the application log. A single chatty module throwing notices can write several rows per page load and push this into the millions.
• ps_pagenotfound, every 404, including the relentless bot traffic probing for /wp-admin, /phpmyadmin and the like.
• ps_statssearch, every internal search query a visitor types.
• ps_cart and ps_cart_product, every cart ever created, the overwhelming majority of which never became an order.
• ps_mail. A log of every email PrestaShop has sent.
• the native stats tables (ps_statssearch, ps_searchengine, and on some installs ps_referrer/ps_referrer_cache), the analytics layer that almost nobody actually reads inside PrestaShop because they use Google Analytics instead. Exactly which of these tables exist depends on your PrestaShop version, which stats modules are installed, and the upgrade history of the database, so audit the actual schema before targeting any of them rather than assuming a fixed list.

The "so what" is simple: after two or three years these tables routinely hold millions of rows, MySQL's working set no longer fits comfortably in memory, the InnoDB buffer pool thrashes, and even well-indexed queries get slower because the engine is reading more pages off disk. Your store didn't get heavier in any way a visitor can see. It got heavy where only the database can feel it.

Find out what is actually bloated on your store first

SELECT table_name, table_rows, ROUND((data_length + index_length) / 1024 / 1024, 1) AS mb
FROM information_schema.tables
WHERE table_schema = DATABASE()
  AND table_name LIKE 'ps\_%'
ORDER BY (data_length + index_length) DESC
LIMIT 20;

Don't clean blindly. Every store bloats differently depending on traffic mix and which modules are installed, so the first move is to measure table sizes. Run this in phpMyAdmin (or Adminer, or the MySQL CLI), swapping your real database name in, it lists your biggest tables by row count and on-disk size:

• SELECT table_name, table_rows, ROUND((data_length + index_length)/1024/1024, 1) AS size_mb FROM information_schema.tables WHERE table_schema = 'your_db_name' ORDER BY (data_length + index_length) DESC LIMIT 25;

Read the result before touching anything. If your top tables are ps_orders, ps_order_detail and ps_product_lang, that's healthy bloat. Real business data you must keep. If the top of the list is ps_connections, ps_guest, ps_log, ps_statssearch or ps_pagenotfound, you've found junk worth gigabytes. This one query tells you whether the rest of this article applies to you at all, and which tables to target.

Which tables to clean, and which to never touch

This is where guides get dangerous, so be precise. The tables fall into three groups.

The cart table needs a scalpel, not a hammer

The mistake we see most is truncating ps_cart entirely to reclaim space. Don't. A cart row with no matching id_cart in ps_orders is an abandoned cart and is safe to remove once it's old; a cart that does link to an order is part of that order's history. The safe pattern identifies only orphan carts older than a window, then deletes their child rows in ps_cart_product first and the carts themselves second, so you don't strand orphaned references. The reliable way to do this is to build a list of candidate id_cart values up front, then delete by that list. Which also lets you batch cleanly. Note that MySQL does not allow LIMIT on a multi-table (JOIN) DELETE, so we avoid the JOIN form on the delete itself entirely:

• CREATE TEMPORARY TABLE tmp_orphan_carts AS SELECT c.id_cart FROM ps_cart c LEFT JOIN ps_orders o ON o.id_cart = c.id_cart WHERE o.id_order IS NULL AND c.date_add < DATE_SUB(NOW(), INTERVAL 60 DAY);
• DELETE FROM ps_cart_product WHERE id_cart IN (SELECT id_cart FROM tmp_orphan_carts) LIMIT 20000;, repeat until it reports 0 rows affected.
• DELETE FROM ps_cart WHERE id_cart IN (SELECT id_cart FROM tmp_orphan_carts) LIMIT 20000;, again, repeat until 0 rows.
• DROP TEMPORARY TABLE tmp_orphan_carts;

Each DELETE now targets a single table with no join, so the LIMIT 20000 is valid and you can re-run each statement until it clears. Always finish the child deletes in ps_cart_product before deleting the parent rows in ps_cart, and that batching is deliberate, the next section explains why it matters more than it looks.

How to clean safely without taking the store down

Step 1, back up the database, no exceptions

Before any DELETE or TRUNCATE, take a full data backup (mysqldump or your host's snapshot). PrestaShop's own Advanced Parameters → Database → DB Backup tool exists, but for large tables a server-side mysqldump is more reliable. If a cleanup goes wrong, restore is your only safety net, treat it as mandatory, not optional.

Step 2, delete in batches, not in one giant query

DELETE FROM ps_connections
WHERE date_add < DATE_SUB(NOW(), INTERVAL 180 DAY)
LIMIT 5000;

A single DELETE that removes two million rows takes a long lock and can make your storefront unresponsive while it runs. Work in batches of 10,000–50,000 rows (the LIMIT in the cart queries above), with a short pause between runs, and repeat until the table is clean. When you want a retention window rather than an empty table (keep the last 90 days, drop the rest), use a date-filtered DELETE such as DELETE FROM ps_connections WHERE date_add < DATE_SUB(NOW(), INTERVAL 90 DAY) LIMIT 50000;, repeated until it reports 0 rows. Only when you genuinely want everything gone from a disposable log table should you reach for TRUNCATE TABLE ps_connections;, it is far faster because it drops and recreates the table instead of removing rows one at a time, but it cannot be filtered by date and it resets auto-increment, so reserve it for emptying whole tables, and do it in a dependency-safe order if related tables reference each other.

Step 3, reclaim the disk space with OPTIMIZE TABLE

Here is the part merchants miss. PrestaShop uses InnoDB, and after a big DELETE the row count drops but the file on disk does not shrink, the freed space stays reserved as internal fragmentation, and queries can still read those empty pages. You have to explicitly rebuild the table:

• OPTIMIZE TABLE ps_connections, ps_connections_source, ps_guest, ps_log, ps_pagenotfound, ps_statssearch;

On InnoDB, OPTIMIZE TABLE rebuilds the table and re-analyses its indexes, which defragments the data. Whether it actually returns space to the operating system depends on your setup: with innodb_file_per_table enabled, the default on modern MySQL/MariaDB, each table has its own tablespace and the freed space is released to the filesystem; on a shared system tablespace it is not. The rebuild can also lock or copy the table depending on your MySQL/MariaDB version and configuration, so run it after the deletes, in a maintenance or low-traffic window, and verify the behaviour on your specific server version before relying on the space being reclaimed.

Step 4. Clear PrestaShop's cache only if you need to

Deleting rows from log and stats tables is a data cleanup, not a code or schema change, so it does not normally require a cache flush. PrestaShop is not caching those raw log rows. Clear the cache only if your cleanup touched data that something currently caches (for example dashboard stats that feed a cached widget), or after the kind of code, module or schema changes that genuinely invalidate compiled output. When you do need it, use Advanced Parameters → Performance → Clear cache in the back office (or delete the contents of var/cache/); on versions that maintain a compiled class index, that rebuild happens as part of the same clear.

Doing it from the back office instead of raw SQL

The status list checks PHP version, modules, database tables, fragmentation, cleanable rows and cache size, each marked not checked yet.

Configuration belongs in the module, not in ad hoc SQL kept on someone's laptop.

Raw SQL is precise but unforgiving, one missing JOIN and you orphan an order. If you'd rather not write DELETE statements against a live shop, you have two safer in-PrestaShop routes:

• SQL Manager (built in): Advanced Parameters → Database → SQL Manager lets you save and re-run the size-audit query above and any read-only checks. It's primarily a SELECT tool, but it's the right place to measure before and after, and to keep your audit query handy month to month.
• A maintenance module: a dedicated cleanup module handles the orphan-cart logic, the batch deletes and the OPTIMIZE step from a back-office screen with checkboxes, no SQL, with the destructive operations gated behind a confirmation. This is the route we'd point a non-developer store owner to, precisely because it removes the chance of fat-fingering a DELETE on ps_orders.

Whichever you choose, the policy is the same as the SQL above, the tool just changes how safely you execute it.

Make it recurring, not a once-a-year panic

Bloat is continuous, so cleanup should be too. A store that gets cleaned once and then ignored is back to the same row counts within months. Set a standing monthly routine:

• Skim ps_log and ps_pagenotfound for anything real (recurring errors, genuinely broken links), then truncate entries older than 30 days.
• Delete orphan carts older than 60 days (the two cart queries above), children in ps_cart_product first, then the carts.
• Delete ps_connections, ps_connections_source and ps_guest rows older than 90 days with a date-filtered DELETE (watch the ps_guest → ps_customer FK), or TRUNCATE them outright if you keep no retention window at all.
• Trim ps_statssearch and ps_mail to ~90 days with the same date-filtered DELETE pattern.
• Run OPTIMIZE TABLE on whatever you cleaned.

The whole sequence can live in a SQL script fired by a monthly cron job on your server, or run by a maintenance module on a schedule. Automating it is the difference between a store that stays fast and one that quietly slides back into 4-second page loads while you're busy running the business.

One layer deeper: indexes and the queries themselves

Pruning rows is the highest-leverage move because it shrinks every table scan at once, but two related issues are worth a mention so you know where the boundary is:

• Missing indexes from third-party modules. Plenty of modules create their own tables without indexing the columns they filter on. Enable MySQL's slow query log, watch for queries hitting a module table with a full scan, and add an index on the WHERE/JOIN column. A clean table with the right index can turn a 2-second query into a few milliseconds.
• Server-level MySQL tuning (innodb_buffer_pool_size and friends) and broader query optimisation are a different discipline, that's hosting and configuration territory, covered in performance tuning from database queries to full-page cache and choosing hosting for PrestaShop. Cleaning the data first means you're tuning a lean database instead of an overgrown one, so do this step before you go buy a bigger server.

Measure the before and after, properly

Don't rely on "feels faster." Before you clean, record your TTFB (Time to First Byte) on a consistent set of pages, homepage, one product page, one category page, and the back-office dashboard (the admin is often where bloat hurts most, because the stats tables feed those dashboard widgets). Note your top table sizes from the audit query too. After the cleanup and OPTIMIZE, re-measure the same pages and the same tables.

We won't hand you a guaranteed percentage. The gain depends entirely on how bloated you were to begin with. A store whose ps_connections held three years of logs will see a far bigger jump than one that was already tidy. If your diagnostics aren't pointing clearly at the database, start with a structured slowness check rather than guessing: is your store slow. How to check and what to do. And if you want to see what a fully optimised PrestaShop looks like end to end, we documented one at PageSpeed 99/100 on a real store with 130 modules.

The principle holds whatever route you take: PrestaShop will happily log itself into the ground if you let it, because nothing prunes those tables for you. Find your real bloat, delete only what's genuinely disposable, reclaim the space with OPTIMIZE, and put it on a schedule. Do that and your store keeps the speed it had on launch day, years into operation, without buying a single thing.

Frequently asked questions

Is it safe to truncate ps_connections and ps_guest?

Truncating ps_connections and its companions (ps_connections_source, ps_connections_page) is safe if you don't use PrestaShop's native stats, those tables feed analytics most merchants replace with Google Analytics anyway. ps_guest needs slightly more care because of its foreign-key link to ps_customer; prefer a date-filtered DELETE that keeps recent rows over a blind TRUNCATE. What you must never truncate is ps_cart. Abandoned carts are mixed in with carts that belong to real orders, so that one needs the orphan-only scalpel shown above, not a hammer.

I deleted millions of rows but the disk didn't shrink. Why?

InnoDB doesn't return freed space to the operating system after a DELETE, the row count drops but the file on disk stays the same size, with the freed pages held as internal fragmentation. You have to rebuild the table with OPTIMIZE TABLE to reclaim it, and even then it only releases space to the filesystem if innodb_file_per_table is on (the default on modern MySQL/MariaDB). Run OPTIMIZE after the deletes, in a low-traffic window, since it can lock or copy the table depending on your version.

Do I need to clear the PrestaShop cache after cleaning the database?

Usually no. Deleting rows from log and stats tables is a data cleanup, not a code or schema change, and PrestaShop isn't caching those raw rows. Clear the cache only if your cleanup touched data that something currently caches, dashboard stats feeding a cached widget, say, or after genuine code/module/schema changes. Routine log and cart pruning doesn't require it.

Why delete in batches instead of one query?

A single DELETE that removes two million rows takes a long lock and can freeze your storefront while it runs. Working in batches of 10,000–50,000 rows with a short pause between them keeps each lock short and the store responsive. Use a date-filtered DELETE with a LIMIT, repeated until it reports zero rows. TRUNCATE is the exception, it's near-instant because it drops and recreates the table, but it can't be filtered by date, so reserve it for emptying a whole disposable log table.

Won't this just bloat again?

Yes, that's the whole point of making it recurring. PrestaShop has no built-in retention policy, so a store cleaned once is back to the same row counts within months. Put the sequence (prune the log/stats/cart tables, then OPTIMIZE) on a monthly cron, or let a maintenance module run it on a schedule, so the tables never get the chance to grow back. Automating it is the difference between staying fast and quietly sliding back to 4-second page loads.

Related reading

• Database Cleanup. Runs the orphan-cart logic, batch deletes and OPTIMIZE from the back office on a schedule, with row counts shown before anything is deleted
• Performance Revolution, when the cleaned database is still TTFB-bound, the full-page cache and critical-CSS layer on top
• Performance tuning: database queries to full-page cache, indexes, EXPLAIN and InnoDB tuning for the lean database you've just created
• What actually makes PrestaShop slow. Confirm bloat is your cause before you start deleting