Author: David Zhang

Feed: Planet PostgreSQL. 1. Overview I have been working on an internal project based on PostgreSQL for a while, and from time to time, I need to run some specific test cases to verify my changes. Here, I want to shared a tip to run a specific regression TAP test quickly, especially, when you are focusing on a particular bug and you know which test case can help verify the fix. A details document about the regression test can be found at Running the Tests. 2. Regression test PostgreSQL provides a comprehensive set of regression tests to verify the SQL ... Read More

Feed: Planet PostgreSQL. 1. Overview When I was working on some backup and recovery related features for a project based on Postgres, I noticed that there is file called backup_label. By quickly google search, you can find some very nice blogs or books which discussed this topic, such as, The Internals of PostgreSQL, one of my favourite books. In this blog, I am going to talk it a little more based on my experience. 2. What is backup_label? The backup_label is a file created in $PGDATA folder when there is an exclusive backup triggered by pg_start_backup() and the backup is ... Read More

Feed: Planet PostgreSQL. 1. Overview In my previous blog, I briefly walked through how the bulk/batch insertion was done for postgres_fdw in PG14. In this blog, I am going to run some basic tests to compare the performance for before and after the batch insertion was introduced in postgres_fdw, so that we can have a general idea about whether this feature makes any difference. 2. PG Servers Setup The key of the blog is to see if there is any difference for batch insertion. To make the testing simple, here is how I set up a simple environment. As this ... Read More

Feed: Planet PostgreSQL. 1. Overview postgres_fdw has been existing in PostgreSQL for many years, and it was one of the most popular interfaces used in many extensions. such as the PostgreSQL foreign data wrappers wiki page, and PostgreSQL Extension Network/PGXN. However, I had not touched this postgres_fdw in deep until I got a task about combing multiple columns from multiple tables using this amazing Foreign Data Wrapper interface. After some hack, I was be able to combine multiple columns from multiple tables into one table based on postgres_fdw, but I was just curious about how many enhancements has been done ... Read More

Feed: Planet PostgreSQL. 1. Overview I was working on the PostgreSQL storage related features recently, and I found PostgreSQL has designed an amazing storage addressing mechanism, i.e. Buffer Tag. In this blog, I want to share with you my understanding about the Buffer Tag and some potential usage of it. 2. Buffer Tag I was always curious to know how PostgreSQl can find out the tuple data blocks so quickly when the first time I started to use PostgreSQL in an IoT project, but I never got the chance to look it into details even though I knew PostgreSQL is ... Read More

Feed: Planet PostgreSQL. 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 about how to build your own System Information Functions into PostgreSQL. 2. Analyze the requirement Since there are so many functions built-in PostgreSQL already, you should perform some research and analysis before you decide to create a new one. In this blog, ... Read More

Feed: Planet PostgreSQL. 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 code, so that this environment can be used for later development. Simply prepare two Ubuntu 18.04 using VirtualBox with below network settings. Primary: 192.168.0.181 Standby: 192.168.0.182 It is pretty simple to install postgres from source code. Here we checkout the Postgresql-13 stable ... Read More

Feed: Planet PostgreSQL. 1. Overview PostgreSQL is a great open source database developed and maintained by many great software engineers around the world. In each release, there are many features added to this open source database. For example, one feature is very helpful for developer is backtrace_functions introduced in PostgreSQL 13 , which allows a developer to dump out the backtrace when certain errors happened on the server. In this blog, I am going to explain it in a little bit more detail. 2. What is backtrace_functions? The backtrace_functions option is an option introduced for developers as it is described ... Read More

Feed: Planet PostgreSQL. 1. Overview Previously, we discussed the MAIN fork file and corresponding extension at Heap file and page in details. This blog will explain a little bit more about the Free Space Mapping file and corresponding extension. 2. What is a Free Space Mapping file A Free Space Mapping, FSM, file is a file that keeps track of the availability of free space of a page. Within each FSM is a binary tree, stored in an array with one byte per node. The FSM is used to quickly locate a page with enough free space to hold a ... Read More

Feed: Planet PostgreSQL. 1. Overview PostgreSQL is a great open source database, and many users chose it because of the efficiency of its central algorithms and data structures. As a software developer, I was always curious about how each part was done, such as the physical files storage. The reason is that I always see a lot of files and folders were created after a simple initdb command. For example, $ ls -ltr /home/user/pgdata drwx------ 4 user user 4096 Oct 8 13:38 pg_logical drwx------ 5 user user 4096 Oct 8 13:38 base drwx------ 2 user user 4096 Oct 8 13:38 ... Read More