Enterprise PostgreSQL Solutions

How PostgreSQL Executes Sequential Scans with the Help of Table Access Methods APIs

1. Introduction There are many approaches for PostgreSQL to retrieve the data back to the user. Depending on the user’s input query, the planner module is responsible for selecting the most optimum approach to retrieve the requested data. Sequential scan is one of these approaches that is mostly selected when the user requests a large…
Read more

The mapping of oid and relfilenode in PG

A table in PostgreSQL has a relfilenode value, which specifies the file name of the table on disk (except foreign table and partition table). In general, this value can be found in the relfilenode field of the pg_class table, but there are some specific tables whose query result in the relfilenode field is 0. This…
Read more

HighGo CA released second major version of Community PostgreSQL release

HighGo Software Canada (a subsidiary of HighGo Software Inc.) is pleased to release HighGo Postgres Server 2.1 General Availability (HG-PGSQL 2.1 GA) on its website https://www.highgo.ca/products/highgo-postgresql-server/2-1-2/. The GA release contains everything that was released with HG-PGSQL 2.0 beta few weeks earlier with the addition of some important bug fixes and updates to user documentation. With…
Read more

Checkpoints In PostgreSQL

PostgreSQL database writes all changes happening in the database to a log file before it writes them to the actual data files. These log files are called WAL (Write-Ahead Log). Until these changes are flushed to the disk, they are kept in memory and is returned from the memory when it’s asked for. Writing to…
Read more

How to dump out a backtrace during runtime

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…
Read more

Rise and Fall for an expected feature in PostgreSQL – Transparent Data Encryption

TDE is an important feature missing from PostgreSQL, it is also a crucial tick in the box which people are deciding to move to PostgreSQL from other DBMS systems. The community has been trying to add this feature in PG however it has taken too long because of lack of attention from serious community members…
Read more

Installing PostgreSQL on CentOS6 Post EOL

If you’ve seen the following error, you know that your CentOS6 has reached end of life (EOL). Loaded plugins: fastestmirror, ovl Setting up Install Process YumRepo Error: All mirror URLs are not using ftp, http[s] or file. Eg. Invalid release/repo/arch combination/ removing mirrorlist with no valid mirrors: /var/cache/yum/x86_64/6/base/mirrorlist.txt Error: Cannot retrieve repository metadata (repomd.xml) for…
Read more

Bulk loading into PostgreSQL: Options and comparison

You have a file, possibly a huge CSV, and you want to import its content into your database. There are lots of options to do this but how would you decide which one to use. More often than not the question is how much time would the bulk load would take. I found my self doing the same…
Read more

What’s the special for logical level of PostgreSQL WAL

When using logical replication with PostgreSQL, the wal level needs to be set to ‘logical’, so the logical level wal contains more data to support logical replication than the replicate wal level. I think it’s unclear to many users or DBAs about the difference between logical and replicate level. This blog is about the difference…
Read more

2020 PG Asia Conference Ended Successfully at an Unprecedented Scale!

Introduction On November 17-20, 2020, PostgresConf.CN & PGconf.Asia2020 (referred to as 2020 PG Asia Conference) was held online for the very first time! This conference was jointly organized by the PG China Open Source Software Promotion Alliance, PostgresConf International Conference Organization, and PGConf.Asia Asian Community. This conference was broadcast exclusively via the Modb Technology Community…
Read more

PostgreSQL High Availability: Setup and Uptime Considerations

My previous blog on “PostgreSQL High Availability: Considerations and Candidates” mostly talked about defining an HA considerations for PostgreSQL, RPO and RTO and briefly touched on some of the open source solutions available. Granted that I didn’t list them all and people shared some additional ones that I may end up reviewing as well. In…
Read more

Calling a Stored Procedure in PostgrSQL from JAVA and its Current Limitations

This is my third blog about Stored Procedure support in PostgreSQL, the previous two blogs are reachable from the HighGo CA blogs site https://www.highgo.ca/author/ahsan-h/. The first blog was introduction and usage of Stored Procedures and its difference with Stored Functions, the second blog focussed on creating and using Procedures with Definer and Invoker rights. The…
Read more

Technical Overview of pg_stat_monitor extension: A more detailed pg_stat_statements

Database monitoring is a very essential for most of the database system, it can prevent system crises, improve performance, database failure and auditing the traffic for identify any unwanted access. PostgreSQL has an extension called pg_stat_statements, which can capture a lot of query execution details. Recently, percona release a new extension called pg_stat_monitor which can…
Read more

How to Analyze a PostgreSQL Crash Dump File

1. Introduction In this blog post, I will talk about how to enable the generation of crash dump file (also known as core dump) and some common GDB commands to help a developer troubleshoot a crash-related issues within PostgreSQL and also other applications. Proper analysis of the issue normally will take time and certain degree…
Read more

Free Space Mapping file in details

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…
Read more

The way PG store null value in record

When inserting data into a table, you can specify that the value of a field is null. For example, for a table t (i int, j int, k int), we can execute insert into t values (8,1,6), or insert into t values (3, null, 7) to insert a record with a null value. This blog…
Read more

Heap file and page in details

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…
Read more

Configuring Pgpool-II watchdog: It’s going to be a lot easier

Watchdog is the high availability component of Pgpool-II. Over the past few releases watchdog has gotten a lot of attention from the Pgpool-II developer community and received lots of upgrades and stability improvements. One of the not very strong areas of pgpool-II watchdog is its configuration interface. Watchdog cluster requires quite a few config settings…
Read more

PostgreSQL WAL Archiving and Point-In-Time-Recovery

WAL is short for Write-Ahead-Log. Any change to the data is first recorded in a WAL file. The WAL files are mainly used by RDBMS as a way to achieve durability and consistency while writing data to storage systems. Before we move forward, let’s first see why we need a WAL archiving and Point in…
Read more

pgpool II 4.2 features

The pgpool II community is gearing up to release the Alpha version of its next major release; pgpool II 4.2. It is going to be another exciting release of pgpool II that is a middleware product and provides mission critical functionality like load balancing, high availability, connection pooling etc for PostgreSQL server. We have written…
Read more