Local-First Relation Views

Abstract

In today's digital landscape where cloud-oriented approaches are widespread and an integral part, local-first software emerges to offer an alternative. It addresses concerns such as data control, privacy, offline capabilities, collaboration, and performance. The open-source relational database engine SQLite is a fitting candidate for local-first software as it is not reliant on network connectivity. SynQLite is an extension of SQLite that enables CRR (conflict-free replicated relations) support, applying CRDTs (conflict-free replicated data types) to allow replicating changes between sites without a dedicated coordinator. In this thesis, we implement materialized views in SQLite on top of SynQLite. The views are incrementally maintained to increase performance in comparison to complete refresh, and use data provenance and causal length in order to track changes across sites and determine how they should be applied to the views. Experiments comparing the incremental refresh against the complete refresh show that the incremental approach is generally faster, at the cost of extra storage usage.

In today's digital landscape, characterized by widespread cloud-oriented approaches, the concept of local-first software has emerged as an alternative solution to address concerns related to data control, privacy, offline capabilities, collaboration, and performance. This thesis focuses on implementing materialized views on top of SynQLite, an extension of the SQLite relational database engine, to enhance performance and support local-first software principles.

The SynQLite extension to SQLite offers CRR(conflict-free replicated relations) support by applying CRDTs (conflict-free replicated data types). This allows for replicating changes between sites without a dedicated coordinator. By implementing incrementally maintained materialized views, we aim to increase performance compared to complete refresh approaches. Our implementation incorporates data provenance and causal length to track changes across sites and determine how they should be applied to the views.

Through experiments comparing the incremental refresh with the complete refresh, we find that the incremental approach generally achieves faster performance, albeit with additional storage usage. These findings demonstrate the potential of materialized views within local-first software applications. This research contributes to the understanding and implementation of local-first software principles in the context of SQLite and SynQLite, paving the way for improved data management and collaboration in decentralized environments.