Object-relational database management system (ORDBMS) with an emphasis on extensibility and standards compliance
Hi everyone, I’m Alexey. I’m a big fan of observability, and in this post, I want to share something I’ve been working on — the pgpro-otel-collector.
TL;DR: pgpro-otel-collector is an OpenTelemetry collector (aka monitoring agent) tailored for gathering Postgres metrics and logs — brought to you by PostgresPro.
If you've ever run multiple instances of PostgreSQL or other software on a single machine (whether virtual or physical), you've probably encountered the "noisy neighbor" effect — when instances disrupted each other. So, how do you make them get along? We’ve got the answer!
If you work with PostgreSQL, you've likely run into performance issues at some point — especially as your database grows. Things may have been running smoothly at first, but as your client database expanded, queries started slowing down. Sound familiar? Here's a guide to help you identify and fix problematic queries, so you can get your PostgreSQL database running at peak performance again.
Imagine a familiar situation: it’s Monday morning, tasks are piling up, and you need to quickly spin up a new service using Postgres Pro. Or maybe you’ve just upgraded your database server over the weekend — added more CPUs, more RAM.
Here’s how to get your database tuned and ready to make the most of the new hardware and workload, without wasting time.
DBAs often struggle to identify the most resource-hungry processes that degrade system performance. Back in 2017, DBA — and now Postgres Professional engineer — Andrey Zubkov faced the same challenge. This led him to develop pg_profile for PostgreSQL, which has since evolved into pgpro_pwr.
In this article, we’ll dive into strategic database monitoring and show you how to pinpoint bottlenecks in your databases using our tools.
According to Gartner, natural language queries will replace SQL as early as 2026.
While Gartner's prediction may be optimistic, the shift toward natural language interfaces for databases is inevitable. The timeline may vary, but the transition itself is a certainty.
Storing all your data in one place might seem convenient, but it’s often impractical. High costs, database scalability limits, and complex administration create major hurdles. That’s why smart businesses rely on Information Lifecycle Management (ILM) — a structured approach that automates data management based on policies and best practices.
With Postgres Pro Enterprise 17, ILM is now easier than ever, thanks to the pgpro_ilm extension. This tool enables seamless data tiering, much like Oracle's ILM functionality. Let’s dive into the challenges of managing large databases, how ILM solves them, and how you can implement it in Postgres Pro Enterprise 17.
While pg_probackup 3 is still in the works and not yet available to the public, let’s dive into what’s new under the hood. There’s a lot to unpack — from a completely reimagined application architecture to long-awaited features and seamless integration with other tools.
For many years, the PostgreSQL community was skeptical about using this database management system (DBMS) for high-transaction environments. While PostgreSQL worked well for lab tests, mid-tier web applications, and smaller backend systems, it was believed that for heavy transactional loads, you’d need an expensive DBMS designed specifically for such purposes. As a result, PostgreSQL wasn’t particularly developed in that direction, leaving a range of issues unanswered.
However, the reality has turned out differently. More and more of our clients are encountering problems that stem from this mindset. For example, in the global PostgreSQL community, it’s considered that 64 cores is the maximum size of a server where PostgreSQL can run effectively. But we’re now seeing that this is becoming a minimum typical configuration. One particular bottleneck that has emerged is the transaction counter, and this is a far more interesting issue. So, let’s dive into what the problem is, how we solved it, and what the international community thinks about it.
Postgres Pro Enterprise 17 introduces major improvements in performance and scalability. The key feature of this new release is the proxima extension, which combines connection pooling, proxying, and load balancing within the database core. Developers also gain improved tools for managing message queues, optimizing queries, enhancing security, and utilizing smart data storage. Want to know how these and other features can impact your applications and simplify database administration?
This article provides a brief overview of the release, accompanied by the links to more detailed information.
Statistically, September CommitFests feature the fewest commits. Apparently, the version 18 CommitFest is an outlier. There are many accepted patches and many interesting new features to talk about.
If you missed the July CommitFest, get up to speed here: 2024-07.
This article is the first in the series about the upcoming PostgreSQL 18 release. Let us take a look at the features introduced in the July CommitFest.
Planner: Hash Right Semi Join support
Planner: materializing an internal row set for parallel nested loop join
Planner support functions for generate_series
EXPLAIN (analyze): statistics for Parallel Bitmap Heap Scan node workers
Functions min and max for composite types
Parameter names for regexp* functions
Debug mode in pgbench
pg_get_backend_memory_contexts: column path instead of parent, new column type
Function pg_get_acl
pg_upgrade: pg_dump optimization
Predefined role pg_signal_autovacuum_worker
Since the PostgreSQL 17 RC1 came out, we are on a home run towards the official PostgreSQL release, scheduled for September 26, 2024.
Let's take a look at the patches that came in during the March CommitFest.
In the age of information, the ability to accurately and quickly retrieve data relevant to a user's query is paramount. Traditional search methodologies, which rely on keyword matching, often fall short when it comes to understanding the context and nuances of user queries. Semantic search, which seeks to improve search accuracy by understanding the searcher's intent and the contextual meaning of terms, has emerged as a solution to these limitations. However, implementing semantic search can be complex, involving advanced algorithms and understanding of natural language processing (NLP).
Existing solutions such as Elasticsearch and Solr have been at the forefront of tackling these challenges, providing platforms that support more nuanced search capabilities. These tools use a combination of inverted indices and text analysis techniques to improve search outcomes. Yet, the advent of machine learning and vector search technologies opens up new avenues for enhancing semantic search, with solutions like OpenAI's Embeddings API and the pgvector extension for PostgreSQL leading the charge.
The November commitfest is ripe with new interesting features! Without further ado, let's proceed with the review.
If you missed our July and September commitfest reviews, you can check them out here: 2023-07, 2023-09.
ON LOGIN trigger
Event triggers for REINDEX
ALTER OPERATOR: commutator, negator, hashes, merges
pg_dump --filter=dump.txt
psql: displaying default privileges
pg_stat_statements: track statement entry timestamps and reset min/max statistics
pg_stat_checkpointer: checkpointer process statistics
pg_stats: statistics for range type columns
Planner: exclusion of unnecessary table self-joins
Planner: materialized CTE statistics
Planner: accessing a table with multiple clauses
Index range scan optimization
dblink, postgres_fdw: detailed wait events
Logical replication: migration of replication slots during publisher upgrade
Replication slot use log
Unicode: new information functions
New function: xmltext
AT LOCAL support
Infinite intervals
ALTER SYSTEM with unrecognized custom parameters
Building the server from source
We continue to follow the news of the PostgreSQL 17 development. Let's find out what the September commitfest brings to the table.
If you missed our July commitfest review, you can check it out here: 2023-07.
Removed the parameter old_snapshot_threshold
New parameter event_triggers
New functions to_bin and to_oct
New system view pg_wait_events
EXPLAIN: a JIT compilation time counter for tuple deforming
Planner: better estimate of the initial cost of the WindowAgg node
pg_constraint: NOT NULL constraints
Normalization of CALL, DEALLOCATE and two-phase commit control commands
unaccent: the target rule expressions now support values in quotation marks
COPY FROM: FORCE_NOT_NULL * and FORCE_NULL *
Audit of connections without authentication
pg_stat_subscription: new column worker_type
The behaviour of pg_promote in case of unsuccessful switchover to a replica
Choosing the disk synchronization method in server utilities
pg_restore: optimization of parallel recovery of a large number of tables
pg_basebackup and pg_receivewal with the parameter dbname
Parameter names for a number of built-in functions
psql: \watch min_rows
We continue to follow the news in the world of PostgreSQL. The PostgreSQL 16 Release Candidate 1 was rolled out on August 31. If all is well, PostgreSQL 16 will officially release on September 14.
What has changed in the upcoming release after the April code freeze? What's getting into PostgreSQL 17 after the first commitfest? Read our latest review to find out!
