January 27, 2021 | www.highgo.ca
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| Highgo Software Inc. - Enterprise PostgreSQL Solutions
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| Highgo Software Inc. - Enterprise PostgreSQL Solutions
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| Highgo Software Inc. - Enterprise PostgreSQL Solutions
Cary is a Senior Software Developer in HighGo Software Canada with 8 years of industrial experience developing innovative software solutions in C/C++ in the field of smart grid & metering prior to joining HighGo. He holds a bachelor degree in Electrical Engineering from University of British Columnbia (UBC) in Vancouver in 2012 and has extensive hands-on experience in technologies such as: Advanced Networking, Network & Data security, Smart Metering Innovations, deployment management with Doc...| www.highgo.ca
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| Highgo Software Inc. - Enterprise PostgreSQL Solutions
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: Parse Analyze Rewrite Plan Execute I wrote another blog to briefly explain the responsibility of each query processing stage. You can find it here. In this| Highgo Software Inc. - Enterprise PostgreSQL Solutions
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| Highgo Software Inc. - Enterprise PostgreSQL Solutions
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: Parse Analyze Rewrite Plan Execute I wrote another blog to briefly explain the responsibility of each query processing stage. You can find it here. In| Highgo Software Inc. - Enterprise PostgreSQL Solutions
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| Highgo Software Inc. - Enterprise PostgreSQL Solutions
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| Highgo Software Inc. - Enterprise PostgreSQL Solutions
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| Highgo Software Inc. - Enterprise PostgreSQL Solutions
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| Highgo Software Inc. - Enterprise PostgreSQL Solutions
Introduction Following on my previous blog here, which outlines the procedure to deploy a metric server cron job to monitor an already deployed PostgreSQL primary and standby nodes on Kubernetes, this blog aims to show the procedure to deploy a pgpool node that is able to load balance write requests to the primary and read| Highgo Software Inc. - Enterprise PostgreSQL Solutions
Introduction Recently I had an opportunity to look into deploying PostgreSQL and pgpool on Kubernetes. The deployment is straightforward, but I also need to obtain the metrics information such as CPU and memory usages that each deployed pod is using. There are several ways to do this, but today I am sharing my way, which| Highgo Software Inc. - Enterprise PostgreSQL Solutions
1.0 Introduction TLS is one of the most commonly used security protocol in most applications but also least understood. In this blog, I will briefly explain the concept of TLS and how it can be configured to Postgres version 15 compiled with compatible OpenSSL library. 2.0 PostgreSQL Server Side Settings These are the TLS settings| Highgo Software Inc. - Enterprise PostgreSQL Solutions
1.0 Introduction My colleague, David, recently published a post "Global Index, a different approach" that describes the work that we are doing to implement global unique index in an approach that does not change current PostgreSQL's partitioning framework, while allowing cross-partition uniqueness constraint. To implement this, we must first know how PostgreSQL currently ensures uniqueness| Highgo Software Inc. - Enterprise PostgreSQL Solutions
1.0 Introduction Recently I have been involved in creating a solution that guarantees cross-partition uniqueness within a partitioned table consisting multiple child tables. Some people refer to this feature as a global index and there has been some discussion about it in the email thread here. Though the idea is good, the approach sparks a| Highgo Software Inc. - Enterprise PostgreSQL Solutions
1. Introduction PostgreSQL's MultiVersion Concurrency Control (MVCC) is an "advanced technique for improving database performance in a multi-user environment" according to Vadim Mikheev. This technique requires multiple "versions" of the same data tuple exist in the system governed by snapshots taken during different time periods. In other words, under such technique, it is PG's responsibility| Highgo Software Inc. - Enterprise PostgreSQL Solutions
1. Introduction PostgreSQL's 2 phase commit (2PC) feature allows a database to store the details of a transaction on disk without committing it. This is done by issuing PREPARE TRANSACTION [name] command at the end of a transaction block. When the user is ready to commit, he/she can issue COMMIT PREPARED [name] where [name] should| Highgo Software Inc. - Enterprise PostgreSQL Solutions
1. What Is Security? The word “Security” is a very broad concept and could refer to completely different procedures and methodology to achieve. Knowing what security means to your application is very important, so you could execute proper security practices and procedures to ensure the safety of your company's assets. Data compromises could often lead| Highgo Software Inc. - Enterprise PostgreSQL Solutions
1. Introduction I am working on a new PostgreSQL feature that redefines the way a tuple's visibility status is determined. The feature is working very nicely until I start doing a large SELECT query, which triggers PostgreSQL to spawn multiple parallel workers to process the request. When this happens, the feature I am working on| Highgo Software Inc. - Enterprise PostgreSQL Solutions
1. Introduction As an experienced PostgreSQL user, you may have a lot of experience in setting up streaming replication in your database clusters to make multiple backups of your data. But have you wondered how the standby is able to correctly determine if a tuple sent from the primary should be visible to the user| Highgo Software Inc. - Enterprise PostgreSQL Solutions