Neues vom PostgreSQL Planet

PostgreSQL Person of the Week Interview with Abhijit Menon-Sen: My name is Abhijit, and my family and I live in a small village in the Himalayas in India, overlooking a peaceful little valley with a small river that we can hear when it rains heavily.

1. Introduction

PostgreSQL’s 2 phase commit (2PC) feature allows a database to store the details of a transaction on disk without committing it. This is done by issuing PREPARE TRANSACTION [name] command at the end of a transaction block. When the user is ready to commit, he/she can issue COMMIT PREPARED [name] where [name] should match the [name] in PREPARE TRANSACTION command. Because the transaction details are stored on disk with 2PC, the server is able to commit this transaction at a later time even if it crashes or out of service for some time.

For many developers, databases are basically magic. Like Penn & Teller, this blog post is set to break the illusion. Databases are just like any other code, they have algorithms and processes. These algorithms and processes are meant to improve performance, but can cause limitations if they are not expected.

Disclaimer: it is okay to break the rules. Sometimes, you may choose to have slow database interactions, because you know they are rare events.

Awesome news, pg_stat_monitor has reached a GA STATUS!  Percona has long been associated with pushing the limits and understanding the nuances involved in running database systems at scale, so building a tool that helps get us there brings a bit more insight and details around query performance and scale on PostgreSQL systems fits with our history. So what the hell does pg_stat_monitor do, and why should you care?  Excellent question!

Recently, there has been a bit of a debate here at Crunchy Data around SQL editors. While some members of the Crunchy Team such as Elizabeth (@e_g_christensen) prefer pgAdmin 4, others such as Craig (@craigkerstiens) prefer using psql. And one the great things about the PostgreSQL ecosystem is that there is no right answer to this debate!

Moodle is a popular Open Source online learning platform. Especially since the beginning of the COVID-19 pandemic the importance of Moodle for schools and universities has further increased. In some states in Germany all schools had to switch to Moodle and other platforms like BigBlueButton in the course of a few days. This leads to scalability problems if suddenly several tens of thousands of pupils need to access Moodle.

Recently, there has been a bit of a debate here at Crunchy Data around SQL editors. While some members of the Crunchy Team such as Elizabeth (@e_g_christensen) prefer pgAdmin 4, others such as Craig (@craigkerstiens) prefer using psql. And one the great things about the PostgreSQL ecosystem is that there is no right answer to this debate!

Parquet and Postgres in the Data Lake Static Data is Different

A couple weeks ago, I came across a blog from Retool on their experience migrating a 4TB database. They put in place some good procedures and managed a successful migration, but the whole experience was complicated by the size of the database. The size of the database was the result of a couple of very large "logging" tables: an edit log and an audit log.

Database monitoring is a crucial part of effective data management and building high-performance applications. In our previous blog post, we discussed how to enable pg_stat_statements (and that it comes standard on all Timescale Cloud instances), what data it provides, and demonstrated a few queries that you can run to glean useful information from the metrics to help pinpoint problem queries.

A couple days ago Devrim forked PostgreSQL to get a beer.

That effort is of course laudable, but a bit extreme.

PostgreSQL is very extensible, and allows to implement a beer now clock with just a few easy steps.

Let's walk through this.

 

Continue reading "It's beer o'clock for PostgreSQL"

It’s been many years since the first ideas to implement the MERGE command surfaced on the PostgreSQL mailing list. Many years later, this important feature has finally made it into PostgreSQL core, and will (as of today) make it into PostgreSQL 15. To show people how this vital command works, I have decided to come up with a technical preview to introduce my readers to this wonderful new command. It adds so much power to PostgreSQL.

I decided to do a little more research on the performance of server-side backup compression, which will be a new feature in PostgreSQL 15 unless, for some reason, the changes need to be reverted prior to release time. The network link I used for my previous testing was, as I mentioned, rather slow, and handicapped by both a VPN link and an SSH tunnel. Furthermore, I was testing using pgbench data, which is extremely compressible.

PostgreSQL Person of the Week Interview with Abbas Butt: My name is Ghulam Abbas Butt and I am from a small town called Wah Cantt, near Islamabad, Pakistan. I am a Senior Software Architect at EnterpriseDB. I have been working for EnterpriseDB since Jan 2011. In about two years I will be celebrating the silver jubilee of my professional career, that mainly spans product development.

The MERGE statement is one of the long awaited features and it’s coming in the upcoming Major version of PostgreSQL 15. Although the PostgreSQL 15 release is quite a distance away, the MERGE statement patch has been committed to the development branch and it should become available as early as beta release around May, 2022.

The MERGE statement is mostly used for adding, updating or removing rows from the target tables, based on the information and condition from a source, in a single transaction and using a single statement.

1. Overview

Nowadays, supporting distributed transactions is a typical requirement for many use cases, however, the global deadlock detection is one of the key challenging issues if you plan to use PostgreSQL to setup a distributed database solution. There are many discussions about global deadlock, but this blog will provide you a step-by-step procedure about how to create such a global deadlock and share some thoughts based on personal experience.

We are thrilled to announce that the FINAL version of pgwatch2 v1.9 is now ready for your production environment! pgwatch provides a secure, open-source, flexible, self-contained PostgreSQL metrics monitoring/dashboarding solution. pgwatch2 v1.9 supports monitoring PG versions 9.0 to 14 out of the box. Upgrade, or install pgwatch today to see how fantastic it really is. Download it from Github.

Why autovacuum is important in PostgreSQL and how to configure it properly. Continue reading Tuning PostgreSQL Auto-vacuum→

The post Tuning PostgreSQL Auto-vacuum appeared first on Vettabase.

Howto guide for upgrading PostgreSQL from version 13 to 14 on Ubuntu, after its upgrade from version 21.10 to 22.04 (Jammy Jellyfish).

The future of community in light of Babelfish

This blog post was adapted from the PGConf NYC 2021 keynote. Originally published at https://postgresconf.org.

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 manner, or “intangible”, those attributes best expressed using the catch-all phrase “it depends”. For example, a 2TB database is, to many people, a large database.