introducing pgreflex - an open source convex alternative for postgres

moritz

Gulaschprogrammiernacht 24 (GPN24) · Day 3 · ZKM Kubus

Overview

In the rapidly evolving landscape of web development, ensuring that user interfaces remain consistently updated with backend data changes is a perennial challenge. Moritz's talk, "Introducing pgreflex - an open source Convex alternative for Postgres," tackles this fundamental problem head-on. The presentation introduces pgreflex, a novel open-source project designed to provide real-time, reactive UI updates directly from a PostgreSQL database, mirroring the capabilities of the proprietary Convex framework but without its associated limitations.

Watch on YouTube

Visual summary for introducing pgreflex - an open source convex alternative for postgres by moritz
Visual summary for introducing pgreflex - an open source convex alternative for postgres by moritz

Key moments

  1. 0:00 Introduction: The UI synchronization problem
  2. 11:45 Live demo: Real-time UI updates from database changes
  3. 15:00 Demystifying real-time sync: How queries detect changes
  4. 20:00 Critiques of Convex: Vendor lock-in and limitations
  5. 30:00 pgreflex architecture and simplified API design
  6. 34:00 The Postgres magic: Write-Ahead Log and logical replication
  7. 45:45 Q&A: Demonstrating pgreflex's cleaner join API

introducing pgreflex - an open source convex alternative for postgres

Speakers: Moritz

Conference: GPN24

YouTube: https://www.youtube.com/watch?v=IBaAFqAc21c

Overview

In the rapidly evolving landscape of web development, ensuring that user interfaces remain consistently updated with backend data changes is a perennial challenge. Moritz's talk, "Introducing pgreflex - an open source Convex alternative for Postgres," tackles this fundamental problem head-on. The presentation introduces pgreflex, a novel open-source project designed to provide real-time, reactive UI updates directly from a PostgreSQL database, mirroring the capabilities of the proprietary Convex framework but without its associated limitations.

The core problem pgreflex addresses is the "reactive UI" paradigm: when a user performs an action (e.g., closing an issue in a project management tool), not only should their own interface update instantly, but all other users observing the same data should also see those changes reflected in real-time, across different tabs and devices. Moritz, a seasoned developer with nearly two decades of experience in web software, presents pgreflex as a solution that extends the declarative UI approach from the frontend to the entire application stack, simplifying the development of complex, real-time web applications.

This talk is significant for any developer or architect grappling with state synchronization, cache invalidation, and the complexities of building real-time features. By leveraging the robust capabilities of PostgreSQL's logical replication and integrating with popular TypeScript tools like TRPC and Drizzle, pgreflex offers a compelling, truly open-source alternative to existing solutions. It promises to enhance developer experience, reduce boilerplate code, and provide a performant foundation for reactive web applications, democratizing a powerful development pattern that has historically been complex or tied to specific vendor ecosystems.

Background

▶ Watch: Introduction: The UI synchronization problem (0:00)

The evolution of user interface development has seen a significant shift from imperative rendering to declarative UI. In imperative rendering, exemplified by older frameworks like Windows Forms or raw JavaScript DOM manipulation, developers explicitly write code to describe how the UI should change in response to events. For instance, if a text input starts with '4', one might imperatively change a button's text to 'Pay Visa'. This approach, while straightforward for simple interactions, quickly leads to complex, error-prone code and inconsistencies in larger applications, as developers must meticulously track and update every affected UI element.

Facebook famously addressed these issues with React, popularizing the declarative UI paradigm. Here, developers describe what the UI should look like based on the application's state, and the framework handles the "how" of updating the DOM. This functional programming approach dramatically simplifies UI development, but its primary focus remains client-side. The challenge persists: how do you keep this client-side state in perfect sync with changes happening on the server or, more critically, directly in the database? If "Bob closes a PR," how does "Alice know without reloading her page?" This is the fundamental gap that Convex, and subsequently pgreflex, aims to bridge.

Convex emerged as a notable solution to this problem, positioning itself as an "entire backend for your TypeScript app" that guarantees real-time synchronization across frontend code, backend logic, and database state. Convex achieves this by providing its own database engine, a backend server/code runner, and a frontend framework, all tightly integrated to manage the interconnections. Its core innovation lies in live queries: developers declare data queries, and Convex automatically ensures that any UI consuming these queries updates instantly whenever the underlying data changes, eliminating the need for manual state managers, cache invalidation, or WebSockets for synchronization.

