Performance with PostGres database

[Reinhard]

Hello,

After talking shortly to Julian and Tim I am opening this topic and our performance related question .

We migrated to Operaton (now version 2.1.2) last year after using Camunda 7 for about 6 years. Each year we experience performance issues when in the end of the year we have like 10 or 20 times more processes than normal. So were considering to implement PGBouncer for connection manageme nt.

Does anyone have experience with this on Operaton (or Camunda 7)?

Some configuration informa tion:

• Operaton is running on 4 pods in a Kubernetes cl uster.

• Our Postgres database (version 17) is running on Kubernetes, one master with 2 slav e pods.

• This years camunda7/Operaton usage metrics (today = 1- 8-2026):

• Number of rows in ACT_RU _JOB: 191.623

• Hikari config :

spring.datasource.hikari.idle-timeout=10000
spring.datasource.hikari.maximumPoolSize=30
spring.datasource.hikari.m inId le=10

• Solarwinds provided us with information on some high load queries in our Operaton p ostgres database.

• High load queries usually touch history tables and runt ime autorizations.

• This first query is accounting almost always for most of the execution time, typically about 25% of instance execution time. With usually 98%- 100% i n Memory/CPU.

<SELECT DISTINCT res.*

FROM

(

SELECT self.*,

def.name_ ,

def.version _,

def.deployment_ id_

FROM act_hi_procinst self

LEFT JOIN act_re_procde f def

ON self.proc_def_id_ = d ef.id_

LE FT JOIN

(

SELECT a.*

FROM act_ru_aut horization a

WHER E a.type_ < ?

AND (a.us er_id_ IN (?+)

OR a.gro up_id_ IN (?+))

AND ((a.re source_type_ = ?

AND a.perms_ & ? = ?))

)

auth

ON (auth.resource_id_ IN (sel f.proc_def_key_, ?) )

WHERE (? = ?

AND def.key_ = ?)

AND ((self.proc_def_key_ IS NOT NULLAND auth.re source_id_ IS NOT NULL)

OR se lf.proc_def_key_ IS NULL)

)

res

ORDER BY res.start_time_ DESC LIMIT ?OFFSET ? />

• Other high load queries:

[javahippie]

Hi Reinhard, thanks for the detailed writeup! Before asking some specifics: Are you seeing real delays in the execution of processes? High database load might slow things down, but the queries you posted mostly concern historic process instances and don’t seem to be triggered by process execution – this looks like a query from a frontend, or maybe a monitoring job.

@Reinhard

This years camunda7/Operaton usage metrics (today = 1- 8-2026):

This is not a small installation, I have seen bigger ones at clients (up to 2 billion FNI/year), but all of them had to tune their history for performance reasons (see below).

In slack you mentioned, that you mitigated performance issues in the past by scaling the Postgres memory up to 32GB(?). For an Operaton installation of this size, that’s where I would try and start scaling the hardware, too.

@Reinhard

Number of rows in ACT_RU _JOB: 191.623

That’s the first thing I noticed. 190.000 jobs in this table is manageable, and I think with your executed PI per year ( 4,5 million instances so far this year, if I read that right) it is a number I would expect. Beware: The default job executor paging fetches 3(!) jobs per job executor run. That’s not a lot, so the jobs might get drained slowly. If the jobs are really processed slowly, you won’t see that in solarwinds, because the queries themselves return quickly, but they either might return only a few rows, or if the 4 pods are trying to concurrently fetch the same rows, they just receive an empty result because of the locking mechanism.

@Reinhard

spring.datasource.hikari.idle-timeout=10000
spring.datasource.hikari.maximumPoolSize=30
spring.datasource.hikari.m inId le=10

I think a decently sized Postgres instance should not have issues with that.

The queries you analyzed with solarwinds mostly seem to concern the history tables. Do you have housekeeping enabled in your process instance? Which history level do you have configured? Can you see the amount of data in your history tables (a client I worked for had their own table space for historic process data - it grew to 3TB).

[Reinhard]

Hi Tim, tnx for that immediate answer!

