Storing Numeric Vectors in SQLite: Serialization, Extensions & Normalization

Storing and Retrieving Multi-Dimensional Numeric Data in SQLite

1. Understanding SQLite’s Scalar Storage Model and Vector Serialization Requirements

SQLite is a relational database management system (RDBMS) designed around the principle of atomic scalar values. Each cell in a table—the intersection of a row and column—must contain a single value. This design inherently conflicts with the concept of storing multi-dimensional numeric vectors (e.g., [1.1, 3.5, 8.5]) directly in a single column. However, practical workarounds exist to serialize, store, and reconstruct vectors while adhering to SQLite’s constraints.

Key Challenges

1. Scalar Enforcement: SQLite rejects attempts to insert non-scalar values (arrays, lists, etc.) directly. A vector must be converted into a single scalar representation (e.g., text, binary blob, or structured string).
2. Data Integrity: Serialization methods must preserve numeric precision (critical for scientific or financial data).
3. Query Efficiency: Retrieving and processing vector elements without deserialization in SQL is impractical unless extensions or normalized schemas are used.
4. Environment Constraints: The original discussion involves C# on Windows, introducing considerations like 32/64-bit compatibility, .NET serialization APIs, and third-party extension dependencies.

Why Standard Formats Like JSON or TEXT Are Insufficient

• Overhead: JSON parsing/stringification adds computational costs, especially for large vectors.
• Precision Loss: Text-based formats may truncate floating-point numbers (e.g., 1.1000000000000001 becoming 1.1).
• Schema Rigidity: JSON requires additional validation to ensure vector structure consistency.

2. Root Causes of Vector Storage Limitations and Serialization Risks

Cause 1: SQLite’s Relational Model

SQLite enforces first normal form (1NF), which mandates atomic column values. This prevents direct storage of multi-value structures like vectors. Workarounds involve:

• Serialization: Convert the vector to a single scalar (BLOB, Base64 text).
• Normalization: Split the vector into rows or columns (e.g., a child table with one row per vector element).

Cause 2: Binary Serialization Security in C#

The user raised concerns about BinaryFormatter, Microsoft’s legacy API for binary serialization. Key risks include:

• Insecure Deserialization: Malicious payloads can execute arbitrary code during deserialization (CVE-2020-1147).
• Deprecation: BinaryFormatter is obsolete in .NET 5+ and unsupported in .NET Core. Modern alternatives like DataContractSerializer or System.Text.Json are safer but require schema compatibility.

Cause 3: Extension Compatibility Issues

The sqlean array extension provides native array support but introduces challenges:

• Compilation Complexity: Users must compile from source for 32-bit Windows, which involves resolving dependencies (e.g., missing arrayagg_init symbols).
• Runtime Errors: Incorrectly compiled extensions fail to load (e.g., “The specified module could not be found” due to architecture mismatches).

Cause 4: Misalignment with Normalization Best Practices

Storing vector elements in separate rows or columns aligns with relational principles but complicates application logic:

• Insert/Delete Overhead: Modifying a vector requires multiple row operations.
• Query Complexity: Aggregating vector elements across rows/columns is verbose.
• Schema Changes: Adding columns for new vector dimensions requires ALTER TABLE.

3. Solutions for Storing Vectors: Serialization, Extensions, and Schema Design

Solution 1: Binary Serialization with Safe .NET APIs

Step 1: Choose a Secure Serializer
Replace BinaryFormatter with:

• DataContractSerializer: Requires defining [DataContract] and [DataMember] attributes.

  [DataContract]
  public class VectorData {
      [DataMember]
      public double[] Values { get; set; }
  }
  
  // Serialize
  var serializer = new DataContractSerializer(typeof(VectorData));
  using var stream = new MemoryStream();
  serializer.WriteObject(stream, new VectorData { Values = new[] { 1.1, 3.5, 8.5 } });
  byte[] blob = stream.ToArray();
  
  // Store in SQLite
  command.Parameters.AddWithValue("@blob", blob);
  

• System.Text.Json: Lightweight and cross-platform.

  string json = JsonSerializer.Serialize(new[] { 1.1, 3.5, 8.5 });
  command.Parameters.AddWithValue("@json", json);
  

