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
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
ifor dependent variable
y_iis its own table with a btree index on
- One long (100 * 10 million) table with a btree index on
(id, dependent_var), where
dependent_varis a new column with value
y_1the first 10 million lines,
y_2the 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.
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
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
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')));