Sammlung von Newsfeeds

Have you ever wanted to use a non-superuser role in a Postgres database to perform actions that are normally restricted? Even something as simple as reading from the pg_stat_activity view requires special permissions to view the query column because it could contain sensitive information.

This is not the usual Waiting for post, but something should be said. Back in March/April of 2022 Andres Dunstan committed a series of patches that added support for lots of really interesting features from SQL/JSON standard. While I'm not avid user of json in database, I was very, very happy. Wrote couple of blogposts … Continue reading "Waiting for …: SQL/JSON is coming back. Hopefully."

Databases Cursors are one of the key components of development. We have been using them for traversing query results for processing based on business logic or functionality. In Code Conversion, Cursor code always needs to be ported either from PL\SQL or TSQL to Pl\pgSQL, and some special considerations need to be incorporated as best practices to avoid surprises or functionality failures.

On 3rd of April 2024, Tom Lane committed patch: Invent SERIALIZE option for EXPLAIN.   EXPLAIN (ANALYZE, SERIALIZE) allows collection of statistics about the volume of data emitted by a query, as well as the time taken to convert the data to the on-the-wire format. Previously there was no way to investigate this without actually … Continue reading "Waiting for PostgreSQL 17 – Invent SERIALIZE option for EXPLAIN."

On 1st of April 2024, Masahiko Sawada committed patch: Add new COPY option LOG_VERBOSITY.   This commit adds a new COPY option LOG_VERBOSITY, which controls the amount of messages emitted during processing. Valid values are 'default' and 'verbose'.   This is currently used in COPY FROM when ON_ERROR option is set to ignore. If 'verbose' … Continue reading "Waiting for PostgreSQL 17 – Add new COPY option LOG_VERBOSITY."

On 30th of March 2024, Dean Rasheed committed patch: Add support for MERGE ... WHEN NOT MATCHED BY SOURCE.   This allows MERGE commands to include WHEN NOT MATCHED BY SOURCE actions, which operate on rows that exist in the target relation, but not in the data source. These actions can execute UPDATE, DELETE, or … Continue reading "Waiting for PostgreSQL 17 – Add support for MERGE … WHEN NOT MATCHED BY SOURCE."

When you first spin up your app, the emphasis is on getting started and getting data to your clients. But when you don’t have throughput, you are also not going to have enough concurrency to unveil bad queries.

But then you have success. And success means data. More users, more interactions, more everything. Suddenly, queries that performed fine are struggling under the load, hurting performance and scalability. This is all going to mean a far worse user experience when much higher costs because of inefficient resource use.

PeerDB offers a fast and cost-effective way to move data from PostgreSQL to data warehouses, such as Snowflake, and to queues like Kafka. This enables businesses to have real-time and reliable access to data, which is of utmost importance in this AI ...

Last October, I wrote:

Looking at the numbers in the PG17 cycle so far, even though they are much lower than the 2023-03 commitfest, we can expect that PG17 will have more activity in total than previous development cycles.

And now it’s time to count up the score. The final per-commitfest numbers of committed patches were:

A guide to Postgres roles and privileges

Terrific presentation and discussion at last week’s Postgres Extension Ecosystem Mini-Summit. In fact, I later learned that some people couldn’t make it because the Eventbrite event page showed it as sold out!

I recently created a Postgres extension which utilizes the pg_cron extension to schedule recurring activities using the cron.schedule(). Everything worked great. The only problem was when I dropped my extension, it left the cron job scheduled, which resulted in regular errors:

One fun fact of Pl\pgSQL is that it’s an Extension in PostgreSQL that is created as default whenever we create any new databases.

Converting databases, primarily those like Oracle, involves translating multiple functions, procedures, or packages written in PL/SQL to PostgreSQL with PL/pgSQL.During the conversion stage, we utilize conversion tools to automate a significant portion of the initial migration. However, it’s important to note that automatically converted code might not always follow best practices for PostgreSQL development.

I am part of the Fujitsu OSS Postgres team, a group of Postgres engineers at Fujitsu work that have been working continuously to deliver contributions to the community since 2019. I would like to reflect on my past year and also share with you my endeavours for the year.

Tech Preview release of pg_tde now availableAs organizations collect, store, and analyze vast amounts of data, ensuring its confidentiality and integrity becomes a top priority. For PostgreSQL users, the tech preview release availability of the new encryption extension pg_tde delivers unmatched protection for vital data assets.What is pg_tde?pg_tde, developed by Percona, is an open source […]

This post covers my experience compiling and installing PostgreSQL from source code. Primarily I followed official instructions and this blog post Setup PostgreSQL development environment on MacOS. Once installed, we’ll look at how to test doc changes and patches from the mailing list.

While many AI/ML embedding models generate vectors that provide large amounts of information by using high dimensionality, this can come at the cost of using more memory for searches and more overall storage. Both of these can have an impact on the cost and performance of a system that’s storing vectors, including when using PostgreSQL with the pgvector for these use cases.

In the first article in this transforming data series, I discussed how powerful PostgreSQL can be in ingesting and transforming data for analysis. Over the last few decades, this was traditionally done with a methodology called Extract-Transform-Load (ETL) which usually requires external tools. The goal of ETL is to do the transformation work outside of the database and only import the final form of data that is needed for further analysis and reporting.

Are you worried that PostgreSQL cannot scale writes within a single node, or do you think that scaling PostgreSQL can only be done horizontally, across different Kubernetes nodes? Discover the surprising truth behind PostgreSQL’s vertical scalability in this first article of a two-part series. Explore the potential of optimizing CPU, RAM, and storage resources through meticulous measurement and benchmarking, challenging conventional scaling wisdom.