Rollup vs View
Rollup Tables (Physical Tables)
- What they are: Real database tables that store pre-computed, aggregated data
- How they’re updated: Database triggers automatically update them when source data changes
- Storage: Data is physically stored on disk
- Performance: Very fast reads (no computation needed)
Example:
-- This function UPDATES the rollup tables
CREATE FUNCTION update_event_rollups(_instance_id text, _event_type text, ...)
RETURNS void AS $$
-- Updates latest_instance_metadata table
INSERT INTO latest_instance_metadata (instance_id, launch_date, ...)
-- Updates latest_instance_state table
INSERT INTO latest_instance_state (instance_id, state, ...)
$$;Views (Virtual Tables)
- What they are: Stored SQL queries that compute results on-demand
- How they work: Execute the underlying query each time you access the view
- Storage: No data stored, just the query definition
- Performance: Slower (must compute results each time)
Example:
-- This is a VIEW - just a stored query
CREATE VIEW public.latest_live_instance_state_metadata AS
SELECT * FROM public.latest_instance_state_metadata
WHERE (state <> 'EC2_STATE_CHANGE_SHUTTING_DOWN'::text);