Pstgre Archives

Tech Optimizer

July 9, 2026

DBA2: Configuration and Monitoring of PostgreSQL 16 is now available in English

Postgres Professional has translated the DBA2: Configuration and Monitoring of PostgreSQL 16 course into English. This four-day program is designed for PostgreSQL administrators with basic Unix skills and knowledge equivalent to the DBA1 course. The curriculum covers topics such as Multi-Version Concurrency Control (MVCC), isolation levels, vacuuming, buffer cache management, write-ahead logging (WAL), and server upgrades. Participants will learn to configure PostgreSQL parameters, monitor the server, manage localization settings, and handle extensions. The course materials include a student guide for setting up a virtual machine and performing practical tasks. The translation was done by Elena Sharafutdinova, with assistance from Ilya Bashtanov and Alexander Meleshko.

Tech Optimizer

July 8, 2026

PostgreSQL Extensions Are an Architecture Decision, Not an Afterthought

A common issue in database migrations is the unplanned accumulation of extensions in PostgreSQL, leading to extension sprawl. Teams often install extensions without documenting the rationale, resulting in a complex web of dependencies that complicates future upgrades and removals. The installation process involves PostgreSQL accessing a control file that details the extension's version and dependencies, which can lead to multiple extensions being installed unintentionally. Upgrading and removing extensions are often neglected, causing risks such as the loss of dependent objects. Extensions typically default to the public schema, which can become cluttered; relocating them to dedicated schemas can improve organization. Trusted Language Extensions (TLE) allow non-privileged users to utilize procedural languages in managed databases without needing superuser access. Key extensions recommended for use include pg_stat_statements, pg_trgm, hstore, citext, and PostGIS, each serving specific use cases. Proper governance is essential for managing extensions, including documenting their purpose, ownership, and dependencies, to prevent operational surprises.

Tech Optimizer

July 8, 2026

Critical PHP PDO Driver Bugs Expose Firebird SQL Injection and PostgreSQL DoS Risks

A recent examination of PHP's database driver layer revealed two significant vulnerabilities in PHP Data Objects (PDO) affecting the pdo_firebird and pdo_pgsql drivers. The first vulnerability, CVE-2026-25289, allows SQL injection attacks through NUL byte manipulation in quoted strings, compromising the safety of SQL statements. The second vulnerability, CVE-2026-25290, can cause application crashes due to null pointer dereferencing when invalid multibyte character sequences are processed. Both vulnerabilities stem from assumptions about C string handling and have been addressed in a security release. Researchers recommend treating binary input as untrustworthy and encapsulating financial workflows within atomic transactions.

Tech Optimizer

July 8, 2026

Logical replication improvements in Amazon RDS for PostgreSQL 18

PostgreSQL logical replication has operational challenges, such as the absence of generated columns in the replication stream, replication conflicts causing apply worker halts, and lingering replication slots consuming write-ahead log (WAL) files. PostgreSQL 18 introduces enhancements to address these issues, including the ability to include STORED generated columns in the replication stream with a new publication parameter, detailed conflict counters in pg_stat_subscription_stats, automatic invalidation of idle replication slots, and the ability to toggle two-phase commit settings on live subscriptions. To set up a test environment, two PostgreSQL 18 instances (one publisher and one subscriber) are required, along with an EC2 instance for executing commands. The publisher and subscriber must have specific configurations for logical replication, including enabling logical replication and setting appropriate parameters. PostgreSQL 18 allows the replication of STORED generated columns by using the publish_generated_columns parameter. The subscriber must define these columns as regular columns. The new version also enhances conflict monitoring by providing detailed counters for different types of replication conflicts. Parallel streaming is now the default behavior for subscriptions, improving efficiency. The two-phase commit feature can now be modified on existing subscriptions without needing to recreate them. PostgreSQL 18 also introduces automatic cleanup of idle replication slots, which can be configured to invalidate unused slots after a specified duration. Considerations for implementation include ensuring that the subscriber's column types match those of the publisher for generated columns, the requirement for track_commit_timestamp for certain conflict counters, and the fact that sequences are not replicated. Cleanup of replication resources is recommended after testing to prevent unnecessary costs and WAL accumulation.

