predictivecache

PredictiveCache

PredictiveCache is a local Go library that learns repeated SQL template transitions and prefetches likely next SELECTs using conservative online workload modeling.

It is built for read-heavy application paths where SQL access follows repeated request flows, such as:

select user by id
  -> select orders for that user
  -> select recommendations for that user

It does not replace the database optimizer, call external model services, or infer business meaning from SQL text. The current pre-v1 API is a database/sql wrapper with exact result caching, online transition prediction, conservative argument binding, optional metadata persistence, and PostgreSQL-first parsing.

Use it for repeated, bindable multi-query request flows with enough time for a background query to finish. Use a regular exact cache when the same SQL and arguments already repeat frequently.

Status

Implemented through Milestone 9:

• A database/sql wrapper API: Open, Wrap, QueryContext, QueryRowContext, ExecContext, Explain, Stats, and Close.
• PostgreSQL parsing, normalization, and fingerprinting through pg_query_go; a conservative no-cgo fallback remains available.
• Bounded exact result caching with typed argument keys, TTL, row/entry/total byte limits, and PostgreSQL table-version invalidation.
• A custom predictivecache.Rows implementation for live and cached rows.
• Online order-1 through order-3 transition prediction conditioned by route and tenant.
• Explainable PostgreSQL structural features, weighted Jaccard similarity, and a bounded inverted index for cold templates.
• Conservative learned parameter binding with explicit rejection of incomplete or low-confidence plans.
• Opt-in background prefetch with bounded queueing, worker concurrency, timeout, QPS limiting, duplicate suppression, and write-generation protection.
• Public InvalidateTables and InvalidateAll hooks for writes that occur outside the wrapper.
• Metrics for cache behavior, prediction accuracy, binding, invalidation, prefetch completion, hits, waste, errors, timeouts, and rejection reasons.
• Optional local SQLite metadata persistence with background checkpoints, restart restoration, corrupt-state recovery, and shutdown flush.
• A deterministic synthetic e-commerce benchmark, a structural cold-start benchmark, and a Go-native BenchBase-derived Twitter benchmark against real PostgreSQL.

Writes made through PredictiveCache invalidate cache entries that depend on the modified PostgreSQL tables. External database writes are not observed automatically; applications must call InvalidateTables or InvalidateAll. Explicit transaction support and a transparent database/sql/driver wrapper are later work.

Persistence is opt-in through WithLogDir. It stores normalized templates, transition counts, binding statistics, table versions, structural features, and metrics. It does not store sessions, query arguments, result rows, or cache entries. Original executable SQL is excluded unless WithPersistRawSQL(true) is explicitly enabled.

Quickstart

Prerequisites:

• Go 1.22 or newer.
• A C compiler for the default PostgreSQL parser build. CGO_ENABLED=0 selects the narrower fallback parser.
• initdb and pg_ctl on PATH for the real PostgreSQL demo and integration tests.
• No Docker or Colima requirement.

The PostgreSQL scripts create disposable clusters under /tmp. Set PCACHE_TMPDIR to an existing short-path directory when /tmp is unavailable.

Install the library:

go get github.com/Hetul3/PredictiveCache@latest
go get github.com/jackc/pgx/v5

Run the unit and simulator suite:

go test ./...

Run the real PostgreSQL demo:

./scripts/run-demo.sh

Expected final line:

DEMO PASS: a learned transition executed on PostgreSQL in the background and served the next query from cache

Run the synthetic e-commerce evaluation:

go run ./cmd/pcache-bench -requests 1200 -seed 7 -write-percent 0 -noise-percent 0

Current deterministic output:

MODE                   SELECTS  CACHE_HIT_RATE  CACHE_LIFT_VS_EXACT  PREFETCH_HIT_RATE  PREFETCH_WASTE_RATE  ACC@1  ACC@3  STRUCTURAL_ACCEPTED  STRUCTURAL_ACC@1  MEMORY_KB
no_cache               3600     0.000           0.000                0.000              0.000                0.996  0.996  0                    0.000             11
exact_cache            3600     0.031           0.000                0.000              0.000                0.996  0.996  0                    0.000             8318
predictive             3600     0.668           0.637                1.000              0.000                0.996  0.996  0                    0.000             8318
predictive_structural  3600     0.668           0.637                1.000              0.000                0.996  0.996  0                    0.000             8318

Run the structural cold-start evaluation:

go run ./cmd/pcache-bench -scenario structural-cold -cold-templates 24

Current deterministic output:

MODE                   SELECTS  CACHE_HIT_RATE  CACHE_LIFT_VS_EXACT  PREFETCH_HIT_RATE  PREFETCH_WASTE_RATE  ACC@1  ACC@3  STRUCTURAL_ACCEPTED  STRUCTURAL_ACC@1  MEMORY_KB
no_cache               248      0.000           0.000                0.000              0.000                0.797  0.992  0                    0.000             6
exact_cache            248      0.000           0.000                0.000              0.000                0.797  0.992  0                    0.000             130
predictive             248      0.302           0.302                0.735              0.265                0.797  0.992  0                    0.000             144
predictive_structural  248      0.399           0.399                0.792              0.208                0.992  0.992  24                   1.000             153

See BENCHMARKS.md for methodology, upstream benchmark provenance, and real PostgreSQL benchmark results.

See ARCHITECTURE.md for component diagrams, data-flow diagrams, sequence diagrams, cache state transitions, runtime concurrency, and deployment notes.

See PROJECT_JOURNEY.md for the problem definition, design process, experiments, approaches that were rejected, benchmark iterations, and final tradeoffs.

See demo/README.md for a working PostgreSQL example and a step-by-step guide to integrating the wrapper into an existing Go service.

Benchmark Map

The regular Go cache baseline in the PostgreSQL benchmark is github.com/hashicorp/golang-lru/v2, a fixed-size thread-safe LRU cache. It is used as an application-level exact cache keyed by SQL plus args.

Cache hit rate, three-trial means:

Scenario                         LRU exact      Predictive     Use Predictive?
Flow, 500 users, 5ms gap         ################# 82.6%  ################### 94.2%  Maybe
Flow, 5000 users, 5ms gap        #### 17.5%             ############## 72.4%       Yes
Flow, 5000 users, 2ms gap        #### 17.5%             ###### 27.5%              No
Twitter mix, 500 users, 2ms gap  ## 11.5%               ############## 68.8%      No
Flow, 500 users, tiny LRU        ## 7.6%                ################### 94.2%  Yes, if exact cache is capacity-bound

Latency and waste decide the borderline cases:

Scenario                         Best read
Flow, 500 users, 5ms gap         LRU p50 3.5us; exact repetition dominates, predictive adds hit-rate but not enough latency benefit.
Flow, 5000 users, 5ms gap        Predictive +54.9pp hit-rate lift, 99.8% prefetch hit, 0.2% waste, lower p95 than LRU.
Flow, 5000 users, 2ms gap        Predictive waste 73.0% and issues 56.4% more PostgreSQL queries than exact cache.
Twitter mix                      Predictive has no meaningful lift over table-aware exact caching and adds PostgreSQL work.
Tiny LRU capacity                Exact-key LRU collapses when capacity is too small for the working set.

Rule of thumb: use PredictiveCache for repeated, bindable multi-query request flows with enough time for background prefetch to land. Use a regular exact cache when the same SQL+args repeat frequently. Disable predictive prefetch for single-query mixes, result-dependent second-stage queries, or very tight local-DB call chains.

Architecture

Application code
  |
  v
PredictiveCache Client
  |
  +-- PostgreSQL parser and template registry
  |
  +-- exact cache key: dialect + template id + args + table versions
  |
  +-- bounded in-memory result cache
  |
  +-- real database execution through database/sql + pgx
  |
  +-- async query event processor
        |
        +-- transition predictor
        +-- binding rule learner
        +-- structural cold-start index
        +-- metrics and explain output
        +-- optional SQLite metadata checkpoint
        |
        v
      bounded background prefetch scheduler

The foreground query path performs parsing, cache lookup, and database execution. Model training, metadata checkpointing, and prefetch execution happen asynchronously.

Basic Use

import (
	"database/sql"
	"time"

	"github.com/Hetul3/PredictiveCache"
	_ "github.com/jackc/pgx/v5/stdlib"
)

db, err := sql.Open("pgx", dsn)
if err != nil {
	return err
}

client, err := predictivecache.Wrap(
	db,
	predictivecache.DialectPostgres,
	predictivecache.WithPrefetchEnabled(true),
	predictivecache.WithLogDir("./.predictivecache"),
)
if err != nil {
	return err
}
defer client.Close()

ctx := predictivecache.WithSessionID(request.Context(), sessionID)
ctx = predictivecache.WithRoute(ctx, "GET /users/:id")

rows, err := client.QueryContext(ctx, "select id, name from users where id = $1", userID)
if err != nil {
	return err
}
defer rows.Close()

QueryContext returns *predictivecache.Rows, not *sql.Rows. It supports the common Next, Scan, Columns, ColumnTypes, Close, and Err methods, plus FromCache.

Prefetch is disabled by default. Production users should tune warm-up thresholds, confidence thresholds, cache bounds, worker count, timeout, and QPS limit for their workload.

Structural fallback is enabled by default in cgo builds, but it only affects cold templates with no observed transition context. Disable it with WithStructuralFallbackEnabled(false).

When another process writes to the same database:

if err := client.InvalidateTables("users", "orders"); err != nil {
	return err
}

Use InvalidateAll when the affected tables are unknown.

Documentation

Demo Metrics

The real PostgreSQL demo trains a user-to-orders transition, triggers a prefetch, then proves the next query came from cache. A representative metrics line is:

Metrics: attempts=2 hits=2 wasted=0 cache_entries=4 cache_bytes=64

attempts counts background prefetch executions. hits counts prefetched entries later used by an application query and can vary slightly with background timing in the demo. wasted counts prefetched entries evicted, expired, or invalidated before first use. The benchmark command reports the higher-level rates used for evaluation: cache hit-rate lift over exact caching, prefetch hit rate, waste rate, prediction accuracy, structural fallback use, and memory.

Verification

# Unit, simulator, parser, wrapper, cache, predictor, binding, persistence, and scheduler tests
go test ./...

# Data-race validation
go test -race ./...

# Verify the conservative parser build without cgo
CGO_ENABLED=0 go test ./...

# Static analysis and module integrity
go vet ./...
go mod verify

# Real PostgreSQL integration and syntax coverage
./scripts/test-postgres-integration.sh
PCACHE_GO_TEST_FLAGS=-race ./scripts/test-postgres-integration.sh

# BenchBase-derived multi-trial comparison against disposable PostgreSQL
./scripts/run-benchbase-twitter.sh

Versioning

The module follows semantic versioning. The recommended first public tag is v0.1.0, which correctly signals that the API is not yet stable. See RELEASING.md for the exact tag, push, proxy indexing, and pkg.go.dev verification steps.

License

MIT