Background info
I'm sitting on 5000 files each consisting of 10 million lines and 15 columns. Each file i
stores regression information for a given dependent variable y_i
on an independent variable id
. For a given file i
the data looks like this:
id beta se pvalue ...etc
where column id
represents the name of the dependent variable. Note: the id
column is identical for all files, it is the value of the other columns that changes per file.
I regularly need to loop through each file, retrieve m
lines and then merge them. To do this I either read the data into memory and manipulate it with R or I run an awk script. This is obviously slow so I took 100 files and put them into a Postgres (v12.11) database with two different designs:
- Each file
i
for dependent variabley_i
is its own table with a btree index onid
. - One long (100 * 10 million) table with a btree index on
(id, dependent_var)
, wheredependent_var
is a new column with valuey_1
the first 10 million lines,y_2
the next 10 million lines and so on.
I set up a simple simulation where I randomly draw m
indexes and k
dependent variables and then query the database with select * from relation where id in (...);
for design 1 in parallel and for design 2 a similar query but one that also uses the dependent_var
column. The results were as follows:
Both design beat the current system and,
Design 2 crushes design 1 performance wise.
The question
Now, my question is: does design 2 scale up to a table of size 5000 * 10 million or is there an even better way of doing this?
I appreciate any help!
Update 1: Create table and select
Assume m = 4
and k = 3
.
For a given dependent variable y_1
I create an empty table with create table y1 (id integer, beta double precision, se double precision, pval double precision);
. I then iterate over each file on the disk and populate the database with psql -d dbname -c "\copy y1 from filepath (FORMAT CSV, DELIMITER ';', HEADER)"
. Once the data base is populated I create an index with create index y1_idx on y1 (index)
. The query is select * from y1 where index in (1, 2, 4834, 56);
For design 2 I create table longtable
which has the same columns as y1
above with the addition of column dependent_var text
. I proceed to populate the database with the copy
command from above, replacing y1
with longtable
. Once I'm done populating the table I create an index with create index longtable_idx on longtable (index, dependent_var);
. The query is select * from longtable where ((index in (1, 2, 4834, 56)) and (dependent_var in ('y_1', 'y_2', 'y_99')));
CREATE TABLE
andSELECT
statements again?O(log(n))
complexity, so Log2 of 50 bln is only 35, therefore not that many lookups. Sure, store it one table, as long as you have a good index why not? You do need to be able to store that much info, back of the envelope saysdouble
andbigint
are 8 bytes so you need about 7 terabytes.