Neues vom PostgreSQL Planet

The folks at TimescaleDB have published their "State of Postgres" survey results in a new micro-site where you can find a summary of responses, some more detailed analysis, and the source data from the survey. This survey was conducted for about 2 months during the late summer/early fall of 2019 and while I haven't gone through all the raw data as of yet, after reading the results... well, I have some opinions.

How do you know if the next update to your software is ready for hundreds of millions of customers? It starts with data. And when it comes to Windows, we’re talking lots of data. The Windows team measures the quality of new software builds by scrutinizing 20,000 diagnostic metrics based on data flowing in from 800 million Windows devices.

In PostgreSQL, there is this amazing feature called logical replication, which enables you to replicate a set of table(s) from one server to other. Being a new feature it is less mature and needs many improvements. Nevertheless, it should not limit us enjoying its advantage and so here is a post to highlight some interesting aspects of the feature.

It's not a secret that databases are damn complicated systems. And they tend to run on top of even more complicated stacks of software. Nowadays you will not surprise anyone (or at least not that much) by running your database on a Kubernetes cluster or inside a virtual machine. It's probably still questionable whether it's good and appropriate, but this approach is something we have to face — sometimes it's at least convenient, sometimes it allows to be more resource efficient and sometimes it's the only available infrastructure in a company.

PostgreSQL 12 has a very interesting feature to turn on when doing an execution plan analysis.

PostgreSQL 12 EXPLAIN SETTINGS

PostgreSQL 12 has a new feature that can be turned on in the EXPLAIN output: SETTINGS. This option provides some information about all and only those parameters that can affect an execution plan if and only if they are not at the default setting.
What does it mean in practice? Let’s see an old plain EXPLAIN:

SQL is query a language that is usually pretty easy to read. However, if people don’t format their queries properly even SQL turn out to be a nightmare. That’s why developers often turn to an SQL beautifier to turn an ugly query into a nicely formatted string. Various tools are available on the web to achieve exactly that.

 

 

Can the same thing be achieved using only PostgreSQL onboard tools? The answer is yes. This post will show you how to achieve that.

Just pushed new version of explain.depesz.com site. Changes: most importantly : changed favicon to be more related to site extended, quite a lot, help page added a way to embed explains on your page The embedding looks like: <iframe width=800 height=400 src="https://explain.depesz.com/i/mYSY"></iframe> effect: Basically, in plan url, you have to change /s/ to /i/.

There is a less-talked-about improvement in PostgreSQL 12 which can greatly reduce the benign log entries. This patch is probably one of the smallest in PostgreSQL 12.

The commit message says:

I am attending re:Invent this week. Thanks to the kind sponsorship of AWS, we are having a community Q&A and dinner on Thursday, December 5 in Las Vegas. The Meetup page is now online if you want to register.

Photo by Alberto Bigoni unsplash-logoAlberto Bigoni Today is Black Friday, then followed by a week end that leads directly to Cyber Monday. This is a period where you can find great deals online, with many discounts. I’ll make it short, because we all are busy, right? From today to Monday included my book The Art of PostgreSQL is available at a 50% discount with the coupon “BLACKFRIDAY2019”. Enjoy!

Version 12 of PostgreSQL is not exactly fresh out of the oven, as the first minor release was already announced. However, I think it’s fair to say that this version can be still considered fresh for most users, and surely only a small percentage of users has upgraded. So I think it makes sense to go over some new features. This will be my last article beating on the v12 drum though, I promise

PostgreSQL 12 contains two new server settings: ssl_min_protocol_version ssl_max_protocol_version As the names indicate, these are used to control the oldest (minimum) and newest (maximum) version of the SSL and TLS protocol family that the server will accept. (For historical reasons, in PostgreSQL, all settings related to SSL and TLS are named ssl_something, even though TLS […]

On 19th of November 2019, Thomas Munro committed patch: Allow invisible PROMPT2 in psql.   Keep track of the visible width of PROMPT1, and provide %w as a way for PROMPT2 to generate the same number of spaces.

Image © Laurenz Albe 2019

If you thought that the B-tree index is a technology that was perfected in the 1980s, you are mostly right. But there is still room for improvement, so PostgreSQL v12 (in the tradition of v11) has added some new features in this field. Thanks, Peter Geoghegan!

In this article, I want to explain some of these improvements with examples.

pgBackRest is a reliable and simple to configure backup and restore solution for PostgreSQL, which provides a powerful solution for any PostgreSQL database; be it a small project, or scaled up to enterprise-level use cases.

If you have ever used postgreSQL and wondered about the size of the tables, this post might help. A postgreSQL table is no simple table rather it has many things associated with it like index tables, toast tables, then there are other important stuff like free space map and visibility map. Now postgreSQL provides multiple options for measuring the size of table and there are interesting distinctions among them.

PostgreSQL does not use IN-PLACE update mechanism, so as per the way DELETE and UPDATE command is designed,

if somebody has problem with missing clang like "clang: command not found", then workaround can be running make with variable:

with_llvm=no make -e

To enable PQtrace, we need to add the following code into the client-side source in the function where it establishes the connection with the server.