Can Spring-JPA work with Postgres partitioning?

Question

We have a Spring Boot project that uses Spring-JPA for data access. We have a couple of tables where we create/update rows once (or a few times, all within minutes). We don't update rows that are older than a day. These tables (like audit table) can get very large and we want to use Postgres' table partitioning features to help break up the data by month. So the main table always has this calendar month's data but if the query requires retrieval from previous months it would somehow read it from other partitions.

Two questions:

1) Is this a good idea for archiving older data but still leave it query-able? 2) Does Spring-JPA work with partitioned tables? Or do we have to figure out how to break up the query and do native queries and concatenate the restult set?

Thanks.

Roughshod

Answer 1

I am working with postgres partitioning with Hibernate & Spring JPA for a period of time. So I think, I can try to answer your questions.

1) Is this a good idea for archiving older data but still leave it query-able?

If you are applying indexes and not re-indexing table frequently, then partitioning of data may result faster query results.

Also you can use clustered index feature in postgres as well to fetch the data faster.

Because table with older data will not going to be updated, so clustered index will improve the performance efficiently.

2) Does Spring-JPA work with partitioned tables? Or do we have to figure out how to break up the query and do native queries and concatenate the restult set?

Spring JPA will work out of the box with partitioned table. It will retrieve the data from master as well as child tables and returns the concatenated result set.

Note : Issue with partitioned table

The only issue you will face with partitioned table is insertion in partitioned table.

Let me explain, when you partition a table, you will create a trigger over master table, and that trigger will return null. This is the key behind insertion issue in partitioned table using Spring JPA / Hibernate.

When you try to insert a row using Spring JPA or Hibernate you will face below issue

Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1

To overcome this issue you need to override implementation of Batching batcher.

In hibernate you can provide the custom implementation of batcher factory using below configuration

hibernate.jdbc.factory_class=path.to.my.batcher.factory.implementation

In spring JPA you can achieve the same by custom implementation of batch builder using below configuration

hibernate.jdbc.batch.builder=path.to.my.batch.builder.implementation

References :

• Custom Batch Builder/Batch in Spring-JPA
• Demo Application

Beamon

Incase you need to implement your own custom batching you can refer my blog anilagrawal038.wordpress.com/2016/12/11/… – Beamon

Thanks @Anil. Did you successfully use this implementation against Postgres DB? It'd be awesome if you have a small working app that uses your proposed solution. – Roughshod

@Roughshod Yes, I have implemented the same with Postgres DB. I would be happy to help you, shortly I'll share a sample app – Beamon

Here is running project for the same github.com/anil-agrawal/spring-jpa-partitioning – Beamon

I am still getting the same error with springboot 2 and hibernate 5.2.x. – Bloodstream

It is not picking up the customer batcher after declaring the given property. – Bloodstream

@AnilAgrawal Do you have a solution using newer versions of JPA and Hibernate. I've noticed that the class "AbstractBatcher" has been replaced with "BatchingBatch" and do not have the same implementation. Thank in advance. – Markel

I didn't got time to explore more about nw changes, but as I can see BatchingBatch is also extending AbstractBatchImpl. docs.jboss.org/hibernate/orm/5.0/javadocs/org/hibernate/engine/… – Beamon

May be this blog can help you to solve your problem. anilagrawal038.wordpress.com/2016/12/11/… – Beamon

Answer 2

In addition to the @Anil Agrawal answer.

If you are using spring boot 2 then you need to define the customBatcher using the property.

spring.jpa.properties.hibernate.jdbc.batch.builder=net.xyz.jdbc.CustomBatchBuilder

Bloodstream

Answer 3

You do not have to break down the JDBC query with postgres 11+.

If you execute select on the main table with plain jdbc, the DB would return the aggregated results from the partitioned tables.

In other words, the work is done by the Postgres DB, so Spring JPA will simply get the result and map it to objects as if there were no partitioning.

For having inserts work in a partitioned table you need to make sure that your partitions are already created, i think spring data will not create them for you.

Pilgrimage