Zero-Copy Database

High-performance database operations with minimal memory overhead

The Database Performance Problem

Traditional database operations in PHP involve multiple data copies:

┌─────────────────────────────────────────────────────┐
│  Database → Driver → PDO → PHP Array → JSON         │
│     1          2       3        4         5          │
└─────────────────────────────────────────────────────┘

Each arrow represents a memory copy:

1. Database sends result over network
2. Native driver allocates memory for result
3. PDO copies data into PHP-compatible structures
4. Your code converts to PHP arrays
5. json_encode() serializes for API response

For a 10MB result set, traditional PHP may allocate 50MB+ of memory across all these copies.

Zero-Copy Architecture

Lyger’s Zero-Copy Database keeps data in Rust memory and only copies once:

┌─────────────────────────────────────────────────────┐
│  Database → Rust → [Memory Pointer] → JSON → PHP    │
│     1        2            3             4      5     │
└─────────────────────────────────────────────────────┘

1. Database sends result to Rust
2. Rust stores in native memory structures
3. Rust returns pointer (memory address) to PHP
4. When needed, Rust serializes directly to JSON
5. PHP receives final JSON (one copy)

The result set stays in Rust’s memory until you’re ready to use it. No intermediate copies.

How It Works

Query Execution

// From Lyger/Core/Engine.php
public function dbQuery(string $dsn, string $query): int
{
    if ($this->ffi === null) {
        return 0;
    }

    try {
        // Execute query in Rust, return pointer to results
        return (int) $this->ffi->lyger_db_query($dsn, $query);
    } catch (\Throwable $e) {
        return 0;
    }
}

The lyger_db_query FFI function:

• Connects to the database (or reuses connection)
• Executes the SQL query
• Stores results in Rust’s heap memory
• Returns the memory address as an integer

The return value is a memory pointer disguised as an integer. PHP doesn’t touch the actual data.

Result Serialization

// From Lyger/Core/Engine.php
public function jsonifyResult(int $ptr): string
{
    if ($this->ffi === null || $ptr === 0) {
        return '[]';
    }

    try {
        // Pass pointer back to Rust for serialization
        $result = $this->ffi->lyger_jsonify_result($ptr);
        $string = FFI::string($result);
        $this->ffi->lyger_free_string($result);
        return $string;
    } catch (\Throwable $e) {
        return '[]';
    }
}

The lyger_jsonify_result FFI function:

• Receives the pointer to the result set
• Iterates through rows in Rust (fast!)
• Serializes directly to JSON string
• Returns JSON to PHP

Memory Cleanup

// From Lyger/Core/Engine.php
public function freeResult(int $ptr): void
{
    if ($this->ffi === null || $ptr === 0) {
        return;
    }

    try {
        // Tell Rust to free the result set memory
        $this->ffi->lyger_free_result($ptr);
    } catch (\Throwable $e) {
        // Ignore
    }
}

Always free result sets when done! Failing to call freeResult() causes memory leaks in the Rust process.

High-Level API

The dbQueryJson method combines all three operations:

// From Lyger/Core/Engine.php
public function dbQueryJson(string $dsn, string $query): string
{
    $ptr = $this->dbQuery($dsn, $query);
    if ($ptr === 0) {
        return '[]';
    }

    $json = $this->jsonifyResult($ptr);
    $this->freeResult($ptr);
    return $json;
}

Usage Examples

Basic Query

use Lyger\Core\Engine;

$engine = Engine::getInstance();

$dsn = 'sqlite:database/app.db';
$query = 'SELECT * FROM users WHERE active = 1';

$json = $engine->dbQueryJson($dsn, $query);
$users = json_decode($json, true);

foreach ($users as $user) {
    echo "User: {$user['name']}\n";
}

Manual Memory Management

For advanced use cases, manage the pointer lifecycle manually:

use Lyger\Core\Engine;

$engine = Engine::getInstance();

// Execute query, get pointer
$ptr = $engine->dbQuery('sqlite:database/app.db', 'SELECT * FROM products');

if ($ptr === 0) {
    die('Query failed');
}

// Hold pointer while doing other work
processOrders();
updateInventory();

// Now serialize when needed
$json = $engine->jsonifyResult($ptr);

// Process results
$products = json_decode($json, true);
foreach ($products as $product) {
    echo "{$product['name']}: \${$product['price']}\n";
}

// Clean up
$engine->freeResult($ptr);

Why hold the pointer?

In some scenarios, you may want to defer serialization:

• Execute multiple queries concurrently
• Perform calculations before fetching results
• Reduce memory pressure by serializing only when needed

The result set stays in Rust’s memory (which is more efficient) until you explicitly serialize it.

Database Support

Lyger’s Zero-Copy Database supports multiple database engines through their Rust drivers:

Database	DSN Example	Driver
SQLite	sqlite:database/app.db	rusqlite
PostgreSQL	postgres://user:pass@host/db	tokio-postgres
MySQL	mysql://user:pass@host/db	mysql_async
MariaDB	mysql://user:pass@host/db	mysql_async

The Rust driver is determined by the DSN prefix. All drivers support the same zero-copy architecture.

Connection Examples

// SQLite (file-based)
$dsn = 'sqlite:database/app.db';

// PostgreSQL
$dsn = 'postgres://admin:secret@localhost:5432/myapp';

// MySQL/MariaDB
$dsn = 'mysql://root:password@localhost:3306/myapp';

Performance Comparison

Memory Usage

Let’s query 10,000 user records (~1MB result set):

Approach	Memory Allocated	Description
Traditional PDO	~5MB	Multiple copies through PDO layers
json_encode(PDO)	~7MB	PDO + JSON serialization
Zero-Copy	~1MB	Single copy of final JSON

