Event_entity is very large

Hi

Our table event_entity is growing by 2 mio rows a day. That is expected and OK. But table maintenance (deletions) and selecting on the table etc are getting troublesome and would be easier if using partitioning - and better performance.

We would like to use declarative partitioning (postgres) on the event_entity table based on range (event_time)

But event_time has too be a part of the primary key (id). And now the question:

If I (DBA) are changing the primary constraint:

CONSTRAINT constraint_4_part PRIMARY KEY (id)
to
CONSTRAINT constraint_4_part PRIMARY KEY (id,event_time)

How can I persist this migration over upgrades etc ?

Is there a better way to handle the large event_entity ?

Best

Jan

I’d suggest to implement a custom event listener and forward all events to a message broker, where listeners can get the events and store it in better suitable systems, than a db table.

If done, disable the storing of events in Keycloak, or set the retention time to a low limit (e.g. only some days), as you don’t need the events in Keycloak itself anymore, as you have them in an external system.

This way you have a more loosely coupled system and you can evaluate and analyze the event data completely separated from your Keycloak deployment/system.

1 Like

Hi dasniko

Thanks. I will suggest you idea to the application team :slight_smile:

I sounds to me (as a DBA) like some technology overhead start using message brokers etc to fix something already fixable in the database.

But if keycloak deployment methods doesn’t allow us to change the object (primary / unique key), you idea is very good.

I would never change the database of a product, when the product manages the database itself. You never know which changes will come and cause troubles with your adjustments (although it seems to be very unlikely in your use case).

Keycloak can only provide some set of functionality, it cannot cover any use cases all the customers have, especially not for every database customers use. I see Keycloak as a very good integration platform to extend easily with supported SPIs and thus getting the functionality one needs.

1 Like

You are right…

Maybe it should be addressed in the sourcecode.

Thanks again

Best

Jan