Introduction This blog is aimed at beginners trying to learn the basics of PostgreSQL and PGPool 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 PGPool 4.4.3 on Ubuntu 23.04. We’ll go over an example of setting up multiple PostgreSQL servers, one primary and two replicas, and how they can be balanced using PGPool. Setting Up Our overall architecture will be PGPool sitting in front of 3 PostgreSQL instances, 1 primary and 2 replicas. It will look something like this: To get this setup we first have to create our primary database: $ mkdir data/ $ initdb data/ Set up the primary as you would to allow for replication (edit the correct postgresql.conf and pg_hba.conf files). If you want to learn more about setting up database replication in PostgreSQL, see my previous blog here. Once set up, start the database: $ pg_ctl -D data/ -l logfile start Follow these steps again to create both replicas and set them up to replicate the primary database. Lastly, we need to start PGPool in order to pool our databases together. We’re going to use the following configuration file, pgpool.conf: listen_addresses = 'localhost' port = 9999 pcp_listen_addresses = 'localhost' pcp_port = 9898 load_balance_mode = on #Backend 0 backend_hostname0 = 'localhost' backend_port0 = 5432 backend_weight0 = 0.2 backend_data_directory0 = '/home/tristen/pg/data' backend_flag0 = 'ALWAYS_PRIMARY' backend_application_name0 = 'primary' #Backend 1 backend_hostname1 = 'localhost' backend_port1 = 5433 backend_data_directory1 = '/home/tristen/pg/data1' backend_weight1 = 0.4 backend_flag1 = 'ALLOW_TO_FAILOVER' backend_application_name1 = 'server1' #Backend 2 backend_hostname2 = 'localhost' backend_port2 = 5434 backend_data_directory2 = '/home/tristen/pg/data2' backend_weight2 = 0.4 backend_flag2 = 'ALLOW_TO_FAILOVER' backend_application_name2 = 'server[...]

1.0 Introduction Catalog table is a set of special tables in PostgreSQL for storing metadata information of the database. These tables record the definition, structure, access rights and other important information of database objects. In your PostgreSQL journey, you may not have a chance to explore all of them, but just be aware that they are there, and most of time, they should have everything you need to know about your database instance. There is also a concept of catalog views, which provides a higher level of abstraction for easier viewing and understanding of database structures and objects returned by catalog tables. We normally use catalog views to access catalog information rather than accessing catalog tables directly. Today I will show you how to customize catalog views by going through PostgreSQL source code (Version 15). This would also be a good exercise for anyone wishing to learn more about PostgreSQL development. 2.0 Catalog View Example We will use pg_stat_ssl as example, and we will be adding 2 more columns (not_before and not_after) to its output From: postgres=# select * from pg_stat_ssl; pid | ssl | version | cipher | bits | client_dn | client_serial | issuer_dn ------+-----+---------+--------+------+-----------+---------------+----------- 7448 | t | xxx | xxx | xxx | xxx | xxx | xxx To: postgres=# select * from pg_stat_ssl; pid | ssl | version | cipher | bits | client_dn | client_serial | issuer_dn | not_before | not_after ------+-----+---------+--------+------+-----------+---------------+-----------+------------+---------- 7448 | t | xxx | xxx | xxx | xxx | xxx | xxx | xxx | xxx 3.0 Approach 3.1 Find the schema definition of pg_stat_ssl: This can be done using global search function of your code editor, like Eclipse。 search for the pg_stat_ssl keyword, we should find it at src/backend/catalog/system_view.sql. Let’s add 2 more outputs called not_before and not_after: CRE[...]

How a simple snippet can allow you to save time and improve your PostgreSQL code quality. Using Emacs and YASnippet to quickly write PostgreSQL functions I love Emacs, and I also love PostgreSQL. Whenever I have to write PostgreSQL code, I use Emacs. Emacs can help me improving code quality, for example to write PostgreSQL functions. I use YASnippet as a package to provide the basic template for a PostgreSQL function. A PostgreSQL Function Template (in action) Before explaining the concept, let’s see a couple of short videos that demonstrate my snippet in action: A PostgreSQL Function Template (the code) The code for the template is the following one (I may change some bits here and there as time goes by): # -*- mode: snippet -*- # name: PostgreSQL Function # key: function # -- -- -- Function ${1:function_name} -- Schema ${2:public} -- -- Description: -- $3 -- -- Return Type: ${4:VOID} -- CREATE ${5:OR REPLACE} FUNCTION $2.$1($6) RETURNS $4 AS $CODE$ $0 $CODE$ LANGUAGE ${8:plpgsql} VOLATILE ; The preamble is used from Emacs to understand the template name. The following, is SQL code that works as a template for a function. Every $n placeholder is a tab stop that can be used to place the cursor within the text. For example, ${1:function_name} is the first (1) tab stop, that present the default text function_name that is overwritten as I type in something. The name of function is then automatically replaced into the other $1 placeholder. Note, how I first begin from the documentation, and then jump to the function code. This is a very important added value: writing the documentation first I ensure every piece of code will have at least some documentation, and thanks to the placeholders, what I write in the documentation is used to name the function and its return type. Conclusions Emacs and YASnippet can be very powerful to help writing PostgreSQL code. While this post focuses on functions, it is possible to provide templates[...]

CloudNativePG has a native mechanism that provides an automated way to manage physical replication slots in a high availability Postgres cluster, with one or more hot standby replicas, and lets them survive a failover. This article describes why this is important for self-healing in Kubernetes. [Continue reading...]

On 29th of August 2023, Daniel Gustafsson committed patch: Allow \watch queries to stop on minimum rows returned   When running a repeat query with \watch in psql, it can be helpful to be able to stop the watch process when the query no longer returns the expected amount of rows. An example would be … Continue reading "Waiting for PostgreSQL 17 – Allow \watch queries to stop on minimum rows returned"

