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 1qMAJF-001YU9-9c for pgsql-general@arkaria.postgresql.org; Wed, 19 Jul 2023 16:45:53 +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 1qMAJD-007qDH-LC for pgsql-general@arkaria.postgresql.org; Wed, 19 Jul 2023 16:45:51 +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 1qMAJC-007qD9-Ll for pgsql-general@lists.postgresql.org; Wed, 19 Jul 2023 16:45:51 +0000 Received: from out2-smtp.messagingengine.com ([66.111.4.26]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qMAJ3-000TiG-Ur for pgsql-general@lists.postgresql.org; Wed, 19 Jul 2023 16:45:50 +0000 Received: from compute5.internal (compute5.nyi.internal [10.202.2.45]) by mailout.nyi.internal (Postfix) with ESMTP id AF4815C00C1; Wed, 19 Jul 2023 12:45:41 -0400 (EDT) Received: from mailfrontend1 ([10.202.2.162]) by compute5.internal (MEProxy); Wed, 19 Jul 2023 12:45:41 -0400 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 :reply-to:sender:subject:subject:to:to:x-me-proxy:x-me-proxy :x-me-sender:x-me-sender:x-sasl-enc; s=fm3; t=1689785141; x= 1689871541; bh=aQBSS6rTo1ieZ+2+HNNCn+cUfMDJVU0n1M4FjCwNeDw=; b=e hnG+yLfIy3IAZJJcGL10jv2UD18hjfF7lpjute2cWrbacKl7567ET7MK8L4LKF4U MDBxckf6lnFrEmiIsl95kflCvS5/MXnd8+nRo727U5Lw2QSFK2Bm7Hp6tS6PkcaT UnIHiZxEIJXzGpOeBeJi8T0QHGPLr1pmRLpdDXPser3BxdcnIlk/Q3iUy9eNboJU cJ0JAmMqwOs1TpfijTLExWw3j5Yp0EW0MvkehjrQduqPp8iAT99F3CmD/w/OazWb Mpwn106Pw+u6GFnuTA8ff3sxCMFcPiflm0MPkemCHjeehmrd0fKOYljAemzfSaO8 uC7NneXW4GOZLu0QN4njg== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedviedrgeekgdekjecutefuodetggdotefrodftvf curfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfghnecu uegrihhlohhuthemuceftddtnecusecvtfgvtghiphhivghnthhsucdlqddutddtmdenuc fjughrpeffhffvvefukfggtggugfgjsehtkeertddttdejnecuhfhrohhmpeetlhhvrghr ohcujfgvrhhrvghrrgcuoegrlhhvhhgvrhhrvgesrghlvhhhrdhnohdqihhprdhorhhgqe enucggtffrrghtthgvrhhnpedvkedtffduffdtffffheffhfejjefhgfeiueeukeejkeff gfdufffhudffffeuveenucffohhmrghinhepvghnthgvrhhprhhishgvuggsrdgtohhmne cuvehluhhsthgvrhfuihiivgeptdenucfrrghrrghmpehmrghilhhfrhhomheprghlvhhh vghrrhgvsegrlhhvhhdrnhhoqdhiphdrohhrgh X-ME-Proxy: Feedback-ID: ia2694551:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Wed, 19 Jul 2023 12:45:41 -0400 (EDT) Received: by perhan.alvh.no-ip.org (Postfix, from userid 1000) id 176309D; Wed, 19 Jul 2023 18:45:39 +0200 (CEST) Date: Wed, 19 Jul 2023 18:45:39 +0200 From: Alvaro Herrera To: Luca Ferrari Cc: pgsql-general Subject: Re: suggestion about time based partitioning and hibernate Message-ID: <20230719164539.ladmyshzldsi22cz@alvherre.pgsql> MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 2023-Jul-18, Luca Ferrari wrote: > Dear all, > I'm looking for ideas here, and it could be someone already stepped > into declarative partitioning of an existing database where Hibernate > (a Java ORM) handles the tables. > The situation is as follows: > > create table foo( id primary key, a_date date, ... ); > > Now, the trivial way to partition this would be on a range based on > a_date, so that the primary key of the tables shifts from id to (id, > a_date). Hmm. If you can make partitioning give you some benefit without having to change the model, then by all means explore it. But if you're forced to change the model (in this case, by adding a column to your primary key), that is going to cause you lots of pain, and some queries might become slower rather than faster. Therefore I suggest to avoid doing that. Either look at some other partitioning scheme that doesn't involve adding columns to the primary key, or disregard partitioning for this table entirely. -- Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/