Search
Write a publication
Pull to refresh
161.69

PostgreSQL *

Object-relational database management system (ORDBMS) with an emphasis on extensibility and standards compliance

Show first
Rating limit
Level of difficulty

How to load test PostgreSQL database and not miss anything

Level of difficultyMedium
Reading time14 min
Views257

During load testing of Tantor Postgres databases or other PostgreSQL-based databases using the standard tool pgbench, specialists often encounter non-representative results and the need for repeated tests due to the fact that details of the environment (such as DBMS configuration, server characteristics, PostgreSQL versions) are not recorded. In this article we are going to review author's pg_perfbench, which is designed to address this issue. It ensures that scenarios are repeatable, prevents the loss of important data, and streamlines result comparison by registering all parameters in a single template. It also automatically launches pgbench with TPC-B load generation, collects all metadata on the testing environment, and generates a structured report.

Read more

We’ve learned how to migrate databases from Oracle to Postgres Pro at 41 TB/day

Level of difficultyEasy
Reading time3 min
Views305

41 TB/day from Oracle to Postgres Pro without stopping the source system — not theory, but numbers from our latest tests. We broke the migration into three stages: fast initial load, CDC from redo logs, and validation, and wrapped them into ProGate. In this article, we’ll explain how the pipeline works, why we chose Go, and where the bottlenecks hide.

Read more

Partition and rule: sharing practical knowledge about partitioning in Postgres Pro

Level of difficultyMedium
Reading time11 min
Views429

Declarative partitioning may sound complex, but in reality it’s just a way to tell your database how best to organize large tables — so it can optimize queries and make maintenance easier. Let’s walk through how it works and when declarative partitioning can save the day.

Read more

Getting started with pgpro-otel-collector

Level of difficultyEasy
Reading time4 min
Views367

Now that pgpro-otel-collector has had its public release, I’m excited to start sharing more about the tool — and to kick things off, I’m launching a blog series focused entirely on the Collector.

The first post is an intro — a practical guide to installing, configuring, and launching the collector. We’ll also take our first look at what kind of data the collector exposes, starting with good old Postgres metrics.

Read more

Getting to know PPEM 2

Level of difficultyEasy
Reading time7 min
Views341

Postgres Pro recently announced the release of Enterprise Manager 2, commonly known as PPEM.

In short, PPEM is an administration tool designed for managing and monitoring Postgres databases. Its primary goal is to assist DBAs in their daily tasks and automate routine operations. In this article, I'll take a closer look at what PPEM has to offer. My name is Alexey, and I'm part of the PPEM development team.

Read more

Redundant statistics slow down your Postgres? Try sampling in pg_stat_statements

Level of difficultyMedium
Reading time11 min
Views530

pg_stat_statements is the standard PostgreSQL extension used to track query statistics: number of executions, total and average execution time, number of returned rows, and other metrics. This information allows to analyze query behavior over time, identify problem areas, and make informed optimization decisions. However, in systems with high contention, pg_stat_statements itself can become a bottleneck and cause performance drops. In this article, we will analyze in which scenarios the extension becomes a source of problems, how sampling is structured, and in which cases its application can reduce overhead.

Read more

The performance engineer: a detective licensed to kill… bottlenecks

Level of difficultyEasy
Reading time5 min
Views658

Picture this: a mission-critical SQL query is crawling along. Not for an hour. Not for two. Fifteen hours. A full workday of the system slowly grinding through data while the business bleeds money and users teeter on the edge of a nervous breakdown. And then — cue the dramatic music — in walks the performance engineer.

After a few hours of intense analysis and a couple of pinpoint code tweaks, the same query that took 15 hours now completes in just… two minutes. Sounds like magic? Nope. This is the thrilling (and very real) world of performance engineering.

Read more

How we implemented vector search in Postgres Pro

Level of difficultyEasy
Reading time7 min
Views733

