You can always implement your own table serving as "materialized view". That's how we did it before
MATERIALIZED VIEW was implemented in Postgres 9.3.
You can create a plain
CREATE VIEW graph_avg_view AS
SELECT xaxis, AVG(value) AS avg_val
GROUP BY xaxis;
And materialize the result once or whenever you need to start over:
CREATE TABLE graph_avg AS
SELECT * FROM graph_avg_view;
(Or use the
SELECT statement directly, without creating a
Then, depending on undisclosed details of your use case, you can
INSERT changes manually.
A basic DML statement with data-modifying CTEs for your table as is:
Assuming nobody else tries to write to
graph_avg concurrently (reading is no problem):
WITH del AS (
DELETE FROM graph_avg t
WHERE NOT EXISTS (SELECT FROM graph_avg_view WHERE xaxis = t.xaxis)
, upd AS (
UPDATE graph_avg t
SET avg_val = v.avg_val
FROM graph_avg_view v
WHERE t.xaxis = v.xaxis
AND t.avg_val <> v.avg_val
-- AND t.avg_val IS DISTINCT FROM v.avg_val -- alt if avg_val can be NULL
INSERT INTO graph_avg t -- no target list, whole row
FROM graph_avg_view v
WHERE NOT EXISTS (SELECT FROM graph_avg WHERE xaxis = v.xaxis);
- Add a
timestamp column with default
now() to your base table. Let's call it
- If you have updates, add a trigger to set the current timestamp with every update that changes either
Create a tiny table to remember the timestamp of your latest snapshot. Let's call it
CREATE TABLE mv (
tbl text PRIMARY KEY
, ts timestamp NOT NULL DEFAULT '-infinity'
); -- possibly more details
Create this partial, multicolumn index:
CREATE INDEX graph_mv_latest ON graph (xaxis, value)
WHERE ts >= '-infinity';
Use the timestamp of the last snapshot as predicate in your queries to refresh the snapshot with perfect index usage.
At the end of the transaction, drop the index and recreate it with the transaction timestamp replacing the timestamp in the index predicate (initially
'-infinity'), which you also save to your table. Everything in one transaction.
Note that the partial index is great to cover
UPDATE operations, but not
DELETE. To cover that, you need to consider the entire table. It all depends on exact requirements.