Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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:

  1. Store the meta data directly in the tables.


    • Meta data is directly linked to records
    • No joins are required to retrieve meta data


    • A lot of duplicate columns are required (unless inheritance is used)
    • Meta data and business data are not separated
  2. Create a general meta data table with and use soft foreign keys to link data to the correct tables and records.


    • No duplication of columns
    • Meta data is separated from business data


    • No direct links between meta data and data (FK's can't be used)
    • Joins require an additional condition
  3. Create individual meta data tables for each table requiring meta data.


    • Meta data is directly linked to records
    • Meta data is separated from business data


    • 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?

share|improve this question
What kind of metadata are we talking about? Maybe using a hstore or JSON column could solve your problem? – a_horse_with_no_name Jun 12 '13 at 11:15
@a_horse_with_no_name - Right now I only need creation time, update time and creation source. The fields are fixed so I don't need key-value like storage. I'm only worried about where I should store the data. – Tiddo Jun 12 '13 at 12:42
Then I don't see any reason not to add those three columns to the base table. – a_horse_with_no_name Jun 12 '13 at 12:55
up vote 7 down vote accepted

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 index pointer 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. Read more here:

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.

share|improve this answer
Thanks, this was a way better answer than I expected! – Tiddo Jun 14 '13 at 11:01
What about solving this with table inheritance, are there remarkable drawbacks compared to using metadata colums directly in the table? However if I understand correctly, postgres' table inheritance is not SQL standard compliant, do I? – devrys Jul 18 '15 at 10:17
@devrys: Inheritance has some limitations in Postgres More importantly: I don't see how inheritance could solve saving some additional columns per row. it would be an option if you have some rows with and other rows without metadata. But I would not use it for that. – Erwin Brandstetter Jul 18 '15 at 12:07

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.