#postgresql

Schema_ferry is a tool for continuous schema migration from MySQL to PostgreSQL. Unlike existing single-shot migration tools like pgloader, it handles ongoing schema changes by parsing MySQL schemas via Rails ActiveRecord, applying customizable migration rules with a user-defined DSL, and using ridgepole to generate and apply only the necessary DDL to PostgreSQL.

Multigres now supports PostgreSQL's LISTEN/NOTIFY feature across pooled database connections. This allows applications using connection pooling to receive real-time notifications without losing events, solving a longstanding compatibility issue between pooling and asynchronous notifications.

This first part of a two-part series explains the theoretical foundation of B-tree indexes in PostgreSQL, covering their structure, how they store and organize data for efficient lookups, and the mechanics of search operations within the index.

PgBouncer is a lightweight connection pooler for PostgreSQL that reduces overhead by reusing connections. It works by maintaining a pool of database connections and distributing them to clients as needed, supporting session, transaction, and statement pooling modes to improve performance and scalability.

The article explains why PostgreSQL systems must use strict memory overcommit settings to prevent the Linux OOM Killer from terminating the database process. It details how memory overcommit allows PostgreSQL to allocate more memory than available, leading to out-of-memory conditions, and recommends setting vm.overcommit_memory to 2 to avoid such crashes.

A pull request for pgvector claims to achieve 10x smaller vector indexes, significantly reducing storage requirements for vector similarity search in PostgreSQL.

A developer has created a PostgreSQL extension that loads Commodore 64 BASIC, allowing users to write stored procedures and functions using the retro BASIC v2.0 language directly within the database.

ClickHouse Cloud's managed Postgres service uses a custom PgBouncer sharding approach to overcome single-CPU-core bottlenecks, enabling the connection pooler to scale horizontally across multiple cores for higher throughput and reduced latency.

A new pull request for pgvector introduces TurboQuant, a quantization technique that can reduce PostgreSQL vector index size by up to 10x at the 100 million row scale, aiming to improve storage efficiency for large-scale vector search.

Sorting in Postgres at scale requires careful optimization beyond basic ORDER BY. Key strategies include using indexes properly, reducing sort memory consumption, and leveraging techniques like partial sorting or external sorting to handle large datasets efficiently without degrading performance.

ClickHouse Managed Postgres has scaled PgBouncer, the PostgreSQL connection pooler, to improve connection management and performance for its users. The scaling effort enhances the handling of high volumes of database connections, reducing overhead and improving reliability. This update aims to optimize resource usage in ClickHouse's managed PostgreSQL service.

PL/PHP is a procedural language handler for PostgreSQL that enables users to write database functions using the PHP programming language.

Pglayers provides PostgreSQL extensions as reusable, stackable Docker layers, simplifying extension management in containerized environments.

ClickHouse's managed Postgres service implemented SO_REUSEPORT and socket peering to scale PgBouncer across all CPU cores, eliminating connection bottlenecks and improving performance under high load.

Snowflake is offering the PostgreSQL 19 Beta to allow developers to test new features before general availability. This early release includes over 270 commits and enhancements in areas like performance, observability, and SQL/JSON functionality.

The article explains that having an excessive number of tables in a PostgreSQL database can lead to performance issues, increased maintenance complexity, and slower query planning due to catalog lookups and statistics overhead. It recommends keeping the number of tables manageable by using partitioning, schema organization, or consolidation strategies.

PostgreSQL alone can serve as a comprehensive backend for many applications, replacing tools like Redis, Kafka, and Elasticsearch. Its built-in features such as NOTIFY/LISTEN, full-text search, and partitioning reduce architectural complexity. The author recommends favoring PostgreSQL's integrated capabilities before adding external dependencies.

Gilliom Frontline Digital offers a PostgreSQL Management Platform designed to simplify database administration, monitoring, and performance optimization for enterprises.

Microsoft has introduced DocumentDB, a new NoSQL database layer built on PostgreSQL. It provides document-oriented database capabilities like flexible schemas and native JSON support while leveraging PostgreSQL's reliability and ecosystem. The service is designed for workloads needing both NoSQL flexibility and relational database consistency.

The article compares PostgreSQL JSONB and MongoDB BSON, examining tradeoffs in indexing, queries, and data modeling. JSONB suits hybrid relational+document workloads with ACID compliance, while MongoDB BSON excels in horizontal scaling and flexible schema design.

PostgreSQL organizes data into a database cluster (a server-managed directory). Within it, databases are subdirectories in the base folder, each holding system catalogs. Tables are stored as relation files (relfilenodes) inside their database's directory, located via OID mappings.

The article discusses how PostgreSQL-compatible databases are emerging as a strong foundation for AI workloads at scale, combining the reliability of Postgres with AI-specific features like vector search and distributed processing. It highlights the need for databases that can handle large-scale AI data while maintaining SQL compatibility and operational familiarity.

A pull request adds TurboQuant, a vector quantization method, to pgvector, the PostgreSQL extension for vector similarity search. TurboQuant is designed to improve performance and reduce memory usage for high-dimensional vector data.

PostgreSQL survived the failure of its original commercial venture to become one of the world's most influential open-source databases. After Postgres's creator Michael Stonebraker moved on, a small group of developers kept the codebase alive under a BSD license, and it grew through community contributions and extensible architecture.

The article discusses how PostgreSQL and ClickHouse can be used together as an open source unified data stack, combining PostgreSQL's transactional capabilities with ClickHouse's analytical performance for a complete data infrastructure.

A Rust rewrite of WAL-G, called WAL-RUS, has been developed for PostgreSQL backup management. The project aims to improve performance and reliability by leveraging Rust's safety features while maintaining compatibility with WAL-G's workflow for write-ahead log archiving and restoration.

This guide details a methodology for migrating PostgreSQL databases from on-premises servers to AWS Cloud with zero downtime, covering replication, schema migration, and cutover strategies.

PostgreSQL does not allow adding a column in the middle of a table due to its fixed physical row layout and MVCC architecture. Instead, new columns are appended at the end, as altering the position would require rewriting the entire table, which is costly and complex. The article explains the internal reasons behind this limitation and discusses workarounds like custom views or table rebuilds.

PgBouncer is a lightweight connection pooler for PostgreSQL that manages database connections to reduce overhead. It offers three pooling modes (session, transaction, and statement) and helps improve performance by reusing connections and limiting the number of concurrent connections to the database.

The article argues that PostgreSQL alone can handle many tasks typically requiring separate tools, such as queuing, caching, full-text search, and analytics. It presents PostgreSQL as a versatile, single-database solution for most application needs, reducing infrastructure complexity.