Step 2: Store as BLOB or Base64

• BLOB Column: Directly store the serialized byte array.

  CREATE TABLE vectors (id INTEGER PRIMARY KEY, data BLOB);
  

• Base64 Text: Encode the byte array for text storage.

  string base64 = Convert.ToBase64String(blob);
  command.Parameters.AddWithValue("@base64", base64);
  

  Use SQLite’s base64() function to decode:

  SELECT id, base64(data) FROM vectors; -- Encodes BLOB to Base64 text
  SELECT CAST(base64_decode(json) AS BLOB) FROM vectors; -- Decodes text to BLOB
  

Step 3: Optimize Performance

• Benchmark Serializers: MessagePack (binary) vs. System.Text.Json (text).
• Compression: Apply GZip compression to large vectors before serialization.

Solution 2: Using the sqlean Array Extension

Step 1: Compile the Extension for 32-bit Windows

• Clone the sqlean incubator branch.
• Use MSVC to compile array.c:

  cl.exe /LD array.c /link /DLL /OUT:array.dll
  

• Resolve linker errors by including dependent source files (e.g., arrayagg_init.c).

Step 2: Load the Extension in SQLite

.load 'D:\path\to\array.dll'
SELECT array('[1.1, 3.5, 8.5]'); -- Returns a parsed array BLOB

Step 3: Query Array Elements
Use extension functions like array_length() and array_get():

SELECT array_get(data, 0) AS first_element FROM vectors;

Limitations

• Platform Dependency: Precompiled binaries are 64-bit only.
• API Stability: The incubator extension is experimental and may change.

Solution 3: Normalized Schema Design

Option A: Child Table with Element Rows

CREATE TABLE vectors (
    id INTEGER PRIMARY KEY,
    -- Other metadata
);

CREATE TABLE vector_elements (
    vector_id INTEGER,
    index INTEGER,
    value REAL,
    PRIMARY KEY (vector_id, index),
    FOREIGN KEY (vector_id) REFERENCES vectors(id)
);

Pros:

• Enables per-element queries (e.g., “Find vectors containing values > 5”).
• Avoids serialization overhead.

Cons:

• Inserts require multiple statements.
• Retrieving full vectors needs GROUP_CONCAT or application-side aggregation.

Option B: Wide Table with Fixed Columns

CREATE TABLE vectors (
    id INTEGER PRIMARY KEY,
    value0 REAL,
    value1 REAL,
    value2 REAL,
    -- ... up to max expected size
);

Pros:

• Single-row retrieval.
• Columnar storage optimizations.

Cons:

• Schema changes required for larger vectors.
• Sparse columns waste space if vectors vary in size.

Solution 4: Hybrid Approach (JSON + Virtual Columns)

Use SQLite’s JSON1 extension to store vectors as JSON arrays and create virtual columns for indexing:

CREATE TABLE vectors (
    id INTEGER PRIMARY KEY,
    data TEXT, -- JSON array
    -- Generated columns for first 3 elements
    value0 REAL GENERATED ALWAYS AS (json_extract(data, '$[0]')),
    value1 REAL GENERATED ALWAYS AS (json_extract(data, '$[1]')),
    value2 REAL GENERATED ALWAYS AS (json_extract(data, '$[2]'))
);

Pros:

• Balances flexibility with queryability.
• Avoids deserialization in queries for indexed elements.

Cons:

• JSON parsing overhead.
• Requires SQLite 3.31+ (generated columns).

Decision Guide: Choosing the Right Approach

Final Recommendations

1. Prioritize Security: Use DataContractSerializer or System.Text.Json instead of BinaryFormatter.
2. Benchmark Serialization: Test protobuf, MessagePack, and JSON for speed/size trade-offs.
3. Prefer Normalization for Query Flexibility: If vectors require SQL-level analysis.
4. Consider Extensions Cautiously: Compile sqlean from source and test thoroughly.

By aligning storage strategies with application requirements and SQLite’s constraints, developers can efficiently manage numeric vectors while mitigating security and performance risks.