Analyzing the `filesystem` Table

If the filesystem table in your Universal Messenger database has grown very large (e.g. millions of rows), you can use the following SQL query to break down rows by path prefix. This helps identify which directories contain the most entries, so you can decide where cleanup may be needed.

The Query

The query groups all rows by the first 4 segments of the path column (e.g. cmsbs-work/data/mailingtemplates/12345), counts the number of entries per group, calculates a percentage, and sums up the file sizes.

You can adjust the depth (number of path segments) and the minimum count threshold in the HAVING clause.

PostgreSQL

SELECT
    array_to_string((string_to_array(path, '/'))[1:4], '/') AS prefix,
    COUNT(*)                                                 AS count,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 1)       AS pct,
    pg_size_pretty(SUM(datalen))                             AS size
FROM filesystem
GROUP BY 1
HAVING COUNT(*) >= 10
ORDER BY count DESC;

MySQL

SELECT
    SUBSTRING_INDEX(path, '/', 4)                            AS prefix,
    COUNT(*)                                                 AS count,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 1)       AS pct,
    CONCAT(ROUND(SUM(datalen) / 1024 / 1024, 1), ' MB')     AS size
FROM filesystem
GROUP BY 1
HAVING COUNT(*) >= 10
ORDER BY count DESC;

Oracle

SELECT
    REGEXP_SUBSTR(path, '^([^/]+/){0,3}[^/]+')              AS prefix,
    COUNT(*)                                                 AS cnt,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 1)       AS pct,
    ROUND(SUM(datalen) / 1024 / 1024, 1) || ' MB'           AS size
FROM filesystem
GROUP BY REGEXP_SUBSTR(path, '^([^/]+/){0,3}[^/]+')
HAVING COUNT(*) >= 10
ORDER BY cnt DESC;

Note: Oracle does not support GROUP BY column aliases, so the expression must be repeated in the GROUP BY clause.

Microsoft SQL Server

SELECT
    CASE
        WHEN p4.pos > 0 THEN LEFT(path, p4.pos - 1)
        ELSE path
    END                                                      AS prefix,
    COUNT(*)                                                 AS count,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 1)       AS pct,
    CONCAT(ROUND(SUM(CAST(datalen AS BIGINT)) / 1024.0 / 1024.0, 1), ' MB') AS size
FROM filesystem
CROSS APPLY (SELECT CHARINDEX('/', path)                     AS pos) p1
CROSS APPLY (SELECT CHARINDEX('/', path, p1.pos + 1)        AS pos) p2
CROSS APPLY (SELECT CHARINDEX('/', path, p2.pos + 1)        AS pos) p3
CROSS APPLY (SELECT CHARINDEX('/', path, p3.pos + 1)        AS pos) p4
GROUP BY
    CASE
        WHEN p4.pos > 0 THEN LEFT(path, p4.pos - 1)
        ELSE path
    END
HAVING COUNT(*) >= 10
ORDER BY count DESC;

H2

SELECT
    REGEXP_REPLACE(path, '^(([^/]+/){3}[^/]+).*$', '$1')    AS prefix,
    COUNT(*)                                                 AS count,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER(), 1)       AS pct,
    CONCAT(ROUND(SUM(datalen) / 1024 / 1024, 1), ' MB')     AS size
FROM filesystem
GROUP BY 1
HAVING COUNT(*) >= 10
ORDER BY count DESC;

Common Path Prefixes

The following table explains what is typically stored under each path prefix and whether it can be safely cleaned up.

Path prefix	Contents	Temporary?	Auto-cleanup	Safe to delete

Path prefix	Contents	Temporary?	Auto-cleanup	Safe to delete
`cmsbs-work/data/guisendnewsletter/tmp/`	Temporary attachments and assets created during GUI-based newsletter sending. Kept for 365 days to support "resend from archive".	Yes	Yes (`TempDirCleaner`, 365 days)	Only if older than 1 year
`cmsbs-work/data/assets/mailingtemplates/{id}/`	CSS files, images and other assets used in mailing templates.	No	No	No — required for email rendering
`cmsbs-work/data/attachments/mailingtemplates/{id}/`	File attachments for mailing templates.	No	No	No — required for email sending
`cmsbs-work/data/newsletterdrafts/{draftId}/`	Saved newsletter drafts (`draft.json`). Created by the SendNewsletter plugin.	No	No	No — user-created drafts
`cmsbs-work/data/mailingtemplates/{id}/data.json`	Mailing template version history.	No	No	No — template archive
`cmsbs-work/data/plugininstances/{id}/data.json`	Versioned configuration data of plugin instances.	No	No	No — plugin configuration
`tmp/`	General temporary files created during various operations.	Yes	Yes (`TempDirCleaner`, 24 hours)	Yes, if older than 24 hours
`cmsbs-work/data/{timestamp}-{seq}/`	Raw data of sent newsletters: downloaded HTML files, images and other assets. Named like `1759324997897-5`. One directory per newsletter send.	No	Only by `DeleteOldNewsletters` job (if configured)	Only if the corresponding newsletter is no longer needed
`cmsbs-work/data/tmp-notification-cachedir_{ts}/`	Cached HTML notification content for preview/sending.	Yes	Yes (`TempDirCleaner`, 1 hour)	Yes, if older than 1 hour
`cmsbs-work/data/de.pinuts.cmsbs.contactform/`	Contact form submissions and uploaded files.	Yes	Yes (`FileGarbageCollectorJob`, 6 hours)	Yes, if older than 6 hours

If the table is unexpectedly large, the most likely culprits are:

Newsletter raw data ({timestamp}-{seq}) accumulating over time if the DeleteOldNewsletters job is not configured.
Accumulated temporary files under guisendnewsletter/tmp/ from frequent resends.
Large numbers of mailing template assets if many templates with images are used.

Adjusting the Depth

To change the grouping depth (number of path segments), adjust the queries as follows:

Engine	Depth parameter

Engine	Depth parameter
PostgreSQL	Change `[1:4]` to `[1:N]`
MySQL	Change the `4` in `SUBSTRING_INDEX(path, '/', 4)`
Oracle	Change `{0,3}` to `{0,N-1}` in the regex
SQL Server	Add or remove `CROSS APPLY` levels (one per path segment)
H2	Change `{3}` to `{N-1}` in the regex