Databases & System Design•Part 2: Relational Databases (RDBMS / PostgreSQL / MySQL) (6 - 25)
Stored Procedures, Triggers, and Views:
In Simple Words:
- Stored Procedures: Compiled business logic executing inside the database.
- Triggers: Code executing automatically before/after an insert or update.
- Materialized Views: Queries whose results are saved on disk for fast analytical reporting.
sql
-- Materialized View for fast analytics:
CREATE MATERIALIZED VIEW mv_daily_sales AS
SELECT date_trunc('day', created_at) AS day, sum(amount) AS total_revenue
FROM orders
GROUP BY 1;
-- Refresh periodically in background:
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_sales;