Neues vom PostgreSQL Planet
© 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:
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:
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:
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.
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.
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)