Tech Optimizer

July 7, 2026

PDO PostgreSQL Bug Triggers NULL Pointer Dereference and Crashes PHP Worker Processes

Researchers at Positive Technologies have identified two significant vulnerabilities in the PHP Data Objects (PDO) extension layer, both posing high severity risks. The first vulnerability (CVE-2025-14180) leads to a NULL pointer dereference, causing PHP worker processes to crash. It affects PHP versions 8.1.x prior to 8.1.34, 8.2.x before 8.2.30, 8.3.x before 8.3.29, 8.4.x before 8.4.16, and 8.5.x before 8.5.1. This issue occurs when the pdo_pgsql driver operates with PDO::ATTR_EMULATE_PREPARES enabled, allowing a remote attacker to exploit it without authentication by submitting malformed character sequences. The second vulnerability (CVE-2025-14179) allows for SQL injection and affects PHP versions 8.2.x before 8.2.31, 8.3.x before 8.3.31, 8.4.x before 8.4.21, and 8.5.x before 8.5.6. It arises from a mishandling of NUL bytes in the Firebird driver during the PDO::prepare() process, despite the quoting routine functioning correctly. Additionally, the audit revealed an integer overflow in PostgreSQL’s libpq client library and an information disclosure flaw in Firebird 3’s fbclient.

Tech Optimizer

July 7, 2026

The TechBeat: How Small Postgres Metadata Tables Quietly Throttle Your Largest Queries (7/6/2026)

- Websites detect VPN usage through factors like IP reputation, DNS leaks, WebRTC, and browser fingerprints, with strategies to mitigate tracking. - Slow Postgres dashboards may be caused by stale metadata statistics rather than the fact table, with solutions provided to rectify these issues. - Spec-driven development enhances the reliability of AI coding agents through structured specifications, workflows, reusable skills, and proof. - A comparison of usage-based billing platforms for 2026 includes Credyt, Metronome, Orb, Lago, Stripe Billing, and Chargebee. - Loop Engineering is a rising trend in AI workflows expected to dominate in 2026, though challenges may accompany it. - ONLYOFFICE released version 3.7 of DocSpace Developer, introducing AI file generation, new webhook events, expanded plugin tools, and enhanced form workflows. - ANY.RUN's data inspection tool offers SOC teams comprehensive browser visibility for quicker phishing investigations. - AI systems are reducing obvious hallucinations, but deeper structural failures remain a risk for professionals. - A vendor audit revealed supply chain vulnerabilities in AI, including unpinned models and the use of trust_remote_code=True. - Developers using OpenAI Codex should avoid overloading a single conversation with tasks to maintain performance, recommending the use of subagents. - Barriers to AI adoption are not purely technological, as shared by insights from discussions with 500 individuals. - Guidance on creating production AI agent loops emphasizes state management, tool governance, evidence verification, and domain-specific controls. - Dawnguard launched a security architecture automation platform with million in pre-seed funding to address AI code vulnerabilities. - The AI boom is driven by complex memory architecture rather than GPUs, influencing consumer electronics pricing strategies. - The origins of functional programming trace back to John McCarthy's practical needs for differentiating expressions. - A guide for AI startup founders addresses common pitfalls in understanding AI agents and the autonomy trap for developing reliable systems.

Tech Optimizer

July 6, 2026

Why most AI projects fail: It’s infrastructure and people

