ALEX: Adaptive Log-Embedded Extent Layer for Low-Amplification SQLite Writes on Flash Storage

Abstract

Efficient metadata and page management are essential for sustaining database performance on modern flash-based storage. However, conventional SQLite configurations—rollback journal and WAL—often trigger excessive small writes and frequent synchronization events, leading to high write amplification and degraded tail latency, particularly on UFS and NVMe devices. This study introduces ALEX (Adaptive Log-Embedded Extent Layer), a lightweight VFS-level extension that coalesces scattered 4 KB page updates into sequential, page-aligned extents while embedding compact log records for recovery. The proposed design reduces redundant writes through in-memory page deduplication, minimizes fdatasync()frequency by flushing multi-page extents, and preserves full SQLite compatibility. We evaluate ALEX on both Linux NVMe SSDs and Android UFS storage under controlled workloads. Results show that ALEX significantly lowers write amplification, reduces sync counts, and improves p95–p99 write latency compared with baseline SQLite modes. The approach consistently achieves near-sequential write patterns without modifying SQLite internals. These findings demonstrate that lightweight extent-based coalescing can provide substantial efficiency gains for embedded and mobile database systems, offering a practical direction for enhancing SQLite performance on flash devices.

1. Introduction

The rapid proliferation of mobile and embedded applications has significantly increased the demand for reliable, low-latency local storage on consumer devices. Modern smartphones execute thousands of small data updates per second—such as chat messages, sensor logs, application states, and metadata commits—while maintaining stringent energy and responsiveness constraints. To meet these requirements, most commercial devices adopt Universal Flash Storage (UFS), whose internal organization is based on NAND flash memory. Although UFS offers high sequential bandwidth, its performance degrades substantially under small, random-write workloads, which trigger expensive read-modify-write cycles and frequent garbage collection (GC) inside the flash translation layer (FTL) [1,2,3,4].

A notable source of such workloads is SQLite, the default embedded database in Android, iOS, and numerous IoT systems. SQLite is known for its simplicity and portability, but its default write path generates synchronous 4 KB page updates. These updates cause widespread fragmentation in the physical device layout, amplifying internal I/O and reducing long-term device reliability. Several empirical studies have shown that seemingly small SQLite writes can result in substantial increases in flash block movement and GC pressure, ultimately leading to large tail latencies that degrade user-perceived performance [5,6,7]. The challenge is exacerbated in scenarios where applications flush each transaction to persistent storage for crash consistency, resulting in a high frequency of fsync() or fdatasync() operations.

The community has explored multiple techniques to improve the behavior of small writes on flash devices. Prior work ranges from kernel-level write shaping and FTL-aware scheduling [8,9] to log-structured host-side buffering such as WiscKey and other key-value-store architectures [10,11]. While these approaches demonstrate clear benefits, they often require intrusive modifications to the OS or storage stack, limiting deployability on commercial mobile platforms where the kernel cannot be easily modified. Moreover, many solutions focus on server-grade SSDs and do not adequately address the constraints of mobile UFS devices, where firmware behavior is opaque and tightly optimized for energy efficiency.

In contrast to these heavyweight approaches, our work focuses on a lightweight, userspace solution that requires no OS changes and integrates seamlessly with existing SQLite-based applications. We introduce ALEX (Adaptive Log-Embedded Extent Layer), a user-level write-coalescing mechanism designed to restructure small, fragmented updates into large sequential extents before persisting them to storage. ALEX operates by collecting dirty pages, optionally compressing them, and constructing a log-embedded extent that can be written sequentially. This design directly addresses the primary causes of write amplification and latency spikes observed in UFS devices by minimizing random physical writes and reducing the number of synchronous flush operations.

The design of ALEX is motivated by three key observations:

• Small writes impose disproportionate overhead on UFS devices due to internal RMW cycles and GC.
• Sequential writes are handled far more efficiently by both UFS and NVMe devices, significantly reducing device-level write amplification (WAF).
• A user-level buffering mechanism is sufficient to reshape application I/O without requiring OS or kernel support, making the solution deployable on unmodified smartphones and Linux systems.

In this paper, we conduct a comprehensive evaluation of ALEX on both Android UFS and Linux NVMe SSD environments. The baseline configurations include native SQLite behavior (Baseline naive) and the widely adopted Write-Ahead Logging (WAL) mode. Our experiments examine latency distributions, write amplification factors, synchronous flush behavior, and device-level metrics captured through disk statistics. The results show that ALEX significantly reduces p95 and p99 latencies, lowers device-level WAF by up to an order of magnitude, and significantly decreases the number of fdatasync() calls required by SQLite.