Speed Benchmarks

Query: SELECT * FROM users LIMIT 10000

Approach	Time	Notes
PDO + fetch	~45ms	Fetch each row, build array
PDO + fetchAll	~35ms	Fetch all at once
Zero-Copy	~8ms	Query + serialize in Rust

Actual performance depends on database, network latency, and hardware. Zero-Copy shines with large result sets.

Integration with QueryBuilder

While the Engine class provides low-level access, you’ll typically use the QueryBuilder:

use Lyger\Database\QueryBuilder;

// Traditional PDO approach (used internally)
$users = QueryBuilder::table('users')
    ->where('active', 1)
    ->orderBy('created_at', 'DESC')
    ->limit(100)
    ->get();

Currently, QueryBuilder uses PDO internally. Zero-Copy database is used by Cache and internal systems. Full QueryBuilder integration is planned for future releases.

Advanced: Streaming Results

For extremely large result sets, you can stream data:

use Lyger\Core\Engine;

$engine = Engine::getInstance();
$ptr = $engine->dbQuery('sqlite:data.db', 'SELECT * FROM big_table');

// Process in chunks (pseudo-code - requires Rust support)
while ($chunk = $engine->fetchChunk($ptr, 1000)) {
    $rows = json_decode($chunk, true);
    
    foreach ($rows as $row) {
        processRow($row);
    }
    
    // Each chunk is GC'd after processing
    unset($rows, $chunk);
}

$engine->freeResult($ptr);

Streaming API is not yet implemented in v0.1. Currently, you must fetch the entire result set at once.

Connection Pooling

The Rust layer maintains connection pools for better performance:

// First query: establishes connection
$json1 = $engine->dbQueryJson($dsn, 'SELECT COUNT(*) FROM users');

// Second query: reuses connection (fast!)
$json2 = $engine->dbQueryJson($dsn, 'SELECT * FROM users LIMIT 10');

Connection pooling benefits:

• No connection overhead for subsequent queries
• Connections kept alive across requests (Always-Alive server)
• Automatic reconnection on failure
• Configurable pool size and timeouts

In Always-Alive mode, database connections persist across HTTP requests, making subsequent queries even faster.

Error Handling

use Lyger\Core\Engine;

$engine = Engine::getInstance();

$ptr = $engine->dbQuery('sqlite:database/app.db', 'SELECT * FROM users');

if ($ptr === 0) {
    // Query failed - handle error
    error_log('Database query failed');
    return [];
}

try {
    $json = $engine->jsonifyResult($ptr);
    $results = json_decode($json, true);
    
    if (json_last_error() !== JSON_ERROR_NONE) {
        throw new \Exception('Invalid JSON from database');
    }
    
    return $results;
} finally {
    // Always clean up, even on error
    $engine->freeResult($ptr);
}

Common error scenarios

• Pointer is 0: Query execution failed (syntax error, connection issue, etc.)
• Invalid JSON: Result serialization failed (encoding issue, memory corruption)
• Exception during processing: Network timeout, connection lost, out of memory

Always use try-finally to ensure cleanup!

Best Practices

1. Always Free Results

// ✅ Good
$ptr = $engine->dbQuery($dsn, $query);
$json = $engine->jsonifyResult($ptr);
$engine->freeResult($ptr);

// ✅ Better (automatic cleanup)
$json = $engine->dbQueryJson($dsn, $query);

2. Check for Errors

// ✅ Good
$ptr = $engine->dbQuery($dsn, $query);
if ($ptr === 0) {
    // Handle error
    return [];
}

3. Use Prepared Statements

// ❌ Bad: SQL injection risk
$query = "SELECT * FROM users WHERE id = {$userId}";

// ✅ Good: Use parameterized queries
// (Note: Current API doesn't support params - use with caution)
$query = "SELECT * FROM users WHERE id = 123";

The current Zero-Copy API doesn’t support parameter binding. Always sanitize user input before building queries, or use the QueryBuilder for safe queries.

4. Limit Result Size

// ❌ Bad: Could return millions of rows
$json = $engine->dbQueryJson($dsn, 'SELECT * FROM logs');

// ✅ Good: Limit results
$json = $engine->dbQueryJson($dsn, 'SELECT * FROM logs LIMIT 1000');

Troubleshooting

Query Returns Empty Array

$json = $engine->dbQueryJson($dsn, 'SELECT * FROM users');
// $json = '[]'

Possible causes:

• Table is empty
• Query syntax error
• Connection failed
• FFI not available

Check the pointer:

$ptr = $engine->dbQuery($dsn, 'SELECT * FROM users');
if ($ptr === 0) {
    error_log('Query failed - check database connection and SQL syntax');
}

Memory Leaks

If memory grows over time:

• Ensure you call freeResult() for every query
• Use dbQueryJson() which handles cleanup automatically
• Check for stored pointers in class properties

// ❌ Bad: Pointer stored, never freed
class Repository {
    private int $lastQuery;
    
    public function query($sql) {
        $this->lastQuery = $engine->dbQuery($dsn, $sql);
        // ← Leaked! Never freed
    }
}

Future Improvements

Planned features for future releases:

• Parameter binding - Safe query parameters
• Streaming results - Process huge datasets chunk-by-chunk
• Query result objects - Type-safe result handling
• Async queries - Non-blocking database operations
• QueryBuilder integration - Full zero-copy support in QueryBuilder

Next Steps

Architecture Overview

Understand the complete architecture

Rust FFI Integration

Learn about FFI and memory management

Query Builder

Use the fluent query builder

Models

Work with Eloquent-style models