Neues vom PostgreSQL Planet

Postgres server variables offer powerful control over how the database behaves. Privileged users often want to set superuser-restricted server variables for non-superusers. The simplest approach is for the superuser to issue ALTER ROLE ... SET on the non-superuser role.

This blog post is about how the Logical Replication has evolved over the years in PostgreSQL, what's in the latest release-16, and what's being worked upon for future releases. Logical replication is a method of replicating data objects and their changes, based upon their replication identity (usually a primary key). We use the term logical in contrast to physical replication, which uses exact block addresses and byte-by-byte replication.

I used Postgres since 1995 ( [PG95] mailing list has less than 400 subscribers !) for my scientific projects and operating with numbers and english strings was smooth, but in 1996 I signed up to make a searchable database of electronic archive of "Uchitelskaya gazeta" ( the popular newspaper for teachers community). It was my moonlight work to help me grow kids. The whole project was sponsored by West foundation, if I recall properly, our office was located in Vetoshny Lane,  near the Read Square, I had a lot of fun working there with smart people.

After ten years of working with postgres ( actually, I started with Ingres, then postgres95), I and Teodor got a chance to see live other developers.  We presented there GIN and FTS.  Pictures from the Anniversary Summit at at Ryerson University in Toronto can be  viewed on the flickr under name  PGCon-2006, I hope many of us will enjoy looking back 17 years, how young we were !

“That time of the year”™ again 🎉 As per usual for that “season” - not that I don’t trust the PostgreSQL Global Development Group and its performance farm (they did catch some performance regressions in beta stages!) - but somehow as soon as a new major release arrives, I get...

1. Overview

PostgreSQL provides a configuration file postgresql.conf for end users to customize parameters. You may need to change some parameters to tune performance or deploy a PostgreSQL server in your working environment. In this blog post, we’ll explore different ways to manage these parameters.

Generating random numbers is a surprisingly common task in programs, whether it's to create test data or to provide a user with a random entry from a list of items.

PostgreSQL comes with just a few simple foundational functions that can be used to fulfill most needs for randomness.

Almost all your random-ness needs will be met with the random() function.

A nice addition to the command \watch in the PostgreSQL command line client.

psql \watch improvements

psql is the best command line SQL client ever, and it gets improved constantly. With the new release of PostgreSQL 16, also psql get a new nice addition: the capability to stop a \watch command loop after a specific amount of iterations.

In this article I briefly show how the new feature works.

I want to share the information published by Chealse Dole in the community #pglsql-women Slack channel for a wider audience:

Heads up PG women – the PostgresUS Diversity Scholarship still has funding to support flights, hotels, and conference registration for 2-3 women to attend PGConf NYC on October 3-5th!

When you run advanced SQL in Citus, what’s possible? Which SQL statements work, and which ones don’t? Citus is a PostgreSQL extension that adds powerful sharding capabilities to PostgreSQL. However, every solution does have limitations. Therefore, it makes sense to take a look at the latest version of Citus and learn how to properly use the most frequently-needed SQL features. Note that this is not a comprehensive overview, rather it is a guide through 6 of the most commonly-needed SQL tools:

Now that I have given a presentation about Postgres 16 features in Jakarta and Chicago, I have made my slides available online.

By following best practices and query optimization techniques, you can leverage incremental sort in PostgreSQL and enhance query performance.

The post Incremental Sort in PostgreSQL: A Developer’s Guide appeared first on Stormatics.

Postgres has been steadily building on the JSON functionality initially released more than 10 years ago. With Postgres 16, working with JSON has gotten a couple nice improvements. Primarily, this release added features that ease the manipulation of data into JSON and improve the standard SQL functionality using JSON.

TL;DR:

I am thrilled to introduce the much-anticipated major release of pgxmock v3! This release represents a significant leap forward in enhancing your PostgreSQL database mocking experience in Go.

Here’s a quick overview of the exciting changes and additions in this release:

Introduction We are excited to be announcing that we now support the increasingly popular pgVector Postgres extension for storing and searching vector embeddings in AI-powered applications. Bringing pgVector and pgEdge’s distributed capabilities together makes for a powerful combination that greatly improves performance for users regardless of their geographic location.In this blog we'll demonstrate how to configure pgVector with pgEdge to provide similarity search functionality across a pgEdge Distributed PostgreSQL cluster.

PostgreSQL 16 was released last week. This is the story of a feature.

One of the minor new features in PostgreSQL 16 that I am excited about is the ability to group digits in numeric literals by separating them with underscores, like

SELECT 1_000_000; SELECT * FROM tbl WHERE id > 10_000_000;

It also works with non-integer literals:

In case you missed it, Postgres 16 came out last week - and this year it arrived earlier than the last few years. There are many features that I’ve been looking forward to for the last few months and I’m excited to see them get into the hands of users. Before we dive into the specific features of this release, let’s discuss what a Postgres major release actually means.

With so many Postgres conferences coming up soon, it seemed fitting to share some highlights from a past episode of the Path To Citus Con about why to give talks at Postgres conferences.

PGDay UK 2023 was a week ago today, and I think it's safe to say it was a resounding success!

USING columnar

Row store vs. column store – a lot has been written about this topic in the context of PostgreSQL and Citus. What does it really mean and what are the implications? Are column stores “always cool” and “always beneficial”? No, there’s more to it – which requires a closer look. When trying to understand the benefits of column store over row store or vice versa, it’s important to grasp the basic ideas which lead to the database behavior we’re going to see in this blog.