Re: suggestion about time based partitioning and hibernate - Mailing list pgsql-general

From Alvaro Herrera
Subject Re: suggestion about time based partitioning and hibernate
Date
Msg-id 20230719164539.ladmyshzldsi22cz@alvherre.pgsql
Whole thread Raw
In response to suggestion about time based partitioning and hibernate  (Luca Ferrari <fluca1978@gmail.com>)
Responses Re: suggestion about time based partitioning and hibernate
List pgsql-general
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/



pgsql-general by date:

Previous
From: Adrian Klaver
Date:
Subject: Re: Fwd: Regarding Installation of PostgreSQL
Next
From: David Rowley
Date:
Subject: Re: Effects of dropping a large table