Bilingual

Reading the internals of Postgres: Database cluster, databases, and tables
6 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
5 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
2 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
2 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
a day 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.

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