Bilingual

Reading the internals of Postgres: Database cluster, databases, and tables
22 days 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.
- Tables and indexes are stored in files under base/{database_oid}/{relfilenode}, with forks for free space and visibility maps.
- VACUUM FULL can change a table's relfilenode, decoupling it from its OID.
- Tablespaces allow storing database objects in custom directories, with symlinks in $PGDATA/pg_tblspc/.
- Heap tables consist of 8KB pages containing headers, line pointers, and tuples stored from the page end.
- TOAST handles large values by storing them externally in a separate table, chunked and indexed.
- Writing tuples involves updating page headers and line pointers, with alignment considerations.
- Reading tuples can use sequential scans or index scans (e.g., B-tree), depending on the query.
Looking Ahead to Postgres 19
21 days 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.
- Autovacuum improvements include parallel workers, a scoring system for prioritization, and enhanced monitoring via new views and logs.
- SQL property graph queries (SQL/PGQ) enable graph-style queries on relational data without requiring a separate database.
- COPY command upgrades include skipping multiple header lines, ON_ERROR SET_NULL option, and JSON output for easier data workflows.
- SQL quality-of-life features include GROUP BY ALL, IGNORE NULLS for window functions, and improved upsert behavior with RETURNING.
- Performance enhancements cover planner optimizations, anti-joins, SIMD usage in COPY, and faster foreign key checks.
- The release emphasizes breadth, making Postgres better for multiple personas and workloads, encouraging testing before GA.
Commodore 64 Basic for PostgreSQL
18 days 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.
- Device 8 is mapped to the database, allowing use of OPEN, INPUT#, PRINT#, etc., to execute SQL statements and read results.
- Errors are trapped through the interpreter's ERROR vector, providing PostgreSQL errors with original BASIC error codes.
- Performance benchmarks show it's slower than PL/Python but faster than the original hardware, with a 15-microsecond reset per call.
- Limitations include uppercase-only strings, 255-character string limits, no NULL support, and superuser-only untrusted language status.
PostgreSQL and the OOM Killer: Why You Must Use Strict Memory Overcommit
18 days 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.
- Ubicloud's heuristic for setting the commit limit is: 80% of physical memory (excluding hugepages) plus a fixed 2 GB buffer for sidecar processes.
- Monitoring memory usage and understanding workload characteristics is essential before enabling strict overcommit to avoid frequent ENOMEM errors.
Tests Are SQL Files Too
17 days 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.
- Watch mode provides continuous validation and re-runs tests on save, improving development experience.
- AI TDD is enabled by fast, isolated tests with truthful feedback loops for AI agents.
Show HN: PostgreSQL performance and cost across 23 EC2 instance types
14 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.
Preempt_none Is Dead; Your Postgres Probably Doesn't Care
15 days 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.
- Cloud database users rely on vendors to handle kernel and huge page configurations, with no action required on their part.
- The kernel community suggested using rseq for scheduler hints, but this is not a practical immediate fix; PostgreSQL may adopt it in future versions.
- The benchmark artifact highlights the importance of correct configuration (huge pages) for large shared memory workloads, not a fundamental Linux issue.
The four horsemen behind Postgres outages
11 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.
PgDog (YC P25) Is Hiring a Founding Software Engineer
8 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.
- The work involves building a distributed database from scratch in Rust, with time split among bug fixes, new features, and infrastructure.
Why Huge Pages matter for Postgres
6 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.
- Tests on an EC2 instance showed that with 200 connections, 4KB pages led to 6.12GB of page tables versus only 111MB with 2MB huge pages, and throughput increased by 12% with huge pages due to reduced translation overhead.
Why It's So Hard to Add a Column in the Middle of a PostgreSQL Table
a day 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.

Hasty Briefsbeta

#postgresql

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

Looking Ahead to Postgres 19

Commodore 64 Basic for PostgreSQL

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

Tests Are SQL Files Too

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

Preempt_none Is Dead; Your Postgres Probably Doesn't Care

The four horsemen behind Postgres outages

PgDog (YC P25) Is Hiring a Founding Software Engineer

Why Huge Pages matter for Postgres

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