Designing large static Postgres database for quick index searching

Question

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:

1. Each file i for dependent variable y_i is its own table with a btree index on id.
2. One long (100 * 10 million) table with a btree index on (id, dependent_var), where dependent_var is a new column with value y_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:

1. Both design beat the current system and,

2. 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')));

Answer 1

The two queries don't really match: the one on the large table should have WHERE dependent_var ='y_1' rather than an IN list.

Anyway, the large table is often the better way if all the files have the same column structure. You could have a two-column index on (dependent_var, "index') or, if you need IN lists for both, two individual indexes for the columns.

One reason to consider the first solution would be if you regularly need to remove the data for a whole file. In that case I recommend a partitioned table with one partition per file. That would also save you the index on dependent_var.

What is better depends on the details of your requirements; I find it hard to make a generic recommendation.