AI technology faces significant criticism for its low success rates in delivering business results, with studies indicating a 95% failure rate for enterprise AI solutions and only 9% of organizations in Europe, the Middle East, and Africa achieving measurable outcomes from AI initiatives. Four main shortcomings hinder the transition of AI prototypes to production: 1. Deployment Flexibility: Prototyping environments often lack the necessary flexibility for large-scale production deployment, particularly in regulated sectors. 2. Data Sovereignty: Production transitions can complicate data sovereignty at enterprise and regional levels. 3. Reliability: High availability is crucial for production environments, but vendor-managed platforms may not guarantee seamless upgrades or hardware swaps without downtime. 4. Disconnect in Tool Selection: Developers often choose tools for prototyping without considering production implications, leading to difficulties in scaling. The shortage of database administrators (DBAs) is exacerbated by the increasing use of AI tools, with 84% of developers utilizing them according to a 2025 survey. To address these challenges, Merrick suggests leveraging AI DBA agents to support human DBAs and improve database management efficiency. He emphasizes the need for both robust data infrastructure and enhanced operational support to improve the success rates of AI prototypes.

Tech Optimizer

July 3, 2026

Percona & HexaCluster team up on database migrations

Percona has formed a strategic alliance with HexaCluster to assist organizations in migrating from proprietary databases to open-source alternatives, specifically PostgreSQL, MySQL, and MariaDB. The partnership combines Percona's migration assessment and production support with HexaCluster's migration software for diverse database environments. The migration process will begin with a comprehensive assessment by Percona to outline the scope and plan the transition. Percona will lead the migration efforts, while HexaCluster will provide software solutions for complex migrations. This collaboration aims to help enterprises reduce costs associated with proprietary databases and minimize risks during migration. HexaCluster specializes in PostgreSQL migration and offers tools for migration assessment, schema conversion, data migration, and live replication. Percona supports various database systems and emphasizes open-source solutions to give organizations more control over their data infrastructure.

Tech Optimizer

July 2, 2026

From monolith to Lakebase to LTAP: rethinking the database from storage up

Sixteen years ago, the author began their PhD at UC Berkeley and was advised to focus on analytics rather than OLTP databases, which were considered solved. This led to the creation of Apache Spark and Databricks. However, during the development of Databricks, it was found that OLTP databases were not solved problems; they were difficult to scale and fragile. This realization led to the development of Lakebase, a serverless Postgres database designed with modern technology. Lakebase architecture separates the write-ahead log (WAL) and data files into independent, scalable services. The WAL is externalized to a service called SafeKeeper, which uses Paxos-based replication for durability, while data files are managed by PageServer, which stores them in cloud object storage. This design addresses challenges such as data loss, scaling, and performance interference between transactional and analytical workloads. Lakebase maintains compatibility with Postgres, offers unlimited storage, serverless compute, durable writes, and simpler high availability. It also introduces LTAP (Lake Transactional/Analytical Processing), which allows both transactional and analytical processing to operate on a single data copy in real time, eliminating the need for separate data copies and reducing costs. LTAP utilizes a unified storage layer that allows data to be materialized in both row and columnar formats, optimizing it for both transactional and analytical workloads. The system ensures that analytics can access the most current data without affecting transactional performance by using a log sequence number (LSN) to retrieve the latest changes. Unlike traditional CDC approaches, LTAP requires no explicit table replication, as all data is stored in a single governed copy. This architecture circumvents common issues faced by hybrid transactional/analytical processing (HTAP) systems, such as incomplete feature sets, lack of ecosystem support, and performance contention. The Lakebase architecture has unlocked capabilities like unlimited storage, elastic compute, durable writes, and instant branching, with ongoing developments anticipated in the future.

Tech Optimizer

July 2, 2026

How JSON.stringify Corrupted 150,000 Rows in My Postgres Database

A dataset of over 100,000 company profiles was found to have approximately 150,000 corrupted values across ten jsonb columns due to incorrect serialization caused by using JSON.stringify on values intended for jsonb columns. The problematic code was `await sql` update listings set services = ${JSON.stringify(record.services)} where id = ${record.id}`. This resulted in jsonb strings instead of jsonb arrays. The issue was detected by querying the type of the jsonb columns, revealing that two frequently updated columns had around 105,000 and 41,000 corrupted values, respectively. The repair process involved extracting the inner text and re-casting it as valid jsonb, while also addressing complications such as NUL bytes and double-encoding. The correct method for inserting jsonb values is to use `sql.json(record.services)` instead of JSON.stringify. Regular type checks are now part of a daily integrity job to prevent similar issues.