Database Administration & Monitoring

Declarative partitioning was introduced in PostgreSQL 10 and since then has improved quite much over the last releases. Today almost everything is there what you would expect from such a feature: You can partition by range, list and hash Attaching and detaching partitions Foreign keys Sub-partitioning Indexing and constrains on partitions Partition pruning What is missing, is the possibility to let PostgreSQL create partitions automatically. With this patch this will finally be possible for hash…

A PostgreSQL instance (or cluster) can contain many databases, three of them (template0, template1 and postgres) are there by default. Over the last years we trained many people on PostgreSQL Essentials and there have been mainly two points that needed more clarification when it comes to catalogs and the postgres default database: Does the postgres default database define the catalog and somehow is the master database? What exactly is in the global catalog? In this…

It is a common misunderstanding that VACUUM FULL saves you from running out of disk space if you already have space pressure. Running a VACUUM FULL temporarily requires at least double the space, as the table (and the indexes on the table) get completely re-written. PostgreSQL 14 will probably come with a solution for that as this patch introduces the possibility to move relations from one tablespace to another, when either CLUSTER, VACUUM FULL or…

In the last post we had a look at an interesting patch (schema variables) that is currently waiting to be committed for PostgreSQL 14. Another patch, that currently is in the same state is named [PATCH] Add features to pg_stat_statements. As this does not give much information what the patch is about, here a short summary: When you reach the maximum number of allowed statements to track, pg_stat_statements will de-allocate entries to free space for…