Better Pub/Sub/Events mechanism inside kernelci-api

[nuclearcat]

Right now we are relying on Redis pub/sub which are proven to be broken.
I propose to migrate to replace redis pub/sub by local SQLite database in WAL mode. This will make persistent events database (even if API restarted, now restart of API or Redis make us lose all events), and additionally we can implement guaranteed events processing mechanism.

Database schema proposal:

for Messages

• messages(id INTEGER PRIMARY KEY AUTOINCREMENT, topic TEXT, created_at DATETIME, expires_at DATETIME NULL, payload TEXT, owner TEXT, acked_at DATETIME NULL, acked_by TEXT NULL)

For Subscriptions

• subscriptions(id INTEGER PRIMARY KEY AUTOINCREMENT, topic TEXT, user TEXT, promiscuous BOOLEAN, created_at DATETIME, last_poll DATETIME, last_message_id INTEGER)

Core flows:

• Publish
• Subscribe
• Listen
• Ack (optional)
• Cleanup (background task)

First stage - full compatibility. Goal: removing Redis from equation, improving consistency.

• Publish
  • INSERT into messages(topic, expires_at(24h), owner, payload).
• Subscribe
  • INSERT into subscriptions(topic, user, promiscuous, created_at, last_poll, last_message_id). last_message_id is current last message on topic in messages.
• Listen
  • on promisc - SELECT FROM messages WHERE topic=? AND id > subscriptions.last_message_id LIMIT 1
  • on non-promisc - SELECT FROM messages WHERE topic=? AND id > subscriptions.last_message_id AND owner=subscriptions.user LIMIT 1
    After sending to client we update subscriptions.last_message_id.

Second stage introduction guaranteed message processing by ACK

• Add unack_only flag to subscriptions?
• Modify Listen to respect unack_only, so we receive only unacknowledged messages.
• Add ACK command so we can acknowledge messages (set acked_at=now() and acked_by= user).
  TODO: problem, we might have issue of message received and ACKed by multiple users.
  Maybe UPDATE messages SET acked_at=now(), acked_by=user WHERE id=? AND acked_at IS NULL

[JenySadadia]

Hi,
Thanks for the proposal.
One thing that crossed my mind was why don't we keep on using the current DB i.e., MongoDB, and just add new models and CRUD methods for pubsub?
Also, Redis is fast and performance-efficient. Wouldn't the API performance be impacted by frequent queries to DB?

[nuclearcat]

Yes, i thought about MongoDB, but using it has several drawbacks:
1)Its remote service, so it will add latency.
2)MongoDB is really inefficient with frequently deleted data, it needs compact() on such databases and this is disk I/O heavy operation. MongoDB accumulates fragmentation with frequent deletes and updates.
3)We will "wash" our primary data cache for indexes and frequently accessed data by PubSub data, so API might become slower. Mixing ephemeral pub/sub traffic with primary API data could evict hot indexes from cache, slowing API queries.

Redis mostly make sense for services where:
1)We can dedicate large instance for key/value and we have large volume of such data (not our case AFAIK)
2)Our service is distributed and we have for example multiple API backends. Also not our case, kernelci-api doesn't support sharing PubSub redis.
So, If you don’t need clustering or multiple backends, it’s overkill.

Plus major issue Redis cannot provide us ACK functionality in current mode, and implementing ACK and other features require ACID transactional consistency, which Redis and MongoDB don't have. (e.g. multiple subscribers pickup job, but only one of them will win it, this is technically reliable and easy only with ACID).

This problem not resolved yet, i left comment TODO: problem, we might have issue of message received and ACKed by multiple users., but now i came to conclusion that i might need ACID and job reservation using it.