What is the best way to implement sql query?

Question

PostgreSQL

There is a postgresql DBMS, there is a plate with the fields of time, username, user type and geo.coordinates. There are many users, about 5-7 types of users. Every n minutes, users (and the time at the time of recording) who are in certain zones are written to the table in a batch, if they are not in these zones, then they are not recorded. n for each type can be different. In fact, there are two tables, in one row per user, and in the second type, time and an array of users who were marked at that moment in time.
I would like to get a quick query that would give out the user, the type of event (appeared, disappeared) and the time of the event.
based on window functions lag, lead, somehow it doesn’t work out very quickly, and besides, they can’t be stuffed into where. Maybe there is something else?
P.s. Perhaps it is better to solve such tasks in a stream as new records appear and probably not even using sql, but now there is no other possibility.

Answer 1

Write data to memcache and to the database in case memcache crashes.
Get all the necessary data from the cache.
In fact, memcache will be a shield in front of the database. If nothing is found on the request, then you will have to go to the database for the data.
Although Memcach is not a database, it is also possible to achieve atomicity from it php.net/manual/ru/memcached.cas.php