Copy-and-Replay vs Branch-at-a-Point: Two Backup Recovery Paths
A side-by-side comparison of copy-and-replay recovery and branch-at-a-point restores, showing why restore time scales with data size in one model and not the other.
Loading…
Topic
Postgres
307 posts about Postgres. Every summary links to the original.
- 307 posts
- 15 companies
- 2 explainers
See the idea
Postgres explainers
Agent Memory: What Outlives the Run
Agent memory is the knowledge that survives a run, not the working state inside one. This explainer separates the two, then maps the write path, the read path, and the database limits that cap recall.
The Good Stuff
Paid picks we use ourselves. Links carry a referral code — a subscription through one earns us a commission at no extra cost to you.
Newest first
Posts
OLTP is expensive, you can ClickHouse it.
PostgreSQL’s ACID guarantees cause substantial write amplification: each write flushes WAL for durability, copies rows via MVCC for isolation, and updates every affected index, amplifying 100 bytes of row change into tens of kilobytes of I/O. Costs scale linearly with data size, and even sharded Postgres offers only linear scaling with added coordination overhead, so growing datasets drive rising bills. The post proposes offloading settled, rarely-changing data to ClickHouse, whose columnar compression shrinks datasets 10-30x and whose cloud offering costs about 5x less than comparable Postgres infrastructure while also reducing Postgres compute load through fewer indexes. ClickHouse builds indexes in background batches and runs aggregations at roughly 100x lower cost than Postgres. ClickHouse Cloud provides WalShadow for millisecond-latency CDC and pg_clickhouse FDW, enabling an application pattern that cuts the cost of serving settled data by an order of magnitude.
October 2026 newsletter
The October 2026 ClickHouse newsletter announces CostBench results where ClickHouse Cloud delivered 752× better performance per dollar than Databricks and 438× to 512× better than BigQuery on real-time analytics workloads. The 26.9 release extends LIMIT with AFTER and UNTIL boundary conditions, adds incremental refresh for materialized views, and introduces CREATE TOKEN for time-limited credentials. Executable user-defined functions are now generally available on ClickHouse Cloud, supporting precompiled Rust, Go, C++, and JavaScript binaries with outbound network access and a deterministic flag. Featured community member Hoang Minh V has had over 100 pull requests merged since May, including a fast path for PromQL aggregations and fixes to TimeSeries reads. The newsletter also covers the official Apache Airflow provider for ClickHouse, the chDB durable layer for agent memory, and a Postgres round-up on memory safety and local NVMe storage.
Sync real-time Stripe data to Supabase with Stripe Data Pipeline
Stripe’s Data Pipeline now adds Postgres as a destination, enabling developers to sync Stripe data directly into their Supabase project without maintaining webhooks or working around API rate limits. Setup occurs entirely in the Stripe Dashboard: under Reporting, Data management, users select Data Pipeline, choose Postgres and Supabase, and authorize the connection. Stripe backfills historical records and continuously syncs charges, refunds, disputes, customers, subscriptions, and invoices into a dedicated stripe_sdp schema in the user’s Postgres database. A dedicated stripe_sdp role with scoped permissions, JWT direct connections using short-lived tokens, and enforced SSL ensure the database password is never exposed to Stripe. Developers can then query the synced tables with standard SQL, join them with application data, and implement features like MRR calculations, churn analysis, or subscription-based feature gating without extra API calls.
How Ridgeline unified Iceberg and ClickHouse into one analytics layer for investment management
Ridgeline, a cloud-native investment management platform, rebuilt its performance analytics layer after Postgres could not handle the scale of larger customers. The team evaluated Redshift, SingleStore, Trino over Iceberg, and a custom in-memory engine before choosing ClickHouse for its analytical performance and lower cost. They adopted ClickHouse Cloud on AWS, using separate organizations for staging and production and per-customer services, with assistance from ClickHouse engineers. Historical data lands in Apache Iceberg via a Spark pipeline and is queried through ClickHouse, while recent data is written directly into ClickHouse. The architecture enabled Ridgeline to serve customers 10x larger than before and more than double its live customer count.
Sizing Lakebase Search: lessons from a real app and 4 million passages
Slide Hub, an internal Databricks app for slide search, uses Lakebase Postgres with Lakebase Search extensions (lakebase_vector and lakebase_text) to run hybrid vector and BM25 search on the same database as application data. The app stores 23k slides and indexes up to 4M passages from the MIRACL English corpus. Benchmarking shows that below 8 Compute Units (CU), Lakebase Postgres is the bottleneck; above 8 CU, the app’s worker count limits throughput. Candidate pool size significantly affects cold-start latency: 200 candidates preserves 90.5% of top-10 results while touching a third of the pages of 1,000 candidates. A 14-minute load test cost 0.13, with Lakebase compute billing only while active. For production, a 0.5 CU minimum costs ~ 31-40/month and avoids cold-start delays.
Postgres at Open House NYC
At ClickHouse Open House NYC, the team announced WalShadow, an open-source engine that replicates Postgres into ClickHouse from the physical WAL, achieving sub-second latency and high throughput. They also launched ClickHouse Managed Postgres on Google Cloud in private preview, offering Postgres on local NVMe storage with native CDC into ClickHouse. The chdb Postgres extension was released, enabling fast imports from cloud storage using the embeddable ClickHouse engine. A live demo showed WalShadow keeping ClickHouse under 400 ms behind during a simulated trading platform with 50,000 trades per second, and detecting a fraud burst in under 500 ms compared to over 15 seconds with ClickPipes. The integrated stack aims to provide world-class Postgres for transactions and ClickHouse for analytics, with WalShadow as a key step toward unifying OLTP and OLAP.
Lakebase and Agentic SDLC: Branching Databases for Coding Agents
Coding agents working in parallel on shared databases create schema conflicts, test interference, and data exposure risks. Lakebase Postgres solves this with database branching, which creates an isolated copy of an entire database in under a second using copy-on-write. Each agent gets its own database branch via a post-checkout Git hook tied to a worktree, and each pull request gets an ephemeral branch through CI tooling. Schema changes are tracked in code with migration tools such as Drizzle or Alembic and promoted to the parent branch during deployment, not by merging database branches. Idle branches scale to zero, so running many concurrent agents incurs no compute cost for unused branches.
How we migrated Clera’s 500 GB production database to ClickHouse Managed Postgres overnight
Clera, an AI talent agent representing 200,000+ candidates, migrated its 500 GB production Postgres database to ClickHouse Managed Postgres after outgrowing its previous provider. The previous database suffered from point lookups taking multiple seconds, 100% CPU for six straight days, and timeouts. Clera evaluated Supabase, Neon, PlanetScale, and ClickHouse, choosing ClickHouse for its local NVMe storage that avoids network-attached disk latency. In sysbench TPC-C testing, ClickHouse showed 16–42% higher throughput than the runner-up. The team migrated 500+ tables from the EU to the US overnight using ClickPipes and manual replay, with production traffic still hitting the database. After migration, CPU usage dropped to 10–20%, and no settings have been changed since the cutover.
🗽 Postgres Summit US 2026 retrospective
ClickHouse sponsored Postgres Summit US 2026 in New York City, where David Wheeler presented “Postgres in da ClickHouse” on integrating Postgres and ClickHouse for OLTP and OLAP workloads. He demoed four technologies: pg_stat_ch for query metrics, pg_clickhouse for querying ClickHouse from Postgres (pushing down 16 of 22 TPC-H queries and supporting nearly all Postgres data types), chdb_hook for efficient data lake file import/export via COPY (outperforming similar extensions), and WalShadow for sub-second physical replication. Other sessions covered pg_plan_advice in Postgres 19, index prefetching for Postgres 20, open source CDC plugins, and a graveyard of technologies that failed to “kill” SQL. Wheeler also attended talks on Postgres replication, African Postgres communities, and CloudNativePG extensions. The conference marked ClickHouse’s first Postgres conference sponsorship, with plans to sponsor PGConf.EU in Valencia.
Supabase Select 2026 Recap
Supabase announced a code-first approach at its Select 2026 event, enabling developers to store schema and configuration in code so coding agents can set up and modify projects end to end. Local Supabase now runs as native processes without Docker, allowing multiple instances per directory and compatibility with sandboxes like Claude Code. A new MCP server lets application users connect their own agents, authenticating through existing Auth flows and enforcing row-level security on every tool call. Operational tools include Health Check Advisors for error rates, Database Connections for session management, Explorer notebooks for unified SQL and log queries, and query_logs for agent monitoring. Scaling features encompass Multigres for multi-node Postgres high availability, OrioleDB to avoid table bloat and VACUUM, Supabase Pipelines for near real-time data streaming to analytics platforms, and dbarena for transparent benchmarks.
Scale without limits: Multigres, OrioleDB, and dbarena
At Supabase Select, Supabase announced Multigres for high availability and connection pooling, OrioleDB for high performance storage, and dbarena for transparent benchmarking. Multigres is an open source operating system for Postgres that adds scalable connection pooling and automated multi-node failover using a consensus protocol built on synchronous replication, preventing data loss during failover. OrioleDB replaces Postgres’s heap storage with an undo log-based engine that eliminates bloat, VACUUM overhead, and transaction ID wraparound by using 64-bit transaction IDs, delivering up to 1.8x higher throughput in TPC-C-derived benchmarks. dbarena.com is an open benchmarks site that publishes reproducible performance and cost comparisons across managed Postgres providers including Supabase, Amazon RDS, and Google Cloud SQL, with full methodology and raw data. These tools aim to remove scaling limitations at every layer of Postgres, with all improvements being open source and available to the community.
Supabase is acquiring Turso
Supabase is acquiring Turso to evolve database infrastructure for the massive scale of AI agent-created databases. Supabase already launches over one million databases per week and expects demand to outpace capacity as agents build more software. Turso rebuilt SQLite in Rust, creating a cloud platform where a single server manages millions of databases, loading them on demand and suspending them when idle, avoiding dedicated machines for small workloads. This architecture suits agent workloads needing cheap, on-demand databases, while Postgres remains the path for scaling to production; Turso will continue operating independently with a clear path into Supabase’s ecosystem, and for existing users nothing changes. Glauber Costa will lead the agentic infrastructure effort, and the combined mission is to store the world’s data as agents create more of it.
What is direct I/O, and why does ClickHouse Managed Postgres use it for backups?
A base backup reads every byte of a live database off the same NVMe drives that serve queries. Buffered reads go through the kernel page cache, evicting warm database pages and adding a kernel copy per byte. Direct I/O skips the cache, leaving Postgres’s warm data in place, but also disables kernel readahead. On a RAID0 array, small direct reads hit only one drive, so ClickHouse Managed Postgres sizes each direct read to span the whole stripe and scales the reader count to the hardware. On a 48 vCPU box with four NVMe drives and a 467 GB database, the direct I/O backup evicted nothing, cut query latency impact by about two thirds, used about 14% less CPU, and finished in 71 seconds.
Lakebase Postgres branch-based restores for fast recovery at scale
Traditional Postgres point-in-time recovery is slow because compute and storage are coupled, requiring provisioning a new instance, restoring a snapshot from object storage to disk, and replaying WAL, leading to hours of downtime for large databases. In a survey of developers running 1TB+ production Postgres, 59% had a critical production failure and 30% were down for over three hours. Lakebase Postgres decouples compute and storage using safekeepers, a pageserver, and object storage, storing database history as an immutable timeline. A restore becomes a branch at a point in history, a metadata operation that does not copy data, making it near-instant regardless of database size. This eliminates data copies and WAL replay, so restoring a 100 TB database is as fast as restoring 10 GB, and agents can use restores as ordinary tool calls for versioning and undo.
A practical guide to cost optimization with Lakebase Postgres
Lakebase uses separated storage and compute to reduce costs: branches share storage, autoscaling matches compute to demand, and scale to zero suspends compute after inactivity. Read replicas and high availability add compute without duplicating storage. For synced tables from Lakehouse, sync only the active working set via materialized views and choose the appropriate sync mode—snapshot for volatile data, triggered for incremental, continuous for real-time—and binpack tables into one pipeline to share compute. Right-size initial compute by focusing on the working set, and set PITR retention and snapshot schedules based on actual recovery needs. Snapshot storage is priced at $0.090/GB-month and PITR storage at $0.200/GB-month, both cheaper than regular branch storage. Compute and storage usage can be tracked through system.billing.usage, with separate SKUs for compute, branch storage, PITR, and snapshots.
pg_clickhouse & chdb updates: Encoding, nesting, and types
pg_clickhouse v0.11.0 and the chdb extension v0.1.2 continue their focus on cross-database compatibility between ClickHouse and PostgreSQL with several enhancements derived from header-only C libraries. A new check_encoding server option provides error handlers for invalid byte sequences in text columns: fail, remove, replace, and truncate, with replace displaying the Unicode replacement character for UTF-8 databases. ClickHouse Interval types such as IntervalMillisecond are now imported as PostgreSQL interval columns, and expressions like start_at + duration are pushed down to ClickHouse. Unflattened Nested columns are mapped to two-dimensional text arrays by default, or to arrays of composite types when a structure is specified; parameterized JSON columns are mapped to jsonb with proper ORDER BY pushdown. Additional type mappings include Int128 and UInt256 to numeric, BFloat16 to float4, and spatial types; the deprecated clickhouse_raw_query function was removed, and PostgreSQL 13 support was dropped.
Memory safety for Postgres extensions in C/C++
Postgres MemoryContext allocation combined with setjmp/longjmp error handling bypasses C++ destructors, leading to undefined behavior when C++ code runs inside a Postgres extension. C++ exceptions also bypass PG_CATCH, and an uncaught exception in a background worker aborts the process, forcing the postmaster to restart everything due to potential shared memory corruption. ClickHouse’s four Postgres extensions each used a different approach: pg_clickhouse replaced clickhouse-cpp with a new C library, pg_re2 scoped C++ to a single file, pg_chdb isolated libchdb in a helper executable, and pg_stat_ch uses a terminate handler. pg_chdb’s helper process runs outside the Postgres backend, so a libchdb crash fails only the calling operation without triggering cluster crash recovery. A long-term plan for pg_stat_ch moves its C++ dependencies into a helper executable, with PR #129 as the first step to isolate C++ from Postgres code.
Lakebase Search: State-of-the-art full text and vector search for Postgres
Traditional OLTP systems like Postgres face challenges meeting the low-latency, high-accuracy search demands of AI agents, often requiring standalone search engines with ETL pipelines. Lakebase introduces lakebase_vector and lakebase_text, two extensions that bring scalable approximate nearest neighbor search and BM25 full-text search directly into Lakebase Postgres. lakebase_vector overcomes pgvector’s memory-bound HNSW index by using hierarchical IVF clustering and binary quantization, achieving 71-millisecond P99 latency at 97 percent recall on the 100-million-vector LAION benchmark. The extensions decouple storage from compute for pay-per-use scaling, enable fast parallel index builds, and support native hybrid search that fuses vector similarity with keyword relevance in a single SQL query. Early adopters like Conexiom reduced infrastructure costs by three times while achieving five times higher throughput, and the service is generally available on AWS and Azure.
Can your Postgres survive a bad query?
Postgres memory tuning is difficult because work_mem applies per operation, not per query, and parallel workers multiply memory usage without a query-wide cap. Some executor structures, such as those in recursive queries, have no disk-backed fallback and can grow until the backend runs out of memory. A benchmark using a recursive query that exhausts memory tested ClickHouse Managed Postgres, Cloud SQL, PlanetScale, and Amazon RDS on 16 GiB instances. ClickHouse Managed Postgres enforces a memory ceiling at 57% of host RAM, rejecting queries that exceed it, while RDS allows backends to consume nearly all RAM until the host crashes. Cloud SQL and PlanetScale enforce similar caps, but ClickHouse Managed Postgres’s ceiling is lower, a deliberate tradeoff that returns clean query failures instead of forcing every client through crash recovery.
Postgres on NVMe: performance and the convergence of transactions and analytics
The post examines how local NVMe storage changes Postgres performance, particularly when working sets exceed memory and disk latency becomes the bottleneck. It argues that while faster storage accelerates transactional workloads, it does not alter the row-store layout, so analytical queries still require different approaches. Benchmark results show 9.2× more throughput on NVMe, with a median of 16,030 TPS versus 1,734 on EBS. The post introduces WalShadow, an open-source replication engine that reads the physical Postgres WAL to enable sub-second replication to ClickHouse without logical slots. It concludes that failover is solvable with tools like Patroni, and that storage latency is only part of the performance problem.
Related topics
All topics →Weekly digest
The best engineering reads, once a week.