Neues vom PostgreSQL Planet

Neues vom PostgreSQL Planet Feed abonnieren
Planet PostgreSQL
Aktualisiert: vor 36 Minuten 54 Sekunden

Andreas 'ads' Scherbaum: Stefan Keller

28. Juni 2021 - 16:00
PostgreSQL Person of the Week Interview with Stefan Keller: I’m a Professor for Data Engineering since about 20 years and director of Institute for Software at Eastern University of Switzerland. I’m a spatialist; my focus is on spatial data engineering, spatial data analytics and open source, open data and educational resources.

Michael Christofides: Calculating per-operation times in EXPLAIN ANALYZE

28. Juni 2021 - 15:32

When you’re trying to work out why a Postgres query is slow, EXPLAIN ANALYZE can be a huge help, but calculating the per-operation timings can get tricky. This post will start off fairly gentle, but it gets complex pretty quickly, so strap in!

The basics

The first thing to know is that the reported time of a parent operation is inclusive of its children. So if we want to know the contribution of the parent operation, we need to subtract the timings reported by its children.

ahsan hadi: Some Interesting statistics about PG-14 contributions

28. Juni 2021 - 14:00

I have spent a few days trolling through the features added to PostgreSQL 14 and in this blog, I want to share some statistics about PG-14 contributions, hopefully, you will find these statistics interesting. Please note that this data is based on my research on PG 14 contributions by going through the GIT LOG, commitfest entires, hackers emails threads, PG-14 release notes on the PostgreSQL website, etc. So please do give me the margin of human error in case the numbers aren’t 100% accurate.

The release notes for PG-14 are here

ahsan hadi: Parallel execution of postgres_fdw scan’s in PG-14 (Important step forward for horizontal scaling)

28. Juni 2021 - 14:00

Back in August 2019, I wrote about the Horizontal scaling / Sharding in PostgreSQL and where it is going https://www.highgo.ca/2019/08/08/horizontal-scalability-with-sharding-in-postgresql-where-it-is-going-part-3-of-3/. One of the key feature that i talked about in this blog is parallel foreign scan i.e. asynchronous append of FDW nodes.

Dave Page: Deploying pgAdmin in Kubernetes

28. Juni 2021 - 12:20
pgAdmin has long had a container distribution; however the development team rarely used it, except when testing releases. So virtually all of our experience has been using Docker. Recently, a user ran into an issue when running under Kubernetes that I was unable to reproduce in Docker, so I spent some time learning how a pgAdmin deployment would work in that environment—and ironically it worked just fine; I couldn't reproduce the bug!

Alexey Lesovsky: Updates of PostgreSQL Observability diagram.

28. Juni 2021 - 12:17

Glad to present new updates of PostgreSQL Observability diagram and tell about further improvements.

Too much stats in Postgres

New releases of Postgres bring more and more stats. Upcoming Postgres 14 has new stats features and improvements on existing views.
I’ve reorganized items on the diagram and made a second side columns. It looks a bit clunky, but I think after two major releases all space will be occupied by new stats views and functions.

Weaponry Weaponry: Updates of PostgreSQL Observability diagram.

28. Juni 2021 - 12:17

Glad to present new updates of PostgreSQL Observability diagram and tell about further improvements.

Too much stats in Postgres

New releases of Postgres bring more and more stats. Upcoming Postgres 14 has new stats features and improvements on existing views.
I’ve reorganized items on the diagram and made a second side columns. It looks a bit clunky, but I think after two major releases all space will be occupied by new stats views and functions.

Pavel Stehule: calculating derivation from pg_stat_database and watch statement

27. Juni 2021 - 6:10
The one from important Postgres's stats view is pg_stat_database. We can see in this view lot of important cumullative metrics. But sometimes, we can prefer to see these metric related to last seconds. It is not hard to calculate it:
CREATE OR REPLACE FUNCTION public.tps(text)
RETURNS
TABLE(xact_commit integer,
xact_rollback integer,
tup_returned integer,
tup_fetched integer,
tup_inserted integer,
tup_updated integer,
tup_deleted integer)
AS $function$
DECLARE

Jimmy Angelakos: Practical Partitioning in Production with Postgres [Postgres Vision 2021]

26. Juni 2021 - 10:00

Has your table become too large to handle? Have you thought about chopping it up into smaller pieces that are easier to query and maintain? What if it's in constant use?

