Neues vom PostgreSQL Planet

My introduction to databases  and PostgreSQL was for web application development and statistical analysis. I learned just enough SQL to get the queries to return the right answers.

Indexes are one of the core features of all the database management systems (DBMS). Indexes have a very long history in PostgreSQL, which has quite a rich set of index features. PostgreSQL has B-Tree, Hash,  GIN, GIST, and BRIN indexes. And because the development around indexes is still going on, PostgreSQL 13 provides some enhancements.

We can divide the overall improvements into two categories:

I'm sure everybody is aware you can have PostgreSQL fetch data live across the internet (or locally of course) as part of your queries and use the data directly. In fact there are a large number of drivers available to access different kinds of data for download. But in the simplest case, we can also just use the file_fdw wrapper that's included in the standard PostgreSQL packages, together with everybody's http-swiss-army-knife, curl.

If you are enjoying working with PostgreSQL declarative partitioning, you might be wondering how to check which partition contains a specific record. While it is quite obvious in the cases of list or range partitioning, it is a bit trickier with hash partitioning. Don’t worry. Here you can find a quick way to determine which […]

It is very common to see many customer cases where a sudden increase in disk space usage is caused by a lot of WAL segments filling up the WAL directory (pg_wal). Panicking customers generally ask “Why isn’t PostgreSQL deleting them?”. The most common reasons we used to find were:

SQL and PostgreSQL are perfect tools to analyze data. However, they can also be used to create sample data which has to possess certain statistical properties. One thing many people need quite often is a normal distribution. The main question therefore is: How can one create this kind of sample data?

Tablefunc: Creating normal distributions

The first thing you have to do is to enable the tablefunc extension, which is actually quite simple to do:

tl;dr -- If you are not actively monitoring your PostgreSQL clusters, and alerting on anomalous conditions, you are "flying blind". Don't do that!

On 8th of September 2020, Michael Paquier committed patch: Add support for partitioned tables and indexes in REINDEX   Until now, REINDEX was not able to work with partitioned tables and indexes, forcing users to reindex partitions one by one. This extends REINDEX INDEX and REINDEX TABLE so as they can accept a partitioned index … Continue reading "Waiting for PostgreSQL 14 – Add support for partitioned tables and indexes in REINDEX"

PostgreSQL Person of the Week Interview with Regina Obe: I live in Boston, Massachusetts (USA). I was born in Lagos, Nigeria in 1971 and spent the first 7 years of my life there. My father is Nigerian and mother is American. I moved to the US at age of 7ish when my mother started her medical residency. I think I’m still considered a dual citizen though I haven’t been back to Nigeria since.

PostgreSQL 13 development is coming along nicely, Postgres 13 Beta3 was released on 8/13/2020. The Postgres Beta 1 and 2 releases were released in May and June 2020. One of the features that has my interest in Postgres 13 is the B-Tree deduplication effort. B-Tree indexes are the default indexing method in Postgres, and are likely the most-used indexes in production environments. Any improvements to this part of the database are likely to have wide-reaching benefits.

Our latest release to the Citus extension to Postgres is Citus 9.4. If you’re not yet familiar, Citus transforms Postgres into a distributed database, distributing your data and your SQL queries across multiple nodes. This post is basically the Citus 9.4 release notes.

If you’re ready to get started with Citus, it’s easy to download Citus open source packages for 9.4.

Many of you rely on databases to return correct results for your SQL queries, however complex your queries might be. And you probably place your trust with no questions asked—since you know relational databases are built on top of proven mathematical foundations, and since there is no practical way to manually verify your SQL query output anyway.

A little contribution in spreading the PostgreSQL word!

Hey there! I’m using PostgreSQL!

A few weeks ago I changed my old mobile phone, and so I had to install again all my applications, including something I personally hate: WhatsApp.
While checking the configuration of the application, correctly and automatically cloned from my old phone, I came across the standard status that WhatsApp places for you:

pgBackRest is a well-known powerful backup and restore tool. It offers a lot of possibilities.

While pg_basebackup is commonly used to setup the initial database copy for the Streaming Replication, it could be interesting to reuse a previous database backup (eg. taken with pgBackRest) to perform this initial copy.

This content updates one of my old posts, using PostgreSQL 13 and the latest pgBackRest version.

In a database production environment, a backup plays quite an essential role. The database server can fail for a number of reasons. It can happen because of hardware failure, software malfunction or just because of user error. Whatever the reason, when a live database goes down, a backup is essential in fixing and recovering it.

In a database system, the data is stored in binary files. Every database provider offers some kind of backup tools using which database files may be backed up. PostgreSQL database server also provides a comprehensive set of tools.

It would be very easy if I drove the same car regularly, but because of my family size and travels, I don't have that luxury. Some cars I drive have smart keys, some mechanical keys. Some have gas tank doors on the driver's side, others from the passenger side. They steer differently, have different acceleration capabilities, even different service requirements. I have gotten used to switching cars, but still get confused when I have to fuel the car since I have to remember which side has the gas tank door.

Learn how to effectively configure PostgreSQL log management for troubleshooting performance problems.

Window functions in PostgreSQL are a very useful tool; and since their introduction 10 years ago, they have been gradually adopted for various use cases where a simple aggregation is just not flexible enough — case in point, incremental totals and moving averages. The features introduced in PostgreSQL complete the implementation according to the SQL:2011 […]

This time we talk about using ltree as an alternative to PostgreSQL cursors.

Is it Postgre, PostGreSQL, Postgres or PostgreSQL? We have all seen a couple of wrong ways to spell “PostgreSQL”. The question therefore is: How can one find data even if there are typos? In PostgreSQL there are various solutions to the problem. Depending on what kind of search you need you can choose between various methods.

Before we get started it is necessary to compile some sample data: