While our DB has many tables that are using their own custom sequence, xtknewid is still being used by a select few large tables. What I've found is that while there is a fair few new records inserted, it pales in comparison to the rate of which xtknewid is increasing (eg. if 2 million new records are inserted using xtknewid in total, the sequences still goes up by 20 million or so).
This leaves the most likely conclusion that the cache value of xtknewid is contributing to this growth (it is currently set to 30) and decreasing it to a smaller number will solve this problem. What I want to find out is what is a session in the context of Postgres inside ACC? At the moment, the cache is set to create 30 new records inside the DB per session.
Thanks for the answer. It will indeed most likely be the workflow logs table is contributing largely to this (I've since checked and it is outputting ~200-300K logs per day). I think your last point about the raw activity storage may also be the case as the workflows on the instance are using quite a widespread amount of SQL script to log data into various tables. The cache may be going hand-in-hand with the workflow log session created I think.
Delivery/ broadlog-wise it looks ok as we are using the OOTB sequence nmsBroadlogId for these.
The cache can be configured when using SQL script to configure the sequence cursor set inside the DB.
ALTER SEQUENCE xtknewid .... CACHE 30 CYCLE;
When using this for xtknewid previously we set it to 30 (which I think is the default for it regardless OOTB) alongside the CYCLE option that allow for the sequence values to be recycled.
You could also set this when creating the custom sequence:
We were able to confirm that cache is 30 for xtkNewId using this script.
The above is explained in more detail here Adobe Campaign: Sequences Exhaustion - Adobe Campaign Classic (from 15:00 onwards and 4:00 onwards respectively). For the full scripts, I would contact Customer Care (Adobe Support) for these (I'm not certain if I'm able to share these publicly) or message a member of staff here who I am sure will be happy to provide them for you.