Transactional resources for data engineers

ClickHouse integrates with Postgres to unite OLTP and OLAP workloads. Tools include pgclickhouse to query ClickHouse via native Postgres SQL with query pushdowns, and chdbhook to import and export data lake files through Postgres COPY commands across cloud object storage.

Databricks Serverless Micro Apps enforce zero external egress, restricting compute to internal workspace tools like Unity Catalog and Lakebase. While blocking external scraping, this sandbox excels at internal tooling by scaling compute to zero and cutting idle costs despite a 30-second cold start.

ClickHouse Managed Postgres uses Direct I/O for backups to bypass the Linux page cache, preventing cache eviction of warm database pages during query execution. By aligning reads across striped NVMe drives, it reduced query latency impact by two-thirds and avoided cache pollution entirely.

Aurora PostgreSQL now embeds DuckDB to directly query Apache Iceberg and Parquet in S3 without ETL pipelines. Using the aurora_analytics extension, it supports foreign tables with predicate pushdown and column pruning to join operational and lake data.

Snowflake released general availability for inline stored procedures on hybrid tables, expanding transactional capabilities. Recent updates also added CHECK constraints for hybrid tables, Snowpipe Streaming support for partitioned Apache Iceberg tables, and dynamic table incremental refresh.

🎙️ Practical Data Modeling — Data modeling is fractured across five isolated camps: relational, analytics, application, ML, and knowledge. Stacking modern workloads requires bridging these silos, as uncoordinated application schemas break downstream pipelines and corrupt analytical models.

PostgreSQL 19 adds built-in online table rewrites via REPACK CONCURRENTLY, utilizing logical decoding and a temporary replication slot instead of triggers. While avoiding downtime, its cluster-wide slot holds back autovacuum dead tuple cleanup across all databases until the final swap commits.

Databricks launched Lakebase, combining serverless Postgres with open lakehouse storage to blur OLTP and OLAP. By enabling instant branching, scale-to-zero compute, and native zero-ETL access for Spark and Delta Lake, it removes brittle CDC pipelines and offers unified governance via Unity Catalog.

Change Data Capture extends beyond updating transactional databases to encompass streaming data, API calls, and write-ahead logs. The discussion explores CDC architecture, use cases, and tools like Estuary built for streaming, updating, and materializing pipeline data efficiently.

Postgres bottlenecks often stem from storage latency when working sets exceed RAM. Fast NVMe drives resolve I/O limits for transactions, autovacuum, and replication lag. However, hardware cannot bypass row-store architecture limits for analytical queries.

Adding an index can slow queries when the Postgres planner misjudges scan costs. A createdat index slowed a filtered query from 16.5ms to 241ms by discarding 495,944 rows. A composite index on (userid, created_at DESC) fixed the plan to execute in 0.067ms.

Postgres hypertable queries stalled due to excessive chunk metadata planning overhead. Cold planners walked 11,520 chunks across 188,000 catalog blocks. Increasing the chunk interval to six hours reduced catalog reads by 99%, proving chunk over-partitioning severely degrades query planning.

Data engineers face a shifting landscape where OLTP and OLAP systems collide. Key topics include using Apache Iceberg as an interoperability layer, scaling CDC pipelines to process three billion monthly updates, and rethinking application reporting architectures beyond standard query tuning.

TIN is a Postgres full-text search index extension designed for fast query execution and continuous updates. It supports BM25 scoring, boolean and phrase queries, and transaction visibility while handling complex joins and concurrent writes without requiring large memory footprints.

pgrawparse is a Rust library providing direct access to the PostgreSQL query parser. By mapping Rust structs onto PostgreSQL arena allocators rather than using Protobuf serialization, it enables AST parsing, deparsing, and traversal while operating 20-60x faster with 90% less memory.

DuckDB-Wasm now supports persistent storage using the browser's Origin Private File System (OPFS). Using an opfs:// URI pattern creates standard DuckDB and Parquet files with WAL support that persist across reloads, eliminating manual serialization to IndexedDB.

ClickHouse integrates with PostgreSQL via open-source tools like pg_clickhouse, an Apache 2.0 foreign data wrapper. By mapping ClickHouse tables into a Postgres schema, engineers keep Postgres as the operational front door while offloading analytical heavy lifting directly to ClickHouse.

PostgreSQL 19 adds pgplanadvice and pgstashadvice to lock query plans across ANALYZE runs. Advice captures join orders, methods, and scans without costs or timings, surviving stats changes. Tools like sqlfmt can extract this structure from standard EXPLAIN output for deterministic plan diffs.

Postgres DDL migrations break raw SQL seeding scripts unexpectedly. Adding columns with defaults silently masks outdated data. Positional inserts bypass column renames and fill defaults without checking, meaning the script that survives migrations least reports when it should not have.

PostgreSQL records row-level locks directly inside the tuple header's txmax field and tinfomask flags rather than shared memory. This prevents lock table exhaustion, but it turns every row lock into a physical page write.

Offline-first sync mirrors change-data-capture challenges. Reserving sequence numbers before committing rows creates race windows where cursors advance past uncommitted data. Because cursor increments assume older data is processed, missed rows are skipped forever during incremental sync.

DuckLake is an open table format combining SQL-stored metadata with Parquet data files. Instead of multiple file reads, it resolves target files via a single SQL query and processes hundreds of transactions per second using the Quack protocol.

Efficient database architecture relies on choosing between structured tables and flexible documents, alongside sound schema design. While normalization eliminates data redundancy, strategic denormalization avoids costly join operations when querying data at scale.

Choosing the right database depends on access patterns and scale. While Postgres handles typical OLTP workloads, OLAP engines like DuckDB use columnar disk layouts to cut latency on heavy aggregations across millions of rows. SQLite fits single-file apps, but lacks concurrent write scaling.

Postgres suits OLTP workloads, but analytical queries demand OLAP architectures. DuckDB optimizes aggregations through columnar storage, vectorized execution, and predicate pushdown, reading only needed columns to cut latency and disk I/O when processing millions of rows.

Neki sustained 118 million QPS across 512 Postgres shards holding 1.22 PiB. The read-only benchmark routed single-shard point selects by primary key through 480 routers, scaling linearly to 231k QPS per shard with 15.8M read IOPS and a router p99 latency of 6.06ms.

AI coding agents create competent indexes but heavily over-index write-heavy tables. In testing, piling sixteen indexes on a hot PostgreSQL table broke Heap-Only Tuple updates, doubled WAL writes, and nearly doubled update latency because models add indexes per query without considering writes.

Data engineering developments highlight Diskless Kafka architectures that separate storage from brokers. Other focal points include DuckDB 2.0 performance, executing multi-day PostgreSQL CDC backfills during writes, and optimizing Spark resource waste across the stack.

Neki introduces distributed, sharded Postgres without a modified storage engine. Routers parse queries and map tables to physical shards using a JSON data topology, while sidecars manage connection pooling. Built-in workflows automate resharding and schema migrations with zero downtime.

Postgres uses a process-per-connection architecture to isolate query execution and memory, maximizing code simplicity and crash resistance. While shared buffers handle base tables to ensure correctness, local session buffers manage temporary tables without locking overhead.