I have a Postgres Table (500 rows, 10 columns) that gets updated every 6 hours by a Daemon (that performs some calculations) and executes a series of row-by-row upsert operations. How can I construct day-by-day time series of the state of the table? Is there some way to build this by walking through the update/operation history? Are there any frameworks/techniques out there to make this easy?

Table will grow to max of 5000 rows.


You could maintain a history table that has the same structure as the original table plus a timestamp column.

Then create a trigger that copies the old row into that history table before applying the UPDATE. During the INSERT into the history table, the trigger would populate the timestamp column to store the point in time when the change occurred. The UPDATE will then proceed normally on the main table.

Depending on your needs you might also want to store new rows when they are INSERTed or the old rows before they are DELETEd.

This approach typically does not require any changes in the application. To find out old "versions" of your data, you need to query that history table, rather than the real table. Whether that is acceptable I can't tell.

  • This is exactly what I needed -- thanks! – Neil Philip Jul 16 '19 at 8:48

a quick answer. It's not that clear from your question exactly what you're after, so I'll likely be off the mark.

From the sounds of things, your data changes four times a day, and you want to at least be able to get an idea of where the values were once a day, so one out of every four update cycles. I'd keep the data in the same table with a timestamp or date, which would make such operations simple. But perhaps you have a reason for your hard limit of 5000 rows?

Okay, assuming you've got a good reason for all of that, what it sounds like you're asking for is "time travel." That's not a joke, it's a term used to describe a search that can check on the table at a specific moment in time. Postgres doesn't have this feature now, although it gets discussed.

So, if the table changes every 6 hours, and you want to get a snapshot of its state every 24 hours, you'll need to put that snapshot of the table somewhere. Easiest is to have it in the same table with a date/timestamp that allows you to select only the rows you're interested in. After that, I guess that the next best bet is to push the data into a duplicate table. But it's hard to see how that helps. You can do that with a trigger (they come in several flavors), but it's probably a bad design, so I won't elaborate.

It would help if you explained what problem(s) you're solving by overwriting the rows rather than adding new ones, and by limiting the table to 5000 rows.

It would also help if you clarified if you're trying to find all of the data from a 24 hour period, or only some summary/extract/rollup that's different to the original data.

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