The key contributions of this paper are summarized as follows:

• ALEX Architecture: We design and implement ALEX, a log-embedded extent builder that transparently coalesces small SQLite writes while preserving durability and compatibility.
• Compression-Aware Extent Formation: ALEX adapts extent construction based on data compressibility to reduce write volume and improve effective throughput.
• Cross-Platform Experimental Evaluation: We provide an extensive analysis of ALEX on mobile UFS and Linux NVMe devices, demonstrating clear performance and efficiency benefits over existing SQLite configurations.
• Characterization of Flash Behavior for Small Writes: Our study sheds light on how different I/O patterns interact with modern device firmware, providing insights valuable for future database and storage system design.

Through these contributions, this work demonstrates that a simple yet carefully engineered userspace mechanism can bridge the gap between application-level write patterns and device-level behavior, yielding performance improvements without requiring privilege changes or hardware support.

2. Related Works

Modern storage systems for mobile and embedded platforms face increasing pressure to support workloads dominated by small, frequent updates. A broad body of research has explored how database engines, filesystem structures, and flash translation layers (FTLs) can be redesigned or optimized to mitigate the inherent inefficiencies of NAND flash memory under such workloads. This section reviews three key areas of related work: (i) improvements to SQLite and embedded databases, (ii) host-side write optimization techniques, and (iii) device-level enhancements and write-amplification studies.

2.1. SQLite and Embedded Database Optimizations

SQLite is the de facto data store for mobile applications, and its performance behavior has been thoroughly investigated in prior work. The Write-Ahead Logging (WAL) mode was introduced to improve concurrency and reduce the overhead of journaling [12,13,14]. WAL converts many operations into sequential appends, which reduces random writes at the application layer. However, WAL still triggers frequent fsync() or fdatasync() calls to satisfy durability guarantees, generating a large number of synchronous flush operations on mobile devices.

Several studies have further analyzed how SQLite interacts with underlying storage. Appelbaum et al. examined SQLite performance on Android and showed that even modest write traffic can cause significant latency spikes due to UFS internal behavior [15,16]. Others have explored adaptive checkpointing and batching mechanisms, but these approaches generally require application-level modifications or relax durability constraints [17,18].

In contrast, ALEX operates transparently beneath SQLite, retaining full compatibility while restructuring physical write patterns. This distinguishes it from prior database-centric techniques that focus primarily on logical logging rather than device-efficient physical layout.

2.2. Host-Side Write Shaping and Log-Structured Techniques

A substantial line of work investigates host-side buffering and log-structured techniques to alleviate small-write penalties. Log-structured filesystems such as LFS and F2FS aim to convert random writes into sequential segments, reducing FTL overhead [19,20]. However, these systems typically require kernel support, making deployment on smartphones difficult.

Key-value store architectures—including WiscKey [10] and HashKV [21]—reduce write amplification by separating large values from index updates. Their effectiveness stems from minimizing random writes at the flash layer. Yet these designs require entirely new storage engines, making them incompatible with embedded databases like SQLite.

Buffered write coalescing has also been studied in host-level device mappers and user-level libraries. Wei et al. [22] proposed adaptive write combining for mobile devices, while Mao et al. [23] demonstrated the effectiveness of reorder-and-flush strategies. These approaches share ALEX’s motivation but often rely on modified kernels or privileged subsystems unavailable on commodity Android devices.

ALEX differs in that it provides a userspace, database-agnostic mechanism that can be inserted transparently into existing applications without modifying the kernel or filesystem. Its log-embedded extent formation is conceptually related to log-structured ideas but implemented at the user layer in a lightweight and deployable manner.

2.3. Device-Level Flash Behavior and Write Amplification Studies

Understanding write amplification (WAF) in NAND-based devices has been a long-standing research topic. Several foundational studies [24,25] analyzed how garbage collection, wear-leveling, and internal mapping granularity contribute to amplified physical writes, especially under random-write workloads. More recent work examined UFS behavior in smartphones, highlighting its sensitivity to 4 KB write patterns and frequent synchronization calls [26].

NVMe SSDs have also been extensively studied. Chen et al. [27] characterized tail-latency behaviors caused by firmware-level parallelism, while Wu et al. [28] analyzed how host access patterns influence GC and internal metadata overhead. Although these devices offer higher throughput than UFS, they still suffer from substantial WAF when subjected to fragmented write patterns.

