Approach to take regarding a field that is frequently re-computed

Question

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:

1. Create a table for each version of the processor which stores the text and reference the main table. Drop table as they become redundant.
2. Add new column to the existing processed table and delete the oldest column.
3. 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.

Answer 1

I would highly suggest you look into table partitioning, as it will give all the benefits of creating separate tables for each version of your processed text, while also permitting concurrent access to the newest and any previous versions you wish.

Assume you have some base data storage table:

 CREATE TABLE basedata (id numeric, orig_data text);

Then you want to store your processed data in "one big table":

CREATE TABLE procdata(baseid numeric, ts timestamptz, 
     proc_text text, version numeric);

I would then select a composite primary key as (version, baseid). With this definition of procdata, you can lookup the processed text proc_text according to date, version, which text in the base table it pertains to, etc.

Unfortunately, if you're processing a whole lot of text data, this table could grow to be very large and bloated, and in this case partitioning along the version attribute could be very beneficial.

The partitioned structure would still permit you to run queries such as,

 SELECT * FROM procdata WHERE baseid = ...

so that you might observe in a single returned set the various versions, but also, you could run

SELECT * FROM procdata WHERE baseid = ... AND version = ...

with significantly less burden. Also, rather than Postgres being required to maintain a large composite index, you could build several different PK indexes on each of the "version" tables, using only baseid.

Last but not least, when you are done with old versions forever (say they are a few versions old and you want to be rid of them), with partitioning you will simply be able to delete the whole table which corresponds to the old version, rather than crawling through one big table to delete the relevant rows corresponding to the obsolete version.

Finally, I would recommend looking into partition management if you go this route.

Cheers!

Answer 2

I would add another table for versioning of data. It would have composite key of data and timestamp or version number and in table I would hold actual data. When new data arrives in table data is updated and in versioning table is inserted.

Regarding vacuuming you set auto vacuum for that table. If table gets bloated despite auto vacuum setting you can run vacuum full, but it would fully lock your database. If you are running on linux you can use pg_repack extension.