Neues vom PostgreSQL Planet
A key aspect of maintaining a high performance database is continuous monitoring of system resources, as any issues anywhere on the system can potentially lead to downtime. There are options aplenty for monitoring, so reinventing the wheel doesn’t make much sense. For that purpose, I’ve selected a couple of options that are simple to deploy, customizable, and can provide us with all the relevant information about the health of a PostgreSQL system.
This blog will focus on setting up Nagios for PostgreSQL monitoring before proceeding with enhancing that with Checkmk.
One of the main reasons people use the Citus extension for Postgres is to distribute the data in Postgres tables across multiple nodes. Citus does this by splitting the original Postgres table into multiple smaller tables and putting these smaller tables on different nodes. The process of splitting bigger tables into smaller ones is called sharding—and these smaller Postgres tables are called “shards”. Citus then allows you to query the shards as if they were still a single Postgres table.
The big question we hear quite often is, “Can and should we run production Postgres workloads in a Docker? Does it work?” The answer in short: yes, it will work… if you really want it to… or if it’s all only fun and play, i.e. for throwaway stuff like testing.
Optimizing read and write performance varies for every Postgres database server in a different environment. Continue reading Basic practices for optimizing read and write performance in PostgreSQL→
Pgbench is a simple tool to perform performance tests on PostgreSQL.
GitHub Actions (GHA) are altogether a piece of excellent machinery for continuous integration or other automated tasks on your repo. I started to use them from the release day on as a replacement for CircleCI. Not that I think CircleCI is a bad product; I love to have everything in one place if possible. However, using a young product is a challenge. Even now, there is no easy way to debug actions.
The last time I’ve made changes in pgCenter was back in January, so I thought it was about time to make a few revisions and upgrade to a new version. As a result I’ve added following statistics:
To use these, copy the files into your PostgreSQL 13 Windows 64-bit install folders in same named folders and then run CREATE EXTENSION as usual in the databases of your choice. More details in the packaged README.txt
Development on Citus first started around a decade ago and once a year we release a major new Citus open source version. We wanted to make number 10 something special, but I could not have imagined how truly spectacular this release would become. Citus 10 extends Postgres (12 and 13) with many new superpowers:
PostgreSQL supports many System Information Functions, such as Session Information Functions, Access Privilege Inquiry Functions, Schema Visibility Inquiry Functions, System Catalog Information Functions, Transaction ID and Snapshot Information Functions, etc. However, you may want build some special functions and integrate them into the PostgreSQL. This blog is going to walk through the steps about how to build your own System Information Functions into PostgreSQL.
As an experienced PostgreSQL user, you may have a lot of experience in setting up streaming replication in your database clusters to make multiple backups of your data. But have you wondered how the standby is able to correctly determine if a tuple sent from the primary should be visible to the user or not. In the case where a transaction contains multiple subtransactions, how does the standby determine the visibility in this case? You might say… well it is PostgreSQL so it will just work… This is true.