In Oracle, right or wrong, I was always taught to try to avoid “row movement” between partitions due to the general thought that the extra workload of a “delete” + “insert” (rewrite of the row) should be avoided due to the extra I/O, index fragmentation and the associated risks of a migrating ROWID in the cases where the app developers might have used it in their code (now that’s a whole other problem). Oracle didn’t even let you do it by default. Table by table, you had to explicitly set: alter table [table name] enable row movement; Now, you also had to set this to do table reorganizations such as “alter table…. shrink space / shrink space compact” so it wasn’t something unheard of. However, when a customer recently explained to me that they were going to partition a PostgreSQL table and update the partition key column from null to the date when the row got processed, my mind immediately went to the space of that’s probably bad……. RIGHT?? Well, once I thought about it, maybe it’s not all that bad due to the way MVCC and the subsequent VACUUM operations occur in PostgreSQL. The only thing I could think of that might be a factor is that you would lose any potential benefit of HOT (Heap-Only-Tuple) updates since the row will no longer be part of the original partition, seeing that partitions in PostgreSQL are just another table. The benefit though is that I could limit my vacuum operations to one single partition and SMALLER table. A plus for this customer. **** Note: An implementation like this does not necessarily follow best practices with regards to partitioning. That being said, I was attempting to validate the idea with regards to how PostgreSQL MVCC behaves. That being said, I wanted to at least be able to prove / disprove my thoughts with a demonstration, so off to PostgreSQL we go. First let’s create a simple partitioned table and use pg_partman to help: CREATE TABLE partman_test.partman_partitioned ( id integer not null, val varchar(20) not null, [...]

Ivan Panchenko wrote a great article about why and how businesses should start using Postgres. It covers Postgres's structural benefits like vendor independence, code quality, and technology like NoSQL. It also covers the challenges that could be faced, like cost, time, and in-house expertise. When I saw "Not controlled by a single vendor" in both the benefits and challenges sections, I knew he was on the right track!

Is it a Muffin or a Chihuahua? I tried to solve the famous meme using #PostgreSQL pgvector extension. The results are really impressive! Do you want to try? I created a Python notebook that can give you a jump start, check it out at https://go.aiven.io/muffin-vs-chihuahua You can find the training dataset at https://www.kaggle.com/datasets/samuelcortinhas/muffin-vs-chihuahua-image-classification You can find a managed PostgreSQL for free at https://go.aiven.io/francesco-signup

Exclusion constraints are a feature that is not very well known, but can be used to implement highly sophisticated constraints. A few years ago, Hans wrote his blog post about EXCLUDE USING GIST… WITH. Recently we received a note from someone dealing with a very tricky problem concerning exclusion constraints: (many thanks to @necrotikS at YouTube for a very good problem to solve!) Q: I think this is a very difficult topic. I have the following columns: status, which can be either “SCHEDULED”, “PENDING” or “CANCELED”, field_id and the duration, which is a tstzrange type. I need a constraint that do not allow creating overlapping appointments for the same field ONLY IF THE APPOINTMENT STATUS IS “SCHEDULED”. If there are appointment with status “PENDING” or “CANCELED”, there could be other overlapping appointments. For example: Row 1 (Field 1, "SCHEDULED", '[2023-01-01 08:00:00, 2023-01-01 09:00:00)') Tries to create overlapping appointment with any status, should not allow, since there’s a “SCHEDULED” appointment, for example: INSERT (Field 1, "PENDING", '[2023-01-01 08:00:00, 2023-01-01 09:00:00)') OR (Field 1, "PENDING", '[2023-01-01 08:00:00, 2023-01-01 09:00:00)') OR (Field 1, "CANCELED", '[2023-01-01 08:00:00, 2023-01-01 09:00:00)') However, if we update the Row 1 to status “CANCELED” or “PENDING”, when the user tries to create the previous overlapping appointment, it should allow it, since the status is not “SCHEDULED” anymore. Is it possible to achieve this logic using the examples demonstrated in the video? A: It should be fairly easy by using a partial index, i.e. just append the following: where (status = 'SCHEDULED') …to the index definition Q: Yes, I had already tried using this WHERE clause, however, the only thing that it changes is that it checks for overlapping when the new row has status “SCHEDULED”. But I need the check to run for any status, and throw the error if overlapping with any “SCHEDULED” appointments. With this WHERE you told me [...]

We continue our exploration of PostgreSQL build system performance. A long time ago, I wrote an article about how to optimize the performance of make install. This was quite helpful, as it reduced the time from 10.493 s by default to 1.654 s with some tweaks (6x faster). Now, with different hardware, a much newer PostgreSQL, and a new build system looming, let’s take another look. First, let’s check the time for a standard make install run and then how the optimizations suggested in the old article work. Command macOS 13 Ubunty 22.04 make install 2.384 s 1.472 s make install enable_nls=no 1.634 s 1.090 s make install -s 2.342 s 1.326 s make install -jN 0.984 s 0.666 s make install enable_nls=no -s -jN 0.784 s 0.464 s Ok, the default is already much better than in the olden days. But with some additional techniques applied it’s still 3x faster! There are two tricks suggested in the old article that I did not show here: First, it suggested using dash instead of the default shell bash at the time. The Ubuntu system used here already uses dash by default. I tried dash on the macOS system, but it performed much worse (weird?). Second, the old article suggested overriding the install program. That information is obsolete, because configure has been changed — in response to that article — to pick up an available install program automatically (commit 9db7ccae20). Now, in order to compare this to Meson below, we need to check make install-world-bin, so that we install the same set of files. So let’s get some baselines with that command. [...]