I opted for MonetDB for my static data, but this might be less of a MonetDB-specific question about features than about RDBMS (or just SQL) in general.
I have data with a few "cross-sectional" tables (e.g. the parents of each individual) and many longitudinal/panel tables in annual chunks (e.g. tax records for each year separately).
I wonder if it is a good idea to merge/union the annual tables into long panels.
The benefits seems to be a much cleaner structure for the consistent columns, correspondingly easier code to write, fewer loops, tables names, lines of code. Also, fast joins on individual IDs and years across the tables without intermediary tables or views.
However, the downsides are that not all columns are consistent across years, and there are simply some that appear only in certain years. Storing missing values for other years for these variables is a waste of disk space and memory. I would also spend time on how to merge tables when there is a (rare) type change over the years (usually strings vs.
TINYINTs). The merged tables could be too big anyway: think of tables ~70 GB in some cases, while my server has 128 GB RAM. Many use cases would focus on a subset of years anyway.
Actually some links I found were about how partitioning would improve performance (at least if I can learn to code using partitioning):
Do you have any thoughts about this? Maybe more concrete questions would be:
- How cumbersome would it be (for a novice!) to code joining data from
the annual tables separately, for multiple series of tables? (E.g.
UNION ALLsome columns of tax returns for 1997-2008, then join all residential data for these 12 years year-by-year, then join employment records year-by-year etc.)
- How much slower would this run than simply selecting years from the longitudinal tables?
- Shall I be worried about the longitudinal tables having the size on the same order of magnitude as my physical memory, or MonetDB handles it equally efficiently (it would need to access many of the small annual tables anyway, that also takes memory). Can at least the missing values for entire years be stored efficiently?