Category: postgresql

Enterprise PostgreSQL Solutions

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