Back in August 2019, I wrote about the Horizontal scaling / Sharding in PostgreSQL and where it is going https://www.highgo.ca/2019/08/08/horizontal-scalability-with-sharding-in-postgresql-where-it-is-going-part-3-of-3/. One of the key feature that i talked about in this blog is parallel foreign scan i.e. asynchronous append of FDW nodes. Finally the first patch of this feature was committed to PG-14, while they…
Read more

This is the second of the two-part series of blogs about PostgreSQL data corruption. Part-1 discussed identifying the data corruption in PostgreSQL and lists some DO’s and DON’Ts for avoiding that. In this part, I will attempt to walk you through possible ways to recover from the corruption and ways to salvage data in case of…
Read more

Indexes are key database server features that enhance its performance to retrieve data faster than a complete table scan (unless the index is vastly bloated). They generally work by maintaining a smaller set of data in a more “searchable” structure as compared to the complete table data. This data organization comes at the cost of…
Read more

Back in Nov 2021, I wrote about calling Stored procedure from Java and demonstrated how we can call a simple stored procedure from Java or a one that contains a IN parameter. I have done some more playing around with calling Stored procedure from Java so this blog is about calling a stored procedure with…
Read more

There are many advantages to using bind variables in SQL statements, especially in conditional statements. Using bind variables can save the trouble to parse and optimize the query in every execution. There are also disadvantages such as bind variable will make optimizer lost its prediction accuracy and cause performance decrease. In PostgreSQL are represented by…
Read more

A hierarchical query is built upon parent-child relationship, the relationship exist in the same table or view. The relationship dictates that each child can have one parent while a parent can have many children. Hierarchical query is a SQL query that handles data of hierarchical model i.e. an organisation structure where every employee has one…
Read more

A key aspect of maintaining a high performance database is continuous monitoring of system resources, as any issues anywhere on the system can potentially lead to downtime. There are options aplenty for monitoring, so reinventing the wheel doesn’t make much sense. For that purpose, I’ve selected a couple of options that are simple to deploy,…
Read more

1. Overview PostgreSQL supports many System Information Functions, such as Session Information Functions, Access Privilege Inquiry Functions, Schema Visibility Inquiry Functions, System Catalog Information Functions, Transaction ID and Snapshot Information Functions, etc. However, you may want build some special functions and integrate them into the PostgreSQL. This blog is going to walk through the steps…
Read more

PostgreSQL is one of the most advanced open source databases globally with a lot of great features. One of them is Streaming Replication which was introduced in PostgreSQL 9.0—known as Physical Replication. It works by sending the WAL segments generated by the primary PostgreSQL to another standby server also running PostgreSQL. However, if we only…
Read more

I have spent sometime exploring all the SQL command changes in PostgreSQL 14, this blog will provide a brief summary about the SQL changes along with some examples. There are exciting new features that in progress for PG-14, this blog will only focus on the SQL changes that are committed and will be released with…
Read more

1. Overview PostgreSQL supports different type of replications, i.e. logical and physical, and there are many tutorials discussed about the replications. This blog is a simply walk-through of the WAL Streaming replication using the latest Postgresql-13 on Ubuntu 18.04. 2. Install Postgresql-13 from source code In this blog, we will install Postgresql from the source…
Read more