As part of my Postgres 10 schema, I have two tables;
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.,
current table will be read from frequently. Both
SELECTs must be very fast.
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
So, my questions are:
- 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)?
- 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.
- What are the pros and cons of each approach?
Thanks in advance.
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?