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 1rDoNp-003DBU-KF for pgsql-general@arkaria.postgresql.org; Thu, 14 Dec 2023 16:16:22 +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 1rDoNn-009tVH-J9 for pgsql-general@arkaria.postgresql.org; Thu, 14 Dec 2023 16:16:19 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rDoNl-009tV9-P9 for pgsql-general@lists.postgresql.org; Thu, 14 Dec 2023 16:16:19 +0000 Received: from wout4-smtp.messagingengine.com ([64.147.123.20]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rDoNi-00AO9M-2C for pgsql-general@lists.postgresql.org; Thu, 14 Dec 2023 16:16:16 +0000 Received: from compute2.internal (compute2.nyi.internal [10.202.2.46]) by mailout.west.internal (Postfix) with ESMTP id 41DAB3200CDD; Thu, 14 Dec 2023 11:16:11 -0500 (EST) Received: from mailfrontend2 ([10.202.2.163]) by compute2.internal (MEProxy); Thu, 14 Dec 2023 11:16:11 -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=fm3; t=1702570570; x=1702656970; bh=1H23i+hcPfwNNkxabpdCP+iyHTyFAFFiwxf3g8aJXDE=; b= HiRFisM52JFF1t/g8+tMp6PIqJ22ZQxXPFBPIF9gFVUOO/hfJ33t+4LEFRPX6otJ KJ2/5f14hH7FwO73cOqs1XLFX382b7T4raPXhkZ4JK2sabw8tuxLSUQnMepcmF7k xzt5YSDBPBDUn6c8lFcAf74Mi9TRunyAoKhYtetZqhc9r7Ysfnj3gemMraxtBZP4 qWJl1UuKdSvDYkaKlALXu9cE9bZgcrqbthzKp+PU029osq9w0mTyaorT8gDi5PgA Vgff2400Q9BBo25V4N3QXc3BVWSKgwhHWoggUgxtcTLO6sA8Mi8uhazB1BAZsSWJ eDbpmmdDGwD7Bm05OazE8g== 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=fm1; t=1702570570; x= 1702656970; bh=1H23i+hcPfwNNkxabpdCP+iyHTyFAFFiwxf3g8aJXDE=; b=X qBYwOHiqwKIn6BMm1XOasPCIusdoJ0i3usY6KikFdHUXDBT/qcZIg/bpgygaWdde o6xCIu4Qq26eTLuDenjzuCoFOlMbRwntM8mHcU56rA/DlHov3ZSLX3ykxQHA9Lcm jvkmR3cZQlhkcognstNfGPEEnk7lZ0mX/NmDvr+fct/z087fOt3NLtVrbfi1KijY P5h9w+sQOigRtiO1ugFzImhifqxwvkxOBLXhwE31xccPTFaD2UusvtP/jSiH36jR TUyqsBOIpzcJKesAT/QOi1VwTGdYux2NdoGYNMmdVCJjTjFIkeyxR/Zh+iRZHCL+ t6YpwfNLRi1YB/e4Sl6hA== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvkedrudelledgkeehucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucesvcftvggtihhpihgvnhhtshculddquddttddmne cujfgurhepkfffgggfuffvvehfhfgjtgfgsehtkeertddtvdejnecuhfhrohhmpeetughr ihgrnhcumfhlrghvvghruceorggurhhirghnrdhklhgrvhgvrhesrghklhgrvhgvrhdrtg homheqnecuggftrfgrthhtvghrnhepfedvudevudegteegveejleeuueefhffggfeijeff vdeuiedugfelfeejveeggeeinecuffhomhgrihhnpehgihhthhhusgdrtghomhenucevlh hushhtvghrufhiiigvpedtnecurfgrrhgrmhepmhgrihhlfhhrohhmpegrughrihgrnhdr khhlrghvvghrsegrkhhlrghvvghrrdgtohhm X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Thu, 14 Dec 2023 11:16:09 -0500 (EST) Message-ID: Date: Thu, 14 Dec 2023 08:16:09 -0800 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Increased storage size of jsonb in pg15 To: Sean Flaherty Cc: pgsql-general@lists.postgresql.org References: <87680387-ed25-41ff-b6de-37e357398df9@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/14/23 06:48, Sean Flaherty wrote: > We have a process that runs once an hour to read the .dat file in csv > format then a node script using the pg package > version "8.8.0" to create the > json objects and insert the data records as jsonb data. Now I am not understanding. 1) In your OP you mentioned checking size of the column storage using pg_column_size, yet what you show for increase in size are datafile.dat. 2) So how is datafile.dat related to this issue? 3) Show how you are determining that the storage in the database has increased in size. > > None of the upload process changed during the underlying database upgrade. > > > On Wed, Dec 13, 2023 at 4:56 PM Adrian Klaver > wrote: > > On 12/13/23 15:49, Sean Flaherty wrote: > > More information needed: > > > > > 2) An example of reported size for the 14.? and 15.5 cases. > > > >    Since upgrading from 14.8 to 15.5, the jsonb data that was > previously > > written in 14.8 is reporting a smaller size than the same hourly > data > > written after the upgrade (upgrade indicated in yellow): > > What is producing datafile.dat and how? > > > > > *file*        *hourly_timestamp*      *filename_bytes* > *timestamp_bytes* > > *data_filesize*       *created_at_bytes*      *updated_at_bytes* > > datafile.dat  2023-10-19 12:00:00     23      8       1682    8 >      8 > > datafile.dat  2023-10-19 13:00:00     23      8       1687    8 >      8 > > datafile.dat  2023-10-19 14:00:00     23      8       1685    8 >      8 > > datafile.dat  2023-10-19 15:00:00     23      8       1668    8 >      8 > > datafile.dat  2023-10-19 16:00:00     23      8       2155    8 >      8 > > datafile.dat  2023-10-19 17:00:00     23      8       2178    8 >      8 > > datafile.dat  2023-10-19 18:00:00     23      8       2199    8 >      8 > > datafile.dat  2023-10-19 19:00:00     23      8       2187    8 >      8 > > datafile.dat  2023-10-19 20:00:00     23      8       2180    8 >      8 > > datafile.dat  2023-10-19 21:00:00     23      8       2176    8 >      8 > > datafile.dat  2023-10-19 22:00:00     23      8       2053    8 >      8 > > datafile.dat  2023-10-19 23:00:00     23      8       2043    8 >      8 > > -- > Adrian Klaver > adrian.klaver@aklaver.com > -- Adrian Klaver adrian.klaver@aklaver.com