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 INSERT
s and SELECT
s must be very fast.
The historic
table will likely generally be used for offline analytics, and space is a priority SELECT
performance, but INSERT
s must be fast (because they will be done while updating current
.
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.
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?