PostgreSQL news and engineering summaries

The author argues that Object-Relational Mappers (ORMs) often cause more problems than they solve due to issues like attribute creep, complex foreign key joins, and the Object-Relational Impedance Mismatch. Instead of relying on ORMs for query generation, the author advocates for mastering SQL and treating the database as an API-driven data type.

The article proposes using SQLite instead of Postgres for durable workflow execution, particularly for AI agents. By utilizing a local database file combined with Litestream for S3 backups, developers can achieve durable state management without complex infrastructure. This approach offers cost-effective, simple, and fault-isolated solutions, though Postgres remains better for high-availability requirements.

Many development teams prematurely adopt complex architectures, adding unnecessary databases like Redis, Elasticsearch, or MongoDB. Postgres is often sufficient for most use cases, offering robust features for caching, queues, search, and analytics. Prioritizing simplicity reduces operational overhead, maintenance burdens, and costs, allowing startups to focus on innovation until actual scale necessitates more specialized solutions.

PgDog is an open-source proxy project designed to make Postgres horizontally scalable, supporting over 1 million queries per second and 100 TB+ datasets. Founded by experienced infrastructure engineers backed by $5.5M in funding, it aims to eliminate scaling challenges for Postgres while providing flexible deployment options across cloud and on-prem environments.

This article explores unconventional optimization techniques for PostgreSQL to improve performance and reduce storage costs. Key strategies include using constraint_exclusion to skip unnecessary table scans, leveraging function-based indexes on virtual generated columns for lower cardinality data, and enforcing uniqueness on large values using exclusion constraints with Hash indexes.

pg_durable is a PostgreSQL extension that enables durable, fault-tolerant workflow execution directly within the database. By using a SQL-native DSL for checkpointing steps, it eliminates the need for external queues, workers, or cron jobs, allowing developers to build robust data and AI pipelines with automatic recovery from system failures and crashes.

This article challenges the 'use the right tool for the right job' mantra that often leads developers to manage multiple specialized databases (Elasticsearch, Pinecone, Redis, etc.). Such sprawl creates technical debt, complex security models, and high operational costs. Instead, the author advocates for using PostgreSQL as a unified platform. Innovations like pgvector (vector search), TimeScaleDB (time-series), and pg_textsearch (BM25 search) allow Postgres to match specialized tools' performance using similar algorithms. This simplification is especially critical in the AI era, where maintaining consistent data for agents is easier within a single, atomic environment. The post concludes that 99% of companies can achieve their goals with Postgres alone, reducing architecture fragmentation and potential failure points.

Email validation is fraught with legacy issues and misconceptions. Instead of relying on complex, unreliable regex that often breaks valid cases like international characters, subaddressing, or unique domain formats, prioritize verification. Simply send a verification email to confirm the address is functional and owned by the user, while implementing case-insensitive storage for consistency.

David Steele has announced he is stepping down as the maintainer of pgBackRest, a popular PostgreSQL backup and restore tool, citing a lack of sustainable funding. The project is now unmaintained. Future forks will require a new name. Contributor support is welcomed for potential new maintainers moving forward.

A regression in Linux 7.0 has caused PostgreSQL throughput to drop by half on Graviton4 servers due to scheduler preemption changes. Kernel developers suggest PostgreSQL should adopt Restartable Sequences (RSEQ) rather than reverting kernel changes, potentially leading to performance degradation in upcoming Linux 7.0 stable releases, including Ubuntu 26.04 LTS.

The engineering team at incident.io improved API performance by reducing P95 latency from 5s to 0.3s. They solved pagination bottlenecks for large datasets by implementing Bloom Filters in PostgreSQL and enforcing a mandatory 30-day time window for alert history, significantly optimizing query efficiency for high-volume customer data.

SQL to ER Diagram is a free, open-source browser-based tool that visualizes SQL schemas as interactive ER diagrams. It supports PostgreSQL, MySQL, SQLite, and SQL Server, processing DDL locally to ensure privacy. Users can arrange tables, add notes, and export diagrams as PNG or SVG without creating an account.

