Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pmfjk-0003Dt-OM for pgsql-general@arkaria.postgresql.org; Wed, 12 Apr 2023 19:02:32 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pmfjj-00026m-Mw for pgsql-general@arkaria.postgresql.org; Wed, 12 Apr 2023 19:02:31 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pmfjj-00026d-BA for pgsql-general@lists.postgresql.org; Wed, 12 Apr 2023 19:02:31 +0000 Received: from mail-ot1-x32e.google.com ([2607:f8b0:4864:20::32e]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1pmfjg-002UjD-BK for pgsql-general@lists.postgresql.org; Wed, 12 Apr 2023 19:02:30 +0000 Received: by mail-ot1-x32e.google.com with SMTP id u24-20020a9d7218000000b006a413c893c8so2238237otj.10 for ; Wed, 12 Apr 2023 12:02:28 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1681326146; x=1683918146; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=qeCURCrMxDpx6c4DWaps/o8tbPnYEH3VZbDb7fDti78=; b=iO5A9f//v1RnjL6vcbIRJWv2jtyYXxZmdOWfPRRDlMC861MnITKR7XmUhsnRJajU1x pFx8w3DJ3wp6IK1CoA9QA7jc8Wm6uhHsJBiJKcC6ZI28OYB746kUcsaQgv5y4Ib2v1Yx /jL4mJjp+qG/ydE67tlY00BBwA3XzAxo+FN1IipYZsxRMj9EgkkwJZz3IYEcwKx6+86Z Yl/SoKd4WqMwJKFYTZwwCi0op2BCDF0Tjm68PIYGTFeY+t3yI5DIpoSVxl1pnKYsaobq wVQ3nO4F3cQVpX/gc3qnPqUiGHrAAfXDjTFadHF5n8v6ftnwKP4WwVM8g5jcD4uY6TH7 YnVA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1681326146; x=1683918146; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=qeCURCrMxDpx6c4DWaps/o8tbPnYEH3VZbDb7fDti78=; b=NxFsqj8o5RPnJZadKnTdAGxT5DI/VXLaOtk57xAcHMdwzlcUU23XtCBr+oylQOXvQN 1cpD7WOSilWnQHZ1vsSVLMt8+6ISBHC+CFrh8dtnjG3gl1vTmyMlfrWO2JRgVmNq4Ffb wn85tFXBGDGUgK4hRnyG9pNMkDWcnldga7Fbb2jscG2pQdH0r5ZwOPeFRUvxBxdIkyEG Ad4yUQjxOrHwpYEJlMmYHRBtaEFNlsah9ZYo+lcEuRvxEbKWg21UO9oA9OnFeLeeSRgR b8QiGaiJh+U2gk/k09+lf31A8dLAq8VvS3TzGMUVvlICC1cVFGFsaB8gMj1xvM+sRtae TOig== X-Gm-Message-State: AAQBX9fO82vhAh54edcskm/RxDbigmWfkQKF6MetrlL/mr4CNIH37Q8k utY4EFouYxVTm45Y+WsYF5aC7kM5K9c= X-Google-Smtp-Source: AKy350bZEDVoroXvEbH6pvOIzjdUFdyqnEHSpMzuaO/DKqJ8m3YAtmmieN1r2iyynhv51O+FbMQfJg== X-Received: by 2002:a9d:7514:0:b0:6a1:22fe:2a4b with SMTP id r20-20020a9d7514000000b006a122fe2a4bmr66974otk.18.1681326146347; Wed, 12 Apr 2023 12:02:26 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:7496:6a91:78d5:a628? ([2600:1700:bd2c:b810:7496:6a91:78d5:a628]) by smtp.googlemail.com with ESMTPSA id e8-20020a9d7308000000b006a42bbb998asm442776otk.75.2023.04.12.12.02.25 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Wed, 12 Apr 2023 12:02:26 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------3YZtGTQAo0Nwvogyb3hvNL3i" Message-ID: <4777754a-ad1f-eae3-f6c9-a4f783e803e3@gmail.com> Date: Wed, 12 Apr 2023 14:02:25 -0500 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.9.0 Subject: Re: TEXT column > 1Gb Content-Language: en-US To: pgsql-general@lists.postgresql.org References: <85984fc4-6e08-06ad-78d7-d6f73a5c90a0@gmail.com> <0A8EB6F5-57BC-4519-8E9A-DBB809CC09D5@lbl.gov> From: Ron In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------3YZtGTQAo0Nwvogyb3hvNL3i Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit /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 wrote: >> >> >> >>> On Apr 12, 2023, at 7:59 AM, Joe Carlson 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. --------------3YZtGTQAo0Nwvogyb3hvNL3i Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit 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.
--------------3YZtGTQAo0Nwvogyb3hvNL3i--