ALEX builds on insights from this line of research: instead of modifying firmware or relying on vendor-specific features, it shapes the host’s write pattern such that the storage device naturally experiences lower amplification. By building large, sequential extents, ALEX minimizes GC pressure and reduces the device’s physical write volume.

2.4. Summary

Overall, previous research has extensively explored techniques to reduce small-write overhead in flash storage or to optimize SQLite behavior. However, these efforts often require kernel modifications, redesigning database engines, or device-level firmware access—constraints that limit real-world deployability. ALEX bridges this gap by providing a deployable, userspace-only solution that significantly reshapes I/O behavior without violating SQLite semantics or requiring privileged changes.

3. ALEX System Design

This section describes the internal design of ALEX (Adaptive Log-Embedded Extent layer) and how it integrates with SQLite on both Linux NVMe SSDs and Android UFS devices. ALEX is inserted between the SQLite pager and the underlying filesystem via a VFS wrapper, so that applications and upper-layer SQL logic remain unchanged. Figure 1 gives an end-to-end view of the system, while Figure 2 and Algorithm 1 zoom in on the write path that coalesces scattered 4 KB updates into page-aligned extents.

Figure 1. Overall Architecture of the ALEX-Integrated Metadata Processing System.

Figure 2. Coalesced Write Path of ALEX, including page collection, extent formation, and sequential logging.

At a high level, ALEX collects dirty pages produced by SQLite, deduplicates multiple updates to the same page, and then packs the resulting page set into a large, sequential extent (≈1 MB). The extent is written to flash together with a compact log record that encodes its metadata. During recovery, ALEX replays these extent logs and reconstructs the most recent mapping from logical pages to physical locations. Compared with baseline WAL and rollback-journal modes, this design reduces the number of fdatasync() calls, lowers device-level write amplification, and accelerates tail latency, as quantified later in Section 5.

3.1. ALEX Architecture

Figure 1 shows how ALEX is integrated into the existing SQLite I/O stack. SQLite generates logical page reads and writes through the VFS interface. In the baseline configuration, these calls are translated directly into filesystem operations and eventually mapped to flash pages and erase blocks. In our prototype, the ALEX layer intercepts the write-side operations and redirects them through three internal modules:

• Page Collector (sometimes referred to as the ingest queue in the implementation) receives 4 KB dirty pages from SQLite. For each page ID, the collector stores only the most recent version, discarding obsolete copies when the same page is updated repeatedly. This provides in-memory deduplication and prevents unnecessary writes.
• Log-Embedded Extent Manager maintains an in-memory structure for building extents and a small on-disk metadata log. When enough pages have accumulated, or when memory pressure rises, the manager orders the collected pages by logical offset, packs them into a page-aligned extent, and prepares a log record that describes the extent’s logical range, length, and checksum.
• Flush Scheduler decides when to materialize an extent on storage. It coordinates with the underlying filesystem (ext4 on NVMe, ext4/f2fs on UFS) and issues sequential write requests. The scheduler aims to balance latency and device-level write amplification: flushing too often increases the number of extents and syncs, while flushing too late may increase memory pressure.

Read operations follow a simple path. If the requested page is already present in the in-memory extent buffer, ALEX serves it from memory; otherwise, the request is passed through to the underlying database file as usual. Because ALEX is implemented as a VFS shim, this design is transparent to applications and can be selectively enabled or disabled, which allows our evaluation in Section 4 to compare baseline and ALEX modes under identical workloads.

Algorithm 1: Compressed ALEX write path

Input: Logical page identifier $pid$, payload $data$ Data: Ingest queue Q; extent builder $EB$; Log-embedded extent directory $Dir$; Extent size limit $S_{\mathrm{extent}}$; batch size B; Compression threshold $T_{\min }$; minimum ratio $R_{\min }$

3.2. Coalesced Write Path

Figure 2 illustrates the core intuition behind ALEX, showing how scattered 4 KB page updates in SQLite are transformed into sequential extents. On the left-hand side, random page writes are directly propagated to UFS flash, becoming physically interleaved across multiple erase blocks and triggering frequent garbage collection and high write amplification. On the right-hand side, ALEX first coalesces these pages in memory before flushing them as a single page-aligned extent, resulting in a contiguous physical layout that is far easier for the flash translation layer to manage.