1. With regard to real delays: I don’t care too much about process execution of system tasks; in our case it doesn’t matter if they take 2 seconds or 2 minutes. But in most of our processes users have one or several tasks to perform in the process. And performance matters when requesting tasklists and taskdetails from the frontend (a separate application) as well as doing updates to the process from the frontend. So frontend users are the ones we need to keep happy .

2. Operaton and Postgres are running in a Kubernetes cluster. When upgrading memory we also added CPU. Are you referring to assigning more memory and CPU when you mention scaling the hardware? Only, some people consider that we are using quite a lot now; Since it’s a failover cluster all nodes are configured identically and double in 2 locations.
Also, do I understand correctly that you consider our system is not very small but also not very large? Maybe a 3 or 4 on a scale from 1-5 (5 being the largest). Our postgres database is almost 800 G B?

3. history time to live = 42 days, history level = 3 (full); history is kept mainly for troubleshooting and short term analys is.

4. Furthermore, you are talking of the job executor. In my understanding Job Executor is mostly responsible for internal tasks. We are using External Task Workers – does that make a differe nce?

[javahippie]

Hi Rheinhard,

so I understood that the performance issues are mainly for users using the task list? In this case, I think the authorization checks might cause stress on the DB. We have a fix prepared for Operaton 2.2, released later this year, maybe this can already help a bit, we sped up some queries regarding task authorizations.

I think your installation is quite big, although I have seen and worked with Camunda / Operaton instances that were 4-5 times the size. So I’d say a 4 out of 5. Not trivial anymore, but I’d not expect a big performance degradation, yet – especially, when relying on many external tasks and few jobs. (P.S: You are running a quite large system, if you are happy with Operaton and if your migration from Camunda was smooth, we are always happy about a short writeup, LinkedIn or Blog Post or if the project could name you as a reference!)

You are right, the job executor handles everything that is asynchronous in your process instance, maybe timer intermediate events and manually “Asynchronous Before” and “Asynchronous After” transactions. So from your description I don’t think this could be an issue.

The history time to live seems healthy to me, but if I do a rough estimate, this means that every process instance has ~1,5MB of data. Do you by any chance store BLOB data in the process a lot, serialized classes or serialized JSON? Does not point to an issue, but if your process instances are more lightweight, maybe this means vacuum in Postgres is not aggressive enough and rows which are already deleted in your database are still in the storage until the vacuum job gets to them.

I also understand that 32GB database instances in a shared Kubernetes cluster can hurt and cause discussions, especially in a replicated setup, I would check, which default settings are still applied in postgres and if there is something that can be optimized. I once worked for a client who had 128GB RAM for their PostGres and it didn’t perform, on site we realized that the shared_buffers were still set to the default 128MB(!), so every index scan went to the disk instead of keeping the indexes in memory. Other candidates I’d check are effective_cache_size, work_mem, maintenance_work_mem. For vacuuming, the property autovacuum_vacuum_scale_factor is interesting, default is 0.2, meaning that a 100.000.000 rows table will only be vacuumed if 20.000.000 dead tuples are in it. That can bloat your DB. Not to be misunderstood, I’m not claiming that you misconfigured Postgres and that’s the reason for the slow engine performance, but I usually check these Postgres settings first, before trying to optimize anything on the engine-side.

Additionally I’d try to analyte the queries which were flagged by Solarwinds manually with EXPLAIN PLAN on the prod instance. If from the explain plan you can see that additional indexes could be helpful, this was always allowed, even by Camunda support (after checking back). If you see that indexes should be available, but Postgres opts to a full table scan or other mechanisms instead of using the indexes, there might be a misconfiguration in the database preventing the db from storing the indexes in the memory, properly.

Unfortunately, I think that’s all I can recommend from remote-debugging in a public forum with that information. If you have any additional questions, feel free to follow up, but verfiying database performance is not that easy. If you feel that you need additional eyes on the system, check if one of the partners on operaton.org is available for that.

I’d also be open to performance improvements on the engine. If you can create a ticket in our GitHub issues with a specific UI Operation that takes a long time and specific runtimes for the DB queries of that Operation, we can try and replicate and maybe improve the performance here.