From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-general@lists.postgresql.org
Subject: Re: suggestion about time based partitioning and hibernate
Date: Tue, 18 Jul 2023 01:47:58 -0500
Message-ID: <92ccfffe-8bcf-249e-82c1-a0ae9cebae41@gmail.com> (raw)
In-Reply-To: <CAKoxK+6xJeTK4e8nQzu62preuxT9zsPmsbw0ESyFLOWfpoWifw@mail.gmail.com>
References: <CAKoxK+6xJeTK4e8nQzu62preuxT9zsPmsbw0ESyFLOWfpoWifw@mail.gmail.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: ronljohnsonjr@gmail.com
Subject: Re: suggestion about time based partitioning and hibernate
In-Reply-To: <92ccfffe-8bcf-249e-82c1-a0ae9cebae41@gmail.com>
References: <CAKoxK+6xJeTK4e8nQzu62preuxT9zsPmsbw0ESyFLOWfpoWifw@mail.gmail.com> <92ccfffe-8bcf-249e-82c1-a0ae9cebae41@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox