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.
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.