spring-modulith: Postgres index definitions for event registry breaking inserts of publications
Spring Modulith: 1.2.0-M2
After introducing indexes on ENVENT_PUBLICATION table (https://github.com/spring-projects/spring-modulith/issues/403) events with bigger payload are not working due to index size limit.
INSERT INTO EVENT_PUBLICATION (ID, EVENT_TYPE, LISTENER_ID, PUBLICATION_DATE, SERIALIZED_EVENT)
VALUES (?, ?, ?, ?, ?)
raises error
ERROR: index row requires 36896 bytes, maximum size is 8191
About this issue
- Original URL
- State: closed
- Created 3 years ago
- Comments: 15 (5 by maintainers)
Commits related to this issue
- GH-519 - Improve Postgres default indexes. We now use a hash index for the serialized event to improve performance. — committed to spring-projects/spring-modulith by odrotbohm 3 years ago
I’ll make sure the optimization gets applied in the 1.2 GA release in two weeks and the corresponding service releases for 1.0.x and 1.1.x.
I’m not sure about other databases, but for PostgreSQL we found that the hash index works well given that Modulith is doing a full equality comparison on the serialized event instead of searching for a substring. And it’s not restricted by the size limit that the b-tree index has. We saw a significant performance improvement in the setting of the completion timestamp after adding this index.
Okay, I’ll replace the original index for Postgres then. I think I’ll refrain from adding additional indexes to make sure we only support the interactions our code triggers. Totally fine if you add more indexes to support interactions you need on top.