|
On 01/06/2015 10:09 AM, Michael Heaney
wrote:
I'm
fairly new to Postgres, and have a design issue for which an array
of integers might be a good solution. But I'd like to hear from
the experts before proceeding down this path.
Essentially, I'm trying to model the relationship between a group
of biological samples and their genes. Each sample (there are ~10K
of them now, with more coming) will have about 30,000 genes.
Conversely, a particular gene may be present in almost all
samples.
So I've created the following table to handle the many-to-many
relationship:
create table sample_gene (id serial, sample_id int, gene_id int);
which looks like this when populated:
sample_id | gene_id
---------------------------------------
1 | 1
1 | 2
...
1 | 30475
2 | 1
2 | 2
...
2 | 29973
3 | 1
etc.
The table now contains hundreds of millions of rows (with many,
many more to come). Join performance between samples and genes is
quite slow, even with indexes on sample_id and gene_id.
So it occurred to me: why not eliminate all the duplicate
sample_id values by storing the gene_id's in an array, like so:
create table sample_gene_array (id serial, sample_id int, gene_id
int [] );
So now the table data looks like this:
sample_id | gene_id []
---------------------------------------
1 | [1:30475]
2 | [1:29973]
etc.
The new table is significantly smaller, and performance (using
ANY[] ) is quite good. Nevertheless, I'm uneasy. I come from a
Sybase ASE background, and so have no experience with arrays as
datatypes. Is it okay to store 30K+ gene values in an array in the
linking table (or maybe even in the sample table itself, thus
eliminating the linking table)? Should I unnest the gene_id's
first, before using them to join to the gene table?
TIA for any guidance you can provide. Again, I'm a Postgres
neophyte - but I'm in awe of the power and flexibility of this
database, and wish that I'd started using it sooner.
------
Michael Heaney
JCVI
Think
genotype calls (sample, marker, call) and this just explodes in
terms of number of rows but I'm trying to not let that bother
me. Partion by chromosome, or chromosome arm (even if partition
is just separate tables) and the magnitude becomes manageable.
At least as manageable as multi-gigabyte gvcf files per sample
from GATK.
But I'm a little confused: all samples for a given species will
have the same genes (roughly). Are you storing gene variants
(sequence or otherwise) per sample?
|
