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 1qLeVG-000M2u-Q5 for pgsql-general@arkaria.postgresql.org; Tue, 18 Jul 2023 06:48:10 +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 1qLeVE-0051PA-8s for pgsql-general@arkaria.postgresql.org; Tue, 18 Jul 2023 06:48:08 +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 1qLeVD-0051Ny-Qe for pgsql-general@lists.postgresql.org; Tue, 18 Jul 2023 06:48:07 +0000 Received: from mail-qt1-x82d.google.com ([2607:f8b0:4864:20::82d]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qLeV7-0004gW-1t for pgsql-general@lists.postgresql.org; Tue, 18 Jul 2023 06:48:06 +0000 Received: by mail-qt1-x82d.google.com with SMTP id d75a77b69052e-4039f0d140eso44644081cf.1 for ; Mon, 17 Jul 2023 23:48:00 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1689662879; x=1692254879; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=/pTHXO3zYtuWdYPHuN/YhYKdp67PvhgFeUr3cNp9H0M=; b=Eqy1qlkRK9iFdnaPyGcq9AIcgGyn9/9/qlw7q3LaBDhnrWEHOHrVh95ZnwoPW1VWb3 TI4yiZNTbiIQK5f+ZVD/l1LYSidHqOCCj9hNVCUOEszG7xTHVW6jPas7ov/qYSL1qdxW J+zWlJgesMnuQv/BkHKmCaOdY/+/xJKczKIXoevBX0ihgqVhG0M9yAGeMPQ3lthunlIc 1tgphsXciMQEv5Er+DXpXC94PgfNsdOUxmX71NZPtgiyt9hDe+56vTFkTriT4Pjl4mh7 arB5Ks4xuYdjq0YOLkrMHRs3p7VMgfz8p5jNfRJBv7VqADkwf0SedBSv85VBma9B/yj7 AJ/A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1689662879; x=1692254879; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=/pTHXO3zYtuWdYPHuN/YhYKdp67PvhgFeUr3cNp9H0M=; b=DW0iYxPX/pVCy+tk6AHOvlHEquLbiqwmizEF9a/6n7v0ppFKHWRDVoiZ0AWdZbti+g j7otPxmLM/kWU9Z/7YeZlsy/50skyrdcUJ3tdfEWuKsvr6CyKCdJNU3zitsr1M3OVcL5 u84u6QkabUHPAauWFslrPYiIBtSsTMGoEvkQrUUNRh35rLFmjL1XdVsykGS+wJqKxx98 psL/NK337nnwG5S6LKeeKlVfqx0Y99t9betpqjp2fLNQxGNO1ttH4qbcjSHFWBv//EeR z2SIuTYU/TMQLFJC1Yv/Ssgk3Ch+PAPUH2yvFnq09i248Qdl/xhPowfZYTX12nfd78NG T3ZA== X-Gm-Message-State: ABy/qLbBxN9cLIHmfEGCvezom1L+tDLsEXQy7NCE+5KCvkQC+7MRhJTh 4pPrweRTKi4JJsmCa503MsLrvY9FbqI= X-Google-Smtp-Source: APBJJlH3xUbLYrF0zm/0Mei9QnauYUbfqESt8uVWWCWDlGThiOFl0ns83CJpbHTZJ+NN5hFw4+v5DQ== X-Received: by 2002:a05:622a:19a5:b0:403:c4ef:490a with SMTP id u37-20020a05622a19a500b00403c4ef490amr17812362qtc.9.1689662879550; Mon, 17 Jul 2023 23:47:59 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:359d:e2cf:1438:36be? ([2600:1700:bd2c:b810:359d:e2cf:1438:36be]) by smtp.googlemail.com with ESMTPSA id h81-20020a256c54000000b00c4ec3a3f695sm295448ybc.46.2023.07.17.23.47.58 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Mon, 17 Jul 2023 23:47:58 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------56eJ1j5dYSQEy6OA3aHWKvtA" Message-ID: <92ccfffe-8bcf-249e-82c1-a0ae9cebae41@gmail.com> Date: Tue, 18 Jul 2023 01:47:58 -0500 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.11.0 Subject: Re: suggestion about time based partitioning and hibernate Content-Language: en-US To: pgsql-general@lists.postgresql.org References: From: Ron In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------56eJ1j5dYSQEy6OA3aHWKvtA Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 7/18/23 01: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). One thing that frightens me is that Hibernate does a lot of > per-row lookups by means of the id, so while the partitioning is > probably going to make things more manageable and even faster in some > scenarios, could lead to drawbacks when Hibernate queries by id. > Moreover, hibernate will think id is unique while it is not anymore. > Last but not least, referencing foreign keys are made by Hibernate > thru the id column, and it means that incoming foreign keys to foo > will not be in place anymore. > > Now, I know that I can define a composite key in hibernate, in order > to match the effective new data structure, but this requires a huge > rewrite of the application code. > And after all, we are talking about a non-PostgreSQL related piece, so > the problem is not on the PostgreSQL side. > > Anyone has already done a partitioning in such scenario? > > I am thinking that partitioning on an hash of id could be the only way > to go without having to touch the hibernate side, even if this would > bring up a less balanced partitioned structure. In such case, I mean > partitioning by hash, having a table with 60 millions rows per 50 GB > in size, what would be the rule of thumb to select the number of > partitions (i.e., a suggested modulus)? We're in the exact same situation, using Hibernate and having many tables like foo, partitioned by a_date with PK of (id, a_date) and FK definitions (id, a_date). It was a *massive mistake*, since many queries span partitions.  Within a year, I "departitioned" all tables except two giant tables that have large bytea columns.  (All the formerly partitioned tables still have (id, a_date) as PK and FK.  I'd like to change that, but the amount of code change is untennable given them amount of new features that need to be added.) Thus, my recommendations are: 1. only partition the tables you /must/, and 2. partition by id. -- Born in Arizona, moved to Babylonia. --------------56eJ1j5dYSQEy6OA3aHWKvtA Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit On 7/18/23 01: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). One thing that frightens me is that Hibernate does a lot of
per-row lookups by means of the id, so while the partitioning is
probably going to make things more manageable and even faster in some
scenarios, could lead to drawbacks when Hibernate queries by id.
Moreover, hibernate will think id is unique while it is not anymore.
Last but not least, referencing foreign keys are made by Hibernate
thru the id column, and it means that incoming foreign keys to foo
will not be in place anymore.

Now, I know that I can define a composite key in hibernate, in order
to match the effective new data structure, but this requires a huge
rewrite of the application code.
And after all, we are talking about a non-PostgreSQL related piece, so
the problem is not on the PostgreSQL side.

Anyone has already done a partitioning in such scenario?

I am thinking that partitioning on an hash of id could be the only way
to go without having to touch the hibernate side, even if this would
bring up a less balanced partitioned structure. In such case, I mean
partitioning by hash, having a table with 60 millions rows per 50 GB
in size, what would be the rule of thumb to select the number of
partitions (i.e., a suggested modulus)?

We're in the exact same situation, using Hibernate and having many tables like foo, partitioned by a_date with PK of (id, a_date) and FK definitions (id, a_date).

It was a massive mistake, since many queries span partitions.  Within a year, I "departitioned" all tables except two giant tables that have large bytea columns.  (All the formerly partitioned tables still have (id, a_date) as PK and FK.  I'd like to change that, but the amount of code change is untennable given them amount of new features that need to be added.)

Thus, my recommendations are:
1. only partition the tables you must, and
2. partition by id.

--
Born in Arizona, moved to Babylonia.
--------------56eJ1j5dYSQEy6OA3aHWKvtA--