postgresql

Bilingual

PostgreSQL Autovacuum Internals and Benchmark
4 days ago
- Autovacuum is critical for PostgreSQL performance and database survival; it must be properly tuned to avoid performance degradation.
- MVCC keeps multiple row versions; vacuum reclaims dead tuples that are no longer visible to any transaction.
- The autovacuum launcher starts workers per database; workers independently vacuum eligible tables using thresholds based on dead tuples and inserts.
- Vacuum uses the visibility map to skip pages with no dead tuples, making it efficient; the buffer ring limits cache impact.
more...
It was surprisingly hard to break CloudNativePG replication
4 days ago
- The author tried to create a Postgres replica that lags behind its primary but found it kept catching up because the replica was pulling WAL from the S3 archive when streaming was cut.
- The replica's replay LSN advanced even without streaming, thanks to CloudNativePG's restore_command that fetches WAL from object storage.
- Postgres's standby recovery state machine falls back to archive when streaming fails, creating a loop that keeps the replica nearly caught up.
- CloudNativePG configures both primary_conninfo and restore_command, enabling the fallback; its wal-restore binary prefetches segments from S3.
more...
PostgreSQL's MVCC is bad. So is everyone else's
4 days ago
- PostgreSQL's MVCC stores old row versions in the table itself, leading to bloat, write amplification, and reliance on VACUUM for cleanup.
- Alternative MVCC designs (Oracle/InnoDB, SQL Server, MongoDB, LSM-based databases) relocate costs to undo logs, tempdb, cache, or compaction, with their own failure modes like snapshot-too-old errors, rollback overhead, or instance-wide stalls.
- All MVCC systems face the same fundamental tension: they must choose how to handle version storage, indexing, cleanup, and long-running transactions, and no design eliminates all costs—they merely shift them between writers, readers, background processes, or storage components.
- PostgreSQL's heap-based MVCC offers zero-cost rollback and never cancels readers, but makes garbage visible and operator-driven, contrasting with undo-based engines that impose costs on writes and historical reads.
more...
PostgreSQL as a Temporal Database
10 days ago
- PostgreSQL 18 introduces temporal primary and unique keys using WITHOUT OVERLAPS to prevent overlapping time periods.
- PostgreSQL 19 adds native temporal DML with UPDATE/DELETE ... FOR PORTION OF for surgical history modification.
- Temporal foreign keys using PERIOD clause enforce referential integrity over time, but reference actions are not yet supported.
How to Back Up PostgreSQL in Docker (Local, S3, and Plakar)
11 days ago
- PostgreSQL backups in Docker can be done using pg_dumpall with gzip, storing locally or to S3, with cron scheduling and pruning.
- Traditional daily full SQL dumps are inefficient because even small changes require transferring the entire database each day, leading to high storage and transfer costs.
- Plakar offers deduplication by storing chunks across snapshots, drastically reducing transfer and storage (e.g., ~64 GB/year vs ~12.5 TB for a growing 10 GB DB).
- Setting up Plakar involves creating a repository (local or S3), configuring aliases and a passphrase, and using scripts for backup and restore.
more...
Why It's So Hard to Add a Column in the Middle of a PostgreSQL Table
13 days ago
- PostgreSQL's ADD COLUMN only appends new columns to the end, with no 'AFTER' clause, unlike MySQL. This is due to the attnum system catalog field, which assigns a unique identifier to each column upon creation and never changes. attnum serves three roles: as a column's identity, its physical position in disk tuples, and its logical order in queries, making reordering complex. Dropped columns leave tombstones with their attnum slots unreclaimed, leading to unused space until table rewrite. Column order impacts storage efficiency due to data type alignment padding; 'column tetris' optimization can save up to ~20% disk space by placing wider columns first. PostgreSQL developers have proposed splitting attnum into separate fields for identity, physical, and logical positions since 2014, but implementation is stalled due to widespread code dependencies. Currently, rebuilding the table with an ACCESS EXCLUSIVE lock is the only way to reorder columns for storage gains. MySQL handles column reordering easily because it separates logical and physical column ordering, allowing instant ADD COLUMN without table rewrites in recent versions.
Why Huge Pages matter for Postgres
18 days ago
- Huge pages are large memory units (2MB or 1GB) that allow one page-table entry to cover much more memory compared to standard 4KB pages, reducing the number of translations the CPU's TLB must cache.
- Postgres is sensitive to page size because its shared_buffers cache is mapped by each backend's page tables; with 4KB pages, large caches can consume excessive RAM for page tables and cause TLB misses, slowing reads.
- Using huge pages (e.g., 2MB) dramatically reduces page-table memory usage per connection—from ~31MB to ~0.5MB per connection in tests—and improves TLB coverage, keeping the hot working set in cache and boosting throughput.
- ClickHouse Managed Postgres ensures huge pages by reserving memory early (25% of RAM), configuring huge_pages = 'on' to prevent silent fallback, and sizing shared_buffers to fit the reserved pool via measurement rather than assumption.
more...
PgDog (YC P25) Is Hiring a Founding Software Engineer
19 days ago
- PgDog is a startup focused on scaling PostgreSQL, used by many companies and currently hiring.
- The role involves working with Rust, Postgres, infrastructure, and distributed systems to build and ship features directly to customers.
- Ideal candidates are detail-oriented, test-driven, comfortable with production code, and balance AI use with independent thinking.
- Responsibilities include bug fixes, feature development, roadmap design, customer interaction, and recruitment.
more...
The four horsemen behind Postgres outages
23 days ago
- Common PostgreSQL outages are caused by VACUUM and transaction ID wraparound, where deleted rows accumulate and 32-bit IDs lead to database shutdown if not reclaimed.
- Connection limits and process-based parallelism create scaling issues, requiring restarts or tools like PgBouncer, while a thread-based model in pgrust offers a safer alternative.
- Bad query plans can drastically slow queries, with limited control over the planner, unlike databases that allow hints; pgrust aims to build an adaptive query planner.
- JSON in PostgreSQL lacks statistics and compression, leading to inefficient query plans and storage; pgrust plans to add real statistics and compression to address this.
Preempt_none Is Dead; Your Postgres Probably Doesn't Care
a month ago
- AWS benchmark showed PostgreSQL throughput dropping to 0.51x on Linux 7.0 vs 6.x due to a regression from removing PREEMPT_NONE preemption mode.
- The performance issue is caused by minor page faults inside a spinlock (buffer_strategy_lock) when using 4KB pages with large shared memory (100+ GB), exacerbated by PREEMPT_LAZY.
- Enabling huge pages (huge_pages=on) eliminates the regression by reducing page faults and TLB pressure, making the kernel upgrade safe for most users.
- Self-hosted servers should configure huge pages properly; containerized setups need host-level huge page reservations or reduced shared_buffers.
more...
Show HN: PostgreSQL performance and cost across 23 EC2 instance types
25 days ago
- Choose an AWS instance based on dataset size and throughput needs; select the cheapest option meeting requirements from benchmarked instance-disk combinations.
- The workload for this benchmark is 90% read and 10% write, with a concurrency of 32, using PostgreSQL 17 in the us-east-1 region with on-demand pricing.
- Performance metrics include transactions per second (TPS), latency, and cost per hour; instances like c8i.large and m8g.large offer high TPS at lower costs, while t-series instances are cheaper but less performant.
Tests Are SQL Files Too
a month ago
- NpgsqlRest 3.12.0 introduced SQL files as REST endpoints, eliminating deployment and migration needs.
- NpgsqlRest 3.19.0 enhances this with named parameters, watch mode for real-time validation, and a native test runner.
- Test runner uses SQL files with embedded HTTP comments for in-process endpoint testing, with automatic rollback for isolation.
- PostgreSQL features like transactional DDL, deferrable constraints, and cheap database creation make database testing feasible.
more...
PostgreSQL and the OOM Killer: Why You Must Use Strict Memory Overcommit
a month ago
- Strict memory overcommit is used to protect PostgreSQL databases from catastrophic OOM kills by converting destructive failures into graceful errors.
- PostgreSQL's architecture means an OOM kill can corrupt shared memory, leading to connection drops, transaction aborts, and lengthy crash recovery.
- A kernel bug (introduced in Linux 6.5) caused phantom committed memory inflation, leading to unexpected ENOMEM errors under strict overcommit.
- The bug was fixed in Linux 6.8 by correcting a condition in move_vma() that reversed error handling for memory accounting.
more...
Commodore 64 Basic for PostgreSQL
a month ago
- PL/CBMBASIC allows PostgreSQL to run functions written in Commodore 64 BASIC V2, using the actual 1982 interpreter compiled into C.
- It features line numbers starting at 10, mandatory for code, with function arguments injected as BASIC variables in lines 0-9.
- Includes a validator that enforces C64 BASIC naming rules, preventing variables with embedded keywords (e.g., 'TOTAL' contains 'TO').
- OUT parameters are handled by reading BASIC's variable table from emulated RAM after program execution.
more...
Looking Ahead to Postgres 19
a month ago
- Postgres 19 includes a variety of features across application development, operations, performance, and extensibility.
- REPACK CONCURRENTLY is a built-in command for reclaiming table bloat without locks, improving quality of life for production databases.
- Partitioning enhancements allow merging and splitting partitions, providing flexibility to adapt to changing data workloads.
- Logical replication now supports sequence synchronization, EXCEPT clause for publications, and better configuration with effective_wal_level.
more...
Reading the internals of Postgres: Database cluster, databases, and tables
a month ago
- PostgreSQL uses a 'database cluster' to refer to a single instance managing multiple databases, not multiple servers.
- Databases and other objects are identified by OIDs (unsigned integers), with user-created objects starting from OID 16384.
- System catalogs like pg_class and pg_database store metadata and are regular tables that can be queried.
- The physical structure centers on the data directory ($PGDATA), containing subdirectories for each database (named by OID) and tables.
more...

postgresql

PostgreSQL Autovacuum Internals and Benchmark

It was surprisingly hard to break CloudNativePG replication

PostgreSQL's MVCC is bad. So is everyone else's

PostgreSQL as a Temporal Database

How to Back Up PostgreSQL in Docker (Local, S3, and Plakar)

Why It's So Hard to Add a Column in the Middle of a PostgreSQL Table

Why Huge Pages matter for Postgres

PgDog (YC P25) Is Hiring a Founding Software Engineer

The four horsemen behind Postgres outages

Preempt_none Is Dead; Your Postgres Probably Doesn't Care

Show HN: PostgreSQL performance and cost across 23 EC2 instance types

Tests Are SQL Files Too

PostgreSQL and the OOM Killer: Why You Must Use Strict Memory Overcommit

Commodore 64 Basic for PostgreSQL

Looking Ahead to Postgres 19

Reading the internals of Postgres: Database cluster, databases, and tables