Durable workflows improve reliability by checkpointing task progress to a database. While traditional systems use complex external orchestrators, this article proposes building durable workflows directly on Postgres. By leveraging Postgres for state management, coordination, and observability, developers can reduce infrastructure complexity, improve scalability, and unify security policies without adding new single points of failure.

This comprehensive guide to PostgreSQL indexes explores their internal mechanics, storage on disk using heap files and 8KB pages, and the tradeoffs associated with their use. It details how the query planner decides between sequential and index scans and emphasizes the costs of indexing, including disk space, memory usage, and impact on write operations. The article breaks down various index types, such as B-tree, Hash, BRIN, GIN, GiST, and SP-GiST, while also covering advanced strategies like partial, covering, multi-column, and expression indexes for specialized optimization.

PgDog is an open-source, high-performance PostgreSQL proxy written in Rust. It offers advanced connection pooling, load balancing, and sharding capabilities. Featuring application-layer routing, health checks, and automatic failover, it supports complex cross-shard queries and online re-sharding. It is designed for scalability on commodity hardware and integrates with Kubernetes, AWS RDS, and Terraform.

Rails 8 introduces a significant architectural shift by removing Redis from its standard technology stack in favor of 'Solid' alternatives. By utilizing SolidQueue for job processing, SolidCache for caching, and SolidCable for real-time messaging, developers can now rely solely on relational databases like PostgreSQL, MySQL, or SQLite. This change simplifies infrastructure by reducing the operational overhead of managing separate Redis instances, clusters, and backup strategies. SolidQueue leverages modern database features such as FOR UPDATE SKIP LOCKED to ensure efficient, concurrent job handling without lock contention. While Redis remains superior for extreme low-latency requirements or massive throughput, the new 'Redis-less' approach is capable of handling millions of jobs daily, making it an ideal choice for the vast majority of web applications seeking to minimize complexity.

Database indexes speed up read operations by acting like sorted references, similar to a book index. However, they introduce overhead for writes and storage. Key optimization strategies include understanding composite indexes, using functional indexes for expressions, implementing partial indexes for filtered datasets, and leveraging covering indexes for index-only scans. Always measure performance using EXPLAIN and EXPLAIN ANALYZE.

Oracle's Orasort algorithm, which boosts database sorting speeds by comparing 8-byte segments as 64-bit integers instead of character-by-character, has entered the public domain following its 20-year patent expiration. This efficient method reduces CPU cycle consumption, now enabling open-source databases like MySQL and PostgreSQL to optimize performance and lower operational costs in cloud environments.

OpenAI has successfully scaled its PostgreSQL infrastructure to support 800 million users and millions of queries per second for ChatGPT and its API. Despite a 10x surge in load over the past year, OpenAI utilizes a single-primary architecture on Azure PostgreSQL with nearly 50 read replicas globally. The engineering team implemented extensive optimizations, including offloading read traffic to replicas, using PgBouncer for connection pooling, and employing cache locking to prevent read storms. To manage write-heavy workloads that challenge PostgreSQL's MVCC implementation, OpenAI migrated shardable data to Azure Cosmos DB. This strategy prioritizes operational stability, achieving 99.999% availability while maintaining low latency across distributed regions. Future plans include cascading replication and further migration of write-heavy workloads to specialized sharded systems.

This insightful article explores the inner workings of Oban-py, a Python implementation of the renowned Elixir background job processing library. The author details how Oban-py leverages PostgreSQL features like FOR UPDATE SKIP LOCKED for concurrency, LISTEN/NOTIFY for real-time signaling, and UPSERT mechanisms for leader election. By centralizing coordination within the database, it eliminates the need for external brokers like Redis. The summary distinguishes between the Open Source version, which utilizes single-threaded asyncio, and the Pro version, which offers true parallelism, bulk operations, and sophisticated rescue heartbeats. Ultimately, it highlights the library's clean software architecture and its effectiveness as a reliable, database-backed solution for managing complex background tasks and job lifecycles in Python environments.