Neues vom PostgreSQL Planet

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.

Example:
#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

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.

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 .

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.

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

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

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.

Hi,

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"

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?

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.

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 │
╞════════════╡

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.

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: https://postgr.es/m/20200227165158.GA2071@alvherre.pgsql So, I never made triggers on partitioned tables, so I was kinda sure that they would work anyway.

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.

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.

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.

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.

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..

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 [...]

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 [...]