I have a few fields with text data in a main table which is processed in a certain way to produce a processed version of that piece of text.
Now, the way in which this text is processed is expected to change over time so that at some point a new version of the processed text would have to be stored in the database. We would keep the newest and previous versions of the processed data, but stop inserting new data into the previous version.
I see a number of options:
- Create a table for each version of the processor which stores the text and reference the main table. Drop table as they become redundant.
- Add new column to the existing processed table and delete the oldest column.
- Clear the oldest column and then insert new data into it.
I am wondering what the best approach is here.
As a sidenote - what would be the requirement on using a vacuum etc.
I am using PostgreSQL 9.4 on Debian and the number of rows are around 56 million and continuously increasing.