From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Luca Ferrari <fluca1978@gmail.com>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Subject: Re: suggestion about time based partitioning and hibernate
Date: Wed, 19 Jul 2023 18:45:39 +0200
Message-ID: <20230719164539.ladmyshzldsi22cz@alvherre.pgsql> (raw)
In-Reply-To: <CAKoxK+6xJeTK4e8nQzu62preuxT9zsPmsbw0ESyFLOWfpoWifw@mail.gmail.com>
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/
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: alvherre@alvh.no-ip.org, fluca1978@gmail.com
Subject: Re: suggestion about time based partitioning and hibernate
In-Reply-To: <20230719164539.ladmyshzldsi22cz@alvherre.pgsql>
References: <CAKoxK+6xJeTK4e8nQzu62preuxT9zsPmsbw0ESyFLOWfpoWifw@mail.gmail.com> <20230719164539.ladmyshzldsi22cz@alvherre.pgsql>
* 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