Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rIxHp-00B4vN-Ds for pgsql-general@arkaria.postgresql.org; Thu, 28 Dec 2023 20:47:26 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rIxHn-0022UN-Cp for pgsql-general@arkaria.postgresql.org; Thu, 28 Dec 2023 20:47:23 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rIxHl-0022UE-KC for pgsql-general@lists.postgresql.org; Thu, 28 Dec 2023 20:47:23 +0000 Received: from wout5-smtp.messagingengine.com ([64.147.123.21]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rIxHh-00EMei-SH for pgsql-general@lists.postgresql.org; Thu, 28 Dec 2023 20:47:21 +0000 Received: from compute6.internal (compute6.nyi.internal [10.202.2.47]) by mailout.west.internal (Postfix) with ESMTP id 28AD73200A08; Thu, 28 Dec 2023 15:47:13 -0500 (EST) Received: from mailfrontend1 ([10.202.2.162]) by compute6.internal (MEProxy); Thu, 28 Dec 2023 15:47:13 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:cc:content-transfer-encoding:content-type:content-type:date :date:from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:subject:subject:to:to; s=fm1; t=1703796432; x=1703882832; bh=6ytKy7inc3HNq4Ftr8eOMPSKFVSttGme5H7ColwnvCk=; b= URqwvjET9lO/VEFxI3bIjv6zp9SIKnVDcmA8kMZVD2Iqwk7q36Fli3ggkU23o5W7 59B0SyCLJMTlGBXRMwRcCvqpRaQOLx5LqcsUH28uNfQ7IjMKDX/rZqsBgjC0i+1n nxbM3r8fzhYlfJGPHi/ZzYO1ElYD1I5wuesIl9lcwxutUyJDfu/yk5x/n4oxLKUI Zi8beq9bOMQBUU6eURY5N7pJyBwaetVv6GD27HKT3WHHV3bbcE1m7cV1A2Nl/Dw5 DlvvwxO4SmrbsPqOR7vVDkCQhCdauC0Ps/Z1Ui0w1ZgoWShZDSI4INRoXzCeMqPD 6vgmPKgx8Z+oWQ5gZvhfVw== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:cc:content-transfer-encoding :content-type:content-type:date:date:feedback-id:feedback-id :from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:subject:subject:to:to:x-me-proxy:x-me-proxy :x-me-sender:x-me-sender:x-sasl-enc; s=fm2; t=1703796432; x= 1703882832; bh=6ytKy7inc3HNq4Ftr8eOMPSKFVSttGme5H7ColwnvCk=; b=n aYi8iPlbzVBleNXxaa1NWUzP5RFA1TLRovL61Q3fvAVSR6rnjWaNZbDFx+Y9UlCJ gkvDa0Dr00EyxXoKHAF9oCQrUARIoaj4uM6BnxNvKWeZFlGgoPNlG5gDv9eaUW2u r5liPHUklspYy4GI6F/CMvG5Ar3akZmAbBCD8Tamp33ybwF+5dhH8UkR6zjHyaBb WS1Cwk8yLy8QFzkuV1kaAgxn7UPlbolsELnzFadchfX6Yw22EJ5dhoGXyNpxoUbh LXy8AFXoMD4OqYlZUcv/JGqIoBrLjZrUmD2oWvK3Cfvl2bfZ5r4wUwg9FPPpfh8x o6fiCsvpl+wt5opPLTYkA== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvkedrvdefuddgudegvdcutefuodetggdotefrod ftvfcurfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfgh necuuegrihhlohhuthemuceftddtnecusecvtfgvtghiphhivghnthhsucdlqddutddtmd enucfjughrpefkffggfgfuvfevfhfhjggtgfesthekredttddvjeenucfhrhhomheptegu rhhirghnucfmlhgrvhgvrhcuoegrughrihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrd gtohhmqeenucggtffrrghtthgvrhhnpeefgeefieeutdfggfetgefgheekjeehteeileei gfetieekjedvieeviefgheevtdenucevlhhushhtvghrufhiiigvpedtnecurfgrrhgrmh epmhgrihhlfhhrohhmpegrughrihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrdgtohhm X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Thu, 28 Dec 2023 15:47:11 -0500 (EST) Message-ID: Date: Thu, 28 Dec 2023 12:47:10 -0800 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Increased storage size of jsonb in pg15 To: Sean Flaherty Cc: "David G. Johnston" , pgsql-general@lists.postgresql.org References: <87680387-ed25-41ff-b6de-37e357398df9@aklaver.com> <05dd34df-0fc3-4727-aedb-6232d1ccdd61@aklaver.com> <23b98884-10e6-4a2c-af8d-a0fad18c9f3c@aklaver.com> Content-Language: en-US From: Adrian Klaver 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 12/28/23 12:41, Sean Flaherty wrote: > I'm rather new to the mailing list, are there any additional steps I > should take (i.e. posting to pgsql-hackers, etc.)? > For what purpose? You are seeing differences in compression strategies between lz4 and pglz. The 'fix' would be to go back to pglz. > > On Thu, Dec 28, 2023 at 11:23 AM Adrian Klaver > > wrote: > > On 12/28/23 09:13, Sean Flaherty wrote: > > Follow-up: > > Working with AWS, we found that starting in RDS Postgres 15, the > > default_toast_compression parameter is set to use lz4 compression > > instead of pglz.  This resulted in the increased json storage > size we > > were seeing. > > > > I have been able to reproduce the increased storage size on RDS > Postgres > > and using my local docker instance of postgres 15.5 by changing the > > local default_toast_compression value in postgresql.conf. > > > > I have attached the test script we use to create a table, insert > some > > test records and a query to test the JSON data size on disk. > > I can confirm I see the same results using Postgres 16 installed from > the PGDG repo on Ubuntu 22.04. That the lz4 data size is greater then > the pglz data size. > > > > > > > Kind regards, > > Sean > > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com > -- Adrian Klaver adrian.klaver@aklaver.com