Building on this mechanism, Algorithm 1 formalizes the complete write path, including page collection, extent construction, optional, opportunistic compression, and metadata maintenance. The algorithm provides an operational specification of the design principles described above, bridging the conceptual behavior illustrated in Figure 2 with the actual implementation used in our ALEX prototype. Let N be the number of logical page updates generated by the workload, and let k be the number of pages per extent. In the worst case, the number of synchronization events required by ALEX satisfies the upper bound $\lceil N/k\rceil$. This bound arises because each extent flush handles up to k pages, converting N individual page synchronizations into at most $\lceil N/k\rceil$ batched sync events and thereby reducing fdatasync() pressure.

$$N_{sync}^{ALEX}\le ⌈{\displaystyle \frac{N}{k}}⌉$$

In contrast, a naive rollback-journal configuration may issue a sync for every commit, and a WAL configuration still tends to generate many syncs as the WAL file wraps around. Because k is typically in the hundreds, the number of device-level flushes in ALEX is one or two orders of magnitude smaller than in the baseline systems.

Once the extent layout is constructed, the Log-Embedded Extent Manager generates a compact extent log record that includes the extent identifier, logical page range, length, and checksum. Algorithm 1 then issues a single sequential write that appends the log record and the extent body to the log-embedded area. After the write completes and fdatasync() returns, the Flush Scheduler updates the in-memory mapping table so that subsequent reads can locate the latest copy of each page.

Because the log record is much smaller than a full page and is emitted once per extent rather than per page, the logging overhead of ALEX is modest. At the same time, the use of fdatasync() is aligned with extent flushes rather than individual page updates, which directly leads to the reduced sync counts and improved tail latency observed in Figure 3, Figure 4, Figure 5 and Figure 6.

Figure 3. Write Latency Percentiles under Linux Environment (log scale).

3.3. Metadata, Recovery, and Space Management

To remain compatible with SQLite’s durability guarantees, ALEX maintains a small metadata structure that maps logical page IDs to their latest extent and offset. This mapping is stored primarily in memory for fast lookups, but periodically a compact checkpoint is written to disk so that recovery does not need to scan the entire extent log.

Upon a crash, ALEX performs a recovery procedure that is conceptually similar to a log-structured store. Starting from the most recent checkpoint, the system scans the tail of the extent log, verifies the checksums, and reconstructs the mapping by replaying the extent metadata in order. Because each log record summarizes many pages at once, the amount of data that must be read during recovery is significantly smaller than in a traditional WAL regime, where each committed page generates its own log record. In practice, as shown by our experiments, the number of extents per benchmark run is small enough that recovery overhead remains limited in our evaluated workloads compared with normal execution time.

Figure 4. Cumulative Distribution Function (CDF) of Write Latency on Linux System.

Figure 4. Cumulative Distribution Function (CDF) of Write Latency on Linux System.

Figure 5. Write Latency Percentiles under Mobile Device (Android UFS).

Figure 5. Write Latency Percentiles under Mobile Device (Android UFS).

Space management is handled in a background fashion. When the log-embedded region grows beyond a threshold, ALEX identifies cold extents that no longer contain the latest versions of any pages. These obsolete extents can be reclaimed by either punching holes in the underlying file or by rewriting live extents into a new region and truncating the old one. Since our benchmark focuses on write latency rather than long-running workloads, the prototype implementation used in Section 5 adopts a simple reclamation policy that is sufficient for repeated test runs.

Figure 6. Cumulative Distribution Function (CDF) of Write Latency on Mobile Device (Android UFS).

Figure 6. Cumulative Distribution Function (CDF) of Write Latency on Mobile Device (Android UFS).

Finally, ALEX is exposed to the benchmark driver as a separate mode alongside the two baselines. On both Linux and Android, the runner can select among baseline_naive, baseline_wal, and ALEX. Internally, these modes differ only in the VFS configuration and journaling strategy; all other parameters, such as page size and SQL workload, are held constant. This alignment allows the experimental methodology in Section 4 and the comparative results in Section 5 to reflect the impact of ALEX’s design rather than unrelated configuration differences.

4. Experimental Setup

This section explains the experimental environment used to evaluate the proposed ALEX layer. Two platforms were selected to capture both device-level behavior and application-level performance: a Linux workstation equipped with an NVMe SSD, and an Android smartphone using a commercial UFS storage device (Samsung Electronics, Suwon, Republic of Korea). The same SQLite workload—consisting of 5000 metadata-intensive INSERT operations—was executed on both platforms to ensure comparability.

4.1. Workload Description

