2

As part of my Postgres 10 schema, I have two tables; current and historic -- both have the same columns.

current has about 20 (+/-) columns, and holds the most up-to-date information for accounts. I need to keep a timestamp showing when the information came in, but each of the 20 columns gets updated at a different time. So, per account current will have numberOfColumns rows (as opposed to one row for one account) -- as new data comes in for one column, the row holding the information for just that column will get updated.

This is so I can quickly access the current information for the account.

However, I also need to store the history; so every time current is updated, the row (containing a single entry -- aside from metadata) will be 'moved' to the historic table. This table has the same number of columns.

So, I will end up with a very sparse table of 20 or so columns, and with each row containing a timestamp, one column's worth of 'data', and some metadata (e.g., account_id).

The current table will be read from frequently. Both INSERTs and SELECTs must be very fast.

The historic table will likely generally be used for offline analytics, and space is a priority SELECT performance, but INSERTs must be fast (because they will be done while updating current.

So, my questions are:

  1. Is this a bad design? What is the usual solution for this type of situation (where a number of things like account details are asynchronously updated)?
  2. Should I break it up so I have 20 tables, with 1 data column plus the metadata I need per table? It seems that would waste space.
  3. What are the pros and cons of each approach?

Thanks in advance.

Edit(s):

In response to @JonathanFite:

Adding the schema and sample queries would use up a lot of space; but,

  • The 'data' columns are a number of decimal and int values and one text value, with no constraints.
  • There are 4 additional values with not null constraints (bigserial, text, text, and timestamptz) that will be a part of each insertion.
  • Each insertion consists of those 4, plus a decimal or int (or text in one case).
  • All 20 columns could be updated within the same second, or minutes apart.
  • The selects will request the results to be ordered by time, and could be for anywhere from a single data column to nearly all of them.

Does that help clarify things?

  • 2
    Could you include your actual table schema as well as example insert and select statements? – Jonathan Fite Dec 28 '18 at 13:45
  • I‘d rather use one table and two views. – Andreas Rehm Dec 29 '18 at 18:52
0

You should use one table and views for Current and History data.

Reasons:

  1. This is easier to update (just add a new data row with a newer timestamp)
  2. easier to maintain on tables changes
  3. does not need move logic which can fail
  4. the views can easily be changed to view full history (with current data) without table joins

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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