Hypothesis

We hypothesize that a workload-aware storage routing strategy that directs operations to the optimal backend based on access pattern can achieve 5x read throughput improvement and 3x write throughput improvement over single-backend deployment, while maintaining consistency guarantees through the platform's adapter contract system.

Background

The Prismatic Platform operates four distinct storage backends, each optimized for different access patterns: ETS (in-memory key-value with sub-microsecond reads), PostgreSQL (relational with ACID transactions and complex queries), Meilisearch (full-text search with typo tolerance), and KuzuDB (graph database for relationship traversal).

The storage architecture uses a trait-based adapter system (prismatic_storage_core) that defines common behaviors, with each backend implementing the PrismaticStorage.Adapter behaviour. The Sparkline contract system ensures interface compatibility across adapters. However, performance characteristics vary by orders of magnitude between backends depending on the operation type.

Currently, the platform uses a static mapping: each application configures its preferred backend at compile time. This means an application that primarily reads (benefiting from ETS) but occasionally needs full-text search (requiring Meilisearch) must either use a suboptimal single backend or manually manage multi-backend routing.

This experiment measures per-backend performance across standardized workloads and evaluates dynamic routing strategies that select the optimal backend per operation.

Methodology

We defined five workload profiles representing common platform access patterns:

Read-Heavy (90% reads, 10% writes): Typical for agent state lookup and configuration retrieval
Write-Heavy (10% reads, 90% writes): Typical for telemetry ingestion and event logging
Mixed (50% reads, 50% writes): Typical for CRUD operations on entities
Search (95% full-text queries, 5% writes): Typical for OSINT result exploration
Graph Traversal (80% traversal queries, 20% writes): Typical for relationship analysis

Each workload was executed for 5 minutes at sustained load against each backend, measuring throughput (operations/second), latency (p50, p95, p99), and resource consumption (memory, CPU, disk I/O).

The dataset contained 1 million records with realistic field distributions matching the platform's entity schema (256-byte average record size with 3 indexed fields and 1 full-text field).

Setup

The storage router that directs operations to optimal backends:

defmodule PrismaticStorage.Router do
  @backend_capabilities %{
    ets: %{
      read_latency: :microseconds,
      write_latency: :microseconds,
      search: false,
      graph: false,
      persistence: :memory_only,
      max_dataset: :node_memory
    },
    postgresql: %{
      read_latency: :milliseconds,
      write_latency: :milliseconds,
      search: :basic,
      graph: false,
      persistence: :durable,
      max_dataset: :disk
    },
    meilisearch: %{
      read_latency: :milliseconds,
      write_latency: :tens_of_milliseconds,
      search: :advanced,
      graph: false,
      persistence: :durable,
      max_dataset: :memory_indexed
    },
    kuzudb: %{
      read_latency: :milliseconds,
      write_latency: :milliseconds,
      search: false,
      graph: true,
      persistence: :durable,
      max_dataset: :disk
    }
  }

  @spec route(atom(), map()) :: atom()
  def route(operation, context) do
    case operation do
      :read ->
        if context.requires_persistence do
          :postgresql
        else
          :ets
        end

      :write ->
        if context.requires_durability do
          :postgresql
        else
          :ets
        end

      :search ->
        if context.typo_tolerant or context.full_text do
          :meilisearch
        else
          :postgresql
        end

      :traverse ->
        :kuzudb

      :mixed ->
        select_by_workload_profile(context)
    end
  end

  defp select_by_workload_profile(context) do
    read_ratio = context.read_ratio || 0.5
    needs_search = context.needs_search || false
    needs_graph = context.needs_graph || false

    cond do
      needs_graph -> :kuzudb
      needs_search -> :meilisearch
      read_ratio > 0.8 -> :ets
      true -> :postgresql
    end
  end
end

The benchmark harness:

defmodule PrismaticStorage.Benchmark.Harness do
  @duration_ms 300_000
  @concurrency 50

  def run(backend, workload_profile) do
    dataset = generate_dataset(1_000_000)
    seed_backend(backend, dataset)

    operations = generate_operations(workload_profile, @duration_ms)

    start = System.monotonic_time(:millisecond)

    results =
      operations
      |> Task.async_stream(
        fn op -> execute_and_measure(backend, op) end,
        max_concurrency: @concurrency,
        timeout: 10_000
      )
      |> Enum.to_list()

    elapsed = System.monotonic_time(:millisecond) - start

    %{
      backend: backend,
      workload: workload_profile,
      throughput: length(results) / (elapsed / 1_000),
      latencies: extract_latencies(results),
      errors: count_errors(results),
      resource_usage: measure_resources(backend)
    }
  end
