A PrestaShop store in production for five years rarely weighs less than 6 GB in database. Many exceed 12 GB. It’s not the catalogue that’s exploding — a catalogue of 10,000 products with variants, images, and complete SEO fits under a gigabyte. What inflates is the ballast tables: technical tables that PrestaShop continuously feeds and that no native process cleans. After five years, they often represent 70 to 90% of the total DB weight.
This article maps the culprit tables, gives the SQL queries to measure the potential gain, and explains how to put a sustainable retention policy in place without breaking the store. Not a magazine checklist — the version we actually apply in production.
Why a PrestaShop database silently inflates
PrestaShop has no garbage collector. All tables that record events — internal searches, connections, abandoned carts, logs, orphan metadata — grow linearly, sometimes exponentially, without any native mechanism triggering a cleanup.
Concretely, on a store with 2,000 visitors/day:
ps_statssearchreceives 300 to 800 rows per day (each internal search, even empty).ps_connectionsandps_connections_pagerecord each visit and each page view. Count 5,000 to 15,000 rows per day.ps_guestcreates a record for each non-authenticated visitor.ps_cartkeeps all carts — abandoned included — forever. On some stores, we find 90% empty carts dating back to 2018.ps_logcaptures all errors and admin events.
Multiply by 365 days and 5 years: we’re talking about tens of millions of rows for zero business value. The raw weight isn’t the worst problem. The real cost is elsewhere.
The hidden cost of ballast tables
1. Query performance
InnoDB loads indexes into memory (buffer pool). When technical tables of several GB monopolise the buffer, your catalogue, cart, and order queries become slower. The LCP of a product page can take 200 to 400 ms extra solely because of MySQL memory pressure.
2. Backups and restorations
A 12 GB mysqldump dump takes 30 to 60 minutes depending on the disk. Restoration can take 2 to 4 hours. If your host’s automatic backup exceeds a time window, it starts failing silently. Many stores discover they no longer have a valid backup on the day of an incident.
3. Blocked migrations
Migrating a 12 GB store on PHP 8.2 to a new server, or to PrestaShop 9, requires transferring the dump via rsync, restoring, testing. The weight multiplies each operation. “Simple” migrations become multi-day projects.
4. Third-party modules that grow heavier
Some statistics, marketing, or ERP connector modules create their own tables and let them grow indefinitely. ps_netreviews_, ps_advancedstats_, ps_mailchimp_ are frequent suspects. An audit often reveals a 2 GB table left by a module uninstalled two years ago.
Audit: how much does your database really weigh
Before any cleanup, measure. This query lists your database tables sorted by decreasing size:
SELECT
table_name AS 'Table',
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)',
table_rows AS 'Rows'
FROM information_schema.TABLES
WHERE table_schema = DATABASE()
ORDER BY (data_length + index_length) DESC
LIMIT 30;You generally get a ranking that looks like:
ps_statssearch— 1.8 GBps_connections_page— 1.2 GBps_pagenotfound— 800 MBps_connections— 600 MBps_log— 400 MBps_cart— 350 MBps_guest— 300 MBps_cart_rule+ps_cart_cart_rule— 250 MB
Note that ps_product, ps_product_lang, ps_orders rarely appear in the top 10. Actual business data is a minority in an old PrestaShop database.
Ballast table mapping and retention policy
ps_statssearch — internal searches
This table records each word entered in the search bar. Keeping history beyond 90 days has no analytical interest: search trends from 2019 illuminate nothing in 2026. Recommended policy: 90-day retention.
-- Audit
SELECT COUNT(*) AS total, MIN(date_add) AS oldest
FROM ps_statssearch;
-- Deletion beyond 90 days
DELETE FROM ps_statssearch
WHERE date_add < DATE_SUB(NOW(), INTERVAL 90 DAY);ps_connections and ps_connections_page — visit history
If you use GA4 or Matomo, these tables are redundant. PrestaShop fills them for its own statistics module that no one consults. Policy: 30-day retention, or 0 if you have an external tool.
ps_pagenotfound — 404s
Useful to identify broken links and schedule 301 redirects. But after processing, the history no longer serves. Policy: 60-day retention, after extraction of recurring 404s.
ps_cart — abandoned carts
Subtle. Recent carts serve for retargeting and reminders. Beyond 90 days, an abandoned cart is statistically lost. Policy: keep 90 days, but don’t touch carts linked to orders (joined table ps_orders.id_cart).
-- Safe deletion: only carts WITHOUT associated order
DELETE c FROM ps_cart c
LEFT JOIN ps_orders o ON o.id_cart = c.id_cart
WHERE o.id_cart IS NULL
AND c.date_add < DATE_SUB(NOW(), INTERVAL 90 DAY);ps_guest — non-authenticated visitors
Linked to ps_customer and ps_connections. Cleaning with caution: a guest converted to customer must be preserved. Policy: delete guests without customer and without recent connection.
ps_log — admin logs
Useful for recent debugging, no value past a month. Policy: 30-day retention.
Orphan metadata
When you delete a product, some linked tables keep orphan rows: ps_image, ps_feature_product, ps_specific_price, ps_product_attachment. Same for deleted categories, manufacturers, suppliers. These rows serve no purpose and skew joins.
-- Example: orphan ps_image
SELECT i.id_image FROM ps_image i
LEFT JOIN ps_product p ON p.id_product = i.id_product
WHERE p.id_product IS NULL;The DELETE trap in production
Deleting a million rows in a single query on an InnoDB table locked by your admin and your front is a guaranteed crash. The binary log explodes, replication breaks, the server can go into swap.
The absolute rule: delete in batches of 5,000 to 10,000 rows maximum, with a 100 ms pause between each batch.
-- Batched deletion loop (pseudo-code)
DO WHILE rows_affected > 0:
DELETE FROM ps_statssearch
WHERE date_add < DATE_SUB(NOW(), INTERVAL 90 DAY)
LIMIT 5000;
SLEEP 0.1;
END DO;And systematically: dry-run before execute. You want to know how many rows will disappear and how much space you recover before clicking.
Automating retention: the correct strategy
Doing the cleanup manually once and forgetting it serves no purpose — the database will reinflate. Retention must be continuous and automatic:
- Daily cron that executes the retention policy of each table.
- Security token so that the cron isn’t triggerable from outside.
- Execution logs to track what was deleted.
- Notification in case of failure or anomaly (abnormal deletion volume).
- OPTIMIZE TABLE weekly on cleaned tables to recover disk space (otherwise InnoDB keeps the allocated space).
Our dfcleanup module: the packaged method
Implementing this strategy by hand requires two to three days of dev and as much testing. Our dfcleanup module for PrestaShop 8 and 9 industrialises the entire method described here:
- Six specialised cleaners: searches, carts, logs, statistics, orphan metadata, orphan images. Each with its own configurable retention.
- Three modes: Audit (read-only), Dry-run (traced simulation), Execute (batched deletion of 5,000 rows).
- Gain in MB calculated before action, per cleaner and total.
- Cron task secured by token, ready to use.
- Detailed logs and PrestaShop 8 and 9 compatibility.
For €19, you avoid manual DELETE queries and install a sustainable retention policy. Compared to dev time and the cost of a DBA incident, it’s one of the catalogue’s modules with the best ROI.
FAQ
Should I do an OPTIMIZE TABLE after each DELETE?
No, not after each DELETE — it’s I/O costly and locks the table. Once a week or per month, in off-peak hours, on tables that have undergone massive cleanup. OPTIMIZE TABLE recovers disk space that InnoDB doesn’t naturally free after deletion.
Can cleaning affect SEO or statistics?
No SEO impact: cleaned tables are technical (internal searches, connections, logs), not the catalogue or URL. On the stats side, if you use GA4 or Matomo, your data is stored with them — the PrestaShop DB is redundant. If you use native PS statistics, configure a longer retention (180 days for example).
What retention for abandoned carts?
90 days is largely sufficient. Cart reminder tools (mail, retargeting) trigger within 24 to 72 hours. Beyond 90 days, recovery probability is statistically zero. And you never delete a cart converted to an order — the ps_orders.id_cart join protects this data.
How much gain to expect concretely?
On a 5-year-old store, 2,000 visitors/day, we measure on average 60 to 80% DB reduction on the first pass. A 12 GB store goes back down to 3 or 4 GB. Subsequent passes stabilise the database at a level close to “real business weight”.
Is the module multi-store compatible?
Yes. The cleaners respect the multi-shop scope when relevant (carts, searches per store). Global tables (logs, connections) are cleaned globally.
To go further
Database debt is one of the three main sources of long-term performance degradation of a PrestaShop store, along with obsolete third-party modules and poorly optimised images. See also our Core Web Vitals 2026 checklist for the rest of the picture, and the PrestaShop 9 vs 8 guide if you’re preparing a migration: lightening the DB before migration can divide switchover time by 5.