PostgreSQL: options for large table 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)          |
Indexes:
    "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.
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.