This is my third blog about Stored Procedure support in PostgreSQL, the previous two blogs are reachable from the HighGo CA blogs site https://www.highgo.ca/author/ahsan-h/. The first blog was introduction and usage of Stored Procedures and its difference with Stored Functions, the second blog focussed on creating and using Procedures with Definer and Invoker rights. The…
Read more
Database monitoring is a very essential for most of the database system, it can prevent system crises, improve performance, database failure and auditing the traffic for identify any unwanted access. PostgreSQL has an extension called pg_stat_statements, which can capture a lot of query execution details. Recently, percona release a new extension called pg_stat_monitor which can…
Read more
1. Introduction In this blog post, I will talk about how to enable the generation of crash dump file (also known as core dump) and some common GDB commands to help a developer troubleshoot a crash-related issues within PostgreSQL and also other applications. Proper analysis of the issue normally will take time and certain degree…
Read more
1. Overview Previously, we discussed the MAIN fork file and corresponding extension at Heap file and page in details. This blog will explain a little bit more about the Free Space Mapping file and corresponding extension. 2. What is a Free Space Mapping file A Free Space Mapping, FSM, file is a file that keeps…
Read more
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
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
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
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
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
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
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
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
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
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
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
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
HighGo CA has released an update for its supported database system on all supported platforms. HighGo Software Canada (a subsidiary of HighGo Software Inc) has released an update for HighGo Postgres Sever (HG-PGSQL 1.4). It contains all the changes included in PostgreSQL community release version 12.4 plus additional useful features unique to HG-PGSQL. Refer to…
Read more
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
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
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
Recent Comments