The "magic" behind Convex's live queries, as Moritz explains, involves the system inferring conditions from declared queries. When a database operation (insert, update, delete) occurs, Convex determines if that operation could potentially affect the result set of any active live query. If a row matches a query's conditions either before or after an update, Convex re-runs the query and pushes the new data to subscribed clients via WebSockets. This ingenious approach makes developing reactive applications remarkably simple, as demonstrated by Fio Brown's popular video showcasing instant UI updates from direct database modifications.

However, Convex, despite its technological prowess, comes with significant limitations that motivated the creation of pgreflex. Moritz highlights several key "gripes":

  1. Database Engine Limitations: Convex's custom query engine, while efficient for its specific use case, is restrictive. It struggles with aggregations (e.g., counting tasks), does not support joins between different tables, and requires specific index usage, making common SQL operations either impossible or inefficient. Queries that can't use an index become very expensive, as Convex must send all relevant documents to the app server for processing.
  2. Strong Vendor Lock-in: Convex controls the entire application stack—your data, sync layer, frontend data management, query engine, and backend app server code. This creates extremely high switching costs, making it difficult to migrate away if pricing changes or the service no longer meets needs.
  3. "Open Source" with Caveats: While Convex's core code is publicly available, it uses the Functional Source License v1.1. This license permits use for "any purpose other than a competing use," effectively preventing other companies (like AWS) from offering Convex as a hosted service, and limiting its utility for truly independent self-hosting or modification in certain contexts. Moritz notes that this license meant he couldn't deeply analyze Convex's source code for pgreflex, as that would constitute a "competing purpose."
  4. Self-Hosting Challenges: The self-hosted version of Convex lacks crucial features for production deployments, such as robust multi-instancing for backend servers (limiting an application to a single physical machine for its app server) and comprehensive role-based permission management.
  5. Verbose API for Joins: Performing data joins, a common operation in relational databases, is cumbersome in Convex, often requiring multiple manual queries and conditional logic in application code, a stark contrast to the simplicity of SQL joins.

These limitations, particularly the vendor lock-in and database restrictions, spurred Moritz to seek an alternative that could deliver Convex's real-time synchronization power using a standard, open-source stack.

Key Findings

▶ Watch: Demystifying real-time sync: How queries detect changes (15:00)

pgreflex represents a significant stride in addressing the challenges of real-time application development by offering a robust, open-source alternative to proprietary solutions like Convex. Its key findings and contributions can be summarized as follows:

  1. Convex-like Real-time Synchronization on a Standard Stack: The most crucial finding is the successful implementation of Convex's "always in sync" paradigm using widely adopted, open-source technologies. pgreflex demonstrates that it is possible to achieve seamless, real-time UI updates from database changes without proprietary database engines or complex custom synchronization layers.
  2. Leveraging PostgreSQL's Logical Replication: Instead of building a custom database or query engine, pgreflex cleverly harnesses PostgreSQL's logical replication feature. This allows the system to efficiently capture database changes (inserts, updates, deletes) directly from PostgreSQL's write-ahead log (WAL). This approach eliminates the need for complex database-level instrumentation, relying on a battle-tested, highly performant mechanism inherent to PostgreSQL itself.
  3. Truly Free and Open Source: Unlike Convex's functionally-licensed model, pgreflex is released under the permissive MIT License. This ensures that developers and organizations can use, modify, and distribute the project without restrictions related to "competing use" or vendor lock-in, fostering genuine community collaboration and adoption.
  4. Enhanced Developer Experience and API Design: Moritz prioritized developer ergonomics, designing an API that is arguably more intuitive and less verbose than Convex's, especially for common database operations and joins. Features like simplified select.single.optional methods and a cleaner syntax for where clauses, combined with full TypeScript autocompletion, significantly boost productivity and reduce cognitive load.
  5. Compatibility with Popular Webdev Tools: pgreflex is built upon a modern, popular TypeScript stack, integrating seamlessly with TRPC for end-to-end type safety between frontend and backend, and Drizzle as a lightweight SQL ORM. This choice ensures familiarity for many developers and allows for easier integration into existing projects.
  6. Addressing Convex's Limitations: pgreflex directly counters Convex's shortcomings by:
  • Using a full-featured PostgreSQL database, enabling complex queries, aggregations, and joins that Convex's engine restricts.
  • Eliminating vendor lock-in by relying on open standards and self-hostable components.
  • Providing a foundation for scalable, multi-instance backend deployments, a critical feature missing in self-hosted Convex.