All experiments use a simple key-value table to emulate metadata-intensive SQLite workloads. The table schema consists of an integer primary key and a fixed-size value field, defined as follows:

CREATE TABLE kv (key INTEGER PRIMARY KEY, value BLOB);

Each operation inserts, updates, or deletes a single row identified by the primary key. The payload size of the value field is fixed across all experiments to ensure consistent logical write volume. Payloads are generated using compressible byte patterns to reflect typical metadata updates and to enable evaluation of ALEX’s optional compression mechanism.

For mixed workload experiments, operations are issued with a fixed ratio of INSERT, SELECT, UPDATE, and DELETE requests. All workloads consist of 5000 operations per run, and each configuration is repeated five times to report average behavior.

4.2. Linux NVMe SSD Testbed

The Linux system was designed to allow detailed observation of low-level I/O behavior. The NVMe storage device provides block-layer statistics through /proc/diskstats, enabling accurate measurement of physical write volume, write amplification, and synchronization activity. SQLite was compiled from source to ensure a consistent environment across all modes. Three configurations were evaluated: the default DELETE-mode SQLite (baseline naive), WAL-mode SQLite, and the proposed ALEX layer positioned beneath the VFS.

The essential specifications of the Linux testbed are summarized in Table 1.

Table 1. Linux NVMe SSD Testbed summary.

Component	Specification
CPU	Intel i9-12900k
Memory	32 GB RAM
Storage	2 × 512 GB PCIe NVMe SSD (OS/Test)
Operating System	Ubuntu 22.04.5 LTS
SQLite Version	3.37.2 (source build)
Measurements	Latency, WAF, sync counts, diskstats

4.3. Android UFS Mobile Testbed

To examine how ALEX behaves in a consumer-level storage environment, additional experiments were conducted on an Android smartphone equipped with UFS storage. Unlike NVMe devices, UFS storage is highly sensitive to small, unaligned writes, which often trigger garbage-collection events and introduce latency spikes. For this reason, the mobile experiments focus on latency distribution rather than device-level metrics. A dedicated Android benchmarking application was developed using the NDK. All SQLite operations were executed inside native C++ code to avoid the influence of higher-level framework layers. Latency from each INSERT operation was recorded and stored as CSV files within the application’s private storage directory.

The core specifications of the mobile testbed are summarized in Table 2.

Table 2. Android UFS Mobile Testbed summary.

Component	Specification
Device	Android tablet (UFS 2.2)
OS Version	Android 13
Architecture	ARM64 (aarch64)
SQLite Execution	Native C++ (NDK)
Measurements	Per-operation latency (CSV)

The same 5000-operation workload used in the Linux testbed was executed for each mode. Because UFS does not provide block-layer visibility comparable to diskstats, the analysis concentrates on mean latency and tail-latency metrics (p50, p95, and p99). These indicators effectively capture the degree to which ALEX stabilizes performance in the presence of UFS garbage-collection and internal write-combining behavior.

4.4. Workload and Metrics

Both platforms used the same INSERT-only workload, which reliably stresses SQLite’s metadata path. The benchmark measured the latency of each operation and, on the Linux platform, also quantified device-level behavior such as physical write volume and synchronization frequency. The combination of the two platforms provides a bal-anced evaluation: the Linux system reveals the underlying write-amplification behav-ior, while the mobile testbed reflects user-perceived performance in realistic deploy-ment conditions.

5. Experimental Results

This section presents the performance evaluation of the proposed ALEX layer using both Linux and Android mobile environments. All experiments were conducted using identical synthetic metadata workloads consisting of 5000 insert operations per run unless otherwise specified. We compare three metadata-persistence modes implemented in SQLite:

• Baseline-naive (DELETE journal mode).
• Baseline-WAL. 
• The proposed ALEX layer.

The evaluation focuses on three metrics: operation latency, write amplification (WAF), and forced synchronization calls (fdatasync()).

5.1. Latency Analysis

5.1.1. Linux Results (Insert-Only)

Figure 3 summarizes the p50, p95, and p99 latency values collected on the Linux NVMe environment. Baseline-naive exhibits the highest latency across all percentiles due to per-operation metadata flushing. Baseline-WAL reduces latency significantly by buffering updates in a write-ahead log; however, tail latency remains visible above the millisecond range. In contrast, ALEX achieves near-microsecond latency, with p99 staying below 0.1 ms.

