Problem with ACL related Tables Size

[pibou_Bouvret]

Hi

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.

[Florent_Guillau · Accepted solution]

The table 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).

[pibou_Bouvret]

Quite disapointed by your answer

[Florent_Guillau]

Not necessarily, you can have a nightly job that calls nx_vacuum_read_acls while holding an exclusive lock (ACCESS EXCLUSIVE) on at least aclr, aclr_user, aclr_user_map, aclr_modified, acls, hierarchy and hierarchy_read_acl to make sure that concurrent processes don't mess up the tables.