Realtime batching

I'm building a real-time boat tracker.

The idea:

• A table stores the location updates of each individual boat.

• Another table stores an aggregated JSON document with the positions of all boats. This JSON is sent to all clients with the real-time API.

If there are, for example, 100 boats updating every second, I would end up with 100 updates per second to the aggregated JSON, which is too frequent and creates high load for both clients and server.
I want to limit the frequency of updates to the aggregated document (e.g., update it at most once per second), while still keeping all individual boat positions up-to-date. What would be the best approach to implement this?

I think putting items in a queue and processing them at the same interval would probably work here?

I think I would have a boat location queue table that maybe has something like boat id, lat/lng, and status. Then fetch all that information to aggregate it and bulk update the queue status for them? that way you aren't aggregating a lot per second but once per second for every boat that is active?

The problem is who to schedule for 1s loop aggregation

Can you schedule a function that runs on a 1 second cron?

No 😔

why not? too many runs?

No, the minimum time is 1m

Appwrite doesn't allow you to set something less than that due to chron syntax

Well, maybe I can add some kind of cooldown in the function 🤔

So you need something that gets the data and holds it until there's X amount of data or Y time has elapsed.

Once the time has elapsed, update the aggregate document.

You could do this with a function, but I'm slightly worried about conflicting writes on the aggregate document

Yes, that's the issue, how to hold it for X time

couldn't you have a separate table storing everything with a date/time, and then then either have a cron job running every few seconds checking if there are X amount of data or check the "oldest" data and see if Y time elapsed and if either happens, you just use that data however you need it like write it into another table

essentially using this table as a temporary storage