Table 3 lists the per-run latency statistics. ALEX reduces p95 latency by more than 95% compared with naive and over 90% compared with WAL mode. The improvements stem from coalescing many logical page updates into contiguous extent writes, minimizing fsync pressure.

Table 3. Summary of Write Latency Statistics (Linux Environment) (Mean ± Std, 5 runs).

Mode	Mean (ms)	Std (ms)	p50 (ms)	p95 (ms)	p99 (ms)
Baseline Naive	4.272	0.055	3.784	4.992	5.594
Baseline WAL	0.079	0.030	0.036	0.058	0.068
ALEX	0.032	0.002	0.006	0.007	0.050

Overall, ALEX reduces mean latency by 54%, median latency by 94%, and tail latency (p95–p99) by more than 90% compared with WAL mode. These gains clearly demonstrate that converting page-level writes into extent-level writes drastically suppresses both average and tail delay.

To further illustrate tail behavior, Figure 4 provides the cumulative distribution function (CDF). The naive mode shows a long right-side tail, with a substantial fraction of operations exceeding 5 ms. WAL mode stabilizes the distribution but still produces occasional multi-millisecond spikes. ALEX shows a very steep CDF curve where more than 99% of all writes complete within 0.1 ms, confirming that ALEX not only improves average performance but also significantly suppresses tail latency. As summarized in Table 4, ALEX improves p95 and p99 latency by more than 90% compared with WAL mode, confirming its ability to suppress tail latency under NVMe SSD workloads.

Table 4. Performance Improvement of ALEX Compared to WAL on Linux.

Metric	Improvement (%)
Mean latency	54.49
p50 latency	94.05
p95 latency	95.06
p99 latency	56.27

Positive values indicate latency reduction relative to WAL. ALEX outperforms WAL across all percentiles, notably reducing tail latency (p95–p99).

5.1.2. Mobile Results (Insert-Only)

Figure 5 and Figure 6 show the latency characteristics of the same metadata workload executed on an Android device equipped with UFS storage. Mobile storage typically exhibits higher variance due to internal garbage collection, thermal throttling, and shared resource contention.

Nevertheless, the trends mirror the Linux results. Baseline-naive again shows the slowest behavior, with p95 close to ∼12 ms. WAL mode reduces latency to approximately ∼5 ms, while ALEX achieves sub-millisecond performance (∼0.05 ms mean), representing more than an order-of-magnitude improvement.

Table 5 confirms that ALEX provides the most stable execution profile. Tail latency collapse is particularly important for mobile, where UFS garbage collection normally amplifies worst-case delay. ALEX largely avoids this by converting scattered page-level writes into large sequential extents, as shown in Figure 2. Table 6 quantifies this improvement, showing that ALEX reduces mean, p50, p95, and p99 latency by more than 97% relative to WAL mode. Unlike NVMe, UFS internal GC and write combining introduce higher latency variance, which explains the multi-millisecond spikes observed in WAL mode. ALEX avoids these conditions by converting all writes into large sequential extents, effectively bypassing GC-sensitive small writes.

Table 5. Summary of Write Latency statistics under Mobile Device (Android UFS).

Mode	Mean (ms)	p50 (ms)	p95 (ms)	p99 (ms)	N
Baseline Naive	13.0902	13.504	16.096	19.899	25,000
Baseline Wal	5.085	5.069	6.027	8.736	25,000
ALEX	0.048	0.040	0.078	0.129	25,000

Table 6. Performance improvement of ALEX compared to WAL on mobile device (Android UFS).

Metric	Improvement (%)
Mean latency	97.27
p50 latency	97.24
p95 latency	97.68
p99 latency	98.55

5.1.3. Mixed Workload Results

To further examine the applicability of ALEX under realistic access patterns, we analyze the tail latency of individual operation types in a mixed workload. Figure 7 presents the p99 latency for INSERT, SELECT, UPDATE, and DELETE operations.

As expected, SELECT operations are largely unaffected by ALEX, as reads bypass the extent buffering mechanism and directly access the underlying database pages. This confirms that ALEX remains performance-neutral for read-dominated phases. For write-intensive operations, INSERT and UPDATE exhibit moderately higher p99 latency compared to the baselines due to extent formation and deferred flushing. However, no pathological tail behavior or latency spikes are observed, indicating that ALEX maintains stable execution even under mixed workloads. DELETE operations show comparable latency across all configurations, suggesting that ALEX does not interfere with space deallocation semantics.

