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?
