Neues vom PostgreSQL Planet

Neues vom PostgreSQL Planet Feed abonnieren
Planet PostgreSQL
Aktualisiert: vor 2 Stunden 29 Minuten

Pavel Stehule: new pspg - version 3.0.0 with named pipe support

6. April 2020 - 21:15
Today I released version 3.0.0. The ui is same like older versions. New is a possibility to communicate with client via named pipe. Now pspg can work well with redirected content (\o command in psql and psql watch mode is supported too.

#create named pipe
mkfifo ~/pipe

#use psql
[pavel@nemesis ~]$ psql
psql (13devel)
Type "help" for help.

postgres=# \o ~/pokus
postgres=# select * from obce limit 10;
postgres=# --redirect content to pipe

Álvaro Hernández: JOINs the hard way: comparing MongoDB and Postgres

6. April 2020 - 20:20
JOINs the hard way: comparing MongoDB and Postgres

Dr. Michael Stonebraker and I have just published a new blog post on our series comparing MongoDB and Postgres. This second post compares JOINing data in MongoDB and Postgres, and follows on our previous post, “Schema Later” Considered Harmful.

Lætitia AVROT: Debian Default Logging Explained

4. April 2020 - 17:42
I really love what Debian did to make Postgres administration tasks easier. However, when I was writting the article “What’s wrong with Postgres?", I found something unexpected in my Ubuntu server. I was trying to find Postgres logfile, so I tried postgres=# select pg_current_logfile(); pg_current_logfile -------------------- (1 row) But, I know that, by default, I’ll find my logs under /var/log/postgresql, and sure enough: [root@elinor] ll /var/log/postgresql/ drwxrwxr-t 2 root postgres 4096 Apr 4 13:08 .

Julien Rouhaud: New in pg13: Monitoring the query planner

4. April 2020 - 14:06

Depending on your workload, the planning time can represent a significant part of the overal query procesing time. This is especially import in OLTP workload, but OLAP queries with numerous tables being joined and an aggressive configuration on the JOIN order search can also lead to hight planning time.

Hubert 'depesz' Lubaczewski: Changes on

3. April 2020 - 6:03
I just released new version of Pg::Explain Perl library that is handling parsing of plans for There are quite a lot of changes, but mostly internal, but one thing is pretty interesting – Pg::Explain, and because of this also should be able to parse plans with arbitrary values of border, linestyle, format, unicode_border_linestyle, … Continue reading "Changes on"

Pavel Stehule: simple monitoring of PostgreSQL incremental statistics

2. April 2020 - 21:35
Everybody should to know PostgreSQL's stats views: pg_stat_user_tables, pg_stat_database. There are lot of interesting values. For better interpretation we need a difference of these values in some time. It is impossible to get previous value with clean SQL, but in Postgres we can use PLpgSQL, and we can use custom configure variables as session variables. I wrote function buffer, that returns difference between current value and buffered, and save current value to buffer:

CREATE OR REPLACE FUNCTION public.buffer_dif(text, bigint)
RETURNS bigint
LANGUAGE plpgsql

Hubert 'depesz' Lubaczewski: Waiting for PostgreSQL 13 – Allow pg_stat_statements to track planning statistics.

2. April 2020 - 21:26
On 2nd of April 2020, Fujii Masao committed patch: Allow pg_stat_statements to track planning statistics.   This commit makes pg_stat_statements support new GUC pg_stat_statements.track_planning. If this option is enabled, pg_stat_statements tracks the planning statistics of the statements, e.g., the number of times the statement was planned, the total time spent planning the statement, etc.

Devrim GÜNDÜZ: Updates from the PostgreSQL project

1. April 2020 - 11:14

Given the recent Covid-19 outbreak, PGDG (PostgreSQL Global Devrim Group) recently announced a few updates to the project: Continue reading "Updates from the PostgreSQL project"

Kaarel Moppel: Performance differences between normal and generic audit triggers

1. April 2020 - 9:30

Recently I was talking in a more general way about some common auditing / change tracking approaches for PostgreSQL…but it also made me curious, how it roughly looks from the performance side?

Álvaro Hernández: Postgres Ibiza 2020: cancelled. Mark 2021 in your calendars!

31. März 2020 - 23:09
Postgres Ibiza 2020: cancelled. Mark 2021 in your calendars!

The World has been taken by storm by the COVID-19. Our thoughts are with the people who, for any reason, are suffering from this. We are with you.

Pavel Stehule: Postgres 13, new family of polymorphic types - anycompatible

31. März 2020 - 22:02
One very interesting PostgerSQL's feature are polymorphic types. It's interesting mainly for people who writes extensions.

Example: I can write own isnull function:
CREATE OR REPLACE FUNCTION isnull(anyelement, anyelement)
RETURNS anyelement AS $$
SELECT coalesce($1, $2)
$$ LANGUAGE sql;

postgres=# SELECT public.isnull(NULL, 1);
│ isnull │
│ 1 │
(1 row)

postgres=# SELECT public.isnull(NULL, CURRENT_DATE);
│ isnull │

Ibrar Ahmed: Useful Queries For PostgreSQL Index Maintenance

31. März 2020 - 17:29

PostgreSQL has a rich set of indexing functionality, and there are many articles explaining the syntax, usage, and value of the index. In this article, I will write basic and useful queries to see the state of database indexes. People develop databases and after some time, when there is a demand to do changes in the architecture of software, they forget to do the previous indexes’ cleanup. This approach creates a mess and sometimes slows down the database because of too many indexes.

Hubert 'depesz' Lubaczewski: Waiting for PostgreSQL 13 – Enable BEFORE row-level triggers for partitioned tables

31. März 2020 - 9:58
On 18th of March 2020, Alvaro Herrera committed patch: Enable BEFORE row-level triggers for partitioned tables   ... with the limitation that the tuple must remain in the same partition.   Reviewed-by: Ashutosh Bapat Discussion: So, I never made triggers on partitioned tables, so I was kinda sure that they would work anyway.

Lætitia AVROT: Can't connect to Postgres

30. März 2020 - 19:42
This is kind of frustrating… You’ve been at it for the best part of the day and you can’t figure it out. You already tested that Postgres was running and that you were attempting to connect to the right port and you checked the host several times but you still can’t connect to Postgres! I won’t give you a full review of all possible causes, because they are too many and I’m pretty sure I will forget some, but here ar some clues for you.

David Z: PostgreSQL GSSAPI Authentication with Kerberos part-3: the status of authentication, encryption and user principal

30. März 2020 - 17:15
1. Overview

In previous two blogs, we explained how to setup Kerberos, and how to configure PostgreSQL to support GSSAPI user authentication. This blog will be focusing on how to check GSSAPI authentication, encryption and user principal information when given different connection options.

Andreas 'ads' Scherbaum: Sarah Conway Schnurr

30. März 2020 - 16:00
PostgreSQL Person of the Week Interview with Sarah Conway Schnurr: I am from Southern California, where I’ve spent most of my free time hiking in the beautiful local deserts and pursuing many creative endeavors. Primarily, I am a software engineer & front-end website developer at Crunchy Data, a violin teacher & violin/viola performer, as well as a creator of zero-waste and all-natural homemade goods. I am also the co-parent of four beautiful cats, as well as the many adoptive fosters, strays, and friendly neighborhood cats that my husband and I visit on our daily walks.

movead li: PostgreSQL_WAL_Evolution_Part_II

30. März 2020 - 5:10

This is the second part of the topic, the more historical version is described in the previous part, and here is the vitality of wal in PostgreSQL which born replication、logical replication and more performance related configure, let’s continue to redo it.

1. Replication(V9.0)

Replication is implemented here, and many corresponding GUC are added for replication. Corresponding to warm standby, replication can also be called hot standby, which helps to achieve the data synchronization using WAL record between the primary and the standby.

movead li: PostgreSQL_WAL_Evolution_Part_I

30. März 2020 - 5:10

WAL is one of the most important parts of PostgreSQL., WAL records all the database activity. Hense we can regard wal as a change roadmap of the history of PostgreSQL database, and the crash recovery, logical replication etc aren’t possible without WAL. The following picture describes the various wal related GUC (based on PG12) involved in the production and use of wal logs. It is very important for us to know the meaning of each parameter to optimize database performance and configure high availability cluster..

Dan Langille: creating a Nagios check on a PostgreSQL table

29. März 2020 - 20:53
When I started this, I thought it was be straight forward and take only a few hours at most. It turned into multiple hours over two days. This is part two of a two-part post. The first part dealt with changing a database column from time to timestamp. In this post: PostgreSQL 12.2 Nagios 3.5 [...]

Dan Langille: Changing a column type

29. März 2020 - 19:46
The plan for today was not decided until after I got out of bed. I sat down with coffee and looked through the list of tasks. I found one which appealed: monitor the cache_clearing_ports & cache_clearing_dates tables This is part one of a two-part story. The first part deals with fixing a database table before [...]