Overall, these results demonstrate that ALEX’s benefits are not confined to synthetic INSERT-only workloads. Instead, ALEX preserves predictable tail latency behavior in mixed workloads while providing its intended write-path optimizations when write intensity is non-trivial.

Figure 7. p99 latency breakdown by operation type under a mixed SQLite workload. The workload consists of INSERT (40%), SELECT (30%), UPDATE (20%), and DELETE (10%) operations. While ALEX primarily targets write amplification reduction, the results show that it does not introduce additional tail latency for read-dominated operations (SELECT). For write-related operations (INSERT and UPDATE), ALEX exhibits higher p99 latency due to extent buffering, but remains stable without pathological outliers. DELETE operations show comparable behavior across all configurations, indicating that ALEX does not adversely affect mixed workload execution.

Figure 7. p99 latency breakdown by operation type under a mixed SQLite workload. The workload consists of INSERT (40%), SELECT (30%), UPDATE (20%), and DELETE (10%) operations. While ALEX primarily targets write amplification reduction, the results show that it does not introduce additional tail latency for read-dominated operations (SELECT). For write-related operations (INSERT and UPDATE), ALEX exhibits higher p99 latency due to extent buffering, but remains stable without pathological outliers. DELETE operations show comparable behavior across all configurations, indicating that ALEX does not adversely affect mixed workload execution.

5.2. Write Amplification and Synchronization Behavior

To further understand how ALEX changes the interaction between SQLite and the underlying storage hardware, we analyze write amplification and synchronization behavior using device-level counters from the NVMe SSD. Figure 8 and Figure 9 visualize the measured write patterns, and Table 7 summarizes the extended I/O metrics collected from five runs of 5000 operations each.

Table 7. Extended I/O metrics on Linux NVMe SSD (5000 operations per run, 5 runs per mode). Physical write bytes are measured at the block layer using /proc/diskstats. Logical write bytes correspond to the total application-level payload size issued by the database engine, excluding internal journaling overhead. The fdatasync() count represents the average number of synchronization calls per run.

Mode	Mean WAFdev	Mean $\mathit{p}95$ Latency (ms)	Mean fdatasync() Calls per Run
Baseline_naive	152.47	2.336	20,404.0
Baseline_wal	12.04	0.227	26.0
ALEX	3.22	0.003	11.0

Following common practice, we define write amplification (WAF) as the ratio of physical write bytes to logical write bytes measured at the storage device, as shown in Equation (1), where physical write bytes are computed from /proc/diskstats by tracking device-sector increments, and logical write bytes represent the total amount of data issued by the database engine, computed as the number of operations multiplied by the payload size.

$$WA{F}_{dev}={\displaystyle \frac{\mathrm{Physical}\mathrm{Write}\mathrm{Bytes}}{\mathrm{Logical}\mathrm{Write}\mathrm{Bytes}}}$$

(1)

A WAF close to 1.0 indicates efficient I/O; higher values indicate garbage collection (GC), out-of-place writes, or log rewriting.

Figure 8 presents the device-level WAF for the three modes. The baseline-naive mode exhibits a very high WAF, frequently exceeding two orders of magnitude. This is attributed to SQLite’s DELETE—journal mode, where each update forces a journal creation and subsequent page rewrite, resulting in a large number of scattered flash writes. The baseline-WAL mode significantly reduces WAF by batching updates in the write-ahead log, but it still generates a large number of small, partially overlapping writes, which are costly on NVMe SSDs. ALEX achieves the lowest WAF among all tested modes. Because ALEX consolidates many scattered updates into one sequential extent, the SSD handles fewer internal relocations and garbage-collection events. As a result, the effective WAF approaches a small constant factor, confirming that sequentializing writes is highly beneficial for flash-based devices.

Figure 8. Device-level write amplification (WAF) on Linux NVMe SSD for each SQLite mode (baseline_naive, baseline_wal, ALEX).

Figure 8. Device-level write amplification (WAF) on Linux NVMe SSD for each SQLite mode (baseline_naive, baseline_wal, ALEX).

Figure 9 shows the distribution of synchronization operations (fsync() and fdatasync()) for each mode. The baseline-naive mode issues tens of thousands of fdatasync() calls per run because each journal write is forced to stable storage, severely increasing latency. Baseline-WAL reduces this number dramatically due to WAL buffering, but regular log checkpoints still require forced flushes. ALEX shows the lowest number of synchronization calls, issuing only a small constant number per run. Because ALEX flushes only when closing an extent, it greatly reduces pressure on the NVMe controller’s writeback cache and flash translation layer, which aligns with the lower tail latency observed in Figure 3 and Figure 4.

