Hasty Briefsbeta

All tags

#postgresql

112 stories total

Bilingual

Database Linting and Analysis for PostgreSQL
7 months ago
- PG Linter is a PostgreSQL extension designed for developers and operations teams to detect database design issues and best practice violations.
- It follows a rule-based approach, allowing users to enable/disable rules and configure thresholds based on organizational standards.
- Key features include performance analysis, schema validation, security auditing, and SARIF output for CI/CD integration.
- Rules are categorized into Base Rules (B-series), Cluster Rules (C-series), Table Rules (T-series), and Schema Rules (S-series).
- Installation is simple with `CREATE EXTENSION pglinter;` and analysis can be run with `SELECT pglinter.perform_base_check();`.
- The tool integrates into CI/CD pipelines, database migrations, monitoring, and code reviews for seamless workflow incorporation.
JIT: So you want to be faster than an interpreter on modern CPUs
7 months ago
- The blog discusses the challenges of optimizing a JIT compiler for PostgreSQL, focusing on performance improvements over the interpreter.
- Modern CPUs with out-of-order execution and branch prediction complicate performance optimization efforts.
- The interpreter's main loop with opcode dispatch can be inefficient due to unpredictable branching, leading to performance losses.
- Using 'computed gotos' instead of a switch statement can improve interpreter performance by making jumps more predictable.
- Optimizing a simple query like 'SELECT a FROM table WHERE a = 42' involves reducing unnecessary null checks and inlining common operations.
- Inlining the int4eq function call significantly reduces instruction count and improves performance.
- The author explores various optimizations, including removing null checks and inlining, showing measurable performance gains.
- Despite CPU optimizations, the interpreter remains a tough competitor, but the JIT compiler shows promise with further improvements.
- The author seeks collaboration and support for the project, including code contributions, sponsoring, or job offers.
Exploring PostgreSQL 18's new UUIDv7 support
7 months ago
- UUIDv7 is a new type of UUID introduced in PostgreSQL 18, incorporating a timestamp for natural sortability.
- UUIDv7 improves performance over UUIDv4 by reducing index fragmentation and improving cache efficiency.
- UUIDv7 is supported across various platforms, including Python 3.14, ensuring consistent format usage.
- UUIDv7 is not recommended for external-facing applications due to privacy concerns from timestamp leakage.
- Migration from UUIDv4 to UUIDv7 requires careful planning, including testing and updating dependent systems.
Derek Sivers's database and web apps
7 months ago
- Derek Sivers maintains a private database with public code, mirrored on GitHub, GitLab, and Codeberg.
- The database setup includes circular references in tables.sql, with functions organized in schemas for easy reloading.
- Testing is done using pgTAP on a clone of the live database, with minimal data for focused testing.
- HTML templates are stored in the database, not on disk, for easy editing and use.
- PostgreSQL functions return two values: head text for HTTP headers and body text for the content.
- Different directories correspond to different web apps, each with its own set of functions and tests.
- Derek encourages discussions about programming and responds to every email.
Oracle has adopted BOOLEAN in 23ai and PostgreSQL had it forever
6 months ago
- Oracle introduced support for the Boolean data type in release 23ai, addressing a long-standing limitation.
- Previously, developers used workarounds like VARCHAR2/NUMBER to simulate Boolean values, impacting performance and storage efficiency.
- PostgreSQL has supported Boolean data natively for over two decades, offering efficient storage and simpler queries.
- Workarounds in Oracle included using CHAR(1) or NUMBER(1) with checks, leading to redundant conversions and conditions.
- PostgreSQL allows direct logical operations with Boolean values, resulting in cleaner and more performant queries.
- A comparison table highlights the advantages of Boolean over CHAR(1) and NUMBER(1) in terms of storage, type safety, and readability.
- HexaRocket, a migration tool, can automatically map Oracle's CHAR(1)/NUMBER(1) to PostgreSQL's Boolean during database migration.
- The article promotes HexaRocket for seamless migration from Oracle or SQL Server to PostgreSQL.
Matrix: Post-mortem of the September 2 outage
6 months ago
- Matrix.org homeserver experienced a 24-hour outage due to a failed database during routine maintenance.
- Attempts to restore the primary database led to losing the secondary, requiring a lengthy restore from 51TB S3 backups.
- No data was lost, but the outage lasted from 2025-09-02 17:45 UTC to 2025-09-03 18:00 UTC.
- The incident highlighted issues with database server naming conventions, backup strategies, and recovery processes.
- Lessons learned include the need for better safeguards, improved tools, and community communication during outages.
Replacing EBS and Rethinking Postgres Storage from First Principles
6 months ago
- Fluid Storage is a new distributed block storage architecture designed for elasticity, zero-copy forks, and synchronous replication.
- It is fully compatible with PostgreSQL and other databases, offering instant forks, automatic scaling, and no downtime.
- Every database in Tiger Cloud’s free tier now runs on Fluid Storage, providing developers with these advanced capabilities.
- Fluid Storage addresses the limitations of traditional 'elastic' cloud storage like EBS, which lacks true elasticity and efficient scaling.
- The architecture consists of three layers: Distributed Block Store (DBS), storage proxies, and a user-space storage device driver.
- Key features include fork-first design, true elasticity, usage-based billing, predictable performance, and Postgres compatibility.
- Benchmarks show Fluid Storage sustains 110,000+ IOPS and 1.4 GB/s throughput while maintaining copy-on-write and elasticity guarantees.
- Fluid Storage is built for agentic workloads, enabling autonomous systems to spin up, scale, and tear down environments efficiently.
- Reliability is ensured through storage replication, database durability, compute recovery, and region-level isolation.
- Fluid Storage is available today in Tiger Cloud’s free tier, with early-access partnerships open for agentic and infrastructure platforms.
Postgres Conference 2026: Call for papers
6 months ago
- Postgres Conference San Jose 2026 will be held from April 21-23 in San Jose, United States, focusing on PostgreSQL.
- Postgres Conference Orlando 2025 was a success, featuring expanded tracks and sessions for PostgreSQL professionals.
- Keynote speaker Malika Boukhelifa will present 'Code Your Way Out of Burnout' at Postgres Conference Orlando 2025.
- Online events related to Postgres Conference will run from January 02 to December 31, 2025.
Avoid UUID Version 4 Primary Keys
6 months ago
- UUID Version 4 (random) primary keys cause poor database performance due to randomness leading to inefficient index usage.
- UUIDs consume more space (16 bytes) compared to integers (4-8 bytes), impacting storage and IO.
- Random UUIDs cause index page splits and fragmentation, increasing insert latency and write IO.
- UUID v4s are not secure and should not be used as security capabilities.
- Time-ordered UUIDs (Version 7) mitigate some issues by including a timestamp for better index performance.
- Alternatives to UUIDs include generating obfuscated codes from integers or using sequences/big integers for primary keys.
- Mitigations for UUID performance issues include rebuilding indexes, increasing memory (shared_buffers, work_mem), and clustering on orderable fields.
- Recommendation: Use integers or big integers for primary keys unless UUIDs are necessary, then prefer UUID v7.
Moving tables across PostgreSQL instances
6 months ago
- Moving specific tables between PostgreSQL instances requires logical replication, not Google's DMS which migrates entire databases.
- Grant replication access to user accounts on both source and destination PostgreSQL instances.
- Copy table schema using pg_dump, initially without constraints and indexes for efficiency.
- Logical replication operates in two modes: initial data dump and CDC (Change Data Capture) for real-time updates.
- Handle constraints and indexes separately; rebuild them after the initial data dump is complete.
- Create a publication on the source instance and a corresponding subscription on the destination to start replication.
- Monitor replication progress and lag using PostgreSQL system tables like pg_replication_slots and pg_stat_subscription.
- Manually sync sequences between source and destination instances before switching traffic.
- Use PgBouncer for near-zero downtime during the switchover by pausing, reloading, and resuming connections.
- Clean up by dropping the subscription and publication once the migration is confirmed successful.
Pg_lake: Postgres with Iceberg and data lake access
6 months ago
- pg_lake integrates Iceberg and data lake files into Postgres, enabling it to function as a standalone lakehouse system.
- Supports transactions and fast queries on Iceberg tables, and works directly with raw data files in object stores like S3.
- Allows creating and modifying Iceberg tables from PostgreSQL with transactional guarantees.
- Enables querying and importing data from Parquet, CSV, JSON, and Iceberg files stored in S3 or compatible object stores.
- Supports exporting query results back to S3 in Parquet, CSV, or JSON formats using COPY commands.
- Reads geospatial formats like GeoJSON and Shapefiles, and supports compression with .gz and .zst.
- Features a built-in map type for semi-structured or key-value data.
- Combines heap, Iceberg, and external files in the same SQL queries with full transactional guarantees.
- Infer table columns and types from external data sources like Iceberg, Parquet, JSON, and CSV files.
- Leverages DuckDB’s query engine for fast execution within Postgres.
- Two setup methods: Docker for easy testing and building from source for manual setup or development.
- Includes PostgreSQL extensions, pgduck_server application, and S3-compatible storage setup.
- pgduck_server is a standalone process using DuckDB to execute queries, accessible via psql on port 5332.
- Supports setting memory limits, init file paths, and cache directories for pgduck_server.
- Relies on DuckDB secrets manager for credentials, with support for AWS and GCP.
- Allows creating Iceberg tables with 'USING iceberg' and querying them directly.
- Supports COPY commands for importing/exporting data in Parquet, CSV, or JSON formats.
- Modular design with components like pg_lake_iceberg, pg_lake_table, pg_lake_copy, and pg_lake_engine.
- Developed by Crunchy Data, later acquired by Snowflake, and open-sourced as pg_lake in 2025.
- Dependent on third-party projects Apache Avro and DuckDB, with patches applied during build.
RegreSQL: Regression Testing for PostgreSQL Queries
6 months ago
- RegreSQL brings PostgreSQL's regression testing methodology to application queries, catching correctness bugs and performance regressions before production.
- SQL queries are often treated as 'just glue code' and not systematically tested, leading to production issues.
- RegreSQL, originally created for 'The Art of PostgreSQL', applies PostgreSQL's regression testing framework to application queries.
- The tool validates SQL queries against expected results, tracks performance baselines, and detects common query plan regressions.
- RegreSQL supports both positional arguments and psql-style variables in SQL queries.
- Performance regression testing in RegreSQL detects issues like sequential scans and nested loop joins, suggesting optimizations.
- Test data management in RegreSQL uses YAML files for declarative, reproducible, and scalable data generation.
- RegreSQL can intercept ORM-generated SQL to catch performance issues before they hit production.
- The fixture system in RegreSQL allows for stackable and dependency-aware test data setup.
- Future plans for RegreSQL include better documentation, tutorials, and integration with SQL Labs.
RRules (yes handling RSCALE) using only PL/pgSQL
6 months ago
- Pure PL/pgSQL implementation of iCalendar RRULE (RFC 5545) for PostgreSQL without C extensions.
- Key features include full timezone support with DST handling, consistent behavior across environments, and production readiness with a comprehensive test suite.
- Offers 50-75x faster performance than Node.js for single-schedule operations and supports batch processing for multiple schedules.
- Compatible with various PostgreSQL services like Google AlloyDB, Amazon Aurora, and Azure Database for PostgreSQL.
- Includes standard RRULE patterns (DAILY, WEEKLY, MONTHLY, YEARLY) and supports sub-day operations (HOURLY, MINUTELY, SECONDLY) with security warnings.
- Provides functions for generating and querying occurrences, utilities for counting and checking overlaps, and supports both TIMESTAMP and TIMESTAMPTZ.
- Installation is simple with a single SQL file and includes detailed documentation for usage, API reference, and compliance with RFC standards.
- Encourages contributions with guidelines for testing, documentation updates, and adherence to RFC specifications.
- Performance advantages include set-based batch operations, streaming results, and early-exit optimizations.
- Requires PostgreSQL 12 or higher with no external dependencies or C extensions.
How to Use UUIDv7 in Python, Django and PostgreSQL
6 months ago
- UUIDv7 provides time-ordered identifiers, improving index locality and reducing random writes compared to UUIDv4.
- Python 3.14 includes built-in support for UUIDv7 via `uuid.uuid7`, allowing client-side generation without database dependency.
- PostgreSQL 18 introduces native functions `uuidv7` and `uuid_extract_timestamp`, enabling server-side UUID generation and timestamp extraction.
- Django 5.2 integrates smoothly with UUIDv7 using `db_default` for PostgreSQL-generated UUIDs and `GeneratedField` for timestamp extraction.
- UUIDv7 is ideal for internal use but should be masked (e.g., with UUIDv47) in public APIs to avoid exposing creation timestamps.
- UUIDv7 is stable and supported in Python 3.14, Django 5.2, and PostgreSQL 18 without requiring custom fields or extensions.
PgFirstAid: PostgreSQL function for improving stability and performance
6 months ago
- pgFirstAid is an open-source PostgreSQL function for database health checks.
- Provides prioritized actions to improve database stability and performance.
- Inspired by Brent Ozar's FirstResponderKit for SQL Server.
- Zero dependencies—single SQL function with no external tools required.
- Includes 12+ built-in health checks covering critical performance issues.
- Issues ranked by severity: CRITICAL, HIGH, MEDIUM, LOW, INFO.
- Each issue comes with actionable recommendations and documentation links.
- Easy to deploy—copy and paste the function into PostgreSQL and run it.
- Designed to be lightweight and safe for production systems.
- Works with PostgreSQL 10+ and compatible databases like Amazon RDS, Aurora.
- GPLv3 licensed, made for the PostgreSQL and Open Source community.
Just Use Postgres for Everything
5 months ago
- PostgreSQL can replace multiple backend technologies like Redis, Kafka, MongoDB, and Elasticsearch, simplifying the tech stack.
- Using PostgreSQL for everything reduces operational overhead, speeds up development, and lowers cognitive load for developers.
- PostgreSQL supports various functionalities such as caching, message queuing, document storage, full-text search, and geospatial queries.
- Extensions in PostgreSQL enhance its capabilities, making it versatile for different use cases without needing additional tools.
- Simplification with PostgreSQL leads to fewer failure points, unified monitoring, and easier scaling strategies.
- PostgreSQL's approach is likened to Linux, absorbing good ideas from other databases and implementing them effectively.
- Common concerns about performance, senior developer resistance, and technical debt are addressed with practical solutions.
- The author, Stephan Schmidt, advocates for PostgreSQL based on extensive experience in simplifying tech stacks for startups and scale-ups.
Show HN: Downloadable Extensions for Postgres.app
5 months ago
- Postgres.app includes many popular extensions that can be enabled with the CREATE EXTENSION command.
- Standard extensions like plpgsql and pg_crypto are included, but PL/TCL and PL/Perl are excluded due to macOS limitations.
- Additional popular extensions are available for download and easy installation via an installer package.
- Users can request missing extensions by opening an issue in the Postgres.app GitHub repository.
- For PostgreSQL 18 or later, extensions should be installed in the 'Application Support' directory for compatibility with updates.
- Postgres.app configures search paths for extensions installed in ~/Library/Application Support/Postgres/Extensions/XX.
- Before building an extension, ensure pg_config --version matches the expected PostgreSQL version.
- Building an extension requires a compiler (Xcode or Command Line Tools) and follows standard git clone and make commands.
- For PostgreSQL 17 or earlier, extensions must be installed in the PostgreSQL installation directory without the prefix argument.
- If encountering 'Operation not permitted', grant Terminal permission in System settings and note that extensions must be reinstalled after updates.
Locks in PostgreSQL
5 months ago
- Deadlocks occur when transactions wait for each other in a circular manner, leading to infinite waits.
- PostgreSQL detects deadlocks by building a wait-for graph and checking for cycles.
- Deadlocks are resolved by aborting one of the involved transactions, usually the one initiating the check.
- Deadlocks often indicate application design flaws, such as inconsistent locking orders.
- Predicate locks in PostgreSQL track data dependencies between transactions to ensure serializable isolation.
- Predicate locks can escalate from tuple-level to page-level or relation-level to manage system resources.
- Advisory locks allow custom locking logic for resources not directly tied to database objects.
- Page-level locks are used in GIN indexes to manage pending list movements efficiently.
- Relation extension locks prevent concurrent page additions during inserts or vacuum operations.
Show HN: Diesel-guard – Lint Diesel migrations for unsafe PostgreSQL patterns
5 months ago
- Diesel Guard detects unsafe PostgreSQL migrations that can cause downtime or lock tables.
- Provides safe alternatives for operations like adding columns with defaults, dropping columns, and adding indexes non-concurrently.
- Works with any Diesel project without requiring configuration.
- Supports safety-assured blocks for manually verified operations.
- Commands include `diesel-guard check` to analyze migrations and `diesel-guard init` to create a configuration file.
- Common unsafe operations include adding columns with defaults, dropping columns, and changing column types.
- Multi-step approaches are recommended for safe migrations, such as adding columns without defaults first and backfilling data separately.
- Safety-assured comment blocks can bypass checks for verified operations.
- Contributions are welcome, with guidelines provided in CONTRIBUTING.md and AGENTS.md.
Pg_ClickHouse: A Postgres extension for querying ClickHouse
5 months ago
- ClickHouse Cloud users commonly migrate from PostgreSQL, facing challenges with query and application code migration.
- pg_clickhouse v0.1.0 released: a PostgreSQL extension to execute analytics queries on ClickHouse transparently.
- Goals include ClickHouse query execution from PostgreSQL, unmodified existing queries, and query pushdown to ClickHouse.
- Improvements over clickhouse_fdw: modernized code, added test cases, TLS support, and advanced pushdown features.
- Aggregate pushdown feature converts PostgreSQL functions like percentile_cont to ClickHouse's quantile function.
- SEMI JOIN pushdown significantly improves performance for analytical queries, as shown in TPC-H benchmark results.
- Future roadmap includes more pushdown coverage, support for all PostgreSQL functions, and DML features.

first prev3next

About|Login

#postgresql

Database Linting and Analysis for PostgreSQL

JIT: So you want to be faster than an interpreter on modern CPUs

Exploring PostgreSQL 18's new UUIDv7 support

Derek Sivers's database and web apps

Oracle has adopted BOOLEAN in 23ai and PostgreSQL had it forever

Matrix: Post-mortem of the September 2 outage

Replacing EBS and Rethinking Postgres Storage from First Principles

Postgres Conference 2026: Call for papers

Avoid UUID Version 4 Primary Keys

Moving tables across PostgreSQL instances

Pg_lake: Postgres with Iceberg and data lake access

RegreSQL: Regression Testing for PostgreSQL Queries

RRules (yes handling RSCALE) using only PL/pgSQL

How to Use UUIDv7 in Python, Django and PostgreSQL

PgFirstAid: PostgreSQL function for improving stability and performance

Just Use Postgres for Everything

Show HN: Downloadable Extensions for Postgres.app

Locks in PostgreSQL

Show HN: Diesel-guard – Lint Diesel migrations for unsafe PostgreSQL patterns

Pg_ClickHouse: A Postgres extension for querying ClickHouse