[Feature Request] [Architecture] Postgresql compatibility fix for yugabytedb ysql interface

[mszmidt]

Is your feature request related to a problem? Please describe.
N/A

Describe the solution you'd like
Postgresql single db connection url is a single point of failure in tbmq system. I'd like to kindly ask for adaptation of database indexes to be yugabytedb compatible. Additionally could you please add yugabytedb java connector alongside postgresql as a choice.
Similarly we have redis and redis sentinnel option for HA setups.
This is especially crucial in on-prem environments, not everything runs in cloud yet ;)

Describe alternatives you've considered

1. AFAIK there are drop-in paid extensions to postgres that provides multi-master RW, yet its not an option for me.
2. Another HA-database like yugabyte for instance TiDB

Additional context
Shortly: yugabytedb ysql interface is almost fully compatible HA implementation of postgresql with multimaster RW. (IT has additionally cassandra NOSQL ycql that could maybe also good as yet another cache db?).

Problematic part is WITH FILLFACTOR as yugabytedb places physically data diffrently:
With this statement delete, it passes.

CREATE INDEX IF NOT EXISTS idx_stats_event_main
    ON stats_event (entity_id ASC, ts DESC NULLS LAST); -- no with fillfactor

CREATE INDEX IF NOT EXISTS idx_lc_event_main
    ON lc_event (entity_id ASC, ts DESC NULLS LAST); -- no with fillfactor

CREATE INDEX IF NOT EXISTS idx_error_event_main
    ON error_event (entity_id ASC, ts DESC NULLS LAST); -- no with fillfactor

What's more, I have not found a proper instruction for manual DB initialization with those changes.
I tried running

java -cp /usr/share/thingsboard/...tbmq.jar -Dloader.main=org.thingsboard.mqtt.broker.ThingsboardMqttBrokerInstallApplication \
                    -Dspring.jpa.hibernate.ddl-auto=none \
                    -Dinstall.upgrade=false \
                    -Dlogging.config=/usr/share/thingsboard-mqtt-broker/bin/install/logback.xml \
                    org.springframework.boot.loader.launch.PropertiesLauncher

pointing changed schema in env variables but it failed with error about not finding dao folder. so i dropped this method

As far as i succeeded is I had copied all sql scripts i found:
schema-entities.sql
schema-entities-idx.sql
system-data.sql

and executed them (modified only indexes script) then start tbmq on top of it.
tbmq booted but i guess sth more happens during initialization because it failed on with database error:

2026-03-10 12:57:49,576 [https-jsse-nio-0.0.0.0-8083-exec-14] WARN  o.t.m.b.controller.BaseController - Database error: InvalidDataAccessApiUsageException - IllegalArgumentException: No enum constant org.thingsboard.mqtt.broker.common.data.security.MqttAuthProviderType.HTTP
2026-03-10 12:57:59,687 [https-jsse-nio-0.0.0.0-8083-exec-22] WARN  o.t.m.b.controller.BaseController - Database error: InvalidDataAccessApiUsageException - IllegalArgumentException: No enum constant org.thingsboard.mqtt.broker.common.data.security.MqttAuthProviderType.HTTP
2026-03-10 12:58:00,096 [sql-queue-2-timeseriesqueue-5-thread-1] ERROR o.t.m.b.s.h.s.HistoricalStatsTotalConsumer - [total] Failed to save timeseries entries [BasicTsKvEntry{ts=1773147480000, kv=LongDataEntry{value=0} BasicKvEntry{key='sessions'}}, BasicTsKvEntry{ts=1773147480000, kv=LongDataEntry{value=0} BasicKvEntry{key='subscriptions'}}]
org.springframework.dao.CannotAcquireLockException: PreparedStatementCallback; SQL [INSERT INTO ts_kv (entity_id, key, ts, long_v) VALUES (?, ?, ?, ?) ON CONFLICT (entity_id, key, ts) DO UPDATE SET long_v = ?;]; Batch entry 4 INSERT INTO ts_kv (entity_id, key, ts, long_v) VALUES (('total'), ('102'::int4), ('1773147480000'::int8), ('0'::int8)) ON CONFLICT (entity_id, key, ts) DO UPDATE SET long_v = ('0'::int8) was aborted: ERROR: Restart read required (query layer retries aren't supported when executing non-first statement in batch, will be unable to replay earlier commands)  Call getNextException to see other errors in the batch.
        at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:122)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:107)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:116)
        at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1556)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:677)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:701)
        at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:1049)
        at org.thingsboard.mqtt.broker.dao.sqlts.insert.sql.SqlInsertTsRepository.saveOrUpdate(SqlInsertTsRepository.java:38)
        at jdk.internal.reflect.GeneratedMethodAccessor108.invoke(Unknown Source)
        at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.base/java.lang.reflect.Method.invoke(Method.java:569)
        at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:360)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:196)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
        at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:138)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
        at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:380)
        at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
        at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:728)
        at org.thingsboard.mqtt.broker.dao.sqlts.insert.sql.SqlInsertTsRepository$$SpringCGLIB$$0.saveOrUpdate(<generated>)
        at org.thingsboard.mqtt.broker.dao.sqlts.AbstractChunkedAggregationTimeseriesDao.lambda$init$1(AbstractChunkedAggregationTimeseriesDao.java:95)
        at org.thingsboard.mqtt.broker.dao.sql.TbSqlBlockingQueue.processElementsQueue(TbSqlBlockingQueue.java:82)
        at org.thingsboard.mqtt.broker.dao.sql.TbSqlBlockingQueue.lambda$init$0(TbSqlBlockingQueue.java:60)
        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
        at java.base/java.lang.Thread.run(Thread.java:840)
