0

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

4
  • 1
    What were those CREATE TABLE and SELECT statements again? Jul 14, 2022 at 10:50
  • I updated my question with the information you asked for.
    – docjay
    Jul 14, 2022 at 11:15
  • 2
    Bear in mind that a B-Tree index has approx 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 says double and bigint are 8 bytes so you need about 7 terabytes. Jul 14, 2022 at 12:02
  • @Charlieface True in theory, but the base of the logarithm is so large that for all practical purposes, the expense of an index scan is independent of the table size. Jul 14, 2022 at 15:20

1 Answer 1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.