Local-First Architecture: Syncing Postgres with Electric SQL and PGLite

The traditional web application architecture—where the client sends a request to an API, waits for a database query to resolve, and then renders the response—is increasingly becoming a bottleneck for user experience. Even with modern CDNs and optimized backends, the inherent latency of the round-trip remains. Users expect interfaces that feel instantaneous, work offline, and handle multi-device synchronization without the dreaded 'loading spinner' appearing at every interaction.

Enter the Local-First paradigm. In a local-first application, the primary data source is a database living directly on the client device. The UI interacts with this local database with zero latency. Synchronization with the server happens in the background, treating the network as an enhancement rather than a hard dependency.

In this article, we will explore a cutting-edge stack for implementing this architecture: PGLite, a WASM-based PostgreSQL build for the browser, and Electric SQL, a sync engine that bridges the gap between your server-side Postgres and your client-side PGLite instances.

The Core Challenge: Why Sync is Hard

Building a local-first app isn't just about putting a database in the browser. We've had IndexedDB for years, and more recently, SQLite via WASM. The real challenge lies in synchronization and conflict resolution.

If you have ten users editing the same dataset offline, how do you merge those changes when they reconnect? How do you ensure that the subset of data on a user's phone is consistent with the massive multi-terabyte database on your server?

Historically, developers had to write custom synchronization logic, often involving complex timestamp tracking, 'outbox' patterns, and manual conflict resolution code. Electric SQL and PGLite aim to solve this by providing a transparent, robust replication layer that treats the client-side database as a logical extension of the server-side Postgres.

PGLite: Postgres in the Browser

Until recently, if you wanted a relational database in the browser, SQLite was the only viable option. While SQLite is excellent, it creates a 'dialect gap' if your backend is running PostgreSQL. You end up writing different schemas, different queries, and dealing with different data types.

PGLite is a breakthrough because it is a build of the actual Postgres source code compiled to WebAssembly (WASM), packaged into a lightweight TypeScript library. It allows you to run a full Postgres instance inside a browser tab, a Web Worker, or a Node.js process.

Key Features of PGLite:

• Small Footprint: It is roughly 3MB compressed, which is remarkable for a full SQL engine.
• Persistence: It can persist data to IndexedDB or the newer, faster Origin Private File System (OPFS).
• Full SQL Support: It supports nearly everything Postgres does, including JSONB, Common Table Expressions (CTEs), and even extensions like pgvector.
• Reactive Queries: It allows you to subscribe to query results, making it perfect for building reactive UIs.

Electric SQL: The Sync Bridge

If PGLite provides the storage, Electric SQL provides the transport. Electric SQL is a sync layer that sits between your main Postgres database and your client-side PGLite instances. It uses Postgres's native logical replication to track changes and stream them to clients.

The "Shape" Protocol

One of the most powerful concepts in Electric SQL is the Shape. In a traditional sync system, you often have to sync entire tables or nothing at all. This doesn't scale. A user shouldn't have to download the entire orders table to see their last five purchases.

An Electric Shape is a subset of the database defined by a query. For example, a shape might be defined as: SELECT * FROM tasks WHERE project_id = '123'.

Electric ensures that the client's local PGLite instance stays perfectly in sync with that specific shape. When data changes on the server that falls within that query's criteria, it is pushed to the client. When the client writes to their local PGLite, those changes are captured and sent back to the server.

Architecting a Local-First Application

To understand how these pieces fit together, let's look at the architectural flow of a reactive, offline-capable task manager.

1. The Backend Setup

You start with a standard PostgreSQL database. You run the Electric SQL sync service (usually as a Docker container) alongside it. Electric connects to Postgres using a replication slot, allowing it to see every INSERT, UPDATE, and DELETE in real-time.

2. The Client-Side Initialization

On the frontend, you initialize PGLite and connect it to the Electric sync service.

import { PGLite } from '@electric-sql/pglite';
import { syncShape } from '@electric-sql/client';

// Initialize the local Postgres
const db = new PGLite('idb://my-app-db');

// Define the shape we want to sync
const shape = {
  url: 'https://api.my-app.com/v1/shape/tasks',
  params: {
    where: "project_id = 'abc-123'"
  }
};

// Start the sync process
syncShape(db, shape);

3. Reactive UI Rendering

