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 1rjPk7-005PoW-BH for pgsql-general@arkaria.postgresql.org; Sun, 10 Mar 2024 20:26:00 +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 1rjPk4-006CmO-88 for pgsql-general@arkaria.postgresql.org; Sun, 10 Mar 2024 20:25:56 +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 1rjPk2-006CmF-Gp for pgsql-general@lists.postgresql.org; Sun, 10 Mar 2024 20:25:56 +0000 Received: from wfout8-smtp.messagingengine.com ([64.147.123.151]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rjPjy-003oH9-8s for pgsql-general@lists.postgresql.org; Sun, 10 Mar 2024 20:25:54 +0000 Received: from compute4.internal (compute4.nyi.internal [10.202.2.44]) by mailfout.west.internal (Postfix) with ESMTP id 0A0841C00092; Sun, 10 Mar 2024 16:25:44 -0400 (EDT) Received: from mailfrontend2 ([10.202.2.163]) by compute4.internal (MEProxy); Sun, 10 Mar 2024 16:25:45 -0400 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=1710102344; x=1710188744; bh=VQ5ru51DVUt3m/cCFH0iV+OhUhl1yhNttwn1hi4ms4k=; b= ty7/VlVw943uCM3G/ARD/ilGKnL8mCFK4V72u7FcNkHAtK47uSSgzj3rpWIQIdCP X0q5hQXocv3oU17IvWuMG+BtWOfMGxFHWDBOaUpSuHJKT46oIfz8VC+7Vepauy5z IELfMT1Lcc/QpYYjNMKl/SbN9ndglQSqRbCd7uKgpiDtgx5i5XTmCeYi1tTDPxrZ 9rP18F82LIWNKJNYRjm+m9GF5vsOGNBxRpBjS88GjQV/E3DwDTnmCrnSBjEXcUXs j3HRhBhBPFUv819bv7dbOk+ewoOt1TepsIgk4M2aaxGpFmQMEYfLhM35HazC2pt3 XMkbf566YYyPpP5nr+xc9Q== 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=1710102344; x= 1710188744; bh=VQ5ru51DVUt3m/cCFH0iV+OhUhl1yhNttwn1hi4ms4k=; b=F 8738AJkO+bu/0OFXrTMdts3u8yJQKYCj5w2Yg3haZeJdioz48jtPpXhduO9OoAPI XqQqgOToWsZbSRhkjo/4fJBFNnjuLBE2cUaAbnHmBNmCJCGcjyDOrNbAQZyTvmVI fDDrhdsP0nHfhSI8ZjIRNm+8QbpgVPIynI2LzXC27A+sMarVg21Rn9a2RJS08eVx 5RjEa7cDMwjCK7gIcoO6pvgrmgIYPCJhVimjca4IB1exoVm+sZVhSvmNBgZaXofZ QioP/XHzioBcSKda6cbZ/QJ9ou7HP/HY2/Ttob9C3HzBdgY8Mxz20s2snwAHoIJM M6qcd2h2lIQFJVOyU3AzA== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvledrieelgddufeekucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucenucfjughrpefkffggfgfuvfevfhfhjggtgfesth ekredttddvjeenucfhrhhomheptegurhhirghnucfmlhgrvhgvrhcuoegrughrihgrnhdr khhlrghvvghrsegrkhhlrghvvghrrdgtohhmqeenucggtffrrghtthgvrhhnpefgudefke fhveefleevieeuveehvdduudekuddvvdelhfeuueeijedtuedvvedvueenucffohhmrghi nhepphhoshhtghhrvghsqhhlrdhorhhgnecuvehluhhsthgvrhfuihiivgeptdenucfrrg hrrghmpehmrghilhhfrhhomheprggurhhirghnrdhklhgrvhgvrhesrghklhgrvhgvrhdr tghomh X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Sun, 10 Mar 2024 16:25:43 -0400 (EDT) Message-ID: <14f28b7a-9ea4-49b2-9000-b88dbe0332ec@aklaver.com> Date: Sun, 10 Mar 2024 13:25:42 -0700 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Question related to partitioning with pg_partman To: sud Cc: pgsql-general References: <7ca9af73-6cb2-4ac0-8dfb-b83a50c64363@aklaver.com> <46b8c853-a98c-41ee-b3f3-e1fcf11f26db@aklaver.com> <0a663c42-32fe-486e-8fca-63c0faa7b7c9@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 3/10/24 11:34, sud wrote: > > On Sun, Mar 10, 2024 at 11:31 PM Adrian Klaver > > wrote: > > 1) The partition will be across one day(24 hours) it is just the times > may confuse people. Per you example 2024-03-07 00:00:00+00  is the same > time as 2024-03-06 19:00:00-05 for EST. The issue is that the +00 and > -05 maybe ignored. Also it depends on the clients being consistent in > using timestamptz. > > 2) You still have not answered what the datetime range(not date range) > is that will be queried. If you have the partitions Midnight to > Midnight > UTC and the clients are querying Midnight to Midnight local time the > query will not match the partitions. > > >  My apology if not able to clearly put the details. Actually, the query > will always happen on a day basis i.e they can query from one day to 15 > days transactions. But as you rightly pointed , the partitions can only > span from midnight to midnight in one timezone, and thus users who > queries the data from another time zone will mostly scan two partitions > (even if they just queries one days transaction data in their own > timezone). And I don't see an easy solution for this , which will help > all users across all time zones to scan only a single partition in the > database, when they queries data for a single transaction date. > > And thus my question was, is it necessary to have the creation of > partitions to happen on UTC time zone only? and then whatever > transaction data inserted by the users from respective time zones will > be stored in the database as is and will be queried based on the user > timezone (it may span across multiple partitions though for a single > user transaction date). This is going to depend on many things. 1) Partitions are not free they have overhead, which is fine if the cost(overhead) is less then the benefits. For details on that see: https://www.postgresql.org/docs/current/ddl-partitioning.html and partition parts of https://www.postgresql.org/docs/current/sql-createtable.html As part of this there is the consideration of whether daily partitions are really what you want? 2) What you hope to get out of the partitioning? a) If it is confining queries to the partition boundaries then you have already stated that is not going to happen. b) If it is for data pruning purposes, then you have something to consider on both ends. Creating/dropping partitions with Midnight to Midnight UTC means you will need to consider whether they cover the range of datetimes that your users are interested in. In other words creating a partition ahead that covers local times that resolve to a UTC time in the 'future'. On the back end not dropping a partition until it has gone out of scope for everybody. To answer 1 & 2 you are probably going to need to create a test setup and verify how the expected queries are actually going to work with your partition scheme. -- Adrian Klaver adrian.klaver@aklaver.com