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 1pmdST-00042W-CY for pgsql-general@arkaria.postgresql.org; Wed, 12 Apr 2023 16:36:33 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pmdSR-0005zN-W8 for pgsql-general@arkaria.postgresql.org; Wed, 12 Apr 2023 16:36:32 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pmdSR-0005zE-LO for pgsql-general@lists.postgresql.org; Wed, 12 Apr 2023 16:36:31 +0000 Received: from mail-pj1-x1031.google.com ([2607:f8b0:4864:20::1031]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1pmdSP-0002QL-Jh for pgsql-general@lists.postgresql.org; Wed, 12 Apr 2023 16:36:30 +0000 Received: by mail-pj1-x1031.google.com with SMTP id h24-20020a17090a9c1800b002404be7920aso12179371pjp.5 for ; Wed, 12 Apr 2023 09:36:29 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1681317388; x=1683909388; h=content-transfer-encoding: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=TkXs5hzKanIpJPoJbnxp40erbeRNzje9F+BnmNUQREs=; b=QJkbwRQr805Tzj1sCHQwSM0oC7uLHH4DsNzwR8T++p+4+uzaMdH0T8FTY7Vu6ePGYx 6hkqwwqQ0yqkhvD2d07yZIK0MkHTZ2C0xQx6XRsRnFf9/uely9Ff8MgEv/ABrrCottKf n6F5qeduWWQRn/yn/I8haR5o776d37WAQsd3Q1uFaTGDqre3udedN/JWm9iWCcVksI99 mCsa0Ve4BK8x0eKUhXXJkKUvxYCo4CrRvXIF18ImcwrnLXshb+lw/u6Fbmz0CEr1q5Y1 JH6sHbzi24iMoa5R2FHw/xRzrfLhTHsCR35MxDFGXUSc3aqocc1HsTS8Em0JDIKNeVmr 5vng== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1681317388; x=1683909388; h=content-transfer-encoding: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=TkXs5hzKanIpJPoJbnxp40erbeRNzje9F+BnmNUQREs=; b=Pls9g6fNZblaR/K4Peu86T/BadwfDerQlSGgenw9TFSEcU2zMOJgiOMKlqouMbtE+/ A7HswR6Yq/iK3akGHc9b5Q++rhJrOrqtm1ooyXNmHpytw8N8qsPlItZDFahAApVHcw+J bQvwwjHehnK5DM+1LD9pIBICNbs3/x5O4aJKPljDedEIAkIdPo34e7D6R/xwYcX+ad4l VgbvfKBupo0G7QrquM1/sq+tVlqbih9ojuusvXZvn1JVzeAA2cz4lDOzGnZygmFxXJKQ hCpiamy8ijrqbwuDg6a4LlgX3avxnFzaq3LEQn9r65fIzJB0mfODyFgni6OvrPSf4okx IUQw== X-Gm-Message-State: AAQBX9ePr9oT9Ug9YnrprouQx542u5yg4SgnwE9ffQo2mKkOy5XPeGR+ iojzxCQielfFX4GFCx+3Ax0Df5lvD+M= X-Google-Smtp-Source: AKy350ZFJQq0YDms98DFsBozKDNTzyv8Pzsuxii0REvXJWnJ1CihPHHaNKcFLQiXzTP8TayxDJGrzQ== X-Received: by 2002:a17:902:f7cb:b0:1a6:4770:8383 with SMTP id h11-20020a170902f7cb00b001a647708383mr2956058plw.29.1681317388325; Wed, 12 Apr 2023 09:36:28 -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 l2-20020a170902d34200b001a1abc93eabsm11840500plk.226.2023.04.12.09.36.26 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Wed, 12 Apr 2023 09:36:27 -0700 (PDT) Message-ID: Date: Wed, 12 Apr 2023 10:36:25 -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> From: Rob Sargent In-Reply-To: <0A8EB6F5-57BC-4519-8E9A-DBB809CC09D5@lbl.gov> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 4/12/23 08:59, Joe Carlson wrote: > I’m curious what you learned. I’ve been tripping over the buffer > allocation issue when either splitting input text into chunks or > aggregating chunks in selects. I’ve decided that I need to move this > to client side. > > The use case is genomics. Extracting substrings is common. So going to > chunked storage makes sense. > > And we have a menagerie of code to deal with. Legacy Perl for loading. > Clients in Perl, Java, node, python. A whole zoo. > > Thanks My first stab was a java implementation based Selector class which worked ok using a fixed buffer size but finding the end of last transmission was hokey.  I move to an embedded Tomcat implementation and tomcat does the heavy lifting PERFECTLY.  Easily slipped in to both client and backend. rjs