Add partition key to updates

Hibernate ORM
1

Hello,

I have a large table on PostgreSQL which is partitioned by a column that is not the primary key.
That’s ok for SELECTs as we use native queries, and for INSERTs which include the partition key.

However, UPDATEs and DELETEs are managed and issued by Hibernate, which only adds primary key to the WHERE clause. While this is proper SQL and works, query planning and execution time are huge if we don’t add the partition key for pruning.

Is there anyway to solve this in Hibernate?

I was thinking if there’s a way to customize UPDATEs and DELETEs for partitioned entities or, even better, annotate the partition key column so that Hibernate is aware of that.

Thank you very much.

2

There is currently no way to do this, but I can see how this can help with query planning performance. I created Allow marking partition columns · Discussion #5353 · hibernate/hibernate-orm · GitHub where you can chime in.

3

Hey @beikov thanks for the fast reply.

I just upvoted there. Do you think I should move discussion there and close this topic?

Anyway, would it be too much of a hack to intercept queries with org.hibernate.Interceptor and try to rewrite the queries, adding the extra condition on the partition key? Is there any safe way to do this? Have you every seen it?

Thanks.

4

If you want to discuss the design of the annotations i.e. add your use cases, you can comment on the GitHub discussion, but for other things we can keep the conversation here.

As for a workaround, you could implement a custom EntityPersister by i.e. extending SingleTableEntityPersister and specifying the class on your entity with @Persister(impl = MyPersister.class). There you can build your custom update/delete statements by overriding org.hibernate.persister.entity.AbstractEntityPersister#generateUpdateString(boolean[], int, java.lang.Object[], boolean) and org.hibernate.persister.entity.AbstractEntityPersister#generateDeleteString, as well as override org.hibernate.persister.entity.AbstractEntityPersister#update(java.lang.Object, java.lang.Object[], java.lang.Object[], java.lang.Object, boolean[], int, java.lang.Object, java.lang.Object, java.lang.String, org.hibernate.engine.spi.SharedSessionContractImplementor) and org.hibernate.persister.entity.AbstractEntityPersister#delete(java.lang.Object, java.lang.Object, int, java.lang.Object, java.lang.String, org.hibernate.engine.spi.SharedSessionContractImplementor, java.lang.Object[]) to bind the additional parameters.

1 Like
5

That’s awesome. It’s a pretty straightforward way and I will surely use it. Thanks a lot.