pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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.

view thread (7+ messages)  latest in thread

Message-ID: <92ccfffe-8bcf-249e-82c1-a0ae9cebae41@gmail.com>
Permalink:  ../92ccfffe-8bcf-249e-82c1-a0ae9cebae41@gmail.com/
Also on:    postgresql.org/message-id/92ccfffe-8bcf-249e-82c1-a0ae9cebae41@gmail.com

 · 

reply

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