Let me start by stating that my DBMS is PostgreSQL 9.4.
I have a database with, say 30 tables. Each table has a mirror table with historical data, that is the same columns and some timestamps.
To service a webpage, where all kinds of history is filterable and sortable, I need some way to combine these 30 tables, so that I can sort by e.g. the "changed_at" date. In this case, "changed_at" is a column that is common between all the tables. Other columns may be unique to the different tables (which would make the
UNIONs a little weird, since I would have to pad with
NULL columns to make the tables fit together)
My first idea is to use
table1 UNION table2 UNION ... UNION table30
There are some additional requirements, which make these 30 queries non-trivial (each need to join another table to get some values).
Ultimately, this gets pretty slow. So my question is how to improve on this?
- Can I use indexing to somehow speed things up?
- Are there an alternative to
UNION, that I am not aware of?
- Will materialized views be the solution?
Since it is the joining that is slow, I would imagine that the materialized view would be a big help here. I know there is the caveat that the materialized view does not represent current data, as it needs to be refreshed. I believe this fits okay with my requirements, depending on how often I can refresh the view (would every 30 minutes be okay?)
I know I may have essentially solved my problem, but I am here for pointers as to what I may have missed.
An concrete example that I tested (with just two unions) can be found below. Each query joins a "comments" table to attach a comment and has a lateral self join to attach the previous version for each row.
( SELECT 'control', a.id, a.sys_period, p.sys_period as p_sys_period, b.comment, b.author_id FROM controls_control_history_view as a -- With comments LEFT JOIN log_changecomment as b ON referenced_table = 'controls_control_history' AND referenced_id = a.id AND b.referenced_date = lower(a.sys_period), -- With previous LATERAL ( SELECT * FROM controls_control_history_view prev WHERE prev.id = a.id AND prev.sys_period < a.sys_period ORDER BY prev.sys_period DESC LIMIT 1 ) AS p -- Unioned ) UNION ALL ( SELECT 'action', a.id, a.sys_period, p.sys_period as p_sys_period, b.comment, NULL FROM actions_action_history_view as a -- With comments LEFT JOIN log_changecomment as b ON referenced_table = 'actions_action_history' AND referenced_id = a.id AND b.referenced_date = lower(a.sys_period), -- With previous LATERAL ( SELECT * FROM actions_action_history_view prev WHERE prev.id = a.id AND prev.sys_period < a.sys_period ORDER BY prev.sys_period DESC LIMIT 1 ) AS p ) ORDER BY sys_period ;
EXPLAIN ANALYSE output can be found here