In this article, we’ll explore what vector search is, what problems it solves, and how the pgpro_vector extension for Postgres Pro brings powerful vector capabilities directly into a relational database — no need for separate specialized systems.

Read more

On reordering expressions in Postgres

Level of difficultyEasy
Reading time4 min
Views535

Today, I want to talk about one of those sneaky tricks that can help speed up query execution. Specifically, this is about reordering conditions in WHERE clauses, JOINs, HAVING clauses, and so on.

The idea is simple: if a condition in an AND chain turns out to be false, or if one in an OR chain turns out to be true, there's no need to evaluate the rest. That means saved CPU cycles — and sometimes, a lot of them. Let’s break this down.

Read more

Automated management of extended statistics in PostgreSQL

Level of difficultyMedium
Reading time6 min
Views636

Here I describe the results of developing a PostgreSQL extension I built just out of curiosity. Its purpose is to automatically manage extended column statistics. The idea came to me while finishing work on another "smart" query-driven product for improving PostgreSQL planning quality. I realized that the current architecture of PostgreSQL isn’t quite ready for fully autonomous operation — automatic detection of bad plans and adaptive optimizer tuning. So why not try the other way around and build an autonomous data-driven assistant?

Read more

What May Surprise You About UUIDv7

Level of difficultyMedium
Reading time3 min
Views3.2K

UUIDv7 was inspired by ULID. Like ULID, it is a 128-bit identifier, containing a timestamp on the left side and random data on the right side. But RFC 9562 establishes many requirements for UUIDv7.

In databases and distributed systems, a properly implemented UUIDv7 is always preferred over any other identifier type, including natural keys, autoincrement, UUIDv4, TypeID, ULID, KSUID, CUID, NanoID, and Snowflake ID.

Surprising distinctions of UUIDv7

Whose feature is better, or how to compare the efficiency of SQL query plans

Level of difficultyMedium
Reading time5 min
Views401

How to compare the efficiency of SQL query plans? “Measure the execution time, of course!” — an experienced reader would say. And they would be absolutely right: from a practical perspective, the more efficient DBMS is the one that delivers higher TPS. However, sometimes we need to design a system that doesn't exist yet or predict behavior under loads that haven't occurred yet. In such cases, we need a characteristic that allows us to perform a qualitative analysis of a plan or compare two plans. This post is dedicated to one such characteristic — the number of data pages read.

Read more

SQL Server vs. PostgreSQL query optimization: room for improvement?

Level of difficultyMedium
Reading time15 min
Views736

For years, we’ve studied Oracle to make PostgreSQL a more migration-friendly option. We introduced tools similar to SQL profile and SQL plan baseline as AQO and sr_plan extensions. In some cases, PostgreSQL even outperforms Oracle, especially in automatic re-optimization.

Migrations from Oracle to PostgreSQL are usually smooth performance-wise, and we’ve even developed session variable extensions to make the transition easier. While many enterprise-only features exist, PostgreSQL often integrates popular solutions directly into the core.

Read more

Postgres Pro OpenTelemetry сollector

Level of difficultyMedium
Reading time4 min
Views633

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;DRpgpro-otel-collector is an OpenTelemetry collector (aka monitoring agent) tailored for gathering Postgres metrics and logs — brought to you by PostgresPro.

Read more

cgroups: how to eliminate the “noisy neighbor” effect in PostgreSQL

Level of difficultyMedium
Reading time6 min
Views622

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!

Read more

How to catch and optimize problematic queries in PostgreSQL

Level of difficultyMedium
Reading time8 min
Views883

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.

Read more

One-click Postgres Pro optimization with pgpro_tune

Level of difficultyEasy
Reading time4 min
Views663

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.

Read more

Database performance analysis using pg_profile and pgpro_pwr

Level of difficultyEasy
Reading time4 min
Views354

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.

Read more

By next year, we'll be talking to databases in natural language

Level of difficultyEasy
Reading time4 min
Views722

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.

Read more

Authors' contribution