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 1pmePT-0007HH-L1 for pgsql-general@arkaria.postgresql.org; Wed, 12 Apr 2023 17:37:31 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pmePS-0002aA-0p for pgsql-general@arkaria.postgresql.org; Wed, 12 Apr 2023 17:37:30 +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 1pmePR-0002a0-MS for pgsql-general@lists.postgresql.org; Wed, 12 Apr 2023 17:37:29 +0000 Received: from mail-pj1-x1034.google.com ([2607:f8b0:4864:20::1034]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1pmePK-002Tl6-Jv for pgsql-general@lists.postgresql.org; Wed, 12 Apr 2023 17:37:28 +0000 Received: by mail-pj1-x1034.google.com with SMTP id 98e67ed59e1d1-246a1c48021so580411a91.3 for ; Wed, 12 Apr 2023 10:37:22 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1681321040; x=1683913040; h=content-transfer-encoding:in-reply-to:from:content-language :references:cc:to:subject:user-agent:mime-version:date:message-id :from:to:cc:subject:date:message-id:reply-to; bh=mg0MADXpVfU+xG/i8IA0/oMaFDH8Adwg5B6SsCU1bow=; b=INNS0aD5iBSwdZdRSMYVdKkbtvIspf9GPRmf6vLjY7YqaXZOd9vDbGTpKG/sdyoU0Q R3Ue73Cp9ggpcn21AxfBj0to5aEPg0hlRvBgpas/+lOi2lQrWKDBSjd+ijg6toAoIje+ 0xHmXCbXPPnF4ePKPi6clb3yFrSpDlKTwL47+wV/KD6FDlr8Wx12bKJb3GyP+G7lqkN9 6lGemoQBy1FDuetD9F0yI8E3Xr8ImqcCNoIzghJQVN5qjqgTPyZIqb7LONjednmJRXbh IVex8UOwfnsH1sXEPkBs3aiaLRxBeIUUOeIFv4sUky8MNBhwp/UW1XK0544u2ugEHOZw jwrw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1681321040; x=1683913040; h=content-transfer-encoding:in-reply-to:from:content-language :references:cc:to:subject:user-agent:mime-version:date:message-id :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=mg0MADXpVfU+xG/i8IA0/oMaFDH8Adwg5B6SsCU1bow=; b=AhMzZUP+/8zHcIegeodtOCWnkSVlR/legRahGw30UWxNmgbnYRXrDtDs0fgIu/I7nD MGkH8Uzj0hNrtNhc5fVXKGutW/gmNHONhYY5+GiFYg2GgsCFsN/mXNeJPnL2/LoV4ksU jv2GnPtAIKxAY3l9z9xhfJeEVPeMIQjFxFfak3wTeeGyL8Yc1I0iDX4wpe5onVGlUXOn bnfi6iWRwE4Xyo+RpMJH7DJzmBgQ+38DVR5EhmYShyGcozjvDXz+sh4JbHm1U+4lKAAw EuPk04sSQ0oXxJd5klcovL5sOksdBmhPLrOzqGPp47oYQjABZHpRG57Yh5jx8/oQKstX PEjQ== X-Gm-Message-State: AAQBX9dHS4y2qfXLQHJ+R53wq6ZoAbaO3eF73d5k2wDh8FJYGOXki4D8 h3KuC3Vma3noeGMA+8y7No4= X-Google-Smtp-Source: AKy350bt4tAeKn8y5oEia6oN3vOUL20YOOTRZ8gywcXrDZziJ+ryhKHdoEfMBK7vjky24HKpxP3IKQ== X-Received: by 2002:aa7:9697:0:b0:5a0:c4b6:edd6 with SMTP id f23-20020aa79697000000b005a0c4b6edd6mr20112055pfk.0.1681321040098; Wed, 12 Apr 2023 10:37:20 -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 u12-20020a62ed0c000000b0062e23e81b24sm12001374pfh.114.2023.04.12.10.37.19 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Wed, 12 Apr 2023 10:37:19 -0700 (PDT) Message-ID: <51512362-ab2d-e927-d520-4bd67517273b@gmail.com> Date: Wed, 12 Apr 2023 11:37:18 -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 To: Benedict Holland , Joe Carlson Cc: Mark Dilger , pgsql-general References: <85984fc4-6e08-06ad-78d7-d6f73a5c90a0@gmail.com> <0A8EB6F5-57BC-4519-8E9A-DBB809CC09D5@lbl.gov> Content-Language: en-CA From: Rob Sargent In-Reply-To: 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 11:24, Benedict Holland wrote: > For documents that long I would seriously consider using large objects > and refencing them with their OIDs. Text fields get put in a special > location within the database. It's similar (possibly exactly) to using > large objects. Also, you can potentially compress them to save space > on write and read. 1gb of text is a lot of text. > I've gone down the premature optimization coded/compressed route and am still regretting it. I would either not bother or move to LO as per Benedict's suggestion.  The test may come to whether you plan to use database features (functions, procedures) to work on the data versus feeding it out to the endless list of sequence/genetic analysis tools.   General compression is robust and fast.  My custom code/comp is likely neither :( .