Enterprise PostgreSQL Solutions

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

Types of Indexes in PostgreSQL

Finding relevant information quickly speeds up performance. For example, while reading a book in which you have to find a topic that you would like to read, if you know that it is in a certain chapter then you will simply go to that chapter, perhaps look through it and start reading the desired topic.…
Read more

An Overview of PostgreSQL Backend Architecture

1. Introduction PostgreSQL backend is a collection of processes forked from the main process called Postmaster. Each forked process has different roles and responsibilities in the backend. This article describes the responsibility of core backend processes that power the PostgreSQL system as we know it today. The overall PostgreSQL backend architecture can be illustrated by…
Read more

Transactions in PostgreSQL and their mechanism

Transaction is the most basic concept of a database. Using begin and end command in PostgreSQL can start and commit a transaction. Of course, this is the most common PostgreSQL transaction. In addition, there are sub transaction, multi transaction, 2pc transaction concepts in PostgreSQL. In this blog, I will demonstrate the emergence scenario and kernel…
Read more

Optimizing SQL: Simplifying Queries with Window Functions

The term “Window Functions” never really give much away in terms of the capability and various options they provide. So, it made sense to explore these and while doing so, I thought it’s worth sharing. Turns out, Window functions are quite useful and can simplify writing complicated SQL queries. When fetching data, you could fetch…
Read more

MongoDB Logical Decoding Plugin – First Community Release on GitHub

HighGo Software Inc. (Canada) is pleased to announce the first GitHub community release of MongoDB Logical Decoding plugin v1.0.6 (wal2mongo), which can be used to replicate PostgreSQL database changes to an output format that can be directly fed into the mongo client tool to achieve logical replication between PostgreSQla and MongoDB. Wal2mongo plugin is useful…
Read more

Phoney table columns in PostgreSQL

The above SQL creates ‘one_column_table’ with only a single column. But does the table actually have only one column? Let’s query the pg_attribute catalog to find out how many columns our one_column_table has. What?? As per the ‘pg_attribute’ catalog, the table we just created has seven columns. Somehow PostgreSQL has added six extra columns to our…
Read more

Build PostgreSQL and Extension on Windows

1. Overview PostgreSQL is an open-source RDMS and running across many platforms including Linux (all recent distributions), Windows, FreeBSD, OpenBSD, NetBSD, Mac OS X, AIX, HP/UX, IRIX, Solaris, Tru64 Unix, and UnixWare. There are many discussions about how to build Postgres and extensions from source code on a Linux-like environment, but sometimes, a developer may…
Read more

Benefits of External Key Management System Over the Internal and How they Could Help Securing PostgreSQL

1. Introduction Data and user security have always been important considerations for small to large enterprises during the deployment of their database or application servers. PostgreSQL today has rich support for many network level and user level security features. These include TLS to secure database connections, internal user authentication, integration with external user authentication services…
Read more

Optimizing SQL – Step 1: EXPLAIN Costs and Plans in PostgreSQL – Part 2

This is the second blog in a series of blogs attempting to walk you through the query optimization process. We started from the very basics of understanding the “EXPLAIN” command. Reading part 1 is not a prerequisite, however, if you wish to understand how you can reconstruct a query from a given plan, Optimizing SQL…
Read more

How to backup multiple tablespaces with pg_basebackup

I was doing some testing recently with parallel backup feature along with backup manifest feature which was one of the last feature that got committed to PostgreSQL 13. Parallel backup is not committed to PG yet, it is currently under discussion in the hackers community. I was trying to take parallel backup of a database…
Read more

Can Sequence Relation be Logically Replicated?

1. Introduction I have noticed that there is a page on the offical PostgreSQL documentation (https://www.postgresql.org/docs/current/logical-replication-restrictions.html) that states several restrictions to the current logical replication design. One of the restrictions is about sequence relation type where any changes associated with a sequence is not logically replicated to the subscriber or to the decoding plugin. This…
Read more

Stored Procedures also have rights

This is a follow-up of my recent blog tittled “Stored Procedures in PG 11 – Better late then never” posted on highgo.ca and also on planet postgres. It is available at https://www.highgo.ca/2020/04/10/stored-procedures-in-pg-11-better-late-then-never/. In this short blog titled “Stored Procedures also have rights”, I will be discussing the definer and invoker rights for stored procedures, the…
Read more

The Origin in PostgreSQL logical decoding

While studying the WAL structure, I came across a keyword named “origin” in the WAL log that I found intriguing. There is not much details on PG official documentation on this keyword in the WAL record and its functions. Hence I set upon the research in the code to find more about this secret data,…
Read more

Replicate multiple PostgreSQL servers to a single MongoDB server using logical decoding output plugin

1. Overview “Postgres, is a free and open-source relational database management system (RDBMS) emphasizing extensibility and SQL compliance.” This is the highlight of PostgreSQL in a sentence from Wikipedia. Yes, the extensibility of PostgreSQL is extremely useful when you have some special requirements. This blog will discuss how to use logical decoding output plugin to…
Read more

Stored Procedures in PG 11 – Better late then never

I was going to give a short talk on this subject in pgconf New York 2020, however unfortunately the conference like many other conference this year got cancelled due to COVID-19. So I decided to write a short blog about this schema object introduced in PG 11. As a Oracle database developer / DBA in…
Read more