Problem with ACL related Tables Size
I have a problem with the growing size of some tables :
relation | size -----------------------------+--------- aclr_user_map_acl_id_idx | 15 GB aclr_user_map | 12 GB aclr_user_map_user_id_idx | 8482 MB
The total size of the /var/lib/psql is around 40Gb
Once dumped, the archive is around 3 Gb
If I restart the server, the db falls back to its “real size” : less than 10 Gb
What's happening ? That's our third or fourth nuxeo installation, and it is the first time this kind of problem occurs.
aclr_user_map, as described in the doc contains the necessary info to be able to do efficient searches. It's a relationship table between the identity of the user (all his groups) and the synthetic ACLs applying to sets of documents. It will grow with the number of users that have made a search since the last truncation, and with the variety of ACLs set on documents.
At Nuxeo restart this table is truncated (when not in cluster mode).
Once restarted, our pgsql partition indeed fell back to 6 Gb.
nx_vacuum_read_aclswhile holding an exclusive lock (
ACCESS EXCLUSIVE) on at least
hierarchy_read_aclto make sure that concurrent processes don't mess up the tables.