Neues vom PostgreSQL Planet

Data isn’t only about storage and accumulation – sometimes it’s also about deletion, cleanup and archiving. In SQL there’s more than one way to empty a table. Two essential methods are available:

PostgreSQL Person of the Week Interview with Chris Travers: I guess the best way to answer this is to say I am from the US. I moved around a lot as a kid – California, Michigan, Utah, Washington State. In 2010, I moved abroad, and have not moved back. Since then I have lived in Indonesia, Sweden, and Germany. I am now living in Indonesia again.

It's the easiest thing in the world to put a timestamp on a column and track when events like new records or recent changes happen, but what about reporting?

Binning data for large data sets like time series is a great way to let you group data sets by obvious groups and then use SQL to pull out a query that easily works in a graph.

Here's some PostgreSQL secrets that you can use to build up complete reports of time-based data.

This is part of an occasional series of blogs about PostgreSQL commitfest patches. Look at the series intro which also lists some ground rules and expectations for what to expect in my thoughts below. 🐘 Patch title and thread: Create visible links for HTML elements that have an id to make them discoverable via the … Continue reading "PostgreSQL Patch of Interest (To Me): Documentation Linking"

The table statistics of databases are a necessary dependency to choose the correct index: The number of rows matching a condition and whether an index or table scan is more efficient is estimated based on the stored distribution of values. But the statistics may become stale if you change a significant number of rows with bulk operations (e.g. importing contacts or deleting many rows). The skewed information may lead to not using any index in production until the database refreshes the statistics. You should manually trigger a statistics update to prevent that problem.

Last month, I was proud to talk about the internals of logical replication to a room full of PostgreSQL enthusiasts at PGConf India 2023. In case you missed it, let's go through it together.

Postgres 15 has a release note page full of items, and this commit is one of the fun parts from the point of view of a hacker:

Our team is proud to introduce a new pg_timetable v5.3 release!

This time we focused solely on implementing new features for logging.

Not everyone knows, but at since PostgreSQL 9.6, we have some built-in roles. Of course, there is always superuser (usually called postgres), but I'm not talking about it. I'm talking about magical roles that have names starting with pg_. Over the years we got more and more of them: pg_signal_backend (since PostgreSQL 9.6) pg_monitor (since … Continue reading "System roles – what, why, how?"

TL;DR; The 2023 PASS Data Community Summit will be held from November 14-17 at the Seattle Convention Center. For 2023 it is a fully in-person event. This year we are actively encouraging PostgreSQL experts to submit sessions during the CFP to help us build an inclusive event that spans multiple data platforms. CFP is open … Continue reading "PostgreSQL Content at the 2023 PASS Data Community Summit"

© Laurenz Albe 2023

In this blog post, and probably one or more following ones, I want to discuss how collations in PostgreSQL work internally. See also this previous post about the work we have done for collations in PostgreSQL 15. And there is even more coming together in PostgreSQL 16 right now, which we will talk about in the future.

If you google around for how to set work_mem in PostgreSQL, you’ll probably find something like:

To set work_mem, take the number of connections, add 32, divide by your astrological sign expressed as a number (Aquarius is 1), convert it to base 7, and then read that number in decimal megabytes.

So, I am here to tell you that every formula setting work_mem is wrong. Every. Single. One. They may not be badly wrong, but they are at best first cuts and approximations.

PostgreSQL Person of the Week Interview with Vincent Picavet: My name is Vincent Picavet. Originally an applied maths engineer, I created and run Oslandia since 2009, focusing on Open Source Geographical Information Systems 🌍, namely QGIS, PostGIS. I live in France in the Drôme valley, work remote since 2009, and speak French, English, Spanish and German. My favorite projection is an octant projection with Reuleaux triangles.

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 available in postgresql.conf related to TLS. Note that these parameters start with prefix SSL, which is a term used interchangeably with TLS (which is a newer term). Both refer to the same thing.

It is impossible to be present at two conferences simultaneously, but that’s why we have on-demand talks!

CitusCon is on April 18-19, and my brand new talk Implementing Temporal Features in PostgreSQL: SQL Standard and beyond will be available!

Backups in the database world are essential. They are the safety net protecting you from even the smallest bit of data loss. There’s a variety of ways to back up your data and this post aims to explain the basic tools involved in backups and what options you have, from just getting started to more sophisticated production systems.

This post begins with this commit added to Postgres 16:

I have decided to do a series of mini-articles on human factors in database operations.   This is the first, and covers fatigue.

pgec is an Apache licensed real-time in memory PostgreSQL logical replication cache with Redis, Memcached and REST API.