Michael Avrukin writes about activerecord-materialized, a Ruby gem that provides materialized view functionality for Rails applications running on databases like MySQL or SQLite. By precomputing complex queries into cache tables and refreshing them in the background when dependent data changes, it offers fast read access through standard ActiveRecord methods without requiring native database support for materialized views.
* Employs incremental maintenance (IVM) by default to refresh only affected partitions of GROUP BY views.
* Features a self-healing mechanism that reconciles the view against its source to fix potential data drift.
* Demonstrates significant performance gains, with benchmark speedups up to 49,000x for specific queries.
* Employs incremental maintenance (IVM) by default to refresh only affected partitions of GROUP BY views.
* Features a self-healing mechanism that reconciles the view against its source to fix potential data drift.
* Demonstrates significant performance gains, with benchmark speedups up to 49,000x for specific queries.
This article introduces an experimental build of MySQL 9.7 that integrates DuckDB as a storage engine. By marking tables with ENGINE=DuckDB, users can perform high-speed analytical queries using a columnar engine within the same connection and without duplicating data. The integration allows for automatic query pushdown where complex aggregations are offloaded to DuckDB while simple lookups remain on the standard InnoDB row path.
* Integration of an in-process columnar query engine into MySQL 9.7
* Dramatic performance increases for TPC-H workloads compared to traditional InnoDB
* Automated routing between row and column storage paths based on query complexity
* Efficient data loading via DuckDB's COPY command interface instead of per-row insertion
* Integration of an in-process columnar query engine into MySQL 9.7
* Dramatic performance increases for TPC-H workloads compared to traditional InnoDB
* Automated routing between row and column storage paths based on query complexity
* Efficient data loading via DuckDB's COPY command interface instead of per-row insertion