In essence, pgreflex proves that the powerful concept of declarative, real-time data synchronization can be realized within the open-source ecosystem, offering developers greater control, flexibility, and freedom.

Technical Deep Dive

▶ Watch: Critiques of Convex: Vendor lock-in and limitations (20:00)

pgreflex's architecture is a testament to the power of combining existing, robust open-source components in an innovative way. Moritz's design choices prioritize familiarity, type safety, and the leveraging of PostgreSQL's advanced features.

The core pgreflex Stack is comprised of:

  • TRPC: This framework provides a type-safe API layer, allowing developers to call backend procedures directly from the frontend with full TypeScript inference. It acts as the communication backbone between the frontend and the pgreflex server.
  • Drizzle: A lightweight and performant TypeScript ORM (Object-Relational Mapper). Drizzle enables developers to define database schemas and construct queries using TypeScript, providing excellent type safety and a pleasant developer experience.
  • PostgreSQL: The chosen database for pgreflex. Moritz champions PostgreSQL for its robustness, extensive features, and battle-tested reliability. Crucially, pgreflex relies heavily on PostgreSQL's advanced replication capabilities.

The pgreflex Architecture consists of three main logical components:

  1. PostgreSQL Database: This is a standard PostgreSQL binary, running as the primary data store.
  2. pgreflex Server: This is the core logic responsible for subscribing to database changes and invalidating live queries. Currently implemented in C# (with a potential future rewrite in Rust), it acts as an intermediary between PostgreSQL and the application server.
  3. App Server: This is the application's backend server, typically a Node.js server running TypeScript code. From the pgreflex server's perspective, the app server is a "client" because it receives updates and forwards them to the frontend via TRPC.

The mechanism for live queries in pgreflex is a sophisticated orchestration of these components:

  1. Query Definition: Similar to Convex, developers define server-side procedures (e.g., a me endpoint) using pgreflex's API, which integrates with Drizzle. For example, a common query might involve selecting a single, optional user row based on a secret ID:

Moritz highlights the simplicity and type safety of this API, where ctx.db.select.single.optional('users').where({ secret: ... }) intuitively expresses the desired query, complete with autocomplete for table and column names.

  1. Frontend Subscription: On the frontend, TRPC's useSubscription hook is used to subscribe to these server procedures:

This hook establishes a WebSocket connection (managed by TRPC) to the App Server, which in turn communicates with the pgreflex Server.

  1. Change Data Capture via PostgreSQL's WAL and Logical Replication: This is where pgreflex's innovation truly shines. PostgreSQL maintains a Write-Ahead Log (WAL), a sequential record of all changes made to the database. The WAL is crucial for data durability and crash recovery. pgreflex leverages logical replication, a PostgreSQL feature that allows external consumers to receive a stream of data-modifying changes from the WAL in a decoded, human-readable format.
  • The pgreflex Server acts as a logical replication client. It connects to PostgreSQL and subscribes to a replication slot, receiving real-time notifications of INSERT, UPDATE, and DELETE operations on monitored tables.
  • For the pgreflex server to effectively determine if a change affects a live query, it needs not just the ID of the changed row, but often the full old and new row data. This is achieved by setting the REPLICA IDENTITY for relevant tables to FULL. While this has a slight performance impact (sending more data over the replication stream), Moritz notes that in practice, it's often negligible for many applications.
  1. Invalidation Loop and Data Push:
  • The pgreflex Server maintains a list of conditions for every active subscription. These conditions are derived from the where clauses of the live queries (e.g., secret = <user_secret> for the me query).
  • When a database change arrives via logical replication, the pgreflex Server iterates through its list of active subscriptions. For each subscription, it checks if the changed row (using its full old and new state, thanks to REPLICA IDENTITY FULL) matches the query's conditions either before or after the database operation.
  • If a match is found, indicating that the query's result set might have changed, the pgreflex Server signals the App Server. The App Server then re-runs the original query against the PostgreSQL database, retrieves the fresh data, and pushes it to the subscribed frontend clients via TRPC's async iterator mechanism over the WebSocket.

Join Handling: One of Convex's significant weaknesses was its cumbersome approach to joining data. pgreflex offers a much more elegant solution, as demonstrated by Moritz. Instead of multiple manual fetches, pgreflex allows for a more SQL-like join syntax:

