How to cleanup the JdbcMetadataStore?

Question

Initially our flow of cimmunicating with google Pub/Sub was so:

1. Application accepts message
2. Checks that it doesn't exist in idempotencyStore
3. 3.1 If doesn't exist - put it into idempotency store (key is a value of unique header, value is a current timestamp)
   3.2 If exist - just ignore this message
4. When processing is finished - send acknowledge
5. In the acknowledge successfull callback - remove this msg from metadatastore

The point 5 is wrong because theoretically we can get duplicated message even after message has processed. Moreover we found out that sometimes message might not be removed even although successful callback was invoked( Message is received from Google Pub/Sub subscription again and again after acknowledge[Heisenbug]) So we decided to update value after message is proccessed and replace timestamp with "FiNISHED" string

But sooner or later we will encounter that this table will be overcrowded. So we have to cleanup messages in the MetaDataStore. We can remove messages which are processed and they were processed more 1 day.

As was mentioned in the comments of https://stackoverflow.com/a/51845202/2674303 I can add additional column in the metadataStore table where I could mark if message is processed. It is not a problem at all. But how can I use this flag in the my cleaner? MetadataStore has only key and value

Answer 1

In the acknowledge successfull callback - remove this msg from metadatastore

I don't see a reason in this step at all.

Since you say that you store in the value a timestamp that means that you can analyze this table from time to time to remove definitely old entries.

In some my project we have a daily job in DB to archive a table for better main process performance. Right, just because we don't need old data any more. For this reason we definitely check some timestamp in the raw to determine if that should go into archive or not. I wouldn't remove data immediately after process just because there is a chance for redelivery from external system.

On the other hand for better performance I would add extra indexed column with timestamp type into that metadata table and would populate a value via trigger on each update or instert. Well, MetadataStore just insert an entry from the MetadataStoreSelector:

return this.metadataStore.putIfAbsent(key, value) == null;

So, you need an on_insert trigger to populate that date column. This way you will know in the end of day if you need to remove an entry or not.