System roles – what, why, how?

Was asked recently about optimization of interesting case. There was table like: =$ CREATE TABLE input_data ( category_id INT8, object_id INT8, interaction_ts timestamptz, interaction_type TEXT, interaction_count INT4 ); And there was a code that was grouping it all by sum()ing interaction_count per category, object, interaction_type, and...

Checking logs for pgdoc.link I noticed that some people where searching for system views, like pg_stat_activity, or pg_stat_all_tables in Pg 9.3. Now, these will work. This increased total number of known keywords from 1840 to 1883. Not much, but it's a progress 🙂

On 1st of November 2024, Michael Paquier committed patch: Add SQL function array_reverse() This function takes in input an array, and reverses the position of all its elements. This operation only affects the first dimension of the array, like array_shuffle(). The implementation structure is inspired by array_shuffle(), with a subroutine...

PostgreSQL documentation is, generally speaking, great. But it isn't the easiest thing to search in. Over the years I memorized urls to certain docs, but there is a limit to it. What's more, there are certain inconsistencies. For example – most pages that describe program have name that starts with app-. But not all. Some … Continue reading "New...

We recently hit an interesting case where planner picked wrong plan. Kinda. And figured it could be used to show how to deal with such cases. So, we have some databases on PostgreSQL 14 (yes, I know, we should upgrade, but it is LONG project to upgrade, so it's not really on the table now). … Continue reading "Case study: optimization of weirdly...