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 1qLfCG-000Naq-Dz for pgsql-general@arkaria.postgresql.org; Tue, 18 Jul 2023 07:32:37 +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 1qLfCE-005VP4-M0 for pgsql-general@arkaria.postgresql.org; Tue, 18 Jul 2023 07:32:34 +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 1qLfCD-005VOv-VS for pgsql-general@lists.postgresql.org; Tue, 18 Jul 2023 07:32:34 +0000 Received: from smtp46.i.mail.ru ([95.163.41.84]) by makus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qLfC8-00056Q-Jn for pgsql-general@lists.postgresql.org; Tue, 18 Jul 2023 07:32:32 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=aeronavigator.ru; s=mailru; h=Content-Type:MIME-Version:Message-ID:Date:Subject:In-Reply-To:References:To:From:From:Subject:Content-Type:Content-Transfer-Encoding:To:Cc; bh=9yCk1wAV2vcv5qD4c0KtrxUhF2fJy6wfL9chU3SPBFg=; t=1689665548;x=1689755548; b=1OGPpdTZffn99RODG5N3X9xB2fSED4DIPOUxRUcSgTCBOd73jIfD9LV23EFaaHk0wE2mPMdn/Mkhy/iI/+PwfUeWEvG0hgXGUEeR62edGbcy2qctZVt1Z+4/rGHTdhWsET/mxel5uF0LaQ1NvnbjtXca6jDqTFlJn8dO3L9bKF0=; Received: by smtp46.i.mail.ru with esmtpa (envelope-from ) id 1qLfC4-00ATft-IB; Tue, 18 Jul 2023 10:32:25 +0300 From: To: "'Ron'" , References: <92ccfffe-8bcf-249e-82c1-a0ae9cebae41@gmail.com> In-Reply-To: <92ccfffe-8bcf-249e-82c1-a0ae9cebae41@gmail.com> Subject: RE: suggestion about time based partitioning and hibernate Date: Tue, 18 Jul 2023 10:32:19 +0300 Message-ID: <001c01d9b949$fc2fc300$f48f4900$@aeronavigator.ru> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_001D_01D9B963.217ECFC0" X-Mailer: Microsoft Outlook 16.0 Thread-Index: AQEPoKW2lE187H+8GSDfMP0qL3g1NwMtskDcsTne5yA= Content-Language: ru Authentication-Results: smtp46.i.mail.ru; auth=pass smtp.auth=n.kobzarev@aeronavigator.ru smtp.mailfrom=n.kobzarev@aeronavigator.ru X-Mailru-Src: smtp X-7564579A: 78E4E2B564C1792B X-77F55803: 4F1203BC0FB41BD9A075ED63965291975FF12524F3FDD82B4E108C225E84807B182A05F538085040F7086F0D4F6C5337C127ACA86E6C5F247A0FCAAE6C865C9864DAAB5A5D4E7897 X-7FA49CB5: FF5795518A3D127A4AD6D5ED66289B5278DA827A17800CE75DF2B1F23425CAE5EA1F7E6F0F101C67BD4B6F7A4D31EC0BCC500DACC3FED6E28638F802B75D45FF8AA50765F7900637A8691684BB8CCFB18638F802B75D45FF36EB9D2243A4F8B5A6FCA7DBDB1FC311F39EFFDF887939037866D6147AF826D8E94414FE25BDA24929961C20711C4A3D117882F4460429724CE54428C33FAD305F5C1EE8F4F765FCAA867293B0326636D2E47CDBA5A96583BD4B6F7A4D31EC0BC014FD901B82EE079FA2833FD35BB23D27C277FBC8AE2E8B911D863C7A028B9E389733CBF5DBD5E9B5C8C57E37DE458BD9DD9810294C998ED8FC6C240DEA76428AA50765F7900637A5C41FFB07C62B7BD32BA5DBAC0009BE395957E7521B51C2330BD67F2E7D9AF1090A508E0FED6299176DF2183F8FC7C0A5FD2DD08EB4836CB3661434B16C20ACC84D3B47A649675FE827F84554CEF5019E625A9149C048EE9ECD01F8117BC8BEE2021AF6380DFAD18AA50765F790063735872C767BF85DA227C277FBC8AE2E8B9F5955FECEF5819E75ECD9A6C639B01B4E70A05D1297E1BBCB5012B2E24CD356 X-C1DE0DAB: 0D63561A33F958A5E2ED3B7783E9906FABCD42B299E1362CE6D4EFCDD7F16218F87CCE6106E1FC07E67D4AC08A07B9B0B2DECCBDF547A305BDAD6C7F3747799A X-C8649E89: 1C3962B70DF3F0ADBF74143AD284FC7177DD89D51EBB7742DC8270968E61249B1004E42C50DC4CA955A7F0CF078B5EC49A30900B95165D34103A2CD2CFC42C679CF04144C6997EF82D6E05DA4A7B698937D16F5CFB037FC10EA80D155100AC031D7E09C32AA3244C6276D7EA35AD5DF7124B2518083BB0A0795D98D676DD64D03A1A83191EB2B8F05DA084F8E80FEBD32345C325059F78E992E43EAE9309B056236E14BE1BEB0DF5CC2E138FFB4ACBED X-D57D3AED: 3ZO7eAau8CL7WIMRKs4sN3D3tLDjz0dLbV79QFUyzQ2Ujvy7cMT6pYYqY16iZVKkSc3dCLJ7zSJH7+u4VD18S7Vl4ZUrpaVfd2+vE6kuoey4m4VkSEu530nj6fImhcD4MUrOEAnl0W826KZ9Q+tr5ycPtXkTV4k65bRjmOUUP8cvGozZ33TWg5HZplvhhXbhDGzqmQDTd6OAevLeAnq3Ra9uf7zvY2zzsIhlcp/Y7m53TZgf2aB4JOg4gkr2biojLxQD/rvAZi73psfM/jqXiQ== X-Mailru-Sender: 6B4B2FAA1300123B4BB0C83A48BFD18B7795DABC0EB1E7FBC127ACA86E6C5F24525D33B0D3204FEC9215EFA742CC5EB292D99EB8CC7091A74C2ED15A5AB7F0073B9C7E7614533D2A011BE29E39D2FEA10D4ABDE8C577C2ED X-Mras: Ok List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multipart message in MIME format. ------=_NextPart_000_001D_01D9B963.217ECFC0 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable =20 =20 =D0=9E=D1=82: Ron =20 =D0=9E=D1=82=D0=BF=D1=80=D0=B0=D0=B2=D0=BB=D0=B5=D0=BD=D0=BE: 18 = =D0=B8=D1=8E=D0=BB=D1=8F 2023 =D0=B3. 9:48 =D0=9A=D0=BE=D0=BC=D1=83: pgsql-general@lists.postgresql.org =D0=A2=D0=B5=D0=BC=D0=B0: Re: suggestion about time based partitioning = and hibernate =20 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: =20 create table foo( id primary key, a_date date, ... ); =20 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. =20 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. =20 Anyone has already done a partitioning in such scenario? =20 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. --=20 ---------------------------------------- Hi! =20 It is always depends on database architecture. Partitioning should be = planned in advance and your queries must be aware of it. There are a number of caveats here - if your database grows indefinitely you must have a dynamic = partitioning. As for me I have created a service procedure to create new = partitions to handle new data, and a backup procedure to check existence = of partitions on every insert.=20 - if database is more or less finite and predictable I have created all = the partitions created in advance at once =20 Next I give up range partitioning, I have created a surrogate key = function and partition by it. Function is available on *client* and = server, so every time I want to query data I can calculate this function = and address data directly in known partition. In Hibernate too, just = pass both elements of *composite* key. Partition key MUST be in primary key, so query becomes cheap.=20 In case on subquery it is a matter of query optimizer if it is able to = understand composite keys and do partition pruning. PG 12 can not, you = have to tweak queries. =20 To summarize, if you query by ID =E2=80=93 create a surrogate key from = ID and pass it to query with original ID. All the FK data must follow = the same schema, partition key must remain the same across the tables. = If on the righth, other end of database schema you need other way of = partitioning =E2=80=93 I have created extra column to store = =E2=80=9Cleft side=E2=80=9D partitioning key, so joins may respect all = partitioning schemas. =20 What you should consider more =E2=80=93 query optimizer. Once it may = decide to create a general plan instead of custom. This will lead to = locking all the (thousands of) partitions, than applying parameters, = than pruning. In my case it was nearly half a million of locks for = single query. There was a discussion here, and there was a proposed patch to exchange = pruning-analyzing, I do not know it`s faith. But I given up setting = force_custom_plan and this speed up my queries by more than 10x and no = locks, ~ 5 per query. ------=_NextPart_000_001D_01D9B963.217ECFC0 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

 

 

