Neues vom PostgreSQL Planet

1. Introduction

I have noticed that there is a page on the offical PostgreSQL documentation (https://www.postgresql.org/docs/current/logical-replication-restrictions.html) that states several restrictions to the current logical replication design. One of the restrictions is about sequence relation type where any changes associated with a sequence is not logically replicated to the subscriber or to the decoding plugin. This is an interesting restriction and I took the initiative to look into this restriction further and evaluate if it is possible to have it supported.

This is in continuation of my previous blog entry wherein I had touched upon a topic of PostgreSQL  Extensions. PostgreSQL Extensions are a plug and play set of enhancements that add an extra feature-set to a PostgreSQL cluster.

There are so many multi-host technologies and it is hard to remember the benefits of each one, so I decided to create a list:

High availability: Streaming replication is the simplest way to have multiple copies of your database, ready for fail over

Usually in this blog I’m writing about various fun topics around PostgreSQL – like perhaps new cool features, some tricky configuration parameters, performance of particular features or on some “life hacks” to ease the life of DBA-s or developers.

pgcli is nice TUI client for Postgres. Against psql it has significantly modern look with more aggressive autocomplete support.

I have been interviewed by PostgresWeekly.

Howto guide for upgrading PostgreSQL from version 11 to 12 on Ubuntu, after its upgrade from version 19.10 to 20.04.

25 April is Anzac Day in Australia, New Zealand and many communities around the world where Anzacs have served. Given the shift to online collaboration in 2020, Anzacathon has been set up to help people engage online.

One of the key themes of Anzacathon is data: finding new ways to use the data and also demonstrating the benefits of community engagement with open data.

This is a follow-up of my recent blog tittled “Stored Procedures in PG 11 – Better late then never” posted on highgo.ca and also on planet postgres. It is available at https://www.highgo.ca/2020/04/10/stored-procedures-in-pg-11-better-late-then-never/. In this short blog titled “Stored Procedures also have rights”, I will be discussing the definer and invoker rights for stored procedures, the same concept also applies to stored functions.

And now we arrive at the second article in our migration from Oracle to PostgreSQL series. This time we’ll be taking a look at the START WITH/CONNECT BY construct. In Oracle, START WITH/CONNECT BY is used to create a singly linked list structure starting at a given sentinel row. The linked list may take the form of a tree, and has no balancing requirement. We will show you how to do the same thing using recursive common table expressions in PostgreSQL.

Today's blog post is for all those people who want to create and manage PostgreSQL or x clusters AFTER the PostgreSQL Operator has been installed on their Kubernetes/OpenShift cluster.

In talking to EnterpriseDB customers, I am often asked about the performance limits of Postgres: How many connections can it handle? How many TPS?

Well, those are good questions, but it is hard to give accurate answers since so much depends on the hardware and workload. Eventually, testing of actual workloads on intended hardware has to be done, but not giving ball-park answers is unhelpful. What answer can I give? Well, I came up with this chart:

One of the most popular open source databases is PostgreSQL, in 2018 even declared DBMS of the Year. In this blog Martijn Wallet shares the most important advantages of using Supported PostgreSQL such as EDB Postgres Platform as compared to native PostgreSQL.

© Laurenz Albe 2020

Most people know that autovacuum is necessary to get rid of dead tuples. These dead tuples are a side effect of PostgreSQL’s MVCC implementation. So many people will be confused when they read that from PostgreSQL v13 on, commit b07642dbc adds support for autovacuuming insert-only tables (also known as “append-only tables”).

When I first started working with  PostgreSQL and containers, one of the first items presented to me was a recipe to get PostgreSQL 10 setup with pgAdmin 4 using Docker, which was over two years ago.

PostgreSQL is continuously improving partitions support but there is limitations on number of partitions handled by each release. Based on our experience , if you are using a lot more partitions than it’s limit for Postgres release, it will throw out of memory errors or crash!

Everyone one wants their software to perform as fast as possible. Some people think that unless the program ends before it begins, it's too slow.

However, realistically, making something as fast as possible is not a universal good, meaning that increasing performance beyond a certain point can cause problems that far exceed the value of the improved performance. Let's give some examples:

PostgreSQL Person of the Week Interview with Mark Wong: I am currently living in and am originally from Portland, Oregon. I am working for 2ndQuadrant who provides a range of Postgres products and services. I’ve taken up a variety of hobbies over the years. A couple of my favorites are medium and large format photography, and crocheting stuffed animals. You may be familiar with one particularly cute little elephant endearingly named Chelnik by Gabrielle Roth.

Postgres has a lot of settings and it’s both a good thing (you can really change it the way you want) and a bad thing (newcomers are confused by all those choices). On top of that, there are several ways to change a setting and if you don’t change the right one, your change might have no effect at all!  Settings values overwrite rules The following picture will show you the different ways to set a parameter and how they can be overwritten.