We have a table to store the log of user login, *user\_login\_log*. This table receives a record every time a User logs in.

[![Table](https://i.stack.imgur.com/EA6dC.jpg)](https://i.stack.imgur.com/EA6dC.jpg)

We have a use case to get the last login timestamp for every user and this data will be fetched frequently. The requirement is that this data should be loaded up quickly.

**Requirements**

1. The data should be loaded quickly.
2. The data should not be stale.

The log table can have tens of thousands of records, hence querying them every time will be a slow process. We came up with two approaches to retrieve data quickly.

**Approach 1**: Our initial thought was to use a materialized view.

[![Materialized View Trigger](https://i.stack.imgur.com/Ux5wH.jpg)](https://i.stack.imgur.com/Ux5wH.jpg)

The issue with this approach is that, as the data in the *user\_login\_log* table increases, the query to refresh the materialized view will become slower, hence making INSERT, UPDATE and DELETE operations slower.

**Approach 2**: Another idea was to use a dedicated snapshot table to store the latest login data.

[![Trigger to update Snapshot table](https://i.stack.imgur.com/P0Y6B.jpg)](https://i.stack.imgur.com/P0Y6B.jpg)

And then the data would be retrieved directly from the Snapshot table. The issues with this approach is that we'll be maintaining the same data in two tables and the trigger function will be responsible for maintaining data integrity.

Which of these methods is better for our use case? Is there any other method that will be more effective to filter and retrieve the required data?