This API allows developers to describe joins declaratively, and the pgreflex server can still track changes to related tables to ensure the joined data remains live-updated. This significantly reduces code complexity for common relational data patterns.

In summary, pgreflex cleverly repurposes PostgreSQL's robust internal mechanisms to create a powerful, real-time data synchronization layer. It builds on a foundation of well-established, open-source technologies, offering a developer-friendly API that abstracts away the complexities of change data capture and WebSocket management.

Demo / Proof of Concept

▶ Watch: The Postgres magic: Write-Ahead Log and logical replication (34:00)

Moritz's presentation included several compelling live demonstrations that showcased the core capabilities of pgreflex, illustrating its "always in sync" promise.

The initial demonstration (starting around 02:00) provided a clear visual of the framework's real-time synchronization. Moritz opened a pgreflex-powered web application in two separate browser tabs, one in a regular window and another in an incognito window, ensuring no shared client-side state. He then proceeded to:

  1. Create a New Account: An account was created in one tab, and its presence instantly appeared in the other tab.
  2. Change User Color: Modifying the color associated with a user in one tab immediately updated the color representation in the other tab.
  3. Create a New Poll: A new poll created in one tab seamlessly materialized in the other tab's UI.
  4. Send Chat Messages: In a small chat application built with pgreflex, messages sent from one tab instantly appeared in the other, demonstrating low-latency, real-time communication.
  5. Change User Name: Updating a user's name in one tab caused the name to update everywhere the user was referenced in the application, across all active sessions.

Crucially, Moritz emphasized that these updates occur regardless of how the data is modified in the database. To drive this point home, he performed a database-driven update via the command line (around 12:00). He executed a direct SQL command on the PostgreSQL database: UPDATE messages SET content = 'AAA'. The result was instantaneous: all chat messages in both browser tabs immediately changed their content to "AAA," demonstrating that pgreflex effectively captures and propagates any database change, not just those initiated through the application's API. This is a powerful feature, as it means data migrations, administrative changes, or even other services modifying the database will automatically trigger UI updates, eliminating stale data.

Throughout the technical deep dive, Moritz also provided live coding examples (around 32:00) to showcase the developer experience. He demonstrated the type-safe API for defining queries, highlighting how TypeScript autocompletion guides developers, preventing errors and improving productivity. For instance, attempting to query a non-nullable field for null would result in a type error, and only valid column names and types are suggested. He specifically showed the select.single.optional and where syntax, contrasting its conciseness with Convex's more verbose approach.

Finally, he briefly touched upon the improved join API (around 46:00), showing how pgreflex simplifies multi-table joins compared to Convex's manual fetching, allowing developers to express complex data relationships declaratively while still benefiting from real-time updates.

These demonstrations collectively proved that pgreflex successfully delivers on its promise of real-time, declarative data synchronization, and does so with a developer-friendly API and a robust, standard database backend.

Defensive Implications

▶ Watch: Q&A: Demonstrating pgreflex's cleaner join API (45:45)

