Neues vom PostgreSQL Planet

Beautiful, responsive maps are best built using vector tiles, and PostgreSQL with PostGIS can produce vector tiles on-the-fly.

If you are running PostgreSQL in production you might have already noticed that adjusting checkpoints can be a very beneficial thing to tune the server and to improve database performance overall. However, there is more to this: Increasing checkpoint distances can also help to actually reduce the amount of WAL that is created in the first place. That is right. It is not just a performance issue, but it also has an impact on the volume of log created.

You want to know why? Let us take a closer look!

In the previous blog post, we discussed the different Windows build variants that PostgreSQL supports. In this post, we’ll discuss how, as a Unix-based developer, you can check whether your patch might work on Windows. (For simplicity, I’ll say “Unix” to mean Linux, BSD, macOS, and similar.) First, there are a few ways to check […]

Interview with the Postgres committers who have joined the Postgres team at Microsoft by Sudhakar Sannakkayala (Partner Director, Azure Data) and Ozgun Erdogan (Principal, Azure Data)—cross-posted from the Azure Postgres blog.

"Let's put that on a map!"

What

The March meeting will be held at 18:00 EST on Tues, the 24th. Once again, we will be holding the meeting in the community space at CoverMyMeds. Please RSVP on MeetUp so we have an idea on the amount of food needed.

Foreign data wrappers are part of Postgres’s implementation of the SQL/MED (Management of External Data) extension to the SQL standard, which provides the specs for accessing data that’s not actually in your database.

Some of you have been asking, “what’s happening with the Citus open source extension to Postgres?” The short answer is: a lot. More and more users have adopted the Citus extension in order to scale out Postgres, to increase performance and enable growth.

PostgreSQL Person of the Week Interview with Joe Conway: I grew up in New York, went to undergraduate university at Virginia Tech, and rode nuclear submarines for a few years. After that I married and spent many years in Southern California raising a family and working. A couple of years ago my wife and I moved to Florida to be closer to family. For the past 5 years I have been VP of Engineering at Crunchy Data.

Read our interview with Tomas Vondra who will talk about CREATE STATISTICS - what is it about.

When you start realizing how useful can be generated columns, you start using them as part of your workflow. Here there’s another story of mine in the adventures in PostgreSQL-land.

pgBackRest is a well-known powerful backup and restore tool.

The 2.24 version, released on February 25, introduced auto-selection of backup set on restore when time target is specified. Auto-selection is performed only when --set is not specified. If a backup set for the given target time can’t be found, the latest (default) backup set will be used.

Let’s illustrate it!

PostgreSQL and pgBackRest installation

Let’s install PostgreSQL and pgBackRest directly from the PGDG yum repositories:

Hi, all!!

It's been a long time since the last time I posted here... Wow, almost 6 years!! 
What have I been doing all this time, you may wonder... Well, I've been studying a lot and playing with other projects besides Npgsql, which btw is in excellent Shay Rojansky's hands. 

After making its mark in the OLTP world,  PostgreSQL is moving towards catering the needs of OLAP environment. Hence, the recent advancements in features like parallel query, declarative partitioning, and partition-wise joins and aggregates, etc. 

Lot of people does migration from Oracle's PL/SQL to PostgreSQL's PL/pgSQL. One question is how to migrate private functions? There is not a reply. Oracle's PL/SQL is little bit modified language ADA. This language is procedural and modular. The concept of private functions or public functions is very natural there. Nothing similar is in Postgres. Not in this moment. PLpgSQL functions are called by SQL API every time. So visibility for SQL engine should be same for all functions.

(This came out of something I was trying out + discussing with Postgres enthusiasts - thanks to all for clarifying doubts)

This article aims at highlighting one aspect of how the query planner implementation of SELECT * GROUP BY differs from SELECT DISTINCT.

For example:

SELECT b,c,d FROM a GROUP BY b,c,d;
vs
SELECT DISTINCT b,c,d FROM a;

Selectivity estimation error is one of the main cause of bad query plans. It’s quite straighforward to compute those estimation error using EXPLAIN (ANALYZE), either manually or with the help of explain.depesz.com (or other similar tools), but until now there were now tool available to get this information automatically and globally. Version 2 of pg_qualstats fixes that, thanks a lot to Oleg Bartunov for the original idea!

Any performance feature in PostgreSQL that mentions the “parallel” keyword excites me. So it was natural that parallel vacuum took my complete attention when it was committed in January 2020. 

For the sake of simplicity, we are not going to distinguish between index cleanup or index vacuum in this blog and simply use the term vacuum to refer to both.

Read our interview with Karen Jex who will talk about PostgreSQL through the eyes of an Oracle DBA.

Just as last year was ending, one of our long-time customers came to us because one of their long-existing PostgreSQL queries involving PostGIS geometry calculations was much slower for specific values.  We researched the problem and found out how to solve it; read on! What we found as the cause of the problem will surprise […]

PostgreSQL Person of the Week Interview with Lætitia Avrot: I’m Lætitia from France, near Lyon. If you can’t say my name because it’s too difficult, it’s ok. You may use ‘Joy’ if it’s easier for you. I like savate boxing, running and learning. I love listening to both rock and classical music, as long as it sounds ‘positive’.