PostgreSQL
CentauroDB supports PostgreSQL as an alternative backend. The Python API is identical — you change the connection string and everything else works the same. This guide covers setup, differences, and when to choose PostgreSQL over SQLite.
Installation
Install the PostgreSQL extra:
pip install "centaurodb[postgres]"
This installs psycopg, the modern PostgreSQL adapter for Python.
Connecting
Pass a PostgreSQL connection URL to Engine:
from centaurodb import Engine, Collection
engine = Engine("postgresql://user:password@localhost:5432/mydb")
coll = Collection(engine, "monitoring")
Everything else works exactly the same — write_object(), read_objects(), create_view(), all query operators, relations, time-series. Your Python code is backend-agnostic.
URL formats
# Standard
Engine("postgresql://user:password@localhost:5432/mydb")
# Alternate scheme
Engine("postgres://user:password@localhost:5432/mydb")
What changes under the hood
While the Python API stays identical, CentauroDB adapts the SQL it generates:
| SQLite | PostgreSQL | |
|---|---|---|
| Setup | Zero — built into Python | Requires a running PostgreSQL server |
| Connection | File path or ":memory:" | postgresql:// URL |
| JSON storage | json() text | JSONB (binary, faster queries) |
| JSON extraction | json_extract(meta, '$.field') | meta->>'field' |
| Concurrency | Single-writer | Multi-writer |
| Async | Needs check_same_thread=False | Thread-safe by default |
| Install | pip install centaurodb | pip install "centaurodb[postgres]" |
json_extract vs ->> — just write Python.Starting with SQLite, moving to PostgreSQL
A common pattern is to develop locally with SQLite and deploy to production with PostgreSQL. The migration path is straightforward — change one line:
# Development
engine = Engine("app.db")
# Production
engine = Engine("postgresql://user:pass@db-host/myapp")
No code changes, no model changes, no view recreation. The same Collection, TimeSeriesCollection, create_view, and create_index calls work on both backends.
When to use PostgreSQL
- You need concurrent writers (multiple processes or services writing to the same database)
- You need a network-accessible database (shared across services)
- You're deploying to a server environment with existing PostgreSQL infrastructure
- You need advanced PostgreSQL features via raw SQL (full-text search, triggers, etc.)
When to stick with SQLite
- Local development, prototyping, Jupyter notebooks
- Embedded applications (CLI tools, desktop apps)
- Single-user scenarios or single-writer workloads
- You want zero infrastructure — just a
.dbfile - Edge/IoT deployments where simplicity matters