While pgreflex is primarily a development framework aimed at improving productivity and user experience, its architectural choices and capabilities have several important implications for system design, security, and operational defense.

  1. Mitigation of State Management Bugs: By automating real-time data synchronization, pgreflex significantly reduces the surface area for common bugs related to stale data, incorrect cache invalidation, or race conditions. Developers no longer need to manually manage WebSocket connections, implement polling, or devise complex caching strategies to ensure UI consistency. This inherent correctness at the architectural level means fewer bugs to defend against in production.
  1. Enhanced Data Consistency and User Trust: Guaranteeing that all users see the most up-to-date information at all times is crucial for many applications, from collaborative tools to financial dashboards. pgreflex's direct integration with PostgreSQL's change data capture ensures high data fidelity across all connected clients. This consistency builds user trust and prevents business logic errors that can arise from users acting on outdated information.
  1. Reduced Vendor Lock-in and Increased Flexibility: A key defensive posture for any organization is to avoid single points of failure or proprietary dependencies that can lead to high switching costs. By building on standard, open-source components like PostgreSQL, TRPC, and Drizzle, pgreflex offers a strong defense against vendor lock-in. Should business needs change, or if a different cloud provider is preferred, the underlying technologies are widely supported and portable, offering greater long-term flexibility and cost predictability.
  1. Performance Considerations of REPLICA IDENTITY FULL: The use of REPLICA IDENTITY FULL for logical replication means that PostgreSQL sends the full old and new row image for every UPDATE or DELETE operation. While Moritz suggests this impact is "not that bad" in practice, it's a critical point for database administrators and architects. For extremely high-volume write workloads or very wide tables, the increased data volume on the replication stream could introduce performance overhead. Defensive planning should include thorough load testing and monitoring of replication lag and network bandwidth under production-like conditions.
  1. Scalability Challenges and Future Plans: Moritz openly acknowledges that the current pgreflex server is single-threaded and not yet production-ready for large-scale deployments. This means a single physical machine currently handles all live query invalidations. For high-traffic applications, this could become a bottleneck. The roadmap includes plans for a multi-threaded, load-balanced server architecture, and even a potential rewrite in Rust for better performance and concurrency. Until these features are implemented, organizations deploying pgreflex should carefully consider their scalability requirements and potential limitations.
  1. Security of the Logical Replication Stream: The logical replication stream exposes all data-modifying changes from the database. Defensively, it is paramount to secure the connection between the PostgreSQL database and the pgreflex server. This involves using strong authentication, encrypting the replication stream (e.g., via SSL/TLS), and ensuring network isolation (e.g., running the pgreflex server within a trusted private network segment). Unauthorized access to this stream could lead to sensitive data exposure or manipulation.
  1. Simplified Development, Fewer Security Flaws: While not directly a security feature, the improved developer experience and reduced boilerplate code enabled by pgreflex can indirectly contribute to better security. When developers spend less time battling state synchronization complexities, they can focus more on implementing core business logic securely, potentially leading to fewer application-level vulnerabilities. The type-safe nature of TRPC and Drizzle also helps prevent common injection vulnerabilities and data type mismatches.

In conclusion, pgreflex offers a compelling architectural pattern for building reactive applications with strong defensive benefits in terms of data consistency and vendor independence. However, like any powerful tool, it requires careful consideration of its operational implications, particularly around performance tuning, scalability planning, and securing the underlying data streams.

Key Takeaways

  • Real-time UI Synchronization is Essential: Modern web applications demand instant, consistent updates across all client sessions, moving beyond manual refreshes and complex client-side state management.
  • Convex Offers a Powerful but Constrained Solution: Convex demonstrates the power of declarative, real-time data synchronization but introduces vendor lock-in, a restrictive database query engine, and limitations for self-hosting due to its functional source license.
  • pgreflex Provides a Truly Open-Source Alternative: By leveraging standard, battle-tested technologies like PostgreSQL's logical replication, TRPC, and Drizzle, pgreflex democratizes the "always in sync" paradigm without proprietary constraints.
  • PostgreSQL's Logical Replication is Key: The project's core innovation lies in its efficient use of PostgreSQL's write-ahead log (WAL) and logical replication to capture database changes, providing a robust and performant foundation for real-time updates.
  • Enhanced Developer Experience: pgreflex prioritizes developer ergonomics with a type-safe, concise API for defining live queries and handling joins, significantly simplifying the development of reactive features compared to existing alternatives.
  • Strategic Choice for Modern Web Development: For developers seeking to build highly interactive, real-time applications with robust data consistency and the flexibility of an open-source stack, pgreflex offers a compelling and future-proof architectural solution.

About the Speaker(s)

The talk was delivered by Moritz, a seasoned software developer with extensive experience in web technologies. He notes that he has been "writing software for almost 20 years now, almost always involving the web in some way." Moritz's deep understanding of database systems is evident, as he previously worked on a startup that involved building a database engine into their product. He approaches the topic from an independent perspective, emphasizing that his views are his own and do not represent any specific company or entity. His passion for solving complex development challenges and contributing to the open-source community is clearly demonstrated through his work on pgreflex.

Reviews

Dr. Zero (Offensive Security Researcher) — SOLID

A competent intro to a promising project that solves a real problem — Convex-style reactive queries on Postgres via logical replication. But this is a project announcement with architecture walkthrough, not research. The technical depth stops at 'we use WAL and REPLICA IDENTITY FULL,' which any Postgres DBA already knows.

Heather Calloway (CISO) — WEAK

This is a software development framework talk about real-time database synchronization for web applications. It has no security content, no threat modeling, no governance implications, and no relevance to a security leadership audience. It's out of lane for this review.

→ Top-rated talks at Gulaschprogrammiernacht 24 (GPN24)

All talks from Gulaschprogrammiernacht 24 (GPN24)