Category: postgresql

Enterprise PostgreSQL Solutions

Procedure To Multiple Client Certificate Feature

Introduction I shared a patch some time ago that adds a feature on libpq to allow user to supply multiple client certificate pairs. The feature is capable of choosing one client certificate to send to the server (if it requests one) based on the server’s trusted CA certificate settings during TLS handshake. Refer to this…
Read more

Understand PostgreSQL’s Planner – Simple Scan Paths vs Plans

Introduction When you send a query to PostgreSQL, it normally would go through stages of query processing and return you the results at the end. These stages are known as: I wrote another blog to briefly explain the responsibility of each query processing stage. You can find it here. In this blog, we will only…
Read more

Setting up a debugging environment in PostgreSQL to better understand OpenSSL APIs

1. Overview In my previous blog, I discussed how to enhance PostgreSQL TLS security with OCSP Stapling. In this blog, I will share a simple procedure for setting up a gdb debugging environment to dive into TLS connections and gain a better understanding of the OpenSSL APIs used in PostgreSQL. 2. Build OpenSSL with debugging…
Read more

Multiple Client Certificate Selection – a Simple POC

Introduction I recently came across this email thread discussion from several years ago, which discussed ways to enable a client to choose from a list of client certificates to send to the server. The benefit is obvious; when a client has to communicate with different PostgreSQL servers with different TLS settings and trust structure, the…
Read more

Deciphering PostgreSQL Encryption: A Beginner’s Guide

Introduction In this blog, we’ll go over the different methods we can use to encrypt and decrypt data in a PostgreSQL database. Having some experience with Linux and PostgreSQL is necessary, while experience with encryption is not but is nice to have. This blog was written using PostgreSQL 16 running on Ubuntu 23.04. First I’ll…
Read more

Enhance PostgreSQL TLS Security with OCSP Stapling

1. Overview In my previous blog, I discussed how to quickly set up a TLS connection between a PostgreSQL server and a psql client. In this blog, I will guide you through the process of setting up a TLS connection using OCSP Stapling, which can help improve the security of PostgreSQL. 2. What is OCSP…
Read more

A Comprehensive Overview of PostgreSQL Query Processing Stages

Introduction When you send a query to PostgreSQL, it will undergo several processing stages in the backend. Each of these stages has different responsibilities to ensure that you receive correct responses in shortest amount of time possible. Yes, they can be quite large and complex to fully understand but I believe it is important for…
Read more

JSON in PostgreSQL: Revolutionizing Data Flexibility in Your Database

Introduction In this blog, we’ll go over PostgreSQL’s implementation and handling of JSON objects. Having some experience with Linux, Postgres, and JSON is necessary as we’ll not only be going over these new features but also how to implement them. This blog was written using PostgreSQL 16 (Development Version) running on Ubuntu 23.04. First I’ll…
Read more

How to setup TLS connection for PostgreSQL

1. Overview PostgreSQL is a robust open-source database management system, earning the distinction of DBMS of the Year 2023. Users choose for PostgreSQL due to various reasons, such as SQL support, Query Optimization, and Reliability, etc. In this blog, I will guide you through the process of setting up a TLS connection between a PostgreSQL…
Read more

Experiencing a specific table recovery using file-level backup

1. Overview In previous blog post, we discussed the process of performing backup and restore operations using pg_rman. pg_rman is a user-friendly tool that supports full and incremental backups and restores for PostgreSQL, operating at the file level. In this blog, I will guide you through a potential method for restoring a specific table using…
Read more

New in PostgreSQL 16: Bi-Directional Logical Replication

Introduction In this blog, we’ll be going over some more advanced topics new in Postgres 16. Having some experience with Linux, Postgres, and SQL is necessary as we’ll not only be going over these new features but also how to implement them. This blog was written using PostgreSQL 16 (Development Version) running on Ubuntu 23.04.…
Read more

Explore Table Access Method Capabilities: How Data Insertion is Handled

Introduction In previous blogs, we talked about an overview of PostgreSQL’s table access method API here , and how sequential scan is handled within this API here. Today we will explore how data insertion is handled by the table access method API. APIs Involved The main API for handling insertion is called tuple_insert, which points…
Read more

Experiencing WAL REDO in PostgreSQL

1. Overview Write-Ahead Logging (WAL) is a standard method used in PostgreSQL to ensure data integrity. Many key features rely on this WAL design, such as streaming replication, and Point-In-Time recovery, etc. While there is a detailed online book explaining how WAL works in PostgreSQL, there is a lack of detailed documentation or blogs describing…
Read more

Explore Table Access Method Capabilities: Sequential Scan Analyzed

Introduction In my previous blog about table access method here, we discussed the basis of PostgreSQL’s table access method APIs and the difference between a heap tuple and Tuple Table Slot (TTS). In this blog, let’s talk more about the particular API calls that helps PostgreSQL core to achieve sequential scan. APIs Involved To achieve…
Read more

Managing PostgreSQL Like a Pro: A Kubernetes-based pgAdmin Tutorial

Introduction This blog is aimed at beginners trying to learn the basics of PostgreSQL, pgAdmin and Kubernetes but already have some experience under their belt. For this tutorial, we will assume you have PostgreSQL correctly installed on Ubuntu. All of these steps were done using PostgreSQL 16 (development version), minikube v1.26.3 as the Kubernetes implementation,…
Read more

A quick glance at pg_basebackup compression

1. Overview pg_basebackup is a powerful tool for creating physical backups of PostgreSQL database clusters. Unlike pg_dump, which generates logical backups, pg_basebackup captures the entire cluster state. These backups are crucial for point-in-time recovery or for setting up a standby server. 2. Backup Compression Efforts to enhance backup performance have led to innovations like parallel…
Read more

Quick Overview of PostgreSQL’s Table Access Method

What is a Table Access Method? Table access method is the interface between the PostgreSQL core and data storage management. Since PostgreSQL 12, it is possible to define your own custom table access method that stores data in custom forms by implementing over 45 interface API callback functions. Generally, implementing all of the interface API…
Read more

Getting Started with Psycopg2: Python’s PostgreSQL Adapter

Introduction This blog is aimed at beginners trying to learn the basics of PostgreSQL and Python but already have some experience under their belt. For this tutorial, we will assume you have PostgreSQL correctly installed on Ubuntu. All of these steps were done using PostgreSQL 16 (development version) and Python 3.11.4 on Ubuntu 23.04. We’ll…
Read more

Overview of PostgreSQL Foreign Data Wrapper (FDW)

Introduction A Foreign Data Wrapper (FDW) in PostgreSQL is an extension that allows you to access and manipulate data stored in external data sources as if they were tables within your PostgreSQL database. FDWs enable PostgreSQL to integrate with various data storage systems, both relational and non-relational, and present the data in a unified manner…
Read more

Exploring Various Ways to Manage Configuration Parameters in PostgreSQL

1. Overview PostgreSQL provides a configuration file postgresql.conf for end users to customize parameters. You may need to change some parameters to tune performance or deploy a PostgreSQL server in your working environment. In this blog post, we’ll explore different ways to manage these parameters. 2. Managing Parameters in Different Ways PostgreSQL supports various parameters…
Read more