Bilingual

Database Traffic Control
22 days ago
- Postgres lacks built-in traffic management, making it vulnerable to performance degradation or crashes during query spikes.
- PlanetScale introduces Database Traffic Control™, enabling real-time resource budgeting and limits for queries via rules like query patterns, application names, users, and custom tags.
- Traffic Control allows setting limits on CPU, concurrency, and query timing, with modes for warning or enforcement to throttle or block excessive queries.
- Use cases include incident response for rogue queries, priority-based traffic shaping, isolating AI agent traffic, and prioritizing paid tiers in multi-tenant apps.
- Available via PlanetScale dashboard, API, and CLI, it supports automated budget creation and sqlcommenter tags for enhanced traffic categorization.
Postgres transactions are a distributed systems superpower
20 days ago
- Workflows and application data can be co-located in the same Postgres database, enabling updates in the same transaction to simplify fault tolerance.
- Co-location allows transactional steps to achieve exactly-once execution semantics by combining database updates and checkpoints in a single transaction, eliminating the need for external idempotency logic.
- A transactional workflow outbox pattern can be implemented within the same database using user-defined functions to ensure atomic updates and notifications, reducing operational complexity compared to separate systems.
- DBOS provides tools for building scalable, reliable systems with Postgres-backed durable execution.
Understanding Postgres 19 Property Graphs
17 days ago
- Postgres 19 Property Graphs allow declaring existing tables as nodes and edges without moving data, with queries compiled to relational joins.
- A property graph consists of vertices and edges, defined via CREATE PROPERTY GRAPH as an overlay on foreign-key structures, queried using MATCH patterns.
- Dimension tables map to vertices, pure join tables to edges, while fact tables with multiple foreign keys often become hub vertices with separate edge tables.
- Key columns are not automatically queryable properties; they must be listed in PROPERTIES. Variable-length paths are not supported, requiring recursive CTEs for deep traversals.
- A single table can serve as both a vertex and edge simultaneously by aliasing it in VERTEX TABLES and EDGE TABLES, avoiding duplicate storage.
- Property graphs excel at fixed-shape pattern matching (e.g., finding connected entities) but lack support for variable-length paths or graph algorithms like PageRank.
- The feature integrates with Postgres' optimizer and tools (e.g., \dG for introspection), making it suitable for graph-shaped queries on existing schemas without external graph databases.
Looking Forward to Postgres 19: Split Personality
17 days ago
- Postgres 19 introduces new SPLIT PARTITION and MERGE PARTITIONS syntax for reorganizing partitions, simplifying what previously required multiple manual steps.
- SPLIT PARTITION allows dividing a partition into smaller ones with a single command, moving rows automatically to the correct new partitions.
- MERGE PARTITIONS combines adjacent partitions into one, reducing overhead and maintaining data integrity without manual migration.
- These operations support default partitions, enabling easy redistribution of data from a catch-all default into specific partitions.
- Both SPLIT and MERGE require partitions to be adjacent without gaps, with Postgres providing clear error messages for violations.
- During split or merge, local indexes and constraints defined on individual partitions are lost, while those inherited from the parent are recreated.
- The commands take an ACCESS EXCLUSIVE lock on the entire partitioned table, blocking reads and writes until completion, with no CONCURRENTLY variant yet.
- Split and merge are available for RANGE and LIST partitioning but not for HASH partitioning, which may be a limitation for some users.
- These features address long-standing requests but should be used during maintenance windows due to locking, pending future improvements like concurrent operations.
Do you really need separate systems when you already have Postgres?
16 days ago
- Postgres can often replace separate systems like Redis, Elasticsearch, MongoDB, and others for caching, search, queues, and more, reducing operational overhead.
- Running multiple databases and microservices leads to increased maintenance, monitoring complexity, costs, and debugging challenges, often prematurely.
- Most projects don't reach 'webscale' and can rely on 'boring' tech like Postgres, as used by large companies such as Notion and Netflix.
- Postgres offers built-in or extension-based solutions for caching (UNLOGGED tables), job queues (SKIP LOCKED), full-text search (tsvector), document storage (JSONB), vector search (pgvector), and other needs.
- Specialized infrastructure should only be added after proving Postgres insufficient and accepting the operational costs, to avoid unnecessary complexity.
Why we built yet another Postgres connection pooler
15 days ago
- PgDog is a Postgres proxy designed to scale databases by handling connection pooling more effectively.
- Unlike other poolers such as PgBouncer, it allows clients to keep using SET commands and LISTEN/NOTIFY without requiring code changes.
- It uses a built-in SQL parser to manage connection state and supports multithreading via Tokio for better performance and handling of traffic bursts.
- The tool has been in production for over a year, is open source, and aims to eliminate trade-offs associated with traditional poolers.
Multigres Supports Listen/Notify Across Pooled Connections
14 days ago
- Multigres solves the scaling problem of Postgres LISTEN/NOTIFY by maintaining a shared listener connection that handles subscriptions on behalf of all clients, avoiding per-client session dependencies.
- The pooler uses a single, reserved backend connection for listening, with refcounting for channels to issue one LISTEN/UNLISTEN per channel regardless of client count, ensuring efficient resource use.
- Notifications are delivered asynchronously through a two-level fan-out: from the pooler to gateways via gRPC streams, then to client connections with bounded queues to prevent blocking and match Postgres's best-effort delivery.
- Multigres ensures transactional behavior for LISTEN/UNLISTEN by buffering actions and applying net changes only at commit time, and closes race conditions with readiness handshakes to guarantee no lost notifications.
- The system handles edge cases like reconnection by replaying active channels, matches Postgres quirks such as channel name truncation and quoting, and plans future improvements like cross-shard NOTIFY and stream consolidation.
Postgres locks do not scale
13 days ago
- Database CPU spiked to 100% system time, causing service outages, with no obvious cause like new deploys or AWS incidents.
- Attempted database reboot via RDS failed for an hour, leading to alternative mitigation by blocking instance connections via security groups.
- After connections drained, database recovered but faced high user CPU time; resolved by identifying and removing expensive queries.
- Root cause was a lock convoy: 15k processes waiting on a GIN index extension due to synchronized bot events at peak times, triggering deadlock checks and log explosions.
- Key lessons include monitoring wait queues, isolating GIN indexes, and designing for bursty workloads to prevent synchronized operations from causing system failures.
We're Building Postgres in Rust. Using the LLVM of Databases
6 days ago
- Turso is becoming the LLVM of databases, with SQLite as the first frontend and Postgres as the next.
- Turso is a modern rewrite of SQLite in Rust, featuring concurrent writes via MVCC, a rich type system, and self-updating materialized views.
- The project aims to build a Postgres-compatible database with a modern architecture, including embedded browser support and file-based operation.
- Turso uses a bytecode language (VDBE) and has demonstrated running Doom via this bytecode in a browser.
- The approach involves compiling Postgres SQL to Turso's bytecode, merging pgmicro into Turso as an official project.
- Turso is open-source with over 260 contributors, emphasizing reliability through rigorous testing methods.
- The FAQ addresses compatibility, server support, AI contributions, development pace, and extension handling via WASM.
Just Use Postgres for Job Queues
6 days ago
- Scaling Postgres-backed job queues is challenging but possible with optimizations.
- SKIP LOCKED helps solve contention by allowing workers to lock and skip already locked rows.
- Adjusting transaction isolation levels from REPEATABLE READ to READ COMMITTED reduces serialization failures.
- Optimizing indexes, such as using selective and partial indexes, cuts CPU usage and improves performance.
The instant database scratchpad: xata scratch
5 days ago
- Xata scratch provides a disposable copy of Postgres from the terminal, allowing one-off queries on a forked branch with real data that is automatically deleted after use.
- It supports running heavy analytical queries on production data without resource competition, testing query performance, or dropping into binaries like psql.
- The feature relies on fast, cheap ephemeral database branches (1-2 second boot time, $1 for 1000 branches), enabling workflows previously unimaginable and improving developer experience.
Let's break autovacuum in Postgres: reproducing failures to make it observable
a day ago
- Autovacuum prevents bloat and transaction ID wraparound, but failures are silent and hard to monitor.
- Using raw dead tuple count is misleading; compute 'autovacuum pressure' (dead tuples / threshold) for a normalized signal.
- Coroot detects problems by combining pressure >2x and at least 512MiB of dead data, then identifies root cause via system views.
- Five common failure modes: autovacuum disabled on table, long transaction holding horizon, replication slot pinning horizon, all workers busy, vacuum throttled.
- Each failure has distinct detection fingerprints: reloptions, xmin age by holder, worker count, wait event.
- The tool provides specific findings with table name, cause, and fix, reducing guessing.
- The approach uses existing Postgres system views (pg_stat_user_tables, pg_stat_activity, pg_stat_progress_vacuum, etc.) with smart aggregation.
The startup's Postgres survival guide
8 hours ago
- Start with good schema design using identity columns, primary keys, timestamptz, and foreign keys with cascading deletes for low-volume tables.
- Write performant queries by using indexes (primary keys, unique constraints) to avoid sequential scans; use compound indexes with ORDER BY columns last.
- Keep transactions short, lock only necessary rows, and use CREATE INDEX CONCURRENTLY to avoid blocking writes.
- Use connection pooling (pgbouncer or in-memory pools like pgxpool) to manage connections efficiently.
- Understand the query planner: use EXPLAIN ANALYZE to debug slow queries, keep table statistics updated via autovacuum.
- Batch writes to improve throughput; tune autovacuum settings to prevent dead tuple bloat and transaction ID wraparound.
- Use advanced features: FOR UPDATE SKIP LOCKED for queue/lease operations, partitioning for time-series data, and triggers for large table migrations without long transactions.

Hasty Briefsbeta

#postgres

Database Traffic Control

Postgres transactions are a distributed systems superpower

Understanding Postgres 19 Property Graphs

Looking Forward to Postgres 19: Split Personality

Do you really need separate systems when you already have Postgres?

Why we built yet another Postgres connection pooler

Multigres Supports Listen/Notify Across Pooled Connections

Postgres locks do not scale

We're Building Postgres in Rust. Using the LLVM of Databases

Just Use Postgres for Job Queues

The instant database scratchpad: xata scratch

Let's break autovacuum in Postgres: reproducing failures to make it observable

The startup's Postgres survival guide