Neues vom PostgreSQL Planet

Neues vom PostgreSQL Planet Feed abonnieren
Planet PostgreSQL
Aktualisiert: vor 1 Stunde 54 Minuten

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"

Andrew Dunstan: Running Standalone TAP Tests

19. Juni 2021 - 22:53

Recent discussion set me thinking about what would be involved in setting up standalone TAP tests for Postgres, to do, say, more code coverage than we get from the current core tests. So I started experimenting. And with a tiny bit of Makefile magic it turns out to be absurdly easy.

You need just two things: an entirely boilerplate Makefile and a tiny bit of glue at the top of your TAP test files.

First let's look at the Makefile. Here it is in its entirety:

Michael Paquier: Postgres 14 highlight - Monitoring for COPY

19. Juni 2021 - 3:48

When it comes to monitoring in PostgreSQL, progress reports, able to give the state of an operation at a given point in time, exist since 9.6 and pg_stat_process_vacuum for VACUUM. PostgreSQL 14 is adding a new feature in this area with progress reporting for COPY, as of this commit:

Tatsuo Ishii: Promoting specied node in Pgpool-II

18. Juni 2021 - 13:43
  image by Gerd Altmann from Pixabay
Promoting a standby node

Greg Sabino Mullane: Fun with pg_checksums

17. Juni 2021 - 21:36

Data checksums are a great feature in PostgreSQL. They are used to detect any corruption of the data that Postgres stores on disk. Every system we develop at Crunchy Data has this feature enabled by default. It's not only Postgres itself that can make use of these checksums.

Aleksander Alekseev: How (and why) to become a PostgreSQL contributor

17. Juni 2021 - 17:15

Contributing to open-source projects can be intimidating – and PostgreSQL is no exception. As a Postgres contributor, I share my hard-earned tips to help you make your first contribution (or contribute more).

The article is published in Timescale blog:

How (and why) to become a PostgreSQL contributor

Alexey Lesovsky: Upgrade: pgCenter 0.9.0 is out!

17. Juni 2021 - 12:45

Since the last pgCenter release I’ve been working on some new improvements and now it’s time to introduce you to pgCenter 0.9.0.

Here are a few key features and fixes introduced in this release:

Hans-Juergen Schoenig: Creating and refreshing materialized views in PostgreSQL

17. Juni 2021 - 10:00

A “materialized view” is a database object which stores the result of a precalculated database query and makes it easy to refresh this result as needed. Materialized views are an integral feature of pretty much all advanced database systems. Naturally, PostgreSQL also provides support for materialized views, and offers the end-user a powerful tool to handle more time-consuming requests.

The main questions are now: What are the pitfalls, and how can you make use of materialized views in the first place? Let’s dive in and find out.

Egor Rogov: Locks in PostgreSQL: 1. Relation-level locks

17. Juni 2021 - 2:00

The previous two series of articles covered isolation and multiversion concurrency control and logging.

In this series, we will discuss locks.

This series will consist of four articles:

Bruce Momjian: The Hard Quadrant

16. Juni 2021 - 16:45

Having worked with databases for 32 years, I have always lived in a world of locks, sharing, and complexity, but sometimes, I look out the window and wonder, "Why does everything have to be so hard?" After a while, I just give up figuring it out and get back work.

Gilles Darold: Global Temporary Table in PostgreSQL

16. Juni 2021 - 3:43

One of the common problems while migrating from Oracle to PostgreSQL is the need of Global Temporary Tables in PostgreSQL (GTT). There is no PostgreSQL equivalent for global temporary tables. Due to this reason, I have originally started the developed of an extension called : PGTT, to reduce the overall efforts involved while migrating from Oracle to PostgreSQL.

Denish Patel: Vacuum Those MVs!

15. Juni 2021 - 17:14

On a particular project, Materialized Views (MVs) were being used quite extensively in the Apps that were being migrated from Oracle to PostgreSQL.  One thing I noticed was missing was VACUUM ANLYZE on these MVs after being created or refreshed.  It is extremely important to integrate VACUUM ANALYZE commands into this process.  Here is the reasoning behind it.

1. ANALYZE – updates the statistics for the MV so that the PG Optimizer can make the best decision possible for access path determination for SQL statements running against these MVs.

Oleg Bartunov: What is the size of jsonb ?

15. Juni 2021 - 14:43
It's interesting to know how big is your jsonb and it's not trivial as it looks, since jsonb is a binary format and also may be TOASTed (compressed).

Assume, that jb - is an attribute of type jsonb, than

raw_size = pg_column_size(jb::text::jsonb) -- the size of jsonb in memory
compressed_size = pg_column_size(jb) -- stored size of jsonb (raw_size if not TOAST-ed and non-compressed)