Re: Advice for using integer arrays?

Hi Michael,

I can't comment on the domain-specific stuff, but I recently used numeric
arrays for a project and it worked well. In my case we had one million
simulation results (floats) per scenario, so rather than reading one
million separate rows to compute a histogram, we stored everything in one
row per scenario. Ideally one million floats is 8 megabytes, which is big
but still shouldn't require more than 100ms to read from disk and feed into
a simple computation. Here are some functions I wrote to make it easier &
faster to compute stats from numeric arrays:

https://github.com/pjungwir/aggs_for_arrays/

One caveat is that you will lose a lot of benefits by leaving the
relational model, e.g. being able to filter your integers by other
criteria. In our case all we had were floats, but imagine if you had
metadata attached to each one like the time the simulation was run, who ran
it, etc. Then you'd want to stick with something richer than just an array
of numbers.

But in that case maybe parallel arrays is acceptable. It would be a bit
like a column-store inside of Postgres. :-) I've been meaning to add a
function to that Github repo to filter an array given a same-size array of
booleans, so you can do filtering like in R or Pandas, but I haven't found
a nice way in Postgres to express e.g. `filter_array(simulation_results,
simulation_run_times > now() - interval '1 day')`.

Good luck!
Paul

On Tue, Jan 6, 2015 at 1:18 PM, Michael Heaney <mheaney(at)jcvi(dot)org> wrote:

> On 1/6/2015 2:19 PM, Jeff Janes wrote:
>
> On Tue, Jan 6, 2015 at 9:09 AM, Michael Heaney <mheaney(at)jcvi(dot)org> 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);
>>
>
> What is the value of having this table at all? It doesn't seem to
> contain anything informative, like an allele identifier, a resequence, or a
> copy number variation. If you are just trying to record the fact that a
> gene was present in that sample, perhaps it would be better to instead
> record the genes have been deleted, rather than the ones that have not been
> deleted? That would probably be a much smaller list.
>
>
> I suppose there could be a gene table which would contain data about each
> gene_id.
> But I'm an IT guy, not a biologist, and my sample_gene table doesn't
> actually
> exist. I'm more concerned with how to deal with many-to-many relationships
> when each parent could have tens of thousands of children. Collapsing all
> the children into an array for each parent looked intriguing - but maybe
> it's
> not a good idea. I just don't know, so I thought I'd ask you guys.
>
>
>
>
>
>> 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.
>>
>
> I'm not familiar with the square bracket and colon as a syntax for
> expressing int arrays. Are you taking liberties with the psql output, or
> using a different client program? Does that represent the range from 1 to
> 30475, or the two values 1 and 30475?
>
>
> Yes, it's shorthand for the full range of values from 1 to 30475. Wasn't
> sure how
> to represent it, and almost went with (1,2,3...30475). Apologies...
>
>
> Michael Heaney
> JCVI
>
>
>
>
>
>

--
_________________________________
Pulchritudo splendor veritatis.