Is it a good idea to split an auditable table into two tables based on the update frequency? I'm currently designing a database schema for a small microservice which handles products.
The service contains a simple REST API which allows users to perform basic management of products and an another API which is used to perform operations which consume these products. The maximum amount of consumes per product is limited with an explicitly defined consumables count. When this limit is reached, the product cannot be consumed anymore. Each product typically contains about 1,000 - 1,000,000 consumables, while a single operation may take 1 - 10 consumables at a time.
The frequency of the management operations is quite low, but the rate of the consuming operations is very high.
At the moment, the schema puts all product information into a single table. This table also contains information which must be audited into a snapshot based history table on each INSERT, UPDATE and DELETE operation. This is currently done with DB triggers, which know how to add rows into the history table along with the additional audit information. Each consumable use also adds a new row into consumables table with some additional information related to how it was consumed.
Here's a simplified example about how these tables are currently organized.
------------------------
| product              |
------------------------
| + id                 |
| + name               |
| + is_enabled         |
| + consumable_limit   |
| + consumable_counter |
| ...10 omitted fields |
------------------------

------------------------
| product_history      |
------------------------
| + id                 |
| + product_id         |
| + time               |
| + user_id            |
| + history_type       |
| + name               |
| + is_enabled         |
| + consumable_limit   |
| + consumable_counter |
| ...10 omitted fields |
------------------------

------------------------
| operations           |
------------------------
| + id                 |
| + product_id         |
| + consumable_idx     |
| ...10 omitted fields |
------------------------

In the above diagram, I've omitted some fields to keep things more simple. The thing that keeps me wondering here is that the consumable_counter in the product table gets rapidly updated with consuming operations, which may further flood the product_history table. Would it be better move the consumable counter into an another table where it would not trigger the a full snapshot of the product table row each time the consumable_counter is being updated? Somehow, I feel like it could be ideal as the consumable_counter is the only column in the product table which is being updated with a high rate. A full snapshot on each counter increment somehow feels a bit overkill.
Edit
The consumable_counter acts as a counter of how many times the corresponding product has been used. When it reaches the consumable_limit, the product cannot be consumed any more.
Edit
Each time a product is being consumed, a new entry will be added into the operations table. Entries in this table also act as business logic transactions containing a state which will be managed during the operation execution flow.
I think your assessment is heading in the right direction. You do appear to be capturing too much information. Based  on your description, I think you are describing a consumption event and not product history.
My recommendation is to look at splitting out the consumption attributes from the PRODUCT_HISTORY table into a new table.  Without knowing the omitted columns, I am unable to provide a recommendation. Here are some thoughts:

if the &quot;name&quot; column is the product's name, that doesn't change with a consumption event and should remain in product history.

The consumption_limit might need to be in the product history and consumption event tables. You may need to know what the limit was when the consumption occurred and the limit may change over time, thus that fact should be recorded in the product history table.

A relationship between the consumption event and the operations table might be needed


Does a row in the operations table describe a collection of individual operations (and perhaps there is an operation table) or is it a singular operation?  That is an important detail on figuring out the relationship with consumption events. If it is singular, I recommend you rename the table.