An introduction to the problems that can arise and how PostgreSQL's partitioning features can help, followed by a real-world scenario of partitioning an existing huge table on a live system.

Video from my talk at this year's Postgres Vision 👇

Devrim GÜNDÜZ: Installing PostgreSQL 14 beta/RC on RHEL / Rocky Linux / Fedora and SLES

25. Juni 2021 - 11:28
PostgreSQL 14 beta 2 is released. As written in the announcement, "In the spirit of the open source PostgreSQL community, we strongly encourage you to test the new features of PostgreSQL 14 in your systems to help us eliminate bugs or other issues that may exist."

Jobin Augustine: Understanding pg_repack: What Can Go Wrong – and How to Avoid It

24. Juni 2021 - 15:04

pg_repack is one of the oldest, widely used, extension projects for PostgreSQL. It is so much popular that even DBaaS service providers couldn’t avoid it. It is a “power tool” in the hands of a DBA to deal with bloated/fragmented tables. I can’t imagine a serious production deployment without it these days. It magically replaces the bloated, fragmented tables with a fresh fully packed table without holding an exclusive lock on the table during its processing**.

Hans-Juergen Schoenig: PostgreSQL: Constraints over multiple rows

24. Juni 2021 - 10:00

In PostgreSQL and many other relational databases, constraints are an integral part of the feature set. Many people are aware of primary keys, foreign keys, CHECK-constraints, table constraints and so on. However, from time to time, the situation is way more complicated. That’s when some more advanced techniques are needed to enforce integrity in the way it is desired by end users.

Hubert 'depesz' Lubaczewski: A tale of making company-wide standard psqlrc

23. Juni 2021 - 18:23
At a company we have literally thousands of Pg servers. The layout is also kinda non-obvious. Each database is named the same, but contains different data. And in front of it all, we have pgbouncers. After some talk, it was suggested that perhaps we could make psql prompt show which database it is connected to. … Continue reading "A tale of making company-wide standard psqlrc"

Kat Batuigas: Logging Tips for Postgres, Featuring Your Slow Queries

22. Juni 2021 - 21:39
In the last several months, we've featured simple yet powerful tools for optimizing PostgreSQL queries . We've walked through how the pg_stat_statements extension can show which queries are taking up the most time to run system-wide .

Laurenz Albe: From MD5 to scram-sha-256 in PostgreSQL

22. Juni 2021 - 10:00


© Laurenz Albe 2021

Since v10, PostgreSQL has provided support for scram-sha-256 for password hashing and authentication. This article describes how you can adapt your application safely.

Why do we need scram-sha-256?

PostgreSQL uses cryptographic hashing for two purposes:

Bruce Momjian: Why Vacuum?

21. Juni 2021 - 19:15

Vacuum is a routine database maintenance tasks that is handled manually or by autovacuum. Over the years, people have wondered if there is a way to eliminate the vacuum requirement.

Devrim GÜNDÜZ: How to migrate from CentOS 8 to Rocky Linux 8 (experimental!)

21. Juni 2021 - 16:15
Rocky Linux 8.4 is released today. I already added support and wrote down instructions about how to install PostgreSQL on Rocky Linux 8, and now it is time for a short blog post about migrating from CentOS 8 to Rocky Linux 8. Please note that if you are using CentOS 8 Stream, this blog post is not suitable for you.

Andreas 'ads' Scherbaum: Federico Campoli

21. Juni 2021 - 16:00
PostgreSQL Person of the Week Interview with Federico Campoli: Despite I’m looking younger, I’ve been around this planet for almost 49 years. My hometown is Napoli, Italy, I moved to Tuscany to start my first job as ASP developer on MS-SQL Server in 1999.

Robert Haas: Talking about the PostgreSQL Optimizer at CMU

21. Juni 2021 - 15:00

Professor Andy Pavlo, at CMU, seems to be a regular organizer of technical talks about databases; this year, he organized the vaccination database tech talks, and invited me to give one about the PostgreSQL query optimizer. So I did. It was great.

Hubert 'depesz' Lubaczewski: Explaining the unexplainable – part 6: buffers

20. Juni 2021 - 15:44
Back in 2013 I wrote a series of 5 posts about how to read explain analyze output. Figured that there is one big part missing – buffers info. You don't see this part in normal explain analyze, you have to specifically enable it: explain (analyze on, buffers on). Well, technically you don't need analyze part, … Continue reading "Explaining the unexplainable – part 6: buffers"

Seiten