Enterprise PostgreSQL Solutions

Replication Failover with pg_rewind in PG12

1. Overview In the previous blog, we have discussed how to correctly set up streaming replication clusters between one master and one slave in Postgres version 12. In this blog, we will simulate a failover scenario on the master database, which causes the replica (or slave) database cluster to be promoted as new master and…
Read more

Streaming Replication Setup in PG12 – How to Do it Right

1. Overview PostgreSQL 12 has been considered as a major update consisting of major performance boost with partitioning enhancements, indexing improvements, optimized planner logics and several others. One of the major changes is noticeably the removal of recovery.conf in a standby cluster. For this reason, the procedure to set up a streaming replication clusters has…
Read more

Vancouver Postgres Group Meetup Event – Kubernetes Best Practices for Distributed SQL databases

Date: October 24, 2019 Guest Speaker: Andrew Nelson, Developer Advocate from YugaByte About Vancouver Postgres User Meetup Group Vancouver Postgres is a Postgres user meetup group based in Vancouver, Canada. It specializes in building Postgres users to the related ecosystem including but not limited to technologies such as RDS Postgres, Aurora for Postgres, Google Postgres,…
Read more

Newbie to PostgreSQL – where to start

While managing a small team of development resources working on PostgreSQL development, I sometimes get resources in my team that have good development experience but are new to PostgreSQL. I have developed a short set of training instructions in order to get these resources started with PostgreSQL and get them to familiarise themselves with Postgres…
Read more

Trace Query Processing Internals with Debugger

1. Overview In this article we will use GDB debugger to trace the internals of Postgres and observe how an input query passes through several levels of transformation (Parser -> Analyzer -> Rewriter -> Planner -> Executor) and eventually produces an output. This article is based on PG12 running on Ubuntu 18.04, and we will…
Read more

A Guide to Create User-Defined Extension Modules to Postgres

1. Overview Postgres is a huge database system consisting of a wide range of built-in data types, functions, features and operators that can be utilized to solve many common to complex problems. However, in the world full of complex problems, sometimes these are just not enough depending on the use case complexities. Worry not, since…
Read more

A Guide to Basic Postgres Partition Table and Trigger Function

1. Overview Table partitioning is introduced after Postgres version 9.4 that provides several performance improvement under extreme loads. Partitioning refers to splitting one logically large table into smaller pieces, which in turn distribute heavy loads across smaller pieces (also known as partitions). There are several ways to define a partition table, such as declarative partitioning…
Read more

The Transparent data encryption in PostgreSQL

I have been working with the PostgreSQL community recently to develop TDE (Transparent Data Encryption). During this time, I studied some cryptography-related knowledge and used it to combine with the database. I will introduce the TDE in PostgreSQL by the following three dimensions. The current threat model of the database Encryption policy description and current…
Read more

Key Management

Key management consists of four parts: key generation, key preservation, key exchange, and key rotation. Key Generation Only for the study of symmetric encryption, so I mainly introduce symmetric encryption. The symmetric password generation method is: A random number is a key Password-based key generation HKDF (HMAC-based extraction and extended key derivation) A random number…
Read more

Can you gain performance with Pgpool-II as a load balancer?

The world’s most valuable resource is no longer the oil or gold, but data. And at the heart of data lies the database which is expected to store, process and retrieve the desired data as quickly as possible. But having a single database server doesn’t mostly serve the purpose. A single server has its drawbacks…
Read more

The performance test on the AES modes

After introducing the difference between the AES modes, in this document, I will put the results about the AES modes performance. The following tests just use one core CPU. AES-NI:The Advanced Encryption Standard Instruction Set (or Intel Advanced Encryption Standard New Instructions, AES-NI for short) is an extension of the x86 instruction set architecture for…
Read more

Parallel Foreign Scan of PostgreSQL

PostgreSQL Community is working on adding built-in sharding in Postgres and it seems that the community is following the FDW based sharding approach for adding built-in sharding for PG. The Parallel Foreign Scan is a very important part of this approach as it will provide the functionality of executing multiple foreign scans in parallel and…
Read more

An Overview of Logical Replication in PostgreSQL

Logical Replication appeared in Postgres10, it came along with number of keywords like ‘logical decoding’, ‘pglogical’, ‘wal2json’, ‘BDR’ etc. These words puzzle me so much so I decided to start the blog with explaining these terms and describing the relationships between them. I will also mention a new idea for a tool called ‘logical2sql’, it…
Read more

An Overview of Replication in PostgreSQL Context

Replication is a critical part of any database system that aims to provide high availability (HA) and effective disaster recovery (DR) strategy. This blog is aimed at establishing the role of replication in a database system. The blog will give a general overview of replication and its types as well as an introduction to replication options…
Read more

Horizontal scalability with Sharding in PostgreSQL – Where it is going Part 3 of 3.

Built-in Sharding Architecture The build-in sharding feature in PostgreSQL is using the FDW based approach, the FDW’s are based on sql/med specification that defines how an external data source can be accessed from the PostgreSQL server. PostgreSQL provides number of foreign data wrapper (FDW’s) that are used for accessing external data sources, the postgres_fdw is…
Read more

Horizontal scalability with Sharding in PostgreSQL – Where it is going Part 2 of 3.

Declarative Partitioning So far we have discussed scalability, what is scalability, why and when you need and what are the different types of scalability. Now we are starting to get into the meat of this topic and will discuss declarative partitioning and sharding in PostgreSQL. The sharding functionality is being laid on top of declarative partitioning…
Read more

Horizontal scalability with Sharding in PostgreSQL – Where it is going Part 1 of 3

I recently had the opportunity of taking part in couple of interesting talks on the future of sharding in PostgreSQL. The first talk was delivered by Bruce Momjain in PostgreSQL conf Ottawa (May 2019) in which he presented the future of Sharding in PostgreSQL and talked about current state and future of built-in sharding in…
Read more

The difference in five modes in the AES encryption algorithm

Recently, I did some work with Sawada-san on the TDE. So I studied on the encryption algorithm. So far, I study five modes in the AES. In this document, I will introduce the difference in the five kinds of mode. General The block ciphers are schemes for encryption or decryption where a block of plaintext…
Read more

How To Add Extensions To PG on Ubuntu

How Extensibility Works PostgreSQL is extensible because its operation is catalog-driven. PostgreSQL stores much more information in its catalogs: not only information about tables and columns, but also information about data types, functions, access methods, and so on. These tables can be modified by the user, and since PostgreSQL bases its operation on these tables,…
Read more

Pgpool II 4.1 taking the bull by its horn

I will start with providing some explanation for the blog title before i dive into the blog itself. The title of this blog is that “Pgpool II 4.1 taking the bull by its horn”, the “bull” in this case is Pgpool II performance and “taking it by its horn” means that 4.1 is performance release…
Read more