permission_reference table should be set to `unlogged`

Problem Statement

We maintain a table in dotCMS called permission_reference which is in essence a cache. It hold an asset's permissionable parent for quick future reference. We know it is a cache because we lazily build the table during lookups when call doesUserHavePermission(asset, user, permissionToCheck) and most importantly, we know it is a cache because we TRUNCATE this table when we "flush cache".

Postgres has a feature called "unlogged" tables that make them way more performant at the expense of durability - data can be lost if a pg instance is killed. We really don't care if we "lose" data in the permission_reference table as it will just be rebuilt. This "unlogged" table seems like the perfect setting for the permission_reference.

You can see in large installations updating permission_reference is taking 40%+ of the CPU time from the db:

Steps to Reproduce

Look at the db queries in an install that take the most cpu

Acceptance Criteria

The table should be set to unlogged

dotCMS Version

21.06-latest

Proposed Objective

Please Select

Proposed Priority

Please Select

External Links... Slack Conversations, Support Tickets, Figma Designs, etc.

No response

Assumptions & Initiation Needs

No response

Quality Assurance Notes & Workarounds

No response

Sub-Tasks & Estimates

No response

Start by locating the schema or migration that defines the permission_reference table and review how the table is created in PostgreSQL. Change its table setting to unlogged, then verify that the resulting table is unlogged and that the existing cache rebuild behavior remains intact.