Dbmail quickly get mailbox counts (including deleted emails) [postgresql]

(0 comments)

So while tracking down problems with performance with (webmail - ) clients I found out, that the problem is - at least partly - related to the sheer amount of IMAP folders (dbmail_mailboxes) a user had ( > 3500 !).

So we set out to merge those (old Archive) folders to cut down on the folder numbers (roundcube webmail for example seems to reload the folder list every login and also for *every* compose window, since the user can pick where to save the sent email).

The first thing was checking if I can do it automatically. That unfortunately got discarded quickly, because all the folders had been created manually and contained whitespaces, typos, differences in wording,..

So then the next step was to get a count of mails per folder (doing this through imap was slow and a pain. With a bit of help (FILTER) from someone in #postgresql I made this view:

CREATE VIEW mailbox_mailcount AS
SELECT mb.mailbox_idnr, usr.userid, mb.name, COUNT(*) FILTER (WHERE msg.deleted_flag = 1) AS MessagesDeleted, COUNT(*) FILTER (WHERE msg.deleted_flag = 0) AS Messages
FROM dbmail_mailboxes AS mb
INNER JOIN dbmail_users AS usr ON mb.owner_idnr = usr.user_idnr
LEFT JOIN dbmail_messages AS msg ON mb.mailbox_idnr = msg.mailbox_idnr
GROUP BY mb.mailbox_idnr, usr.user_idnr
ORDER BY usr.userid, name;

This makes it really easy to get the mailboxes for a user (not the internal userid, but the login) with a count of messages - and also marked as deleted messages. (handy for finding empty folders !!)

I also wrote some SQL to "move" (rename) some folders from one parent to another (it uses the view from above to (greatly) simplify the queries):

BEGIN TRANSACTION;

CREATE TEMPORARY TABLE mb_rename
(
mailbox_idnr BIGINT UNIQUE not null,
oldname VARCHAR(255) NOT NULL,
newname VARCHAR(255) NOT NULL,
mailcount BIGINT,
ignorethis BOOLEAN DEFAULT FALSE
);

INSERT INTO mb_rename
SELECT mailbox_idnr, name, REPLACE(name, 'Archiv2012', 'Archiv') AS name_new, messages
FROM mailbox_mailcount
WHERE userid = '<insert your user>' AND name LIKE 'Archiv2012/%'
AND Messages > 0;
-- could use SIMILAR TO instead of LIKE to have regex matching
-- ignore mailboxes, that are empty (or only contain deleted messages)

-- sometimes existing names were overlooked (they should be manually moved / merged)
-- so we ignore ones already present
UPDATE mb_rename SET ignorethis = TRUE
FROM dbmail_mailboxes WHERE dbmail_mailboxes.name = mb_rename.newname;

-- all set so now just update the names
UPDATE dbmail_mailboxes SET name = newname
FROM mb_rename
WHERE mb_rename.mailbox_idnr = dbmail_mailboxes.mailbox_idnr
AND dbmail_mailboxes.name = oldname
AND NOT mb_rename.ignorethis;

-- Check what was done here ! (also make a note of the ignored ones to sort manually)

-- don't forget to run this (commented in case i copy paste too fast):
--COMMIT TRANSACTION;

Now when I did run this i decided to 1) tell the users to close their email clients, but to be save i shut down dbmail-imapd for a minute (query ready,.. just stop, run query, check, start).

Also webmail clients like squirrelmail seem to cache the folder list, so logout and log back in was needed anyway.

Furthermore the VIEW created can of course easily be used to find all empty folders and clear them out with a single DELETE statement (be careful not to remove parent folders that happen to be empty while their child folders are not.. some clients get a bit confused :)

Currently unrated

Comments

There are currently no comments

New Comment

required

required (not published)

optional

required