About PGConf.dev PGConf.dev is an annual developer event focused entirely on contributing to the PostgreSQL ecosystem, including core software development and community building. It serves as a primary hub for PostgreSQL hackers, maintainers, and ecosystem developers to meet, collaborate, and share knowledge. This year’s conference was hosted once again in Vancouver, which also happens to…
Read more
REPACK is a new PostgreSQL 19 feature for physically compacting a table by rewriting it into new storage. Like VACUUM, it deals with the space left behind by dead tuples, but it does so by building a fresh table file instead of mostly cleaning pages in place. Ordinary VACUUM can mark space reusableinside the table…
Read more
Partitioned tables are a core PostgreSQL feature, but one area still causes regular confusion—even for experienced users: How exactly does ALTER TABLE behave when partitions are involved? Does an operation propagate to partitions? Does it affect future partitions? Does ONLY do what it claims? Why do some commands work on parents but not on partitions—or…
Read more
A hash join is one of the most common join methods used by PostgreSQL and other relational databases. It works by building a hash table from the smaller input (called the build side) and then probing it with rows from the larger input (the probe side) to find matching join keys.Hash joins are especially efficient…
Read more
PostgreSQL and GB18030-2022 Support PostgreSQL supports GB18030 as a client-side encoding. A client-side encoding means you can set the encoding from a client application such as psql with: This tells the PostgreSQL backend that the client will send SQL statements encoded in GB18030. When the backend receives a statement, it converts the GB18030 byte stream…
Read more
Introduction PostgreSQL Conference was held on November 6th and 7th, 2024 as part of PASS Data Community Summit in Seattle. Bringing together database enthusiasts, developers, and industry professionals from around the world, the event offered an exceptional platform to delve into all things database. Although nearly a month has passed since I had the opportunity…
Read more
Introduction In previous blogs, we talked about an overview of PostgreSQL’s table access method API here , how sequential scan is handled within this API here, and how data insertion is handled here. Today in this blog, we will look closely into how PostgreSQL handles update. A successful update in PostgreSQL can be viewed as “insert a new…
Read more
Introduction In your PostgreSQL extension development based on the C language, you may come across a need to work with structured data like JSON. Naturally, you would probably introduce a third-party JSON parsers such as cJSON or libjannson in your extension. While they are powerful, easy to use and offering many features, it may be…
Read more
Introduction For the first time ever, PGConf.dev (previously known as PGCon from 2007 to 2023) has taken place in the picturesque city of Vancouver, Canada. This rebranded conference brings a fresh perspective and several new additions, elevating the experience beyond its predecessor. While PGCon was traditionally more developer-focused, attracting experienced developers, contributors, and researchers from…
Read more
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. There is one that…
Read more
1. Overview In this blog post, we will guide you through the process of integrating IvorySQL, an open-source database built on PostgreSQL, into Neon Autoscaling Platform. Throughout this guide, we’ll walk you through each step, providing clear instructions and demonstrations. 2. What is IvorySQL “IvorySQL is advanced, fully featured, open source Oracle compatible PostgreSQL with…
Read more
What is Visibility? Simply put, the visibility refers to whether a row of data (Heap Tuple by default) should be displayed to the user in certain states, backend processes, or transactions. For example, MVCC (Multi-Version Concurrency Control) is a method in which each write operation creates a “new version” of the data while retaining the…
Read more
1. Overview In my previous blogs, I discussed Setting up a debugging environment in PostgreSQL to better understand OpenSSL APIs, Enhance PostgreSQL TLS Security with OCSP Stapling, and How to setup TLS connection for PostgreSQL. In this blog, I will share a simple procedure about How to run SSL/TLS regression tests in PostgreSQL. 2. Postgres…
Read more
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
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
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
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
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
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
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
Recent Comments