sqlitezstd

SQLiteZSTD: Read-Only Access to Compressed SQLite Files

Description

SQLiteZSTD provides a tool for accessing SQLite databases compressed with Zstandard seekable (zstd) in a read-only manner. Its functionality is based on the SQLite3 Virtual File System (VFS) in Go.

Please note, SQLiteZSTD is specifically designed for reading data and does not support write operations.

Features

1. Read-only access to Zstd-compressed SQLite databases.
2. Interface through SQLite3 VFS.
3. The compressed database is seekable, facilitating ease of access.

Usage

Your database needs to be compressed in the seekable Zstd format. I recommend using this CLI tool:

go get -a github.com/SaveTheRbtz/zstd-seekable-format-go/...
go run github.com/SaveTheRbtz/zstd-seekable-format-go/cmd/zstdseek \
    -f <dbPath> \
    -o <dbPath>.zst

The CLI provides different options for compression levels, but I do not have specific recommendations for best usage patterns.

Below is an example of how to use SQLiteZSTD in a Go program:

import (
    sqlitezstd "github.com/jtarchie/sqlitezstd"
)

initErr := sqlitezstd.Init()
if initErr != nil {
    panic(fmt.Sprintf("Failed to initialize SQLiteZSTD: %s", initErr))
}

db, err := sql.Open("sqlite3", "<path-to-your-file>?vfs=zstd")
if err != nil {
    panic(fmt.Sprintf("Failed to open database: %s", err))
}

In this Go code example:

• The SQLiteZSTD library is initialized first with sqlitezstd.Init().
• An SQL connection to a compressed SQLite database is established with sql.Open().

The sql.Open() function takes as a parameter the path to the compressed SQLite database, appended with a query string. Key query string parameters include:

• vfs=zstd: Ensures the ZSTD VFS is used.

Performance

Here's a simple benchmark comparing performance between reading from an uncompressed vs. a compressed SQLite database, involving the insertion of 10k records and retrieval of the MAX value (without an index) and FTS5.

BenchmarkReadUncompressedSQLite-4              	  159717	      7459 ns/op	     473 B/op	      15 allocs/op
BenchmarkReadUncompressedSQLiteFTS5Porter-4    	    2478	    471685 ns/op	     450 B/op	      15 allocs/op
BenchmarkReadUncompressedSQLiteFTS5Trigram-4   	     100	  10449792 ns/op	     542 B/op	      16 allocs/op
BenchmarkReadCompressedSQLite-4                	  266703	      3877 ns/op	    2635 B/op	      15 allocs/op
BenchmarkReadCompressedSQLiteFTS5Porter-4      	    2335	    487430 ns/op	   33992 B/op	      16 allocs/op
BenchmarkReadCompressedSQLiteFTS5Trigram-4     	      48	  21235303 ns/op	45970431 B/op	     148 allocs/op
BenchmarkReadCompressedHTTPSQLite-4            	  284820	      4341 ns/op	    3312 B/op	      15 allocs/op