PrestaShop database maintenance: inspect, retain and clean safely
Inspect growing PrestaShop tables, set retention rules and clean old data with shop scope, dependency checks, backups and bounded batches.
Keep your PrestaShop database manageable by measuring growth and removing only data whose retention period has ended. Protect orders, active carts and shared shop data throughout the cleanup.
What we would do
We would first separate storage growth from a measured performance problem, then agree which records have finished serving a purpose. The right cleanup depends on your existing shop: its modules, payment and ERP integrations, theme customisations, saved carts, uploaded files, multistore sharing and data history. Your markets and team determine who can approve retention and who can recover a failed run.
Rehearse the chosen procedure on a private staging copy with the same schema, theme and modules, with customer emails, live payment actions and outbound integrations disabled. Review the preview and recovery evidence before scheduling the live run in an agreed maintenance window. Keep destructive work bounded and recoverable through a tested backup; deletion itself is not reversible. Introduce automatic execution only after a supervised run passes.
If you need help establishing whether database work will address the slowdown, the PrestaShop Speed & Core Web Vitals Audit provides measured findings and a ranked fix plan; implementation is scoped separately.
Know the tables before changing data
Your installation uses a configurable database prefix. The examples use ps_; replace it with the prefix in your installation before running a query. Inspect your actual schema because installed modules and upgrade history change the available tables.
| Area | Tables and purpose |
|---|---|
| Products | product holds shared product data such as the reference. product_shop stores shop-specific values such as price, visibility and active status. product_lang holds names, descriptions, metadata and URL slugs by product, shop and language. |
| Variants and prices | product_attribute and its related tables describe combinations. specific_price stores specific pricing rules. Use stock_available for available quantity, with the correct shop or shared shop-group scope. |
| Categories | category includes tree fields such as nleft, nright and level_depth. category_lang stores translated fields; category_product stores product membership and position. |
| Sales | orders holds the order header. order_detail holds lines; order_history tracks status changes. order_state and order_state_lang define statuses. |
| People and carts | customer, address, cart and cart_product contain related business and personal data. A cart can already belong to an order. |
| Configuration | configuration stores settings with possible global, group or shop scope. hook_module associates modules with hooks and shops. shop_url maps hosts and paths to shops. |
Use supported back-office forms or application APIs for changes to these entities. Direct edits can skip hooks, files, associations and cache invalidation. Uninstall unwanted modules through their supported procedure instead of dropping their tables by hand.
Read-only examples
This product query selects shop 1 and language 1 explicitly. Change both IDs to the context you need. Stock is deliberately separate because shared quantities require shop-group-aware selection.
SELECT p.id_product, p.reference, pl.name, ps.active, ps.price
FROM ps_product p
JOIN ps_product_shop ps ON ps.id_product = p.id_product
JOIN ps_product_lang pl ON pl.id_product = p.id_product
AND pl.id_shop = ps.id_shop
WHERE ps.id_shop = 1 AND pl.id_lang = 1
ORDER BY p.id_product DESC
LIMIT 20;SELECT id_order, reference, id_customer, total_paid_tax_incl,
current_state, date_add
FROM ps_orders
WHERE id_shop = 1
ORDER BY id_order DESC
LIMIT 20;Measure table growth and query cost
Connections, referral records, search history, mail logs, application logs and carts can grow when the corresponding features are active. Growth can enlarge backups and put pressure on database memory. A large table is a reason to investigate, not proof that it causes slow storefront pages.
Run this audit in a database client such as the MySQL client, Adminer or phpMyAdmin, connected to the shop database:
SELECT table_name, table_rows,
ROUND((data_length + index_length) / 1024 / 1024, 1) AS size_mb
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY data_length + index_length DESC
LIMIT 25;InnoDB row counts are estimates. Compare snapshots over time and inspect the queries that use growing tables. The built-in SQL Manager validates shop-table SELECT queries; use a database client for this information_schema audit.
Choose your next action from the evidence
| What you find | Next action | Evidence needed |
|---|---|---|
| Orders, order lines or catalogue translations dominate. | Keep required business records and investigate the queries using them. | A large catalogue or order history alone does not establish disposable data. |
| Visit or search history dominates. | Apply the retention review before choosing a cleaner. | The feature owner confirms which reports, shops and dependencies still need those rows. |
| Application logs grow again soon after cleanup. | Fix the repeated error in the owning module or integration. | Read a sample of messages and correlate their timing with requests or background jobs. Preserve evidence before pruning. |
| The database is manageable but pages remain slow. | Trace the request before deleting more data. | Combine query timings with a request trace and browser measurements to locate the remaining delay. |
Record table sizes, database load and response times, including time to first byte (TTFB), for the same homepage, product, category and back-office screens before and after maintenance. Keep cache state and test conditions comparable. Use the slow query log and EXPLAIN when a query remains slow; assess an index against its filters, joins and existing indexes before adding one. See PrestaShop performance tuning for wider diagnosis.
Set a retention policy for each data category
Choose retention periods from your operational needs and applicable law. Specify who owns the policy, which shops it covers and when it is reviewed. The GDPR storage-limitation principle requires personal data to be kept in identifiable form no longer than necessary for its purpose, subject to applicable retention duties.
| Data | Checks before cleanup |
|---|---|
connections, connections_source, connections_page | Decide which analytics you still need. Select parent connections by scope and age, account for related source and page rows, then remove eligible dependencies and parents together. |
guest | Visitor-tracking rows in this table have no standard creation or update date. Guest-checkout accounts are customer rows marked is_guest, with account timestamps. Preserve customer, cart and connection references. Keep a visitor row whose age or ownership cannot be determined. |
log, pagenotfound | Review recurring application errors and broken links first. Keep records needed for diagnosis, security or support, then apply a justified cutoff. |
statssearch | Keep the search history you need for merchandising, subject to privacy and retention rules. Check whether the table and its owning module exist. |
mail | Choose a support and delivery-diagnosis window. Mail-log retention does not define retention in your mail provider. |
| Carts | Use last activity and dependency checks. Old creation dates alone do not prove abandonment. |
| Session records | Use the version's expiry handling. Preserve active customer and employee sessions. |
| Orders, customers and catalogue | Use a separate business-data policy and supported deletion or anonymisation workflow. Account for accounting duties, warranties, disputes and relations. |
search_engine contains search-engine definitions, not accumulating search history. Keep it out of analytics cleanup lists. PrestaShop has individual cleanup mechanisms, including configurable cleanup of connection-page details through PS_STATS_OLD_CONNECT_AUTO_CLEAN; it has no single policy covering every table above.
Handle carts and guests with dependency checks
Carts
- Select a small candidate batch within the intended shop scope. Require the cart's last update to precede your cutoff and exclude every cart linked from an order. For example, a cart created before the cutoff but updated afterwards stays out of the batch, even when it has no order.
- Check payment activity, saved-cart features, customisations and module-owned references. Keep carts whose continued use is unclear.
- Recheck the cart's age, shop, activity and order absence immediately before deleting it. A list made earlier can become stale while checkout continues.
- Use a dependency-aware application workflow. Inspect the installed core and any override before using
Cart::delete()for maintenance. The native method refuses an order-linked cart and attempts to remove cart products, cart-rule associations, customisations, customised data and uploaded customisation files. It is not an atomic cleanup: some customisation query and file-deletion results are not checked, and earlier steps are not rolled back if a later step fails. Check return values, errors, related rows and files after each batch. - Ensure module data is handled too. The native method does not prove that every third-party reference is covered. Prevent a concurrent checkout from changing eligibility between the check and the delete; use a controlled maintenance window when your tooling cannot provide that protection.
Do not truncate cart tables or use a recipe that deletes only cart_product and cart. Verify core and module relations after a trial batch, and stop on partial failure.
Rehearse a shop with personalised products
On staging, keep an order-linked cart and an active saved cart containing an uploaded personalisation file as protected examples. Include an eligible abandoned cart with its own upload in the trial batch. Afterwards, open the retained order, restore the saved cart through your theme and confirm its personalisation still loads. Check that only the eligible cart's related rows and files were removed. A lower cart count alone would miss a broken upload or a saved-cart module reference.
Guests
The standard guest visitor table has no date_add field. Establish a visitor row's ownership and age through related activity or a retained observation record. Delete only demonstrably old, unreferenced visitor rows. Preserve records connected to customers, carts or connections, including references from other shops.
Run a bounded, recoverable cleanup
- Take a complete database backup and test the restore procedure. Include files if the cleanup can remove uploads. Store backups securely outside the public web directory. Record the recovery steps and the person authorised to stop the run; use the backup and recovery guide to plan the restore test.
- Rehearse on a staging copy. Inspect schema, dependencies, shop ownership and the exact candidate count with read-only queries. Save the preview and configuration so the production run can use the reviewed policy against freshly checked candidates.
- Choose a justified cutoff, batch size, maximum row count and maximum run time. Begin with a small batch and measure its duration, lock waits and server load.
- Revalidate candidates before every destructive batch. Delete related records in a documented order. Stop when a dependency, scope or eligibility check fails.
- Log affected counts and reasons for skipped rows. Check storefront navigation, cart, checkout and back-office screens after the run. Confirm order history and personalisation still work, and inspect module logs for missing references before starting another batch.
A date-filtered DELETE can retain recent records. A single-table delete can use LIMIT; MySQL's multiple-table delete syntax cannot. A limit alone does not make an operation safe: the predicate, ownership and dependencies still matter.
TRUNCATE empties the entire table, cannot apply a date filter and resets its auto-increment counter. Use it only when the complete table contents are eligible and related tables have been addressed. Do not treat it as an instantly reversible substitute for a bounded delete.
If the live run fails, stop its trigger before investigating partial changes. Restoring an older database over a trading shop can erase newer orders and customer updates. Agree how to preserve and reconcile activity since the backup before choosing a full restore.
Choose a cleanup tool by scope and execution evidence
A back-office preview can make the policy easier to review, but the installed release still needs to pass your dependency and recovery checks. Compare the work you need before installing overlapping cleaners.
| Need | Route to assess | Check on your copy |
|---|---|---|
| Retention-based database cleanup with selected shop scope. | Database Cleanup. A saved-plan workspace and CLI apply cleanup policies; the product listing describes per-area retention, row previews, simulation and manual approval. | The preview excludes protected orders, customer accounts, live products and order-linked carts, and includes only the selected shops. |
| Database records plus orphaned images, unused files or module leftovers. | Cleanup Revolution. Its listing includes previews, a row ceiling, run history and recoverable image trash. | File ownership is established before removal. Image recovery does not replace a database backup. Accounts without orders require separate double confirmation. |
The product listings offer scheduling, but check whether live cron execution is enabled and supported in your installed release. A saved plan, a schedule and a working execution trigger are separate things. Demonstrate on staging that the supported trigger respects the selected shops and limits, records success and failure, and prevents overlapping cleanup runs before relying on unattended execution.
If you already have a supported cleanup task and need visibility over its starts and failures, assess Cron Manager. Confirm that the cleaner exposes a compatible task first; a scheduler does not supply a missing cleanup execution path.
Reclaim space only when needed and review regularly
Deleted InnoDB space can be reused by later writes without shrinking the data file. A rebuild is optional. Consider OPTIMIZE TABLE after substantial deletion only when reclaimable space or measured query behaviour justifies it.
For InnoDB, OPTIMIZE TABLE normally rebuilds the table, but settings such as MySQL's innodb_optimize_fulltext_only can change that behaviour. Returning space to the operating system depends on the tablespace arrangement. Check temporary disk requirements, locks, full-text indexes, replication effects and your exact database version before scheduling it. The MySQL OPTIMIZE TABLE documentation describes these conditions.
Routine log cleanup normally needs no PrestaShop cache flush. Clear affected caches when you changed data that is actually cached, or when the owning feature requires it.
Set a recurring review: inspect errors and 404s, compare table growth, preview eligible records, run approved batches and record the result. Automate only a procedure that preserves the same scope, backup, dependency and execution limits. Use a recurring operational review; do not assume your cleanup tool executes saved schedules automatically.
Recheck the queries and response times after maintenance. If the bottleneck persists, investigate module queries, indexes, database memory and hosting resources before changing more data. Cleanup has no guaranteed speed gain.
What to ask your developer or agency
- Which schema and module releases did you inspect, and which custom or shared-shop references change eligibility?
- Who approved each retention rule, and can you show the preview and skipped records from the rehearsal?
- How does the procedure prevent a checkout or background job from changing a cart while it is being removed?
- Where are the restore evidence, partial-failure checks and plan for orders accepted after the backup?
- What starts an automatic run, where is its result recorded and how will the responsible person learn that it failed?
Questions owners ask
Will switching off statistics remove the history already collected?
Do not assume so. Check what the owning module actually stops collecting and what its uninstall procedure removes. Existing history still needs a retention decision and a supported cleanup route; check the growth snapshots afterwards to see whether collection has stopped.
Can I run the rebuild when the disk is nearly full?
Ask your host to establish the temporary space and recovery capacity first. A smaller candidate count is not proof that a table rebuild will fit. Postpone the rebuild if there is no demonstrated headroom, and address capacity before the database exhausts its writable space.
Can I keep cleanup backups indefinitely?
Give backups their own retention and access rules because they can still contain removed personal data. If you restore one, reapply relevant deletion or anonymisation decisions before returning it to use. Keep the record of those decisions separately from the data being removed.
Is an empty run history proof that there was nothing to clean?
No. Distinguish a successful run with no eligible rows from a trigger that never started, a skipped task or an error before logging completed. Check the scheduler and cleaner records together before deciding that the routine works.