Neues vom PostgreSQL Planet
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.
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.
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.
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:
© 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”).
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: