Materialized views for performance optimization

-- Incremental refresh pattern (manual)

-- Track last refresh time
CREATE TABLE mv_refresh_log (
  view_name VARCHAR(100) PRIMARY KEY,
  last_refresh TIMESTAMP NOT NULL
);

INSERT INTO mv_refresh_log VALUES ('user_statistics', '1970-01-01');

-- Incremental refresh function
CREATE OR REPLACE FUNCTION refresh_user_statistics_incremental()
RETURNS VOID AS $$
DECLARE
  last_refresh TIMESTAMP;
BEGIN
  -- Get last refresh time
  SELECT mv_refresh_log.last_refresh INTO last_refresh
  FROM mv_refresh_log
  WHERE view_name = 'user_statistics';

  -- Delete changed rows
  DELETE FROM user_statistics
  WHERE id IN (
    SELECT DISTINCT user_id
    FROM orders
    WHERE updated_at > last_refresh OR created_at > last_refresh
  );

  -- Recalculate changed rows
  INSERT INTO user_statistics
  SELECT
    users.id,
    users.username,
    COUNT(DISTINCT orders.id) AS order_count,
    COALESCE(SUM(orders.total), 0) AS total_spent,
    COALESCE(AVG(orders.total), 0) AS avg_order_value,
    MAX(orders.created_at) AS last_order_date,
    COUNT(DISTINCT DATE(orders.created_at)) AS active_days
  FROM users
  LEFT JOIN orders ON users.id = orders.user_id
  WHERE users.id IN (
    SELECT DISTINCT user_id
    FROM orders
    WHERE updated_at > last_refresh OR created_at > last_refresh
  )
  GROUP BY users.id, users.username;

  -- Update refresh log
  UPDATE mv_refresh_log
  SET last_refresh = CURRENT_TIMESTAMP
  WHERE view_name = 'user_statistics';
END;
$$ LANGUAGE plpgsql;

-- Run incremental refresh
SELECT refresh_user_statistics_incremental();

-- Compare view vs materialized view performance
EXPLAIN ANALYZE
SELECT
  users.id,
  COUNT(orders.id) AS order_count,
  SUM(orders.total) AS total_spent
FROM users
LEFT JOIN orders ON users.id = orders.user_id
GROUP BY users.id
ORDER BY total_spent DESC
LIMIT 10;
-- Execution time: 2500ms (slow, scans all data)

EXPLAIN ANALYZE
SELECT * FROM user_statistics
ORDER BY total_spent DESC
LIMIT 10;
-- Execution time: 5ms (fast, reads cached data)

-- Materialized view size
SELECT
  pg_size_pretty(pg_total_relation_size('user_statistics')) AS total_size,
  pg_size_pretty(pg_relation_size('user_statistics')) AS table_size,
  pg_size_pretty(pg_total_relation_size('user_statistics') -
                pg_relation_size('user_statistics')) AS indexes_size;

-- Regular view (computed on demand)
CREATE VIEW user_stats_view AS
SELECT
  users.id,
  users.username,
  COUNT(DISTINCT orders.id) AS order_count,
  COALESCE(SUM(orders.total), 0) AS total_spent
FROM users
LEFT JOIN orders ON users.id = orders.user_id
GROUP BY users.id, users.username;

-- Always current, but slower queries
SELECT * FROM user_stats_view WHERE id = 123;

-- Materialized view dependencies
SELECT
  dependent_view.relname AS dependent_view,
  source_table.relname AS source_table
FROM pg_depend
JOIN pg_rewrite ON pg_depend.objid = pg_rewrite.oid
JOIN pg_class AS dependent_view ON pg_rewrite.ev_class = dependent_view.oid
JOIN pg_class AS source_table ON pg_depend.refobjid = source_table.oid
WHERE dependent_view.relkind = 'm'  -- m = materialized view
  AND source_table.relkind = 'r';    -- r = ordinary table

-- Parallel refresh (for very large materialized views)
SET max_parallel_workers_per_gather = 4;
REFRESH MATERIALIZED VIEW CONCURRENTLY large_mv;

-- MySQL alternative: Summary tables with triggers
CREATE TABLE user_statistics (
  user_id INT PRIMARY KEY,
  order_count INT DEFAULT 0,
  total_spent DECIMAL(10,2) DEFAULT 0,
  last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Trigger to update statistics
DELIMITER $$
CREATE TRIGGER update_user_stats_after_order
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
  INSERT INTO user_statistics (user_id, order_count, total_spent)
  VALUES (NEW.user_id, 1, NEW.total)
  ON DUPLICATE KEY UPDATE
    order_count = order_count + 1,
    total_spent = total_spent + NEW.total;
END$$
DELIMITER ;