Figure 9. Average number of fdatasync() calls per run on Linux NVMe SSD (5000 operations per run).

Figure 9. Average number of fdatasync() calls per run on Linux NVMe SSD (5000 operations per run).

Table 7 provides additional insight by including the change in physical pages (delta_pages), offering a device-internal view of how the SSD responds to each mode:

• Baseline-naive and baseline-WAL show large delta_pages, indicating that many flash blocks are rewritten internally due to GC and page-level remapping.
• ALEX shows significantly fewer page expansions, confirming that its sequential extents allow the SSD to allocate contiguous flash blocks with minimal internal copying.

Table 7 corroborates the trends observed in Figure 8 and Figure 9 by quantifying the average WAF and synchronization counts across five independent runs. Combined with the WAF and sync-count results, these measurements confirm that ALEX reduces both external and internal write traffic, thereby reducing garbage-collection overhead and improving tail-latency behavior. These findings indicate that ALEX not only reduces logical-to-physical write amplification but also minimizes forced synchronization, leading to more predictable and efficient storage behavior under metadata-intensive workloads.

5.3. Summary of Findings

Across all platforms and metrics, ALEX consistently outperforms traditional SQLite persistence mechanisms.

• Latency: ALEX cuts p95 and p99 latency by more than 90–95% in both Linux and mobile environments.
• Tail Behavior: The CDF curve confirms that ALEX nearly eliminates long-tail delays, achieving tight latency bounds.
• Write Amplification: Device-level WAF is reduced by 4–50× compared with WAL and naive modes.
• Synchronization Pressure: ALEX issues fewer fdatasync() calls, easing internal GC pressure for UFS and reducing NVMe flush storms.
• Mixed Workload Robustness: Under mixed workloads containing INSERT, SELECT, UPDATE, and DELETE operations, ALEX preserves predictable tail latency behavior without degrading read performance. While write-related operations incur moderate buffering overhead, no pathological latency spikes are observed.
• Long-Term Stability: By relocating write amplification control from the flash translation layer to the host level, ALEX reduces the frequency of device-internal garbage collection. Recovery overhead scales linearly with the number of extents and remains bounded due to lightweight metadata reconstruction.
• Compression Scope: Compression in ALEX is applied opportunistically as part of the extent formation process to reduce write amplification without extending the write critical path. A quantitative evaluation of compression ratio and CPU overhead is beyond the scope of this study and left for future work.

These results demonstrate that ALEX’s log-embedded extent design provides substantial benefits for metadata-intensive workloads, enabling more predictable and efficient storage behavior on both server-grade and mobile hardware.

6. Conclusions

This paper presented ALEX, a lightweight userspace log-embedded extent layer designed to mitigate the fundamental inefficiencies of small, fragmented writes in SQLite-based systems deployed on modern flash storage. Unlike kernel-level or firmware-dependent approaches, ALEX operates transparently beneath the SQLite VFS, reshaping page-level writes into large sequential extents without requiring modifications to the filesystem, storage stack, or application logic. Through a combination of page collection, sorted extent construction, optional compression, and append-only log placement, ALEX effectively reduces random write pressure and aligns host-side I/O patterns with the internal behavior of NAND-based devices.

Experimental results on both Linux NVMe SSDs and Android UFS platforms demonstrate the robustness and portability of the design. ALEX consistently improves median and tail latencies, achieving reductions exceeding 90–95% in p95 and p99 latency compared with WAL mode. Device-level analysis further reveals substantial decreases in write amplification and forced synchronization operations, confirming that ALEX effectively reduces both logical and physical write traffic. These improvements persist across hardware types, indicating that the benefits arise from fundamental I/O shaping rather than device-specific tuning.

Beyond performance, ALEX preserves crash consistency and recovery semantics through an extent-level directory mechanism that enables predictable reconstruction after failures. Its design also supports coarse-grained garbage collection, reducing internal flash copying and further lowering write amplification.

Overall, this work shows that userspace-level I/O restructuring can provide system-level benefits traditionally thought to require kernel or firmware involvement. By bridging the semantic gap between SQLite’s page-oriented write model and the sequential-write preference of modern flash devices, ALEX offers a deployable, scalable, and durable solution for metadata-intensive workloads on both mobile and desktop platforms. Future work includes integrating adaptive extent sizing, cross-transaction batching policies, and multi-tenant coordination for broader deployment in mobile and embedded systems.