Sometimes it's customer enforced. Just based on various places I've worked.

One place for example sold location-based software, so users could have access to say 10k out of 100k locations, but those locations might not always be next to each other. Data would be stored with a location id, and to do things like "show the average star rating for the locations this user has access to", we'd do "aggregate star rating where location id IN [....]"

Oh okay, so you can store authorization data in a table mapping user_ids to location_ids right? Then instead of using `IN (big_list)` (which can't use indexes) you join on the mapping table (which can use indexes). Something like

    CREATE TABLE locations (
        location_id INT PRIMARY KEY,
        rating INT
    );

    CREATE TABLE authorized_locations (
        user_id INT REFERENCES users (user_id),
        location_id INT REFERENCES locations (location_id)
    );
    CREATE INDEX authorized_locations_user_id_idx ON authorized_locations (user_id);
    CREATE INDEX authorized_locations_location_id_idx ON authorized_locations (location_id);

    SELECT AVG(rating) FROM locations JOIN authorized_locations USING (location_id) WHERE user_id = $1;

In (list) can use an index just fine in most databases. It's "not equal" that has trouble.

A better example would be:

Product {LocationID, Rating} User{id} UserLocations{UserID, locationID)

However, let's say you have an architecture where you have 100s of these "product" tables, since every piece of the app restricts access by location. So not only do you have Product but maybe you have FormResult{LocationID}, Feedback{LocationID} etc.

So for a service to find out what a user has access to, first they call the User service to get a list of locations, and then they pass the big list of ids to their own DB, whatever that DB is.

I guess this is one case where a monolith and good ol SQL would win. :)