With the introduction of failover slots in PostgreSQL-17, logical replication can now be effectively utilized in high-availability environments. This feature ensures that logical replication continues seamlessly even if the publisher node fails and its physical standby is promoted as the new publisher.To maintain continuous replication after a...
In my last blog post, Evolution of Logical Replication, I mentioned the future development of a feature to allow "upgrades of logical replication nodes." The upcoming release of PostgreSQL 17 includes this feature. Previously, after major version upgrades (via pg_upgrade), users couldn't immediately connect and write data to logical replication...
This blog post is about how the Logical Replication has evolved over the years in PostgreSQL, what's in the latest release-16, and what's being worked upon for future releases. Logical replication is a method of replicating data objects and their changes, based upon their replication identity (usually a primary key). We use the term logical in...
There are various areas in PostgreSQL like Partitioning, Logical Replication, Parallel Query, Vacuum, etc. which improve with each new version. In this blog, I'll summarize the various enhancements in Logical Replication that users could see in the recently released PostgreSQL 15. You can read the enhancements in this area in the previous release...
In the upcoming release of PostgreSQL-14, we will see multiple enhancements in Logical Replication which I hope will further increase its usage. This blog is primarily to summarize and briefly explain all the enhancements in Logical Replication.Decoding of large transactions:Allow streaming large in-progress transactions to subscribers. Before...
Logical Replication was introduced in PostgreSQL-10 and since then it is being improved with each version. Logical Replication is a method to replicate the data selectively unlike physical replication where the data of the entire cluster is copied. This can be used to build a multi-master or bi-directional replication solution. One of the main...
Vacuum is one of the sub-systems in PostgreSQL which gets improved with each release. I have checked past five releases and each has quite a few improvements for vacuum. Following the trend, there are a number of improvements in vacuum in the upcoming PostgreSQL release (v13) which are covered in this blog. Improvement-1 ---------------------...
This blog post is about the journey of parallelism in PostgreSQL till now and what is in store for the future. Since PostgreSQL 9.6 where the first feature of parallel query has arrived, each release improves it. Below is a brief overview of the parallel query features added in each release. PG9.6 has added Parallel execution of sequential...
There is a lot to say about parallelism in PostgreSQL. We have come a long way since I wrote my first post on this topic (Parallel Sequential Scans). Each of the past three releases (including PG-11, which is in its beta) have a parallel query as a major feature which in itself says how useful is this feature and the amount of work being done on...
In the past few years, PostgreSQL has advanced a lot in terms of features, performance, and scalability for many-core systems. However, one of the problems that many enterprises still complain is that its size increases over time which is commonly referred to as bloat. PostgreSQL has a mechanism known as autovacuum wherein a dedicated process...
PostgreSQL have supported Hash Index for a long time, but they are not much used in production mainly because they are not durable. Now, with the next version of PostgreSQL, they will be durable. The immediate question is how do they perform as compared to Btree indexes. There is a lot of work done in the coming version to make them faster....
Currently when the PostgreSQL database becomes slow especially on systems with high load, it becomes difficult to find the exact reasons. Currently one can use tools like perf, strace, dynamic tracing (http://www.postgresql.org/docs/devel/static/dynamic-trace.html), etc. to find out the reasons of slowdown, but most of the times they are...
Parallelism is now reality in PostgreSQL. With 9.6, I hope we will see many different form of queries that can use parallelism to execute. For now, I will limit this discussion to what we can already do, which is Parallel Sequential Scans. Parallel Sequential Scans are used to scan a relation parallely with the help of background workers which...
Lately, PostgreSQL has gained attention because of numerous performance improvements that are being done in various areas (like for 9.5 the major areas as covered in my PGCon presentation are Read operations, Sorting, plpgsql, new index type for data access, compression of full_page_writes), however still there is more to be done to make it...
As of now, one can't reliably use tar format to take backup on Windows because it can't restore tablespaces data which is stored in form of symbolic links in /pg_tblspc/. The reason for the same is that native windows utilites are not able to create symbolic links while extracting files from tar. It might be possible to create symbolic links...
I have ran some benchmark tests to see the Write performance/scalability in PostgreSQL 9.5 and thought it would be good to share the same with others, so writing this blog post. I have ran a pgbench tests (TPC-B (sort of) load) to compare the performance difference between different modes and scale factor in HEAD (e5f455f5) on IBM POWER-8...
Database Management Systems uses MVCC to avoid the problem of Writers blocking Readers and vice-versa, by making use of multiple versions of data. There are essentially two approaches to multi-version concurrency. Approaches for MVCC The first approach is to store multiple versions of records in the database, and garbage collect records when they...
In PostgreSQL 9.5, we will see a boost in scalability for read workload when the data can fit in RAM. I have ran a pgbench read-only load to compare the performance difference between 9.4 and HEAD (62f5e447) on IBM POWER-8 having 24 cores, 192 hardware threads, 492GB RAM and here is the performance data The data is mainly taken for 2 kind of...