PostgreSQL

PostgreSQL 18: Add function to report backend memory contexts

Another great feature was committed for PostgreSQL 18 if you are interested how memory is used by a backend process. While you can take a look at the memory contexts for your current session since PostgreSQL 14, there was no(…)

PostgreSQL 18: Allow NOT NULL constraints to be added as NOT VALID

Before we take a look at what this new feature is about, let’s have a look at how PostgreSQL 17 (and before) handles “NOT NULL” constraints when they get created. As usual we start with a simple table: postgres=# select(…)

PostgreSQL 18: “swap” mode for pg_upgrade

When you want to upgrade from one major version of PostgreSQL to another you probably want to go with pg_upgrade (or logical replication). There are several modes of operations for this already: –copy: Copy the data(…)

PostgreSQL 18: Add “–missing-stats-only” to vacuumdb

Loosing all the object statistics after a major version upgrade of PostgreSQL with pg_upgrade is one of the real paint points in PostgreSQL. Collecting/generating the statistics can take much longer than the actual upgrade which is quite painful. A(…)

PostgreSQL 18: More granular log_connections

Many of our customers enable log_connections because of auditing requirements. This is a simple boolean which is either turned on or off. Once this is enabled and active every new connection to a PostgreSQL database is(…)

pgvector, a guide for DBA – Part2 indexes

Introduction In the last blog post we did setup a LAB with a DVDRental database mixed with Netflix data on top which we created some embeddings hosted on the same tables thanks to pgvector. In this second part, we(…)

PostgreSQL: Unlogged tables and backups

Recently we faced the following situation and have been asked to analyze the issue: A dump was loaded into a primary instance of PostgreSQL, but the data did not reach the replica. Tables which have been added after the dump(…)

pg_mooncake: (another) Columnar storage for PostgreSQL

A very, very long time ago I’ve written a blog about cstore_fdw, which brings columnar storage to PostgreSQL. This is now part of Citus and does not anymore come as a separate extension. While this still can be(…)

PostgreSQL 18: Virtual generated columns

This is about another feature which was committed for PostgreSQL 18: Virtual generated columns. Generated columns are available already, but they need to be “STORED”. This means the result of an expression is stored to disk and the result is(…)

pgvector, a guide for DBA – Part1: LAB DEMO

Introduction The first release of pgvector occurred in April 2021. Since then a lot has changed and is still changing. Even though we could say that the level of maturity of AI stacks and usage is currently really amazing, things(…)