Instead of calling a REST API to get tasks, your UI components query the local PGLite instance. Because PGLite supports subscriptions, your UI updates automatically whenever the local data changes—whether that change came from a user action or a sync update from the server.

// Example using a generic reactive pattern
db.live.query("SELECT * FROM tasks ORDER BY created_at DESC", [], (results) => {
  renderTaskList(results.rows);
});

Handling Writes and Conflict Resolution

In a local-first world, writes are optimistic by default. When a user clicks 'Complete Task', you execute an UPDATE command against PGLite. The UI updates instantly (0ms latency). In the background, Electric captures this local change and sends it to the server.

Conflict-Free Replicated Data Types (CRDTs)

Electric SQL uses a causal integrity model. By default, it employs a 'Last Write Wins' (LWW) strategy at the column level. However, because it understands the relational structure, it ensures that foreign key constraints and referential integrity are maintained during the sync process.

If two users update different columns of the same row while offline, Electric can merge those changes seamlessly. If they update the same column, the version with the later timestamp (using physical or logical clocks) typically wins, but the system is designed to prevent the database from ever entering an inconsistent state.

Practical Advantages for Development Teams

Transitioning to this stack offers several high-level benefits that go beyond just 'working offline.'

Simplified State Management

In a standard React/Redux or TanStack Query app, a huge portion of your code is dedicated to 'cache management.' You have to worry about invalidating queries, optimistic updates, and keeping the UI in sync with the server.

With Electric and PGLite, the local database is your state. You don't 'fetch' data; you 'observe' it. This eliminates an entire category of bugs related to stale data and manual cache invalidation.

Reduced Server Load

Because clients are querying their local WASM database, your server-side Postgres isn't being hit with every single UI interaction or search filter. The server primarily handles the ingestion of changes and the broadcasting of replication logs. This allows you to scale to more users with smaller server hardware.

Improved Developer Experience (DX)

You get to use the same language (SQL) on both the frontend and the backend. No more mapping Postgres types to JSON, then to TypeScript interfaces, then back to a different client-side store. The schema is the source of truth across the entire stack.

Challenges and Trade-offs

No architecture is a silver bullet. There are specific considerations to keep in mind when adopting this approach:

1. Initial Sync Payload: The first time a user opens the app, they may need to download a significant 'shape' of data. You must carefully design your shapes to ensure the initial load isn't too heavy.
2. Storage Limits: While IndexedDB and OPFS can store gigabytes of data, browsers still impose limits based on disk space. This stack is best suited for operational data (tasks, messages, configurations) rather than massive binary blobs.
3. Schema Migrations: Migrating a database that exists on thousands of client devices is harder than migrating a single server-side DB. Electric SQL provides mechanisms for handling migrations, but it requires a more disciplined approach to schema evolution (e.g., avoiding destructive changes).
4. WASM Overhead: While PGLite is small, it still adds a few megabytes to your initial bundle. For a simple landing page, this is overkill. For a complex SaaS tool, it’s a rounding error.

Real-World Use Cases

Where does this stack shine?

• Collaborative SaaS Tools: Think Linear, Trello, or Notion. These apps require high reactivity and must handle multiple users editing the same project.
• Field Service Apps: Applications used by technicians in areas with spotty connectivity (warehouses, basements, rural areas). They can perform their work offline, and the data syncs automatically when they hit 5G.
• Data-Intensive Dashboards: When users need to filter and sort thousands of rows instantly without waiting for a server to re-run an aggregation query.

Conclusion and Actionable Next Steps

Local-first is no longer a niche requirement for 'offline' apps; it is becoming the standard for high-performance web applications. By combining the power of a full PostgreSQL engine in the browser (PGLite) with a robust, shape-based sync layer (Electric SQL), we can finally build applications that are as responsive as local desktop software while maintaining the centralized data integrity of a traditional web app.

To get started:

1. Evaluate your data model: Identify which parts of your application would benefit most from 0ms latency and define them as 'Shapes.'
2. Experiment with PGLite: Replace a complex piece of client-side state logic with a local PGLite instance to see how SQL simplifies your frontend code.
3. Spin up an Electric instance: Use the official Docker images to connect a local Postgres DB to a PGLite frontend and witness the 'magic' of automatic replication.

The shift from 'Request/Response' to 'Sync/Observe' is a fundamental change in how we think about web development, but for the modern user, it’s a change that is long overdue.