Neues vom PostgreSQL Planet

Neues vom PostgreSQL Planet Feed abonnieren
Planet PostgreSQL
Aktualisiert: vor 2 Stunden 53 Minuten

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)

Laurenz Albe: About cross join in PostgreSQL

15. Juni 2021 - 11:00

© 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.

Luis M Carril : New open source Swarm64 HTAP Benchmark for PostgreSQL

15. Juni 2021 - 6:45

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.

Andreas 'ads' Scherbaum: Josh Berkus

14. Juni 2021 - 16:00
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.

Bruce Momjian: Reducing Planned Downtime Can Increase Unplanned Downtime

14. Juni 2021 - 15:45

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:

Bruce Momjian: Features Move into the Database as they Mature

11. Juni 2021 - 21:45

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.

Michael Aboagye: Row-level Security Policy in PostgreSQL

11. Juni 2021 - 19:01

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.

Greg Smith: PostgreSQL on Linux: Counting Committed Memory

11. Juni 2021 - 19:00

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.

Fernando Laudares Camargos: PostgreSQL HA with Patroni: Your Turn to Test Failure Scenarios

11. Juni 2021 - 14:02

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”.

Weaponry Weaponry: pgSCV 0.6.0 released.

11. Juni 2021 - 6:34

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.

Jędrzej Biedrzycki: Ignore nulls in Postgres

11. Juni 2021 - 5:55
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:

Luca Ferrari: Using ora2pg to do a kind of backup

11. Juni 2021 - 2:00

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