sqlitezstd

README

SQLiteZSTD: Read-Only Access to Compressed SQLite Files

[!IMPORTANT] A new version of this extension written in C is now available. This C version offers the advantage of being usable across different platforms, languages, and runtimes. It is not publicly available and is provided under a one-time fee in perpetuity license with support. The original Go version will remain freely available. For more information about the C extension, please email jtarchie@gmail.com.

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))
}

// Set PRAGMA for each connection
db.SetConnMaxLifetime(0) // Disable connection pooling
db.SetMaxOpenConns(1)    // Allow only one open connection

conn, err := db.Conn(context.Background())
if err != nil {
    panic(fmt.Sprintf("Failed to get connection: %s", err))
}
defer conn.Close()

// PRAGMA's are not persisted across `database/sql` pooled connections
// this is to _ensure_ it happens for this one.
_, err = conn.ExecContext(context.Background(), `PRAGMA temp_store = memory;`)
if err != nil {
    panic(fmt.Sprintf("Failed to set PRAGMA: %s", err))
}

// Use conn for subsequent operations to ensure PRAGMA is applied

In this Go code example:

• The SQLiteZSTD library is initialized first with sqlitezstd.Init().
• The sql.Open() function takes as a parameter the path to the compressed SQLite database, appended with a query string with vfs=zstd to use the VFS.
• Setting the PRAGMA ensures that the read only VFS is not used to create temporary files.

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

Documentation

Index

• func Init() error
• type ZstdFile
  • func (z *ZstdFile) CheckReservedLock() (bool, error)
  • func (z *ZstdFile) Close() error
  • func (z *ZstdFile) DeviceCharacteristics() sqlite3vfs.DeviceCharacteristic
  • func (z *ZstdFile) FileSize() (int64, error)
  • func (z *ZstdFile) Lock(elock sqlite3vfs.LockType) error
  • func (z *ZstdFile) ReadAt(p []byte, off int64) (int, error)
  • func (z *ZstdFile) SectorSize() int64
  • func (z *ZstdFile) Sync(flag sqlite3vfs.SyncType) error
  • func (z *ZstdFile) Truncate(size int64) error
  • func (z *ZstdFile) Unlock(elock sqlite3vfs.LockType) error
  • func (z *ZstdFile) WriteAt(p []byte, off int64) (int, error)
• type ZstdVFS
  • func (z *ZstdVFS) Access(name string, flags sqlite3vfs.AccessFlag) (bool, error)
  • func (z *ZstdVFS) Delete(name string, dirSync bool) error
  • func (z *ZstdVFS) FullPathname(name string) string
  • func (z *ZstdVFS) Open(name string, flags sqlite3vfs.OpenFlag) (sqlite3vfs.File, sqlite3vfs.OpenFlag, error)

Functions

func Init

func Init() error

Types

type ZstdFile

type ZstdFile struct {
	// contains filtered or unexported fields
}

func (*ZstdFile) CheckReservedLock

func (z *ZstdFile) CheckReservedLock() (bool, error)

func (*ZstdFile) Close

func (z *ZstdFile) Close() error

func (*ZstdFile) DeviceCharacteristics

func (z *ZstdFile) DeviceCharacteristics() sqlite3vfs.DeviceCharacteristic

func (*ZstdFile) FileSize

func (z *ZstdFile) FileSize() (int64, error)

func (*ZstdFile) Lock

func (z *ZstdFile) Lock(elock sqlite3vfs.LockType) error

func (*ZstdFile) ReadAt

func (z *ZstdFile) ReadAt(p []byte, off int64) (int, error)

func (*ZstdFile) SectorSize

func (z *ZstdFile) SectorSize() int64

func (*ZstdFile) Sync

func (z *ZstdFile) Sync(flag sqlite3vfs.SyncType) error

func (*ZstdFile) Truncate

func (z *ZstdFile) Truncate(size int64) error

func (*ZstdFile) Unlock

func (z *ZstdFile) Unlock(elock sqlite3vfs.LockType) error

func (*ZstdFile) WriteAt

func (z *ZstdFile) WriteAt(p []byte, off int64) (int, error)

type ZstdVFS

type ZstdVFS struct{}

func (*ZstdVFS) Access

func (z *ZstdVFS) Access(name string, flags sqlite3vfs.AccessFlag) (bool, error)

func (*ZstdVFS) Delete

func (z *ZstdVFS) Delete(name string, dirSync bool) error

func (*ZstdVFS) FullPathname

func (z *ZstdVFS) FullPathname(name string) string

func (*ZstdVFS) Open

func (z *ZstdVFS) Open(name string, flags sqlite3vfs.OpenFlag) (sqlite3vfs.File, sqlite3vfs.OpenFlag, error)