Neues vom PostgreSQL Planet

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.

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

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:

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.

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:

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.

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.

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.

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)

© Laurenz Albe 2021

For many people, “cross join” is something to be afraid of. They remember the time when they forgot the join condition and the DBA was angry, because the query hogged the CPU and filled the disk. However, there are valid use cases for cross joins which I want to explore in this article.

Part of the release engineering process at Swarm64 is to run performance benchmarks that assure us new versions of Swarm64 DA and PG Nitrous cause PostgreSQL performance to improve and not regress.

PostgreSQL Person of the Week Interview with Josh Berkus: I’m from America. I mean, seriously, I grew up in DC, Ohio, Florida, Texas, and California, and now I live in Oregon. So really all over the USA.

I mentioned the problem of safety systems causing safety failures last year. There is a corollary to that that I want to mention related to planned vs unplanned database downtime.

Databases are critical parts of most information technology infrastructure, so downtime is a big concern. There are a few cases where you know your Postgres database will need to be down, if only briefly:

Relational databases started as simple data containers with a relational structure. Over the decades, SQL matured into a language that allows complex processing inside relational databases. The typical life-cycle is that once a client-side feature become well-understood and established, it often moves into a relational database for efficiency and consistency among applications.

Learn how to grant or revoke rows privileges in PostgreSQL. Continue reading Row-level Security Policy in PostgreSQL→

The post Row-level Security Policy in PostgreSQL appeared first on Vettabase.

By default Linux uses a controversial (for databases) memory extension feature calledovercommit. How that interacts with PostgreSQL is covered in the Managing Kernel Resources section of the PG manual.

A couple of weeks ago, Jobin and I did a short presentation during Percona Live Online bearing a similar title as the one for this post: “PostgreSQL HA With Patroni: Looking at Failure Scenarios and How the Cluster Recovers From Them”.

New pgSCV 0.6.0 has been released, with two new features and with minor fixes and improvements.

pgSCV is a Prometheus exporter and monitoring agent for PostgreSQL environment. Project’s goal is to provide a single tool (exporter) for collecting metrics about PostgreSQL and related services.

0. Problem statement

One of the most missing features in Postgres in my opinion was always a possibility to ignore nulls in analytic functions. Other databases support ignore nulls clause which makes the task much easier. Let’s start by creating a simple, two-column table and populating it with data:

create table tbl ( id bigint primary key, a int ); insert into tbl (id, a) select s, case when random() < .5 then s end from generate_series(0, 100) s;

The problem is to generate a column (lnn – last not null) with the last non-null value ordered by id:

How I implemented a kind of Oracle-to-PostgreSQL backup.