Dear all, I just found a nice SELECT which shows the size of the database you are connected to. For my dbmail, it reads this. dbmail_headervalue is about 1/3 of the whole e-mail size, can this be reduced? Is dbmail2.3 saving space only for message parts, or for headervalues also?
nspname | relname | totalsize
---------+---------------------------+------------
public | dbmail_messageblks | 9735 MB
public | dbmail_headervalue | 3195 MB
public | dbmail_messages | 241 MB
public | dbmail_envelope | 148 MB
public | dbmail_tofield | 102 MB
public | dbmail_fromfield | 76 MB
public | dbmail_referencesfield | 74 MB
public | dbmail_subjectfield | 70 MB
public | dbmail_datefield | 69 MB
public | dbmail_physmessage | 40 MB
public | dbmail_replytofield | 23 MB
public | dbmail_ccfield | 20 MB
public | dbmail_users | 4696 kB
public | dbmail_headername | 624 kB
public | dbmail_mailboxes | 424 kB
public | dbmail_aliases | 280 kB
public | dbmail_sievescripts | 272 kB
public | dbmail_replycache | 184 kB
public | dbmail_subscription | 144 kB
The SELECT reads:
SELECT nspname, relname,
pg_size_pretty(tablesize+indexsize+toastsize+toastindexsize) AS
totalsize
FROM
(SELECT ns.nspname, cl.relname, pg_relation_size(cl.oid) AS tablesize,
COALESCE((SELECT SUM(pg_relation_size(indexrelid))::bigint
FROM pg_index WHERE cl.oid=indrelid), 0) AS indexsize,
CASE WHEN reltoastrelid=0 THEN 0
ELSE pg_relation_size(reltoastrelid)
END AS toastsize,
CASE WHEN reltoastrelid=0 THEN 0
ELSE pg_relation_size((SELECT reltoastidxid FROM pg_class ct
WHERE ct.oid = cl.reltoastrelid))
END AS toastindexsize
FROM pg_class cl, pg_namespace ns
WHERE cl.relnamespace = ns.oid
AND ns.nspname NOT IN ('pg_catalog', 'information_schema')
AND cl.relname IN
(SELECT table_name FROM information_schema.tables
WHERE table_type = 'BASE TABLE')) ss
ORDER BY tablesize+indexsize+toastsize+toastindexsize DESC;
--
// Michael Monnerie, Ing.BSc ----- http://it-management.at
// Tel: 0676/846 914 666 .network.your.ideas.
// PGP Key: "curl -s http://zmi.at/zmi.asc | gpg --import"
// Fingerprint: AC19 F9D5 36ED CD8A EF38 500E CE14 91F7 1C12 09B4
// Keyserver: www.keyserver.net Key-ID: 1C1209B4
signature.asc
Description: This is a digitally signed message part.
_______________________________________________ DBmail mailing list [email protected] https://mailman.fastxs.nl/mailman/listinfo/dbmail
