When osquery is installed on Windows Domains which contain several thousands of users, table like users, groups, user_groups perform poorly.
This gets worse when a JOIN is used to obtain the group information for each user.
With a domain controller containing tens of thousands of users and groups, the JOIN query would basically never end, with the LSASS process fully using a core for the whole time.

The reason is that the groups table does not implement improved filtering on index columns,
so it is forced to return all the rows each time, and then sqlite has to do filtering by scanning the results multiple times,
for each user.
Also many tables use gid and uid which are not useable directly by the Windows API, so implementing a performant filtering with that was not possible.
There are other reasons and they were explored by this issue: #7417

Given that changing the schema is always a bit undesirable and given that the process to do so would've taken several intermediate steps to get to a good a place, I've opted to focus on performance first.
Correctness on not using gid, uid and providing the correct group/list of groups can come later.

Note that while on osquery >= 4.9.0 it is possible to write a query joining users and groups to greatly reduce its run time to few minutes, depending on the amount of users:

WITH cached_groups AS MATERIALIZED (
	SELECT * from groups;
)
SELECT username, uid, gid, groupname FROM users as u JOIN cached_groups as cg ON u.gid=cg.gid;

This would also move the processing on the osquery process, so osquery would be using 100% of a core for those minutes required to complete the query, causing the watchdog limits to be increased.

The solution proposed here gives more control in how much resources osquery uses or how much overhead it causes on the system instead. It also should have a normal JOIN query return in few seconds.
The only downside is that while using this new implementation I'm proposing is also further improving the runtime of the above workaround query, because generating the users and groups results is faster, this still hits a cap due to the fact that sqlite has to do the filtering by scanning the results returned by the groups table multiple times, ending up using again 100% of a core for some minutes (fewer than before obviously).
Therefore it's highly recommended to not use that form of query with the WITH clause to do caching.