Split backend connection by read and write

[javuto]

Specify read and writer endpoints for the backend so it can be handled more efficiently.

[zhuoyuan-liu]

I would like to take this issue with focus on osctrl-tls. I believe it's also the first step for other performance optimization tasks.

[zhuoyuan-liu]

Currently, our OsqueryNode table maintains several timestamp columns—such as last_config, last_result, last_query_read, and last_status—to track when a node last reached out. Since these columns all represent similar events (i.e. a node "checking in"), I propose that we consolidate them into a single column called last_status. This change would simplify the schema and make it easier to reason about node activity.

In addition, because osquery nodes only check in every X minutes, we don’t need a 100% precise timestamp. We can tolerate a slight deviation (within less than X minutes) between the actual last-seen time and what’s stored in the database. This approximation would allow us to batch updates, thereby reducing the frequency of write operations.

Furthermore, regarding the IP address:

• We should make IP address recording optional—configurable for environments where privacy is a concern or where IP data is not needed.
• When IP recording is enabled, the system should update the stored IP address only when it actually changes rather than on every node check-in. Since a node's IP can change frequently (and often insignificantly), this condition will further reduce unnecessary write requests to the DB.

I believe these changes would improve our system’s efficiency while still providing sufficient information about node activity.

[javuto]

How about I make the columns last_ to be updated in the DB every few hours, using a goroutine and the current value is kept in redis? I can start working on those changes and it should utilize the backend even less.

[zhuoyuan-liu]

How about I make the columns last_ to be updated in the DB every few hours, using a goroutine and the current value is kept in redis? I can start working on those changes and it should utilize the backend even less.

Yes, it would be the next step. For now, caching them in memory already increased the performance significantly and would fit a lot of use cases. If we need to keep scaling up osctrl, it would be the same way as we did now. We just changed the backend from in-memory to Redis and it would be configurable. You can see the example here: https://github.com/zhuoyuan-liu/osctrl/blob/sync/tls/handlers/writers.go

What I want to discuss is that we can merge these last_ label into one as they have similar meanings ---indicate osquery last seen time. Also, we can remove some of the tables that have written heavy operations but didn't give much value.