Scaling the audit table about supa_audit

[rallisf1]

True, pgaudit is the better choice but I wanted something more practical for Actions Auditing for my app, not a full fledged db auditing, and this extension was a big step towards that.

Anyway, for whomever is concerned I have added partitioning in my fork. This is a breaking change as you cannot add partitioning to an existing table, thus audit.record_version needs to be created from scratch. I guess you could rename the existing table and copy over the data.

The naming convention for the partitions is: audit.record_version_SCHEMA_TABLE_YEAR_MONTH e.g. audit.record_version_public_account_2023_8 but since partition pruning is enabled by default you only need to query audit.record_version.

Year-Month based sub-partitioning only works if you have pg_cron installed beforehand.

P.S. This isn't battle tested yet; I just forked it last night. Let me know if you need an update in a few months.

[olirice]

Can you suggest any optimized ways to partition this table

I haven't had experience with optimizing query performance at that scale yet, but ts seems like the easy choice for partitioning. Im not clear how we'd allow user configurability, or provide useful defaults, for partitioning with the table as part of an extension but its a good question

I'll leave this open in case others have ideas

[MichaelDBA]

thanks

[rallisf1]

I agree this needs partitioning to become relevant for large projects. A good practice would be to partition by table and sub-partition by year & month.

Partition creation can be automated:

Partition by Table

1. Alter audit.record_version table to support list partitions on table_oid
2. Add a partition during audit.enable_tracking

Sub-partition by Year & Month

1. Add a function that adds a partition to an existing audit.record_version_table_oid for the current month
2. During audit.enable_tracking if pg_cron is installed run the function once for the current month and schedule to run using pg_cron at the first day of each month (0 0 1 * *)

Partition Naming Proposal

audit.record_version_table-oid_year_month

Example: audit.record_version_16328_2023_08

P.S. The sql code is very straight-forward but I haven't dealt with pg extensions before. I could really use a contributing guide.

[olirice]

this project is a convenient approach for maintain an audit history for low-mid volume tables for consumption within SQL. Past that use-case, we recommend logging based solutions that are more configurable like https://www.pgaudit.org

[olirice]

I'm going to close this issue as out-of-scope but you're welcome to continue the discussion to suggest partitioning strategies for e.g. forks or local use!