=D0=9E=D1=82: Ron = <ronljohnsonjr@gmail.com> =
=D0=9E=D1=82=D0=BF=D1=80=D0=B0=D0=B2=D0=BB=D0=B5=D0=BD=D0=BE: = 18 =D0=B8=D1=8E=D0=BB=D1=8F 2023 =D0=B3. = 9:48
=D0=9A=D0=BE=D0=BC=D1=83: = pgsql-general@lists.postgresql.org
=D0=A2=D0=B5=D0=BC=D0=B0: = Re: suggestion about time based partitioning and = hibernate

 

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.

-- =
----------------------------------------

Hi!

 

It is always = depends on database architecture. Partitioning should be planned in = advance and your queries must be aware of it.

There are a number of caveats here

- if your database grows indefinitely you must have a = dynamic partitioning. As for me I have created a service procedure to = create new partitions to handle new data, and a backup procedure to = check existence of partitions on every insert.

- if database is more or less finite and predictable I = have created all the partitions created in advance at = once

 

Next I give up range partitioning, I have created a = surrogate key function and partition by it. Function is available on = *client* and server, so every time I want to query data I can = calculate this function and address data directly in known partition. In = Hibernate too, just pass both elements of *composite* = key.

Partition key MUST be in primary = key, so query becomes cheap.

In case = on subquery it is a matter of query optimizer if it is able to = understand composite keys and do partition pruning. PG 12 can not, you = have to tweak queries.

 

To = summarize, if you query by ID =E2=80=93 create a surrogate key from ID = and pass it to query with original ID. All the FK data must follow the = same schema, partition key must remain the same across the tables. If on = the righth, other end of database schema you need other way of = partitioning =E2=80=93 I have created extra column to store = =E2=80=9Cleft side=E2=80=9D partitioning key, so joins may respect all = partitioning schemas.

 

What you = should consider more =E2=80=93 query optimizer. Once it may decide to = create a general plan instead of custom. This will lead to locking all = the (thousands of) partitions, than applying parameters, than pruning. = In my case it was nearly half a million of locks for single = query.

There was a discussion here, = and there was a proposed patch to exchange pruning-analyzing, I do not = know it`s faith. But I given up setting force_custom_plan and this speed = up my queries by more than 10x and no locks, ~ 5 per = query.

------=_NextPart_000_001D_01D9B963.217ECFC0--