Caused by: java.sql.BatchUpdateException: Batch entry 4 INSERT INTO ts_kv (entity_id, key, ts, long_v) VALUES (('total'), ('102'::int4), ('1773147480000'::int8), ('0'::int8)) ON CONFLICT (entity_id, key, ts) DO UPDATE SET long_v = ('0'::int8) was aborted: ERROR: Restart read required (query layer retries aren't supported when executing non-first statement in batch, will be unable to replay earlier commands)  Call getNextException to see other errors in the batch.
        at org.postgresql.jdbc.BatchResultHandler.handleError(BatchResultHandler.java:165)
        at org.postgresql.core.ResultHandlerDelegate.handleError(ResultHandlerDelegate.java:52)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2422)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:580)
        at org.postgresql.jdbc.PgStatement.internalExecuteBatch(PgStatement.java:886)
        at org.postgresql.jdbc.PgStatement.executeBatch(PgStatement.java:910)
        at org.postgresql.jdbc.PgPreparedStatement.executeBatch(PgPreparedStatement.java:1778)
        at com.zaxxer.hikari.pool.ProxyStatement.executeBatch(ProxyStatement.java:127)
        at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeBatch(HikariProxyPreparedStatement.java)
        at org.springframework.jdbc.core.JdbcTemplate.lambda$getPreparedStatementCallback$7(JdbcTemplate.java:1617)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:658)
        ... 22 common frames omitted
Caused by: org.postgresql.util.PSQLException: ERROR: Restart read required (query layer retries aren't supported when executing non-first statement in batch, will be unable to replay earlier commands)
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2734)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2421)
        ... 30 common frames omitted
2026-03-10 12:58:00,096 [sql-queue-2-timeseriesqueue-5-thread-1] ERROR o.t.m.b.s.h.s.TbMessageStatsReportClientImpl - [tbmq1] Failed to save time series
org.springframework.dao.CannotAcquireLockException: PreparedStatementCallback; SQL [INSERT INTO ts_kv (entity_id, key, ts, long_v) VALUES (?, ?, ?, ?) ON CONFLICT (entity_id, key, ts) DO UPDATE SET long_v = ?;]; Batch entry 4 INSERT INTO ts_kv (entity_id, key, ts, long_v) VALUES (('total'), ('102'::int4), ('1773147480000'::int8), ('0'::int8)) ON CONFLICT (entity_id, key, ts) DO UPDATE SET long_v = ('0'::int8) was aborted: ERROR: Restart read required (query layer retries aren't supported when executing non-first statement in batch, will be unable to replay earlier commands)  Call getNextException to see other errors in the batch.
        at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:122)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:107)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:116)
        at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1556)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:677)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:701)
        at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:1049)
        at org.thingsboard.mqtt.broker.dao.sqlts.insert.sql.SqlInsertTsRepository.saveOrUpdate(SqlInsertTsRepository.java:38)
        at jdk.internal.reflect.GeneratedMethodAccessor108.invoke(Unknown Source)
        at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.base/java.lang.reflect.Method.invoke(Method.java:569)
        at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:360)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:196)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
        at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:138)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
        at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:380)
        at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
        at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:728)
        at org.thingsboard.mqtt.broker.dao.sqlts.insert.sql.SqlInsertTsRepository$$SpringCGLIB$$0.saveOrUpdate(<generated>)
        at org.thingsboard.mqtt.broker.dao.sqlts.AbstractChunkedAggregationTimeseriesDao.lambda$init$1(AbstractChunkedAggregationTimeseriesDao.java:95)
        at org.thingsboard.mqtt.broker.dao.sql.TbSqlBlockingQueue.processElementsQueue(TbSqlBlockingQueue.java:82)
        at org.thingsboard.mqtt.broker.dao.sql.TbSqlBlockingQueue.lambda$init$0(TbSqlBlockingQueue.java:60)
        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
        at java.base/java.lang.Thread.run(Thread.java:840)
