Hasty Briefsbeta

All tags

postgres

17 stories total

Bilingual

Massively Parallel Postgres Backups
2 months ago
- PlanetScale automates backups for sharded Postgres and MySQL databases to minimize production impact by spinning up temporary EC2 instances per shard.
- The steady-state backup process involves restoring the previous backup from object storage (e.g., S3), replaying WAL (mostly from S3, with recent segments directly from primary), and saving the consistent, encrypted snapshot.
- Massive parallelism across shards enables high-speed backups (e.g., 32 TB spread over 8 shards backs up in ~2.8 hours; over 32 shards in 42 minutes), achieving rates over 50 GB/s and supporting petabyte-scale databases.
- Initial backups use pg_basebackup to seed nodes and wal-g for durable storage, ensuring consistency with the steady-state format.
- Backups are used not only for disaster recovery but also for database resizing (e.g., Metal databases with Local NVMe) and node replacement due to failures, leveraging restore-and-catch-up workflows.
- For MySQL (with Vitess), the process is similar but uses VTBackup and binary log replication instead of Postgres WAL.
- The ultimate goal is to make backups and resizes effortless, automated, and transparent to users.
PostgreSQL and the Linux OOM Killer: A Better Default
2 months ago
- Memory overcommit allows Linux to grant more address space than physical memory, using physical pages only on first touch; overcommit can trigger the OOM killer when touched pages exceed RAM.
- Postgres is vulnerable to OOM kills because shared memory segments (buffers, WAL, locks) are shared across backends; a SIGKILL can corrupt the segment, forcing the postmaster to restart the entire instance via crash recovery.
- Strict overcommit (vm.overcommit_memory=2) makes the kernel track committed memory and refuse allocations beyond a limit, returning ENOMEM instead of killing a process.
- Postgres handles ENOMEM as a query error: the transaction rolls back, the query fails with 'out of memory', and other connections remain unaffected.
- ClickHouse Managed Postgres uses strict overcommit with a limit set to 80% of available memory (excluding huge pages) plus 2GB, resulting in ~60% of total RAM plus 2GB.
- Experimental comparison on an m7i.2xlarge instance: under default policy, OOM killed a backend, causing 30.2 seconds of downtime and dropping all 20 bystander sessions; under strict overcommit, a single query failed with an error while 19 sessions continued and no downtime occurred.
- Performance overhead of strict overcommit is negligible: 2.2% difference in select-only throughput and 0.6% in read-write throughput, within run-to-run variation.
- The key takeaway: memory exhaustion is inevitable; the policy determines the cost—strict overcommit limits damage to a single query error, while default policy risks instance-wide restart.
Postgres LISTEN/NOTIFY actually scales
2 months ago
- LISTEN/NOTIFY has a reputation for not scaling due to a global lock, but it can be optimized for high throughput.
- Basic streaming design uses a streams table with LISTEN/NOTIFY for low-latency notification instead of polling.
- Initial implementation with trigger on NOTIFY bottlenecked at 2.9K writes/sec because of an exclusive lock during commit.
- The exclusive lock serializes commits of transactions with NOTIFY, preventing group commit and causing poor performance.
- Optimization buffers notifications in memory and flushes them in batches, reducing lock contention.
- A fallback polling mechanism is added to handle potential loss of notifications from process crashes.
- Benchmark shows 60K writes/second on a single Postgres server with 15-100ms latency, a 20x improvement.
- All benchmark code is available on GitHub, and DBOS offers tools for durable execution with Postgres.
TQuel Paper: Implementing Temporal Operators in Postgres
2 months ago
- The paper 'An Overview of TQuel' from 1993 explores temporal relational algebra, extending Quel for Ingres, and provides algebraic identities for temporal operators useful for query planning in Postgres.
- TQuel represents valid-time as sets of chronons (multiranges) per attribute rather than intervals per tuple, which complicates but is representable in Postgres via multiranges.
- Snapshot reducibility allows temporal operators to be evaluated by first taking snapshots of base tables, enabling efficient query optimization for queries focused on a single time point.
- Theorem 13 shows that Cartesian product does not distribute over difference in temporal algebra, as demonstrated by counterexamples using TQuel, rangetypes, and multiranges.
- Retaining input valid-times in temporal operators breaks certain algebraic identities, while dropping them preserves the algebra, suggesting valid-time should be treated as a qualifier, not a regular attribute.
- The author developed a Racket library 'relsim' to simulate temporal operators and find counterexamples, highlighting the need for dedicated executor nodes and optimizer transformations in Postgres.
The startup's Postgres survival guide
2 months 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.
Let's break autovacuum in Postgres: reproducing failures to make it observable
2 months 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 instant database scratchpad: xata scratch
2 months 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.
Just Use Postgres for Job Queues
2 months 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.
We're Building Postgres in Rust. Using the LLVM of Databases
2 months 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.
Postgres locks do not scale
2 months 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.
Multigres Supports Listen/Notify Across Pooled Connections
2 months 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.
Why we built yet another Postgres connection pooler
2 months 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.
Do you really need separate systems when you already have Postgres?
2 months 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.
Looking Forward to Postgres 19: Split Personality
2 months 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.
Understanding Postgres 19 Property Graphs
2 months 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.
Postgres transactions are a distributed systems superpower
3 months 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.
Database Traffic Control
3 months 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.

firstprev0next

postgres

Massively Parallel Postgres Backups

PostgreSQL and the Linux OOM Killer: A Better Default

Postgres LISTEN/NOTIFY actually scales

TQuel Paper: Implementing Temporal Operators in Postgres

The startup's Postgres survival guide

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

The instant database scratchpad: xata scratch

Just Use Postgres for Job Queues

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

Postgres locks do not scale

Multigres Supports Listen/Notify Across Pooled Connections

Why we built yet another Postgres connection pooler

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

Looking Forward to Postgres 19: Split Personality

Understanding Postgres 19 Property Graphs

Postgres transactions are a distributed systems superpower

Database Traffic Control