I'm working on a project dealing with genomic data in a Postgres database. There is one table in particular which is causing trouble. It is slowly approaching a billion rows and has three columns: snp_name varchar(32), genotype_id int, local_genotype char(2)

The first two are foreign keys and together form the primary key of the table.

Querying this data is still OK, response-time-wise. Inserting data takes ages, though. We are now at a point where we get new data faster than we can insert it, and we already COPY into a temporary table first and insert from there. Due to the server still running on spinning disks, inserting a genotype with a million rows takes about 5 hours.

Partitioning by genotype_id would be great, since this would result in ~ a thousand tables with a million rows each and a genotype is basically the package we get the data in. Then, querying by snp_name probably won't perform that well anymore, though. Whether we can live without it, has not yet been decided.

Do you have any suggestions for other options we could try? Are there any database systems, that allow indexing across partitions? Any NoSQL solutions that could fit?

EDIT: The table definition:

# \d user_snps
              Table "public.user_snps"
     Column     |         Type          | Modifiers
 snp_name       | character varying(32) |
 genotype_id    | integer               |
 local_genotype | character(2)          |
    "user_snps_new_genotype_id" btree (genotype_id)
    "user_snps_new_snp_name" btree (snp_name)

There is only one distinct value of genotype_id per insert, and all values of snp_name are unique per insert.

  • 2
    It's not actually the inserting that is taking time - that is just dumping rows into a heap. It must be something else, eg the FK lookups for the RI that have to happen too, or per-row triggers getting fired. – Jack says try topanswers.xyz May 18 '15 at 7:28
  • Have you tried to move the varchar(32) column out to a separate table and do joins when querying? I am wondering how much time the uniqueness check takes on inserts. Anyway, 5 hours for inserting a million rows really suggests a trigger or similar, just like @JackDouglas said above. – dezso May 18 '15 at 8:03
  • I suppose what's taking so long is just updating the indexes, since there are no constraints or triggers on that table. – rausch May 24 '15 at 7:22
  • I would also help if you showed the definitions of the other 2 tables and a few sample rows. (the choice of a varchar(32) for a primary key does not seem optimal). – ypercubeᵀᴹ May 24 '15 at 8:11
  • Updating the indexes may be generating a lot of IO - how many distinct values of genotype_id and of snp_name are there in a typical 1,000,000 row insert? Please reply with @Jack in your comment or I won't know you have... – Jack says try topanswers.xyz May 24 '15 at 10:23

I'm thinking having two copies of that table, one partitioned by genotype_id, one partitioned by snp_name and adding a few constraints and triggers to keep it consistent might be a good enough solution.

| improve this answer | |
  • this will gain you nothing because if you partition by snp_name you are still going to take 5 hours to insert your 1,000,000 rows into that table. – Jack says try topanswers.xyz May 24 '15 at 11:25

A different idea I had is to put the associations into array/hstore columns in the genotypes and snps tables.

| improve this answer | |

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