Sammlung von Newsfeeds

My own first book on PostgreSQL is gaining more and more attention, and this is agood news.

PostgreSQL 11 Server Side Programming it’s gaining attention

I’m happy to say that my very first book on PostgreSQL, PostgreSQL 11 Server Side Programming Quick Start Guide, is gaining more and more attention and the statistics about it is increasing.

By design, the out of the box configuration for PostgreSQL is defined to be a "Jack of All Trades, Master of None". The default configuration for PostgreSQL is fairly painstakingly chosen to ensure that it will run on every environment it is installed, meeting the lowest common denominator resources across most platforms.

Write-Ahead Logs is a critical part of PostgreSQL, that ensures data durability. While there are multiple configuration parameters , there was no easy to monitor WAL activity, or what is generating it.

Parallel query is a big help for large amount of data retrieval. On my project using PostgreSQL 12.2, I found that the feature works in plain SQL but not inside PLPGSQL or even SQL function. For years, I rely on user defined function for some considerations. First, to hide sql structure details from front end logic so that (as long as function declaration does not change) the change in internal query statements does not require front-end code rewriting.

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.

PostgreSQL Person of the Week Interview with Michael Paquier: I am from France, based in Japan. In my spare time, I happen to read a lot of books to focus on something other than work.

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.

PostgreSQL Online Survey – Results

For a better experience of results, switch to desktop mode on your mobile browser [may take a second or two to load the results]

Also, read What does make and make install mean in PostgreSQL

PostgreSQL Online Survey – Everything you need to know how people are using PostgreSQL

In this survey, I just wanted to understand how people are using PostgreSQL and wanted to give the information to the community

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.