Logical replication in PostgreSQL has steadily evolved since its introduction in version 10. In a previous blog post , I discussed how PostgreSQL 14 introduced streaming of large transactions. PostgreSQL 16 took this further by enabling parallel apply of large transactions via a non-default subscription option. Now, with PostgreSQL 18, parallel apply is the default behavior—marking a significant…
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 failover, you need to configure the following…
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 nodes. This was because the slots were lost during…
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 contrast to physical replication, which uses exact…
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 one of my previous blogs . Allow replication of…
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 PostgreSQL-14, the transactions were streamed only…
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 differences as compared with physical replication was…
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 --------------------- Vacuum will be allowed to process indexes in parallel.…
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 scans, joins, and aggregates. PG10 has added (a) Support…
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 this feature. You can read more about parallel…
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 (or set of processes) tries to remove the dead rows…
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. There are multiple ways in which we can compare the…
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 quite inconvenient to use which lead to the development…
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 in turns improve the performance of such scans. I will…
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 better than other commercial RDBMS's and one of the…
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 <data_directory>/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 if cygwin is installed on your system,…
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 having 24 cores, 192 hardware threads, 492GB RAM and here…
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 are no longer required. This is the approach…
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 workloads, when all the data fits in shared buffers…