3

I use PostgreSQL for time series data. There is an Event table and partitioned tables like Event_2016, Event_2017 which inherit from Event with a CONSTRAINT CHECK for the date range. So when querying the Event table, PostgreSQL uses only the child tables that are needed.

To roll up events I use an EventByDay materialized view. Refreshing this requires reading from all Event_* tables.

Can I use Materialized Views the same way as tables above to limit the amount of data in each Materialized View? (EventByDay_2016 inherits from EventByDay).

Nikolay Fominyh
  • 8,946
  • 8
  • 66
  • 102
KingOfCoders
  • 2,253
  • 2
  • 23
  • 34

1 Answers1

4

No, a MVIEW can not participate in table inheritance.

But you can create a (regular) child table and then use insert into .. select ... using the query from the MVIEW. If you want to store the MVIEW's query in the database then create a view that you use for populating the child table.

Something like this:

Initial setup:

create view v_event_by_day_2016 
as 
-- this is the complete query from your MVIEW
select ...;

create table event_by_day_2016
as
select *
from v_view_one;

alter table event_by_day_2016 inherit event_by_day; 
alter table event_by_day_2016 add constraint check (...);

Refresh the table:

truncate table event_by_day_2016;
insert into event_by_day_2016
select *
from v_event_by_day_2016;

Alternatively you can use delete to so that the data in the child table can be refreshed in a transactional manner.

  • Thanks, the "truncate .. insert" would show different behaviour than a concurrent refresh on the MVIEW? – KingOfCoders Feb 23 '17 at 10:04
  • @StephanSchmidt: yes. That's why I also mentioned the `delete` method which only makes the changes visible after you commit the delete and insert. –  Feb 23 '17 at 10:11
  • Ok, then I'll go that route and hope Postgres supports partial update on MVIEWs or inheriting MVIEWs in the future. – KingOfCoders Feb 23 '17 at 11:06