It's a short, easy query to write. I'm not convinced it's a big need.

So, your proposal is basically this: make a metadata table. On every insert or delete from the table, update the metadata table accordingly. MVCC will make sure anybody reading from it will get the right answer. Is that about right?

I do not see how this would be implemented without introducing extra locking which would harm concurrency, which would remove some of the performance advantage of having MVCC.

Now I see it could be worth taking that penalty on specified tables, jsut like you do for indexes. And some databases do support this in a more general form: materialized views.

I think you could fake this proposal with triggers. It might be worth seeing what the additional cost is.

Proper solution (not fake with triggers) should be at most as costly as having partial index on the table.