Postgres Conf 2024: Local-first apps using Postgres logical replication

postgresconf.org/conferences/...
www.powersync.com
Local-first is an architecture where app code works directly with a client-side database which automatically syncs with a backend database in the background.
This talk covers how we used Postgres logical replication to build PowerSync, a high-scalability sync engine for Postgres which syncs to and from an embedded SQLite database on the front-end.
Chapters
00:00 Introduction
00:35 What is local-first?
00:58 Why local-first architecture?
02:03 Why we focused on Postgres
02:22 Available sync engines
02:51 Postgres and SQLite
03:23 Design, architecture and implementation
03:39 Design objectives
04:31 High-level architecture
05:03 Implementing dynamic partial replication
05:55 Our chosen design: pre-defining "buckets" of data
06:20 Dynamic data replication: How it works
09:35 Writes to SQLite: Syncing back to Postgres
10:16 Server Authoritative: No CRDTs needed
11:58 Guaranteeing consistency
12:42 Guaranteeing data integrity
12:54 Tying it all together
13:13 Logical replication challenges and solutions
13:49 Handling schema/DDL changes
15:46 LSNs that overlap between transactions
16:41 Accessing TOASTed values
17:38 Handling different row identifier permutations
18:45 Advancing local-first adoption