Overview
Reports often aggregate large transactional tables repeatedly. A summary table can reduce response time by storing precomputed totals, but it introduces freshness, reconciliation, and refresh complexity. Define the acceptable delay before choosing this design.
Start with the workload and the invariant
Identify the exact report dimensions and measures: for example, daily order count and revenue by tenant and currency. Avoid combining currencies without conversion rules. Choose a grain that supports the required filters without creating an unmanageable number of rows.
Practical example
CREATE TABLE daily_sales_summary (
tenant_id BIGINT NOT NULL,
sales_date DATE NOT NULL,
currency CHAR(3) NOT NULL,
order_count BIGINT NOT NULL,
gross_total DECIMAL(15,2) NOT NULL,
PRIMARY KEY (tenant_id, sales_date, currency)
);
Refresh strategies include scheduled full rebuilds, incremental updates, and event-driven aggregation. Full rebuilds are simpler but may become expensive. Incremental updates are faster but must handle retries, corrections, refunds, and late-arriving events. Keep the source transaction as the system of record.
Trade-offs and failure modes
Make refresh jobs idempotent. One approach is to aggregate a bounded time window into a staging table and replace the corresponding summary range in a transaction. Another is to consume an outbox event with a unique event ID. Avoid incrementing totals blindly when the same event can be delivered twice.
Verification and rollout
Expose the summary's refreshed_at or watermark so the interface can communicate freshness. Reconcile sampled periods against the source tables and alert when totals drift. Build a repair job that can recompute a date range without duplicating values.
Engineering checklist
Test daylight-saving boundaries, timezone conversion, refunds, deleted or cancelled records, multi-currency reporting, and job interruption. Summary tables are worthwhile when the report is frequent and the freshness contract is explicit.
Before shipping, review the database version, storage engine, SQL mode, character set, collation, and deployment process used by the target environment. Features and DDL behavior can differ between MySQL and MariaDB releases. Validate the exact statements on a representative staging database, capture a baseline, and keep a tested recovery path for changes that affect production data.