Neues vom PostgreSQL Planet

I decided to start out this year by looking into my notes from last year and extracting from them a small set of Postgres tips for you. This might not be, strictly speaking, fresh news… but I guess the average RAM of tech workers is about 64KB or so nowadays, so some repetition might not be such a bad thing.

PostgreSQL Person of the Week Interview with Marc Linster: I was born and raised in Luxembourg. After getting a computer science degree at the University of Kaiserslautern, I joined the Gesellschaft für Mathematik und Datenverarbeitung (today part of Fraunhofer Gesellschaft) working on expert systems and AI. In 1992, I moved to the US, working in the software and consulting business, always with a focus on data and databases. After four years at Polycom, where I had the opportunity to work on their Video as a Service, I joined EDB in 2013.

On 17th of January 2021, Magnus Hagander committed patch: Add pg_stat_database counters for sessions and session time   This add counters for number of sessions, the different kind of session termination types, and timers for how much time is spent in active vs idle in a database to pg_stat_database.

GNU Emacs is great! I can prepare my slides with PostgreSQL snippets of code and results.

We started with problems related to isolation, made a digression about low-level data structure, then discussed row versions and observed how data snapshots are obtained from row versions.

A while ago someone wrote on irc that PostgreSQL has built in hard limit to number of partitions. This tweet was linked as a source of this information. Decided to check it. First, let's check the claim itself. Using simple psql script: $ CREATE TABLE test_ranged ( id serial PRIMARY KEY, payload TEXT ) partition … Continue reading "Are there limits to partition counts?"

I finished and released new version of pspg pager. New release has possibility to export content in CSV, TCVS, formatted text or INSERT format to file or clipboard.

1. Introduction

There are many approaches for PostgreSQL to retrieve the data back to the user. Depending on the user’s input query, the planner module is responsible for selecting the most optimum approach to retrieve the requested data. Sequential scan is one of these approaches that is mostly selected when the user requests a large volume of data (for example, “SELECT * from tablename;”) or when a table has no index declared. Sequential scan is mostly handled by the table access method API within PostgreSQL and heap access method is the default one PostgreSQL uses today.

Every now and then someone complains (me included) that PostgreSQL doesn't have job scheduler. This is true, to some extent. I'll try to show you couple of approaches to solving this particular problem. First option, available to anyone (as long as you're running on some kind of Unix/Linux) is system Cron. Virtually every Unix/Linux system … Continue reading "How to run some tasks without user intervention, at specific times?"

A nice addition to the pgenv PostgreSQL binary manager.

For obvious reason the FOSDEM this year is an online event. The staff is building from scratch an infrastructure in order to deliver the speaker’s videos in a virtual environment.

The catch is that all the talks must be pre recorded and uploaded via pentabarf, the software historically used by FOSDEM to manage the talk submissions.

What follows is my experience in recording,uploading and submitting the video for my upcoming talk.

On 6th of January 2021, Tom Lane committed patch: Add idle_session_timeout.   This GUC variable works much like idle_in_transaction_session_timeout, in that it kills sessions that have waited too long for a new client query. But it applies when we're not in a transaction, rather than when we are.   Li Japin, reviewed by David Johnston … Continue reading "Waiting for PostgreSQL 14 – Add idle_session_timeout."

Greetings friends! We have come to our final blog post in my series about the data science workflow using PostgreSQL. In the last blog post, we used PL/R to create a function which returns the output from a logistic regression model trained on our fire data.

On 6th of January 2021, Tomas Vondra committed patch: Report progress of COPY commands   This commit introduces a view pg_stat_progress_copy, reporting progress of COPY commands. This allows rough estimates how far a running COPY progressed, with the caveat that the total number of bytes may not be available in some cases (e.g. when the … Continue reading "Waiting for PostgreSQL 14 – Report progress of COPY commands"

Data types are an important topic in any relational database. PostgreSQL offers many different types, but not all of them are created equal. Depending on what you are trying to achieve, different column types might be necessary. This post will focus on three important ones: the integer, float and numeric types. Recently, we have seen a couple of support cases related to these topics and I thought it would be worth sharing this information with the public, to ensure that my readers avoid some common pitfalls recently seen in client applications.

A table in PostgreSQL has a relfilenode value, which specifies the file name of the table on disk (except foreign table and partition table). In general, this value can be found in the relfilenode field of the pg_class table, but there are some specific tables whose query result in the relfilenode field is 0. This blog will explore the kernel processing of these special tables relfilenode.

PostgreSQL Person of the Week Interview with Gunnar ‘Nick’ Bluth: I’m Gunnar (aka. “Nick”) Bluth, born in ‘73, married with two grown-up kids. I grew up in Münster/Westfalen in Germany, where I repatriated to in 2016 after living near Frankfurt/M. for 13 years. So I’ll probably also rot here ;-)

If you work with databases you are probably familiar with B-Tree indexes. They are used to enforce unique and primary-key constraints, and are the default index type in most database engines. If you work with text, geography or other complex types in PostgreSQL, you may have dipped your toes into GIN or GIST indexes. If you manage a read intensive database (or just follow this blog), you may also be familiar with BRIN indexes.

How to search directly into the PostgreSQL documentation from your Plasma desktop.

How to search directly within the PostgreSQL documentation from your Firefox web browser.