How we used Hibernate Filter and Inspector to dynamically add partition key to SQL queries?

At Intuit, we faced the challenge of migrating our 27TB Database from Oracle to Postgres which is used by our Monolithic Application consisting of around 600 complex business workflows. While migrating to Postgres, we discovered that Postgres does not support Global Indexes and since we had some partitioned tables, we were facing performance issues if we don’t give the Partition Key as part of the where clause for SQL queries.

In this blog post, we will explore how to use Hibernate Filters and Inspector to dynamically add partition keys to your queries, which can help in mitigating the performance issues.

Using Hibernate Filters

Hibernate filters are a powerful feature that allows you to define conditions that are automatically applied to all queries for a specific entity class. This can be useful in a variety of situations, including adding partition keys to your queries.

To use Hibernate filters, you need to define the filter using the @FilterDef and @Filter annotations in your entity classes. For example, to define a filter that adds a countrycolumn to the WHERE clause of all queries for a User table, you could use the following code:

@Entity
@Table(name = "user_table")
@FilterDef(name = "countryFilter", parameters = @ParamDef(name="country", type = String.class))
@Filter(name = "countryFilter", condition = "country = :country")
@Getter
@Setter
public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;

    private String country;

    // Other fields
}

Once you have defined the Hibernate filters, you can enable them at session level in your application. To do this, you can use the enableFilter() method of the Session class and set the parameter values using the setParameter() method. We can also create an Aspect to enable filter for all sessions. For example, to enable the "countryFilter" filter defined above and set the country parameter, you could use the following code:

Session session = entityManager.unwrap(Session.class);
session.enableFilter("countryFilter").setParameter("country", "India");

This code enables the “countryFilter” filter for the current session and sets the country parameter. Any queries executed using this Session will include the country = 'India' condition in the WHERE clause. This will ensure that only records for the specified country are returned by the query.

Problems with Hibernate Filter

Filters are not applied to Update/Delete queries. So to solve this problem we used Hibernate Statement Inspector to manually modify the Update and Delete queries.

Note: Hibernate team is discussing on a native solution for update/delete queries to include partition key, but looks like it is not implemented at the time of writing this blog. You can learn more about that here.

Using Hibernate Statement Inspector

Hibernate Statement Inspector can be used to manually modify the SQL statements generated by Hibernate. This can be useful in situations where you need to modify the SQL statements generated by Hibernate, such as adding partition keys to the WHERE clause of update or delete queries.

For example, to modify the WHERE clause of update and delete queries for a User table to include the country column as a partition key, you could use the following inspector class:

public class SQLStatementInspector implements StatementInspector {

    @Override
    public String inspect(String sql) {
        if (sql.startsWith("update user_table") || sql.startsWith("delete from user_table")) {
            sql = sql + " where country = '" + "India" + "'";
        }
        return sql;
    }
}

You can enable the inspector by adding this property:

<property
    name="hibernate.session_factory.statement_inspector"
    value="com.example.learning.SQLStatementInspector"
/>

Conclusion

Using this combination of Hibernate Filters and Inspector, we were able to solve the critical partitioning issue we faced while migrating to Postgres.

You can see the complete implementation here: https://github.com/Akshit97/hibernate-filter