Caused by: java.sql.BatchUpdateException: Batch entry 4 INSERT INTO ts_kv (entity_id, key, ts, long_v) VALUES (('total'), ('102'::int4), ('1773147480000'::int8), ('0'::int8)) ON CONFLICT (entity_id, key, ts) DO UPDATE SET long_v = ('0'::int8) was aborted: ERROR: Restart read required (query layer retries aren't supported when executing non-first statement in batch, will be unable to replay earlier commands)  Call getNextException to see other errors in the batch.
        at org.postgresql.jdbc.BatchResultHandler.handleError(BatchResultHandler.java:165)
        at org.postgresql.core.ResultHandlerDelegate.handleError(ResultHandlerDelegate.java:52)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2422)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:580)
        at org.postgresql.jdbc.PgStatement.internalExecuteBatch(PgStatement.java:886)
        at org.postgresql.jdbc.PgStatement.executeBatch(PgStatement.java:910)
        at org.postgresql.jdbc.PgPreparedStatement.executeBatch(PgPreparedStatement.java:1778)
        at com.zaxxer.hikari.pool.ProxyStatement.executeBatch(ProxyStatement.java:127)
        at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeBatch(HikariProxyPreparedStatement.java)
        at org.springframework.jdbc.core.JdbcTemplate.lambda$getPreparedStatementCallback$7(JdbcTemplate.java:1617)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:658)
        ... 22 common frames omitted
Caused by: org.postgresql.util.PSQLException: ERROR: Restart read required (query layer retries aren't supported when executing non-first statement in batch, will be unable to replay earlier commands)
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2734)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2421)
        ... 30 common frames omitted
2026-03-10 12:58:00,097 [sql-queue-2-timeseriesqueue-5-thread-1] ERROR o.t.m.b.dao.sql.TbSqlBlockingQueue - [TimeseriesQueue] Failed to process 5 elements.
org.springframework.dao.CannotAcquireLockException: PreparedStatementCallback; SQL [INSERT INTO ts_kv (entity_id, key, ts, long_v) VALUES (?, ?, ?, ?) ON CONFLICT (entity_id, key, ts) DO UPDATE SET long_v = ?;]; Batch entry 4 INSERT INTO ts_kv (entity_id, key, ts, long_v) VALUES (('total'), ('102'::int4), ('1773147480000'::int8), ('0'::int8)) ON CONFLICT (entity_id, key, ts) DO UPDATE SET long_v = ('0'::int8) was aborted: ERROR: Restart read required (query layer retries aren't supported when executing non-first statement in batch, will be unable to replay earlier commands)  Call getNextException to see other errors in the batch.
        at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:122)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:107)
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:116)
        at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1556)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:677)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:701)
        at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:1049)
        at org.thingsboard.mqtt.broker.dao.sqlts.insert.sql.SqlInsertTsRepository.saveOrUpdate(SqlInsertTsRepository.java:38)
        at jdk.internal.reflect.GeneratedMethodAccessor108.invoke(Unknown Source)
        at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.base/java.lang.reflect.Method.invoke(Method.java:569)
        at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:360)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:196)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
        at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:138)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
        at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:380)
        at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:184)
        at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:728)
        at org.thingsboard.mqtt.broker.dao.sqlts.insert.sql.SqlInsertTsRepository$$SpringCGLIB$$0.saveOrUpdate(<generated>)
        at org.thingsboard.mqtt.broker.dao.sqlts.AbstractChunkedAggregationTimeseriesDao.lambda$init$1(AbstractChunkedAggregationTimeseriesDao.java:95)
        at org.thingsboard.mqtt.broker.dao.sql.TbSqlBlockingQueue.processElementsQueue(TbSqlBlockingQueue.java:82)
        at org.thingsboard.mqtt.broker.dao.sql.TbSqlBlockingQueue.lambda$init$0(TbSqlBlockingQueue.java:60)
        at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
        at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
        at java.base/java.lang.Thread.run(Thread.java:840)
Caused by: java.sql.BatchUpdateException: Batch entry 4 INSERT INTO ts_kv (entity_id, key, ts, long_v) VALUES (('total'), ('102'::int4), ('1773147480000'::int8), ('0'::int8)) ON CONFLICT (entity_id, key, ts) DO UPDATE SET long_v = ('0'::int8) was aborted: ERROR: Restart read required (query layer retries aren't supported when executing non-first statement in batch, will be unable to replay earlier commands)  Call getNextException to see other errors in the batch.
        at org.postgresql.jdbc.BatchResultHandler.handleError(BatchResultHandler.java:165)
        at org.postgresql.core.ResultHandlerDelegate.handleError(ResultHandlerDelegate.java:52)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2422)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:580)
        at org.postgresql.jdbc.PgStatement.internalExecuteBatch(PgStatement.java:886)
        at org.postgresql.jdbc.PgStatement.executeBatch(PgStatement.java:910)
        at org.postgresql.jdbc.PgPreparedStatement.executeBatch(PgPreparedStatement.java:1778)
        at com.zaxxer.hikari.pool.ProxyStatement.executeBatch(ProxyStatement.java:127)
        at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeBatch(HikariProxyPreparedStatement.java)
        at org.springframework.jdbc.core.JdbcTemplate.lambda$getPreparedStatementCallback$7(JdbcTemplate.java:1617)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:658)
        ... 22 common frames omitted
