CODEWITHSUNDEEP
postgresqlmaterialized-views
Adnan Arif Sait
Adnan Arif Sait

Reputation: 113

Retrieve data faster from large tables using Materialized Views

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

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

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

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?

Upvotes: 1

Views: 654

Answers (2)

Belayer
Belayer

Reputation: 14861

It seems you have 2 issues. The simple one, how to store the data. This being the issue under discussion. The simplest, as suggested, is just a column in your user table, but raises the question do you need the login history. If so then also create a login_hist table and populate that with a trigger on your user_table. Something else to consider is might other apps (now or in the future) share the same database.
The more difficult issue may be: How do you capture the data in the first place? You have suggested a trigger, but a trigger on what. Unfortunately, Postgres does not supply a logon trigger. Perhaps with the login_hook extension. But that seems to be a non-standard extension, many installations will not allow it installed.
Good Luck!

Upvotes: 0

jjanes
jjanes

Reputation: 44137

Your first approach doesn't make a lot of sense. Why build a trigger, which has the exact information you need, only to throw that information away and have it rebuild the entire table from scratch? Yes, of course that will be slow.

In your second approach, why create a 2nd table just to hold one column of information? Don't you already have an "app_user" table that you can add a column to?

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.

I don't see an issue with that. Isn't this what triggers are for? Just code it correctly, and make sure the user login event is in its own transaction, to avoid locking a record for an undue amount of time.

Another option would be to code up a recursive CTE to do a loose index scan over your existing user_login_log table. This might be fast enough to avoid having to do anything special upon write.

Upvotes: 1

Related Questions