Question about Postgres storage format in self-hosted setup

[strange halo]

I'm testing self-hosted Convex with Postgres and noticed that json_value is stored as BYTEA instead of JSONB.

Why this design choice? Wouldn't storing as JSONB with an additional column to indicate committed rows open up more possibilities?

Use case:
I'd like to use Convex as the sync engine for rapid frontend prototyping, but also have a path for other backend services/languages to work directly with the Postgres DB using standard SQL queries.

With JSONB storage, we could:

• Query/filter data directly in Postgres
• Build analytics/reporting tools
• Integrate with existing Postgres tooling
• Maintain Convex's real-time sync benefits

Is there a technical reason BYTEA is preferred, or is this something that could be configurable in future versions?

[smoky birch]

Hi @strange halo. I'm kapa.ai, your friendly support bot. I will try to answer your question by searching through the knowledge base. :wave:

---

Short answer: Convex’s self-hosted Postgres/MySQL/SQLite backends are implementation details, not a public data model. The Convex layer manages its own document format, indexing, and transactional semantics on top of the storage engine, and Convex tables/indexes do not map 1:1 to Postgres tables/indexes. Because of this, the storage is optimized for Convex’s engine (e.g., storing whole documents as blobs) rather than exposing SQL-native types like JSONB for external querying. [Convex overview; Discord: is Convex really a DB?]

More detail and implications:

• No direct table/type mapping: Convex “uses [the underlying database] essentially as an ordered key/value store.” Your Convex tables and indexes are managed in Convex’s layer; they don’t become Postgres tables/indexes, which is why JSONB-based SQL queries aren’t supported or guaranteed to work. [Discord: is Convex really a DB?; Discord: Postgres extensions]

• Stability and flexibility of the engine: The specific storage engine and layout are considered internal and may change. Convex intentionally abstracts the storage so they can evolve the internals without breaking app code or coupling to Postgres-specific features like JSONB operators or extensions. [Discord: is Convex really a DB?; Convex vs. relational]

• Query model by design: Convex queries are TypeScript running in the database with explicit index usage, and Convex avoids “pretend-cheap primitives” like SELECT column-projection or COUNT because it stores whole JSON documents and optimizes differently than row/column stores. This aligns with storing documents as opaque blobs in the persistence layer rather than columnar JSONB. [Why no SELECT/COUNT?; What they have in common]

• Self-hosted posture: Even when you run with Postgres, the recommended path is to interact through Convex functions/queries, not via direct SQL against the persistence layer. For external analytics, Convex suggests exports (snapshots or streaming) to analytics tools rather than querying the underlying OLTP store directly. [Self-hosting guide; Observability/analytics]

Your specific proposal (JSONB plus a committed flag) would enable SQL-centric workflows, but it would break Convex’s guarantees and portability, and it isn’t how Convex models indexes/consistency. Trusted staff have also said Convex tables won’t directly map to Postgres tables and that extensions/types (like PostGIS) aren’t directly usable because of that abstraction. [Discord: Postgres extensions]

Workable alternatives today:

• Use streaming export to an analytics store (e.g., BigQuery) or export snapshots for ad hoc SQL via DuckDB; this is the intended path for SQL analytics/reporting. [Observability/analytics]

• If you must integrate with Postgres directly for other services, you can build a bridge via HTTP actions/webhooks or a component that writes to your own Postgres schema designed for SQL, keeping Convex as the source of truth and syncing outward. Note that this won’t be in the same transaction as Convex’s internal writes. [Discord: syncing from Postgres; Discord: Postgres extensions]

On configurability: The knowledge sources do not mention an option to switch to JSONB or expose a SQL-friendly schema, and staff explicitly state there isn’t (and won’t be) a direct mapping between Convex tables and Postgres tables. I’m sorry, but there’s no information about making JSONB storage a configurable mode in future versions. [Discord: Postgres extensions]

Note: you can ask me a follow up question by @ mentioning me again :speech_balloon: