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 1pmh5j-0008BO-0h for pgsql-general@arkaria.postgresql.org; Wed, 12 Apr 2023 20:29:19 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pmh5h-0008Tf-TA for pgsql-general@arkaria.postgresql.org; Wed, 12 Apr 2023 20:29:17 +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 1pmh5h-0008TW-Ia for pgsql-general@lists.postgresql.org; Wed, 12 Apr 2023 20:29:17 +0000 Received: from mail-yb1-xb2d.google.com ([2607:f8b0:4864:20::b2d]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1pmh5e-002VPC-Qe for pgsql-general@lists.postgresql.org; Wed, 12 Apr 2023 20:29:17 +0000 Received: by mail-yb1-xb2d.google.com with SMTP id e127so13185457ybf.8 for ; Wed, 12 Apr 2023 13:29:14 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1681331352; x=1683923352; 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=B2ocHIFeAPmiKcYSUkbBl6ReROHWhbOfvDkAghFffWM=; b=WSWo2CfhC3PVIoWBC8Akfy77Gw3zMybia4bCPeW5oVtr2oWf2O8r7V9+yzI4VkrebY TkJk38FpxR6CvlrCbXAxBTSPVUqGbFx3pfnMvu5bW5YkyiEBXCWfP4RJSZ+Xz5S6xnCG vSuM6f9+++oF2fDxcWH/FbgtOzbv7So4JiZGwELvinsvph9EuzckcBFPwOMy63E8AA0Z ubfP022AAvzJ/HWNGei2Rh8jL7M8jlDkcpwREw1gWl6zd5ZMywhX7IZlwF5lewxjQb3O AjOHdUJSk7FL5XiiOzg/SIYXasD0gDFhtd5vjHInyfzfC9o2LXoqmyi9Cx+15T143Vj6 ZqbA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1681331352; x=1683923352; 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=B2ocHIFeAPmiKcYSUkbBl6ReROHWhbOfvDkAghFffWM=; b=j7f8H7Y90YR9IOsd7NznS9+Fuwh+wfWJEciQZrRJscDDDqjP1l6nDkkWKz1koLtFWF QtGG5DGiTLR+CiSH+YjC4bbTeMjSHmyVIH1y9ElsySa37u3Ou9jmlCZXzJe289VwzpuD viNn+eNHFx5SHRnoy+ZqT/EcU0VqFY6r7ENK0L/yXOVHTcn96P02awvcphekLGJz1+y/ Pt7cguYg36KsRNDUCJ2Y3QfXwra6XcNL0VV+I8iYulmUOrWwFNAXaefStvKvpClpMe3r voC+7c2kWcOAFJPICJhm8z15E0rZKoWTeZEBaRje0sjB9521koE3TsPEx5a5FtvSfRUy i/qw== X-Gm-Message-State: AAQBX9e2RyZiJVqLzfeQkGCUNsT4UXZlohD3lIpIEg+DYgzOlCDVrXIM G4z7AoZGULv3F6Sh++8xMntLubHE7Rc= X-Google-Smtp-Source: AKy350a1B0IdbhjxKRBan21zR08SWZ8G69aBa8cwq3PJaUwf6QYhseQ8JNTPnloOhVM/CVea2CjEJw== X-Received: by 2002:a05:6902:702:b0:b8f:3037:1375 with SMTP id k2-20020a056902070200b00b8f30371375mr6360530ybt.9.1681331352520; Wed, 12 Apr 2023 13:29:12 -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 5-20020a251005000000b00b7767ca7473sm4548592ybq.16.2023.04.12.13.29.12 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Wed, 12 Apr 2023 13:29:12 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------lWy00PFWQEVbHH3SwbcgYs0X" Message-ID: <05437ceb-4e0b-d1e9-4339-926122773945@gmail.com> Date: Wed, 12 Apr 2023 15:29:10 -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> <4777754a-ad1f-eae3-f6c9-a4f783e803e3@gmail.com> <04ab2b33-be6e-5073-1004-31a7117531af@gmail.com> From: Ron In-Reply-To: <04ab2b33-be6e-5073-1004-31a7117531af@gmail.com> 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. --------------lWy00PFWQEVbHH3SwbcgYs0X Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 4/12/23 14:21, 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? > > Not sure what OP is doing with plant genomes (other than some genomics) > but the tools all use files and pipeline of sub-tools. In and out of > tuples would be expensive.  Very,very little "editing" done in the usual > "update table set val where id" sense. > > Lines in a vcf file can have thousands of colums fo nasty, cryptic garbage > data that only really makes sense to tools, reader. Highly denormalized of > course.  (Btw, I hate sequencing :) ) My thinking is that you'd never hit a 1GiB limit if every line in the vcf file was in it's it's own row in a table. Performance /would/ drop, though. -- Born in Arizona, moved to Babylonia. --------------lWy00PFWQEVbHH3SwbcgYs0X Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit On 4/12/23 14:21, 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?

Not sure what OP is doing with plant genomes (other than some genomics) but the tools all use files and pipeline of sub-tools.  In and out of tuples would be expensive.  Very,very little "editing" done in the usual "update table set val where id" sense.

Lines in a vcf file can have thousands of colums fo nasty, cryptic garbage data that only really makes sense to tools, reader.  Highly denormalized of course.  (Btw, I hate sequencing :) )

My thinking is that you'd never hit a 1GiB limit if every line in the vcf file was in it's it's own row in a table.

Performance would drop, though.

--
Born in Arizona, moved to Babylonia.
--------------lWy00PFWQEVbHH3SwbcgYs0X--