Pgpool 2 with AWS Aurora: dealing with replication lag

A Pgpool 2(v. 4.3.1) is running in the streaming_replication mode with load_balance_mode = on. PostgreSQL is provisioned by AWS Aurora.

According to Pgpool 2 documentation, in streaming_replication mode PostgreSQL is responsible for replicating data in each server. To deal with a possible replication lag a set of additional configuration parameters can be set: sr_check_period, delay_threshold. If the actual delay exceeds the configured value, Pgpool 2 stops routing queries to reader DB instances.

However, in the Amazon Aurora example it is said that sr_check_period should be set to 0 (disabled) as Aurora does not expose the necessary functionality.

So the question is how to deal with the replication lag issue? Should I look to use the Snapshot Isolation mode for Pgpool 2? Or there is a better approach I should consider?

The replication lag issue will be tackled from multiple fronts, including validating if the existing AWS Aurora cluster corresponds to our needs, as well as application-level transaction optimization. In addition, I came up with a workaround that tells Pgpool 2 which queries should not be load balance. As per Pgpool 2 documentation

If you don't want a query that qualifies for the load balancing to be load balanced by Pgpool-II, you can put /*NO LOAD BALANCE*/ comment before the SELECT statement.

To work with the database our application uses the Spring Data JPA with Hibernate. The later one allows to add custom comments via the query hint @QueryHint(name = org.hibernate.jpa.QueryHints.HINT_COMMENT, value = "NO LOAD BALANCE").

Though, to make it work the session-wise property hibernate.use_sql_comments should be set to true. The drawback is that Hibernate will add default comments(which are the actual SQL queries) to every query it sends to DB making it twice as big. Hence, may degrade the performance. To avoid this I decided to override the method responsible for applying the hints and comments: org.hibernate.dialect.Dialect#addSqlHintOrComment

@Override
public String addSqlHintOrComment(String sql, QueryParameters parameters, boolean commentsEnabled) {

    // Keep this here, rather than moving to Select.  Some Dialects may need the hint to be appended to the very
    // end or beginning of the finalized SQL statement, so wait until everything is processed.
    if (parameters.getQueryHints() != null && parameters.getQueryHints().size() > 0) {
        sql = getQueryHintString(sql, parameters.getQueryHints());
    }
    if ("NO LOAD BALANCE".equals(parameters.getComment()) || (commentsEnabled && parameters.getComment() != null)) {
        sql = prependComment(sql, parameters.getComment());
    }

    return sql;
}

The updated if statement checks if the NO LOAD BALANCE is present and prepends the query if so even the hibernate.use_sql_comments property is false. Set spring.jpa.database-platform property to use the extended custom Dialect.

The last thing is wrapper @NoLoadBalanceQuery annotation around the @QueryHint(name = org.hibernate.jpa.QueryHints.HINT_COMMENT, value = "NO LOAD BALANCE") - to make it easier to use for other devs.

@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
@QueryHints
@Inherited
public @interface NoLoadBalanceQuery {

    @AliasFor(annotation = QueryHints.class, attribute = "value")
    QueryHint[] value() default {
            @QueryHint(name = org.hibernate.jpa.QueryHints.HINT_COMMENT, value = "NO LOAD BALANCE")
    };
}