principlesqlModerate
Best practice for storing record metadata
Viewed 0 times
practicerecordforstoringmetadatabest
Problem
What is the best practice for storing metadata of individual records in a database?
I need to store common meta data such as creation time and time of last update for many tables in my database. I found a few different solutions:
-
Store the meta data directly in the tables.
Pros:
Cons:
-
Create a general meta data table with and use soft foreign keys to link data to the correct tables and records.
Pros:
Cons:
-
Create individual meta data tables for each table requiring meta data.
Pros:
Cons:
Are there more options, pros or cons than the ones I mentioned here? And what is the best practice for storing this meta data?
I need to store common meta data such as creation time and time of last update for many tables in my database. I found a few different solutions:
-
Store the meta data directly in the tables.
Pros:
- Meta data is directly linked to records
- No joins are required to retrieve meta data
Cons:
- A lot of duplicate columns are required (unless inheritance is used)
- Meta data and business data are not separated
-
Create a general meta data table with and use soft foreign keys to link data to the correct tables and records.
Pros:
- No duplication of columns
- Meta data is separated from business data
Cons:
- No direct links between meta data and data (FK's can't be used)
- Joins require an additional condition
-
Create individual meta data tables for each table requiring meta data.
Pros:
- Meta data is directly linked to records
- Meta data is separated from business data
Cons:
- A lot of extra tables are required
- A lot of duplicate columns are required (unless inheritance is used)
Are there more options, pros or cons than the ones I mentioned here? And what is the best practice for storing this meta data?
Solution
The columns you are talking about occupy 20 bytes (if aligned without padding):
creation time, update time and creation source
timestamp .. 8 bytes
timestamp .. 8 bytes
integer .. 4 bytes
The tuple header and item identifier for a separate row in a separate table alone would occupy 23 + 1 + 4 = 28 bytes plus the 20 bytes of actual data, plus 4 bytes of padding at the end. Makes 52 bytes per row. See:
Concerning storage you have nothing to gain. Concerning performance you hardly lose anything with just 16 - 24 bytes more per row.
The columns also directly belong to the row, so it makes sense to keep them together. I make it a habit to add exactly such columns (plus separate source for the last update) to all relevant tables.
It's also easier to write a
Long story short: a strong vote for your option 1.
Where I would go for option 3:
If the metadata is updated often, while the core row is not. Then it might pay to keep a separate 1:1 table to make UPDATEs cheaper and reduce bloat on the main table - or even go for option 2.
Where I would go for option 2:
If the set of metadata columns is highly repetitive. You could have a FK column to the set of metadata in the main table(s). Does not save much for three small columns like in your example.
creation time, update time and creation source
timestamp .. 8 bytes
timestamp .. 8 bytes
integer .. 4 bytes
The tuple header and item identifier for a separate row in a separate table alone would occupy 23 + 1 + 4 = 28 bytes plus the 20 bytes of actual data, plus 4 bytes of padding at the end. Makes 52 bytes per row. See:
- Configuring PostgreSQL for read performance
Concerning storage you have nothing to gain. Concerning performance you hardly lose anything with just 16 - 24 bytes more per row.
The columns also directly belong to the row, so it makes sense to keep them together. I make it a habit to add exactly such columns (plus separate source for the last update) to all relevant tables.
It's also easier to write a
TRIGGER ON INSERT OR UPDATE to keep them current.Long story short: a strong vote for your option 1.
Where I would go for option 3:
If the metadata is updated often, while the core row is not. Then it might pay to keep a separate 1:1 table to make UPDATEs cheaper and reduce bloat on the main table - or even go for option 2.
Where I would go for option 2:
If the set of metadata columns is highly repetitive. You could have a FK column to the set of metadata in the main table(s). Does not save much for three small columns like in your example.
Context
StackExchange Database Administrators Q#44351, answer score: 11
Revisions (0)
No revisions yet.