Jul 08, 2025 |
Insight for DBAs, Percona Software, PostgreSQL, Security
Welcome to the open source implementation of PostgreSQL Transparent Data Encryption! This question was posed on the PostgreSQL forum, and the good news is that it’s actually pretty easy to do! Q: Is it possible to automate the steps to enable pg_tde for all new databases?A: Yes! Here’s the routine: Part I: Download Percona Distribution […]
Mar 18, 2025 |
Insight for DBAs, Open Source, Percona Software, PostgreSQL
This post was originally published in December 2023 and was updated in March 2025. These days, there’s significant industry interest in moving database systems to PostgreSQL. Often, these are greenfield projects focusing on design and architecture. However, decisions are increasingly being made to move from existing platforms, like MySQL, to PostgreSQL for compelling business and […]
Dec 09, 2024 |
Insight for DBAs, PostgreSQL
First things first, a couple of definitions to clear up: ETL: Extract, Transform, Load. Transforms data before loading it into the target system extract raw data from various sources use a secondary processing server to transform that data load that data into a target database ELT: Extra, Load, Transform. Transforms data after loading it into […]
Jun 05, 2024 |
Insight for DBAs, Insight for Developers, PostgreSQL
There are quite a number of methods these days for installing Patroni. I want to discuss the next step, which is how to start tuning a running system. The manner of updating a running Postgres server typically involves editing the standard Postgres files: postgresql.conf postgresql.auto.conf pg_hba.conf Working with a Patroni HA cluster, one has the […]
Mar 12, 2024 |
Insight for DBAs, Insight for Developers, PostgreSQL
PostgreSQL extensions are great! Simply by adding an extension, one transforms what is an otherwise vanilla general-purpose database management system into one capable of processing data requirements in a highly optimized fashion. Some extensions, like pg_repack, simplify and enhance existing features already, while other extensions, such as PostGIS and pgvector, add completely new capabilities. I’d […]
Dec 26, 2023 |
Insight for DBAs, Percona Software, PostgreSQL
This PoC demonstrates how to install and configure pg_stat_monitor in order to extract useful and actionable metrics from a PostgreSQL database and display them on a Grafana dashboard. About the environment Grafana: version 10.0.0 Grafana database backend: Prometheus version 2.15.2+d PostgreSQL version 13 pgbench version 13 In order to investigate the potential opportunities for implementing […]
Nov 03, 2023 |
Insight for DBAs, Insight for Developers, PostgreSQL
When it comes to Citus, successfully building out and scaling a PostgreSQL cluster across multiple nodes and even across data centers can feel, at times, to be an art form because there are so many ways of building it out. There’s an axiom that I think aptly applies to this situation describing the differences between […]
Aug 29, 2023 |
Insight for DBAs, Insight for Developers, PostgreSQL
Over the years, I’ve had the opportunity to architect all sorts of configurations using Postgres as a backend. I’ve always found it very cool and satisfying to implement sophisticated business rules, often in more ways than one has fingers and toes. So, it’s not an understatement when I say that Citus is one of the […]
Aug 21, 2023 |
Insight for DBAs, PostgreSQL
Effectively working with LDAP as an authentication mechanism for PostgreSQL typically requires extensive knowledge in both domains. While trying to be as complete yet succinct as possible, I’m detailing how to enable TLS between a PostgreSQL and the OpenLDAP server. Ironically, the most complicated aspect has nothing to do with either PostgreSQL or OpenLDAP but […]
Jun 15, 2023 |
Insight for DBAs, Insight for Developers, PostgreSQL
This post was originally published in 2020 and has been updated in 2023. Mastering Geographical Information Systems, better known simply as GIS, can be considered in some ways as a rite of passage. The complexities and challenges involved in learning, which are ostensibly non-IT concepts, are steep. However, as they say, “There’s more than one […]
Jun 05, 2023 |
Insight for DBAs, PostgreSQL
From time to time, situations occur where unusual circumstances dictate out-of-the-box thinking. For example, suppose you have a system where you’ve installed multiple data clusters onto a single host. What you end up with is a host breaking up and sharing valuable system resources, i.e., CPU, RAM, disk, etc., between multiple instances of PostgreSQL data […]
Feb 02, 2023 |
Insight for DBAs, Insight for Developers, PostgreSQL
These days, the typical PostgreSQL cluster consists not only of a multi-node replication cluster, which is ordinarily an asynchronous streaming replication model but can sometimes include a logical replication component. Recall that logical replication works by using the PUB/SUB mode, where individual tables are published and are then subscribed by remotely connected databases/tables. […]
Dec 23, 2022 |
Insight for DBAs, Insight for Developers, PostgreSQL
One of the routine operations when administering PostgreSQL is periodic updates to the database system’s architecture. PostgreSQL does a good job of allowing one to update a schema, add types, functions, triggers, or alter a table by adding and removing columns and updating column data types, etc., in a reliable manner. However, there is no […]
Dec 14, 2022 |
Insight for DBAs, PostgreSQL
Prologue This blog is the result of my investigation into column-wise tables. Hopefully, you will find it useful in your deliberations. When I started writing it was meant to be a “Good News” blurb. I was pretty optimistic that I’d truly have impressive numbers to share but as it turns out, while there is a […]
Sep 13, 2022 |
Insight for DBAs, Insight for Developers, PostgreSQL
One of the reasons I’ve never strayed far from PostgreSQL is that I am always discovering features that overcome all sorts of interesting problems. Although, to be honest, from time to time I’ve had the feeling that sometimes some features are a solution in search of a problem. Take, for example, exporting transaction snapshots. Originally […]
Sep 02, 2022 |
Insight for DBAs, Insight for Developers, Open Source, PostgreSQL
After working with PostgreSQL for so many years it’s sometimes surprising to see some of the utilities, to a certain degree, have capabilities that remain relatively unknown to more than a few people. So let me introduce you to manifests. Both pg_dump and pg_restore have switches enabling us to zone in on specific objects of […]
Jul 13, 2022 |
Insight for DBAs, PostgreSQL
Well, it’s that time of the year when once again we have a look at the newest version of PostgreSQL. As tradition dictates, here at Percona, the team is given a list of features to write about. Mine happened to be about a very basic and, I might add, important function i.e. SELECT DISTINCT. Before […]
Apr 27, 2022 |
Insight for DBAs, Insight for Developers, PostgreSQL
It’s a funny thing when the topic of database sizes comes up. Calling one small, medium, large, or even huge isn’t as straightforward as you’d think. Distinguishing the size of a database is based upon a number of factors whose characteristics can be classified as either “tangible”, things that you can measure in an objective […]
Apr 13, 2022 |
Insight for DBAs, Open Source, PostgreSQL
Transaction ID Wraparound occurs when the VACUUM process cannot keep up with database activity and the PostgreSQL service is forced to shut down. In more technical parlance: Transaction ID Wraparound occurs when the semantics of Multi-Version Concurrency Control (MVCC) fail and when the number of unique transaction ids reaches its maximum which numbers about two […]
Jan 31, 2022 |
Database Trends, Insight for DBAs, Insight for Developers, PostgreSQL
Note: In the original version of this post, there were some issues missed. These have been addressed at the bottom of the post. I’d like to introduce to you a very cool feature introduced in PostgreSQL, the Pipeline Mode. So just what exactly is Pipeline Mode? Pipeline Mode allows applications to send a query without […]