Given a table like so:
CREATE TABLE attr (user_id uuid, key text, value text);
with indexes on
value (but not a combined index) what are my options for getting a list of distinct values by key, without also destroying write performance? The obvious
SELECT DISTINCT value WHERE key = :key is not cutting it performance-wise (currently the table is at ~20M rows, the actual table in production has more columns but is semantically similar).
I could maintain a separate table of the distinct values, which is easy enough to append to but is hard to determine when there are no longer any rows with the corresponding value in the
attr table. I don't expect a lot of values to become "stale" given the nature of my data (user attributes) but it is possible, and I'd like to avoid having to manually clean it up!