end

Results

Throughput (operations/second at 50 concurrent connections):

Workload	ETS	PostgreSQL	Meilisearch	KuzuDB	Router
Read-Heavy	847,000	42,300	12,100	8,700	762,000
Write-Heavy	524,000	18,700	3,200	6,100	471,000
Mixed	612,000	28,400	7,800	7,200	548,000
Search	N/A	4,100	18,400	N/A	17,200
Graph	N/A	1,200	N/A	14,300	13,800

Latency at p99 (microseconds):

Workload	ETS	PostgreSQL	Meilisearch	KuzuDB	Router
Read-Heavy	12	4,200	14,800	21,400	48
Write-Heavy	28	8,700	42,100	18,200	94
Mixed	19	6,100	28,400	19,800	67
Search	N/A	48,200	8,700	N/A	9,400
Graph	N/A	124,000	N/A	12,300	13,100

Memory consumption (MB at 1M records):

Backend	Base	At 1M Records	Per Record
ETS	0	312	312 bytes
PostgreSQL	128	384	256 bytes
Meilisearch	256	1,024	768 bytes
KuzuDB	64	448	384 bytes

Router improvement over best single backend:

Workload	Best Single	Router	Improvement
Read-Heavy	847K (ETS)	762K	-10.0%
Write-Heavy	524K (ETS)	471K	-10.1%
Mixed	612K (ETS)	548K	-10.5%
Search	18.4K (Meili)	17.2K	-6.5%
Graph	14.3K (Kuzu)	13.8K	-3.5%

Analysis

The results challenge our hypothesis in an instructive way. The Router does not achieve 5x improvement over single-backend deployment because ETS dominates most workloads by such a wide margin that routing overhead (the cost of the routing decision itself) reduces net throughput by 10%.

However, the Router provides massive improvement for workloads that no single backend handles well. For an application that needs both fast reads AND full-text search, no single backend is optimal. ETS cannot search, and Meilisearch reads are 1,200x slower than ETS. The Router achieves near-ETS read performance (762K ops/s) while also providing near-Meilisearch search performance (17.2K ops/s) by directing each operation to the correct backend.

ETS's dominance in throughput comes with a critical trade-off: no persistence. Applications requiring durability must use PostgreSQL, accepting a 20x throughput reduction for reads and a 28x reduction for writes. The Router intelligently applies this trade-off per operation rather than per application.

KuzuDB's graph traversal performance (14.3K ops/s at 12.3ms p99) is 11.9x better than PostgreSQL's recursive CTE approach (1.2K ops/s at 124ms p99) for the same graph queries. This validates KuzuDB's role as the dedicated graph backend.

Meilisearch's memory overhead (768 bytes per record vs ETS's 312 bytes) is the cost of maintaining full-text indexes with typo tolerance. For search-heavy workloads, this 2.5x memory premium delivers 4.5x better search throughput than PostgreSQL.

Conclusions

ETS is the performance baseline -- nothing competes for in-memory key-value operations.
The Router's value is multi-workload optimization, not single-workload improvement.
Backend selection should be per-operation, not per-application.
KuzuDB justifies its role with 12x graph traversal improvement over PostgreSQL.
Meilisearch's memory cost is warranted for search-intensive applications.

Next Steps

Implement write-through caching (ETS front, PostgreSQL durable) for the common read-heavy + durable pattern
Benchmark at 10M and 100M records to find scaling inflection points
Test concurrent multi-backend writes with consistency reconciliation
Evaluate Redis as an intermediate caching layer between ETS and PostgreSQL
Measure cross-node ETS replication latency for distributed deployments

Pipeline Experimentation -- Pipelines that write to these backends
OSINT Pipeline -- OSINT data stored across multiple backends
Session Lifecycle -- Session state persistence benchmarks
EASM Discovery -- Attack surface data storage requirements

Connect & Contribute

Created by Tomáš Korcak (korczis) | Open Source under GHL

GitHub | GitLab | LinkedIn | Contact
Developer Portal | Architecture | Meet the Creator

Multi-Backend Storage Performance

Hypothesis

Background

Methodology

Setup

Results

Analysis

Conclusions

Next Steps

Connect & Contribute

Experiment Info

Lab Experiments

Related Terms

Multi-Backend Storage Performance

Hypothesis

Background

Methodology

Setup

Results

Analysis

Conclusions

Next Steps

Related Experiments

Connect & Contribute

Experiment Info

Lab Experiments

Related Terms