Caused by: org.postgresql.util.PSQLException: ERROR: Restart read required (query layer retries aren't supported when executing non-first statement in batch, will be unable to replay earlier commands)
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2734)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2421)
        ... 30 common frames omitted

[dmytro-landiak]

Hi @mszmidt, thank you for taking the time to write such a detailed and well-structured feature request — we really appreciate it!

YugabyteDB Support

We understand the motivation behind this request. YugabyteDB YSQL is a solid choice for on-prem HA setups, and the FILLFACTOR incompatibility you've identified is a real and known difference in how YugabyteDB handles index storage (LSM tree vs. PostgreSQL's heap-based approach).

We will definitely discuss the option to add YugabyteDB compatibility internally. However, we want to be transparent: this is not a priority for us at the current moment. Our primary database target is PostgreSQL, and adding official YugabyteDB support would require non-trivial work on our end:

• Providing a separate index DDL variant without FILLFACTOR hints
• Addressing transaction-level incompatibilities in our batched upsert layer (see below)
• Testing the full schema and data access layer against YSQL
• Potentially maintaining a separate InsertRepository strategy per DB type

We will keep this issue open and revisit it in the future.

Root Cause of the Batch Insert Error

The error you're seeing:

ERROR: Restart read required (query layer retries aren't supported when executing non-first statement in batch)

is caused by a fundamental difference between PostgreSQL and YugabyteDB's transaction model.

TBMQ persists timeseries data (statistics) using JDBC batch inserts — multiple INSERT ... ON CONFLICT DO UPDATE statements are sent together in a single executeBatch() call for throughput efficiency. In standard PostgreSQL this works fine.

YugabyteDB, however, uses a distributed MVCC model (similar to Google Spanner). When a serialization conflict occurs on any statement mid-batch, YugabyteDB requires the entire transaction to restart from scratch. Since earlier statements in the batch cannot be replayed, it aborts and throws this error instead.

Possible Workarounds

Here are a few things you can try to mitigate this while running TBMQ on YugabyteDB:

1. Reduce the batch size to 1 — add the following to your thingsboard-mqtt-broker.yml (or environment variables):

   sql:
     ts:
       batch_size: SQL_TS_BATCH_SIZE=1

   This disables effective batching and makes each insert a single statement, which avoids the multi-statement batch restart issue. Note: this will reduce write throughput, so evaluate it based on your load.

2. Use the YugabyteDB JDBC driver — replace the standard PostgreSQL JDBC driver with the YugabyteDB JDBC driver, which includes built-in retry logic for serialization errors. This is the most correct long-term fix from the client side and may transparently handle restarts.

3. Tune YugabyteDB's transaction isolation — lowering the isolation level (e.g., READ COMMITTED with yb_enable_read_committed_isolation=true) can reduce the frequency of restart errors in YugabyteDB and may help with upsert-heavy workloads.

We hope this helps you move forward! Feel free to share your findings — community feedback on workarounds is very valuable and may inform how we approach this officially in the future.

Thanks again for the detailed report!

[mszmidt]

Hi,

1. SQL_TS_BATCH_SIZE=1 didn't help.
2. I didn't want to tinker with recompiling app
3. Didn't want to change this from default snapshot level

For everyone in onprem env. There's something called Patroni. Although I consider python as bad choice for such tasks (or every tasks except scripting...), Patroni has multiple success scenarios comparing to other over-complicated solutions (or cloud native solutions like pgEdge)

It can integrate with some HA tools like consul/etcd (and has inbuilt raft mode) that builds quorum on top of untouched psql installation. It setups replication for you. So thankfully without db extensions hassle. It has simple yaml config, that covers psql together with HA. It exposes api that returns 200 for master or 503 for replicas, that way haproxy can distinguish master from replicas.

No matter of this workaround we still prefer yugabyte as this is distributed db by design.
We kindly please not to close this Feature Request.

[dmytro-landiak]

Hi @mszmidt, thanks for the update and for sharing the Patroni suggestion — that's a useful reference for the community looking at PostgreSQL HA setups.

We understand the implications of such changes regarding the batch size — these are workarounds, not a proper solution. As we mentioned earlier, providing real YugabyteDB compatibility requires dedicated effort and investment on our side. We'll keep this feature request open and see what the future brings.