Category: postgresql

Enterprise PostgreSQL Solutions

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

PostgresConf.CN and PGCONF.Asia 2020 combined together to bring the best

The PostgreSQL community is anxiously waiting for an exciting online PostgreSQL conference taking place in November 2020. It would be a unique opportunity as PostgreConf.CN and PGCONF.Asia will be merged in order to provide wide range of topics covering various aspect of PostgreSQL ranging from Administration, Performance tuning, Hacking PostgreSQL, Security, Scalability, Foreign data wrappers…
Read more

In-Memory Table with Pluggable Storage API

1. Introduction This blog is to follow up on the post I published back in July, 2020 about achieving an in-memory table storage using PostgreSQL’s pluggable storage API. In the past few months, my team and I have made some progress and did a few POC patches to prove some of the unknowns and hypothesis…
Read more

The Share Invalid mechanism of CACHE in PostgreSQL

PostgreSQL will create some catalog and other caches for each connection request to store some queried data locally, which can speed up query efficiency. If a process undergoes a DDL operation that causes catalog to change, other running processes need to be notified that some cache data is invalid. We call this notification mechanism the…
Read more

PostgreSQL Backups

In a database production environment, a backup plays quite an essential role. The database server can fail for a number of reasons. It can happen because of hardware failure, software malfunction or just because of user error. Whatever the reason, when a live database goes down, a backup is essential in fixing and recovering it.…
Read more

Who is spending wal crazily

Recently, some people are asking why the wal of the database is consumed so quickly. This blog gives a simple way to analyze where the wal log is consumed, and you can get the reason for wal expansion. Note: There are two understandings about wal expansion: 1. Too many LSNs are consumed in a period;…
Read more

How to setup PostgreSQL on an IPv6 enabled network

1. Overview PostgreSQL is a great open source database, not only because it supports lot of database features, but also because it supports different network setup. For example, you can set it up on an IPv6 enabled network in just a few steps. This blog will demonstrate how to setup PostgreSQL on an IPv6 network…
Read more

TLS Related Updates in PostgreSQL 13

1. Introduction The upcoming major release of PostgreSQL 13 has several important behavioral updates related to the TLS implementation. These updates may have some to your current PostgreSQL security deployment if you were considering to upgrade to 13 when it officially releases. Today I would like to do a quick summary of these updates. 2.…
Read more

Consensus based failover with Pgpool-II

Pgpool-II probably is the most comprehensive clustering solution existing today for PostgreSQL. It provides a wide range of features like connection pooling, load balancing, automatic failover and high availability while using the Pgpool-II for load balancing and building a highly available PostgreSQL cluster is one of its most common use case. Since Pgpool-II is a…
Read more

PostgreSQL High Availability: The Considerations and Candidates

Almost every organisation that I interact with wants a high availability system for PostgreSQL. This clearly depicts an active trend toward an increase in utilising PostgreSQL for critical business applications. In some cases it is a move away from other major database systems like Oracle or even Teradata. It’s not possible to cover this topic…
Read more

Partitioning Improvements in PostgreSQL 13

The table partitioning feature in PostgreSQL has come a long way after the declarative partitioning syntax added to PostgreSQL 10. The partitioning feature in PostgreSQL was first added by PG 8.1 by Simon Rigs, it has based on the concept of table inheritance and using constraint exclusion to exclude inherited tables (not needed) from a…
Read more

Approaches to Achieve in-Memory Table Storage with PostgreSQL Pluggable API

1. Introduction Recently, I have had an opportunity to perform some in-depth feasibility study in-memory table using PostgreSQL’s pluggable storage API. The pluggable storage API was introduced Since PostgreSQL v12 and it allowed custom table storage Access Methods (AM for short) to be developed. Some famous examples include zheap from EDB, which aims to store…
Read more

The SPI feature under PostgreSQL kernel

Server Programming Interface(SPI) is a module in the PostgreSQL kernel which allows kernel developers to execute SQL statements in C functions and have the ability to manage transactions. This module provides great convenience for PostgreSQL plug-in development through its characteristics so that developers can conveniently call various database languages in the kernel. SPI modules are…
Read more

A simple way found a bug born in 1997

1. Overview Whenever I tried to study PostgreSQL source code a little deeper, I always wanted to find some tools to help me understand better as I really don’t want to read the code line by line for a particular feature, but at the same time, I really wanted to figure it out quickly. Simply…
Read more

Features In PG13 – Deduplication in B-Tree Indexes

PG13; not to be confused with PG-13, only PostgreSQL Guidance required here! PostgreSQL Beta 1 was released on May 21, 2020 and Beta 2 on June 25, 2020. A beta might not be bug free, but it almost always includes all core features of the full release. PG betas are no different. Beta 2 includes…
Read more

Authenticating pgpool II with LDAP

Overview It has been a while since I have written about the new features in a major pgpool II release. Well pgpool II 4.2 is in the works and the plan is to release it towards the end of this year. As usual every major release of pgpool II is compatible with the parser of…
Read more