pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-general@lists.postgresql.org
Subject: Re: TEXT column > 1Gb
Date: Wed, 12 Apr 2023 14:02:25 -0500
Message-ID: <4777754a-ad1f-eae3-f6c9-a4f783e803e3@gmail.com> (raw)
In-Reply-To: <A29ABFF1-8014-46B2-9425-34F59812581A@lbl.gov>
References: <85984fc4-6e08-06ad-78d7-d6f73a5c90a0@gmail.com>
	<0A8EB6F5-57BC-4519-8E9A-DBB809CC09D5@lbl.gov>
	<E18E02B1-39DE-4464-9C8C-F0C06BBF771B@enterprisedb.com>
	<A29ABFF1-8014-46B2-9425-34F59812581A@lbl.gov>

/Must/ the genome all be in one big file, or can you store them one line per 
table row?

On 4/12/23 12:19, Joe Carlson wrote:
> I’ve certainly thought about using a different representation. A factor of 2x would be good, for a while anyway. For nucleotide sequence, we’d need to consider a 10 character alphabet (A, C, G, T, N and the lower case forms when representing ’soft masked’ sequence*). So it would be 2 bases/byte. (Proteins are not nearly so long so a straight storage is simpler.) But these would be bigger changes on the client side than storing in chunks so I think this is the way to go.
>
> We’re working with plant genomes, which compared to human chromosomes, are HUGE. One chromosome of fava bean is over a gig. And pine tree is another monster. This, together with the fact that sequence data collection and assembly have improved so much in the past couple years has forced us to rethink a lot of our data storage assumptions.
>
> * for those curious, especially in plants, much of sequence consists of repetitive element that are remnants of ancient viruses, simple repeats and the like. For people who want to identify particular functional components in a genome, they typically do not want to search against this sequence but restrict searching to coding regions. But the repetitive sequence is still important and we need to keep it.
>
>> On Apr 12, 2023, at 10:04 AM, Mark Dilger<mark.dilger@enterprisedb.com>  wrote:
>>
>>
>>
>>> On Apr 12, 2023, at 7:59 AM, Joe Carlson<jwcarlson@lbl.gov>  wrote:
>>>
>>> The use case is genomics. Extracting substrings is common. So going to chunked storage makes sense.
>> Are you storing nucleotide sequences as text strings?  If using the simple 4-character (A,C,G,T) alphabet, you can store four bases per byte.  If using a nucleotide code 16-character alphabet you can still get two bases per byte.  An amino acid 20-character alphabet can be stored 8 bases per 5 bytes, and so forth.  Such a representation might allow you to store sequences two or four times longer than the limit you currently hit, but then you are still at an impasse.  Would a factor or 2x or 4x be enough for your needs?
>>
>> —
>> Mark Dilger
>> EnterpriseDB:http://www.enterprisedb.com
>> The Enterprise PostgreSQL Company
>>
>>
>>
>
>

-- 
Born in Arizona, moved to Babylonia.

view thread (15+ messages)  latest in thread

Message-ID: <4777754a-ad1f-eae3-f6c9-a4f783e803e3@gmail.com>
Permalink:  ../4777754a-ad1f-eae3-f6c9-a4f783e803e3@gmail.com/
Also on:    postgresql.org/message-id/4777754a-ad1f-eae3-f6c9-a4f783e803e3@gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: ronljohnsonjr@gmail.com
  Subject: Re: TEXT column > 1Gb
  In-Reply-To: <4777754a-ad1f-eae3-f6c9-a4f783e803e3@gmail.com>
  References: <85984fc4-6e08-06ad-78d7-d6f73a5c90a0@gmail.com> <0A8EB6F5-57BC-4519-8E9A-DBB809CC09D5@lbl.gov> <E18E02B1-39DE-4464-9C8C-F0C06BBF771B@enterprisedb.com> <A29ABFF1-8014-46B2-9425-34F59812581A@lbl.gov> <4777754a-ad1f-eae3-f6c9-a4f783e803e3@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox