Performance Guide

CentauroDB stores objects as JSON blobs. Without any tuning you get zero-migration flexibility — but raw JSON queries do a full table scan on every read. This guide covers the tools that turn that into fast, indexed access.

Index the fields you query

Indexing is the single biggest lever for read performance. There are two ways to create indexes:

Via create_index() — create expression indexes on any field:

coll.create_index("unit")       # idempotent — safe to call on every startup
coll.create_index("location")

This produces an expression index:

CREATE INDEX idx_monitoring_unit ON monitoring_objects (json_extract(meta, '$.unit'))

When to index: Add indexes for any field you filter, sort, or aggregate on frequently. Common candidates include status fields, category fields, timestamps, and foreign keys (parent_ref fields).

Limit view columns with include_fields

By default a view extracts every field from the model. On wide models this means parsing the full JSON blob for every row — even when you only need two columns.

# 2 json_extract() calls per row instead of 10
coll.create_view("lite", Sensor, include_fields=["unit", "location"])

Pair with create_index() for maximum impact:

coll.create_view(
    "lite",
    Sensor,
    include_fields=["unit", "location"],
)
coll.create_index("unit")

Disable assignment validation for hot paths

CentauroModel enables validate_assignment=True by default, which re-runs Pydantic validation every time you set an attribute. This catches type errors early but adds overhead in tight loops.

from pydantic import ConfigDict

class SensorReading(CentauroModel):
    __centauro_name__ = "SensorReading"
    model_config = ConfigDict(validate_assignment=False, populate_by_name=True)
    unit: str = ""
    value: float = 0.0

Best for models that are created, immediately written, and never mutated after — such as append-only readings or log entries.

Skip value hydration when you don't need it

Reading a TimeSeriesCollection object with the default hydrate_values=True fires a second SELECT against the values table. If you only need metadata, skip it:

obj = coll.read_object_by_id(42, Metric, hydrate_values=False)
obj.unit       # available immediately
obj.values     # empty — not loaded
obj.df         # raises ValuesNotHydratedError until refresh() is called

This is especially useful when listing many objects for a dashboard:

# Fast: no values query per object
all_metrics = coll.read_objects(Metric, hydrate_values=False)
for m in all_metrics:
    print(m.unit, m.row.edit_time)

Batch-populate values before writing

CentauroDB writes all obj.values in a single executemany call. Populate the list fully before calling write_object rather than appending and updating repeatedly:

# Efficient: one INSERT executemany
metric = Metric(
    unit="kg",
    values=[CentauroValues(time=t, value=v) for t, v in readings],
)
coll.write_object(metric)

# Inefficient: N separate UPDATE round-trips
metric = Metric(unit="kg")
coll.write_object(metric)
for t, v in readings:
    metric.values = [CentauroValues(time=t, value=v)]
    coll.update_object(metric, values_mode="append")

Choose the right values_mode for updates

When calling update_object on a TimeSeriesCollection, the values_mode parameter has significant performance implications:

Query via views for complex filters

read_objects uses json_extract() in the WHERE clause and benefits from expression indexes. For multi-condition queries, creating a view and running raw SQL is often cleaner and can leverage query planner optimizations:

# Python DSL — good for simple filters
results = coll.read_objects(Sensor.fields.unit == "Celsius")

# SQL via view — better for aggregations, JOINs, GROUP BY
df = coll.sql_select("""
    SELECT location, AVG(threshold) AS avg_threshold
    FROM monitoring_view_dashboard
    WHERE unit = 'Celsius'
    GROUP BY location
    ORDER BY avg_threshold DESC
""")

Split collections for table-level performance

By default, one collection per project is the right starting point. But as data grows, a single _objects table can become a bottleneck. Splitting into multiple collections gives each its own table, which helps in three specific scenarios:

Write-volume skew

If one model type produces far more rows than the rest (100x+), every read_objects call on the quiet types still scans past the hot type's rows (even with an index on name, the B-tree is larger). A dedicated collection keeps the hot table separate:

# Before — one table, 10M SensorReading rows slow down Config reads
app = Collection(engine, "app")

# After — hot writer gets its own table
config  = Collection(engine, "config")          # small, fast scans
readings = TimeSeriesCollection(engine, "readings")  # millions of rows, isolated

Independent retention

When you need to purge old data for one model type but keep others, a separate table lets you DELETE or DROP cheaply without locking or fragmenting the shared table:

logs    = Collection(engine, "logs")       # purge weekly
settings = Collection(engine, "settings")  # keep forever

WAL contention (SQLite)

SQLite allows one writer at a time. If two parts of your app write frequently, putting them in the same collection means they compete for the same table lock. Separate collections don't eliminate the global write lock, but they reduce the time each write holds it (smaller indexes to update, smaller pages to flush).

Use in-memory SQLite for testing

The :memory: engine skips all disk I/O. Use it for unit tests and batch processing:

# Unit tests — fast, isolated, no cleanup
engine = Engine(":memory:")

# Batch jobs — process → query → discard
engine = Engine(":memory:")
coll = Collection(engine, "results")
# ... heavy processing ...

For file-based SQLite, CentauroDB automatically enables PRAGMA journal_mode=WAL, which allows readers and a single writer to operate concurrently.

PostgreSQL JSONB for read-heavy workloads

When using PostgreSQL, CentauroDB stores the meta column as JSONB — pre-parsed binary JSON. This is faster for queries because the database doesn't re-parse the blob for each extraction.

Switching from SQLite requires no code changes:

engine = Engine("postgresql://user:pass@host/dbname")

On PostgreSQL, expression indexes use the ->> operator:

CREATE INDEX idx_monitoring_unit ON monitoring_objects ((meta->>'unit'))

Use create_index() to create them automatically.

Summary