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 1pmi2D-00037b-3q for pgsql-general@arkaria.postgresql.org; Wed, 12 Apr 2023 21:29:45 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pmi2A-0002eq-9N for pgsql-general@arkaria.postgresql.org; Wed, 12 Apr 2023 21:29:42 +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 1pmi29-0002eh-Rp for pgsql-general@lists.postgresql.org; Wed, 12 Apr 2023 21:29:41 +0000 Received: from mail-pj1-x1029.google.com ([2607:f8b0:4864:20::1029]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1pmi26-002Vys-K3 for pgsql-general@lists.postgresql.org; Wed, 12 Apr 2023 21:29:40 +0000 Received: by mail-pj1-x1029.google.com with SMTP id my14-20020a17090b4c8e00b0024708e8e2ddso2135018pjb.4 for ; Wed, 12 Apr 2023 14:29:38 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1681334977; x=1683926977; h=in-reply-to:from:references:cc:to:content-language:subject :user-agent:mime-version:date:message-id:from:to:cc:subject:date :message-id:reply-to; bh=lcoVlj8N+vsAnmf/xznRFf2drljoGeTlhlqY0/2LjPA=; b=RD4CSJ1BzfkP2vVYaD+3VillaTCaJ+BnO02ljV6BNFp8vGerk5MpcLQ/tCanvDZUT8 xT2kxbtySmsi1gh+IMztXz5lhl74EC3Dnx+gQyYaVeOYundHODTih6g7sk55f5s7QWLY iSrOHvoovMagwewjrufs2HkdBH+E+Nvvy8dKlQmPaHk4fC4cjaZ6dRDC2jOKmhvPAZKp /iV0u7a7RIEp1P5cW6bIMH37REoBV4T923hCvyrkQ3v6nT9REz+FFkMb16cOgYlSOq8J VXFSyk8/+xuB/XbVYhNY4oeCkD7+REjIoSLWtnKkNnefExN769dfsXgTvydf0NbGZ1oT FkZA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1681334977; x=1683926977; h=in-reply-to:from:references:cc: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=lcoVlj8N+vsAnmf/xznRFf2drljoGeTlhlqY0/2LjPA=; b=UpOZQrp0exHc7X1q4X+QyyjK4iA5XkOUrE4ycJcSJh7SlIdDHts32eZWGie9H2+ua+ pRKfLCmqjpqHi2BRzRPC4kQWpc2DdTiH4OCErUdKF9XZdQ62jilW1fOCKkk1d4tJV2sA SkiiyPpgrPKr/9rbtcPtqRl72oL9+3YlfV+9z4XGoCdMvg/Bsig0tfCoeZeqSTeVr5dp SnixyVv4g2ZDwrjUjq3m0GFNSZEDrmCAaupq1815fwtucaU2VcIqx6j8GCHNVjvaEeHf y8R1UqegLoCHIGXHorM6aQzmfkAiHhNBF9Cbd6xN3P+0O5Kb8AQFUtn1q2AnXrTJwq0j FEzQ== X-Gm-Message-State: AAQBX9dAd1EiovgOV1YwJqeZsnyFeVI0ITX1POBFESLShavRHX14Dvh9 DYz86uodMjlu0JgXulSfBXE= X-Google-Smtp-Source: AKy350ajUcHaD/m2txPmIhNMsNo7b6t3C/Wbx3HtqJom1IPcEHtwQHXo+VjkM13OLhyYAve69d6SQQ== X-Received: by 2002:a17:902:d482:b0:1a6:45e5:a26a with SMTP id c2-20020a170902d48200b001a645e5a26amr379876plg.27.1681334976597; Wed, 12 Apr 2023 14:29:36 -0700 (PDT) Received: from ?IPV6:2601:681:5a00:f790:7096:ab4:cda6:9ce7? ([2601:681:5a00:f790:7096:ab4:cda6:9ce7]) by smtp.gmail.com with ESMTPSA id 12-20020a170902ee4c00b001a6401189a6sm44844plo.147.2023.04.12.14.29.35 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Wed, 12 Apr 2023 14:29:36 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------8m0yV7VkH4UVwZiaKSyMJY0b" Message-ID: <4e976074-5708-01a8-4cbf-f317e4f424b0@gmail.com> Date: Wed, 12 Apr 2023 15:29:34 -0600 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-CA To: Joe Carlson Cc: pgsql-general@lists.postgresql.org References: <85984fc4-6e08-06ad-78d7-d6f73a5c90a0@gmail.com> <0A8EB6F5-57BC-4519-8E9A-DBB809CC09D5@lbl.gov> <4777754a-ad1f-eae3-f6c9-a4f783e803e3@gmail.com> <04ab2b33-be6e-5073-1004-31a7117531af@gmail.com> <131DA24F-23D6-4EA1-816F-ED0E6E5A219D@lbl.gov> From: Rob Sargent In-Reply-To: <131DA24F-23D6-4EA1-816F-ED0E6E5A219D@lbl.gov> 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. --------------8m0yV7VkH4UVwZiaKSyMJY0b Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 4/12/23 15:03, Joe Carlson wrote: > > >> On Apr 12, 2023, at 12:21 PM, Rob Sargent wrote: >> >> On 4/12/23 13:02, Ron wrote: >>> /Must/ the genome all be in one big file, or can you store them one >>> line per table row? > > The assumption in the schema I’m using is 1 chromosome per record. > Chromosomes are typically strings of continuous sequence (A, C, G, or > T) separated by gaps (N) of approximately known, or completely unknown > size. In the past this has not been a problem since sequenced > chromosomes were maybe 100 megabases. But sequencing is better now > with the technology improvements and tackling more complex genomes. So > gigabase chromosomes are common. > > A typical use case might be from someone interested in seeing if they > can identify the regulatory elements (the on or off switches) of a > gene. The protein coding part of a gene can be predicted pretty > reliably, but the upstream untranslated region and regulatory elements > are tougher. So they might come to our web site and want to extract > the 5 kb bit of sequence before the start of the gene and look for > some of the common motifs that signify a protein binding site. Being > able to quickly pull out a substring of the genome to drive a web app > is something we want to do quickly. >> Well if you're actually using the sequence, both text and bytea are inherently substring friendly.  Your problem goes back to transferring large strings and that's where http/tomcat is you friend.  Sounds like you're web friendly already.  You have to stream from the client/supplier, of course. --------------8m0yV7VkH4UVwZiaKSyMJY0b Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
On 4/12/23 15:03, Joe Carlson wrote:


On Apr 12, 2023, at 12:21 PM, Rob Sargent <robjsargent@gmail.com> wrote:

On 4/12/23 13:02, Ron wrote:
Must the genome all be in one big file, or can you store them one line per table row?

The assumption in the schema I’m using is 1 chromosome per record. Chromosomes are typically strings of continuous sequence (A, C, G, or T) separated by gaps (N) of approximately known, or completely unknown size. In the past this has not been a problem since sequenced chromosomes were maybe 100 megabases. But sequencing is better now with the technology improvements and tackling more complex genomes. So gigabase chromosomes are common. 

A typical use case might be from someone interested in seeing if they can identify the regulatory elements (the on or off switches) of a gene. The protein coding part of a gene can be predicted pretty reliably, but the upstream untranslated region and regulatory elements are tougher. So they might come to our web site and want to extract the 5 kb bit of sequence before the start of the gene and look for some of the common motifs that signify a protein binding site. Being able to quickly pull out a substring of the genome to drive a web app is something we want to do quickly. 


Well if you're actually using the sequence, both text and bytea are inherently substring friendly.  Your problem goes back to transferring large strings and that's where http/tomcat is you friend.  Sounds like you're web friendly already.  You have to stream from the client/supplier, of course.



--------------8m0yV7VkH4UVwZiaKSyMJY0b--