Neues vom PostgreSQL Planet

Neues vom PostgreSQL Planet Feed abonnieren
Planet PostgreSQL
Aktualisiert: vor 41 Sekunden

Miranda Auhl: PostgreSQL vs Python for data evaluation: what, why, and how

1. Oktober 2021 - 15:35

As I started writing this post, I realized that to properly show how to evaluate, clean, and transform data in the database (also known as data munging), I needed to focus on each step individually. This blog post will show you exactly how to use TimescaleDB and PostgreSQL to perform your data evaluation tasks that you may have previously done in Excel, R, or Python.

Kirk Roybal: PostgreSQL 14: PostgreSQL Is Still a Tweeny

1. Oktober 2021 - 3:16

This article was a bit harder to write than it would seem on the surface. Amid all the hustle and bustle of the current development cycle, it was hard to find a particular focus of what this effort was all about. Then it finally dawned on me. There wasn’t one.

That may sound a bit harsh for the developers working on it, but please let me explain. PostgreSQL is caught in the middle of a maelstrom. As it continually tries to grow up, the meaning of being a grown up keeps changing.

Umair Shahid: PostgreSQL 14 – Performance, Security, Usability, and Observability

30. September 2021 - 15:04

Today saw the launch of PostgreSQL14. For me, the important area to focus on is what the community has done to improve performance for heavy transactional workloads and additional support for distributed data.

The amount of data that enterprises have to process continues to grow exponentially. Being able to scale up and out to support these workloads on PostgreSQL 14 through better handling of concurrent connections and additional features for query parallelism makes a lot of sense for performance, while the expansion of logical replication will also help.

Lætitia AVROT: 3 ways to auto-increment with Postgres

30. September 2021 - 13:25
Using a generated key is something DBAs often do, mainly for performance reasons. In an ideal world, we would rely on the table natural primary key and we’ll be over. However, using an artificial primary key has been proven beneficial for performance. In this blog post, I won’t explain what’s a primary key or what’s a natural or artificial key. If you’re interested in database design, you’ll find great books out there on the matter.

Laurenz Albe: Gaps in sequences in PostgreSQL

30. September 2021 - 10:00

© Laurenz Albe 2021

Most database tables have an artificial numeric primary key, and that number is usually generated automatically using a sequence. I wrote about auto-generated primary keys in some detail in a previous article. Occasionally, gaps in these primary key sequences can occur – which might come as a surprise to you.

Luca Ferrari: GNU Guix and PostgreSQL

30. September 2021 - 2:00

Installing PostgreSQL via GNU Guix.

Avinash Vallarapu: Upgrading PostgreSQL 9.6 to PostgreSQL 13

29. September 2021 - 17:27

It the the time for everybody using PostgreSQL 9.6 to start planning an upgrade to the latest supported PostgreSQL version. PostgreSQL Global Development Group (PGDG) supports any major version for 5 years after its initial release. PostgreSQL 9.6 with its initial release on September 29, 2016 is about to become an unsupported version. Its final minor version will be released on November 11, 2021, upon which no security fixes, bug fixes or patches are supported by PGDG.

David Christensen: Devious SQL: Dynamic DDL in PostgreSQL

29. September 2021 - 17:09
Supporting PostgreSQL DBAs is an important part of daily life here at Crunchy Data. I’ve recently run across a few use cases where utility queries based on the current state of the database are needed. A simple example could be where you have a table that is the target of logical replication and the id column becomes out of sync with the sequence that generated the data.

Anthony Sotolongo León: Boost query performance using Foreign Data Wrapper with minimal changes

29. September 2021 - 13:30
Foreign Data Wrapper

Nowadays it is becoming more frequent for systems and applications to require querying data from outside the main database. PostgreSQL supports querying external postgres data using two core extensions dblink and postgres-fdw , the last one is a Foreign Data Wrapper (FDW), that is an implementation of SQL/MED standard, which is part of ANSI SQL 2003 standard specification.

Franck Pachot: Bulk load into PostgreSQL / YugabyteDB - psycopg2

28. September 2021 - 21:49

Fast load into a database table is a feature we need on any database. Datawarehouses use it daily. OLTP requires it regularly. And probably from the beginning of their existence to migrate data to it. With Python,the psycopg2 client is commonly used and I'll start there (this is the first post in a series). The main motivation is that psycopg2 doesn't have prepared statements, and parsing each INSERT, even with a list of rows, is not efficient for loading million of rows. But it has a nice alternative as it can call the COPY command.

Pavel Stehule: plpgsql_check 2.0.1

28. September 2021 - 19:59
I released new significant version of plpgsql_check - plpgsql_check 2.0.1. Although there are only two new features (and few bugfixes), these two features are important. I wrote about benefits of plpgsql_check for PL/pgSQL language developers in my blog Why you need plpgsql_check (if you write procedures in PLpgSQL).

Franck Pachot: The cost and benefit of synchronous replication in PostgreSQL and YugabyteDB

28. September 2021 - 18:25

I have seen a people comparing YugabyteDB and PostgreSQL, and surprised by the different throughput when running a simple test on a from a single session. The purpose of a distributed database is to scale out. When running on a single node without the need for High-Availability-without-data-loss (this is a tautology), a monolith database will always perform with lower latency. Because a distributed DB is designed to ensure the persistence (the D in ACID) though RPC (remote procedure calls) rather than local writes.

Here is a simple workload:

Paul Brebner: Using Apache Superset to Visualize PostgreSQL JSON Data (Pipeline Series Part 7)

28. September 2021 - 17:00

In the last installment of the pipeline blog series, we explored writing streaming JSON data into PostgreSQL using Kafka Connect. In this blog, it’s time to find out if our fears of encountering mutant monsters in the gloom of an abandoned uranium mine were warranted or not.

Michael Christofides: Why isn’t Postgres using my index?

28. September 2021 - 13:08

Cover photo: Emily Morter

If you spend any amount of time working with Postgres, it’s likely that at some point you’re going to wonder why it isn’t using an index that you think it should be.

Unlike some databases, you can’t force PostgreSQL to use a specific index, but there are several things you can do to work out what’s going on.

Laurenz Albe: JSON in PostgreSQL: how to use it right

28. September 2021 - 10:00

© Laurenz Albe 2021

The comprehensive JSON support in PostgreSQL is one of its best-loved features. Many people – particularly those with a stronger background in Javascript programming than in relational databases – use it extensively. However, my experience is that the vast majority of people don’t use it correctly. That causes problems and unhappiness in the long run.

In this article, I will try to point out good and bad uses of JSON in PostgreSQL, and provide you with guidelines that you can follow.

Andreas 'ads' Scherbaum: Jeff Davis

27. September 2021 - 16:00
PostgreSQL Person of the Week Interview with Jeff Davis: I’m married, and I have two children and a labrador. I grew up in a suburb in central California.

Hubert 'depesz' Lubaczewski: Using recursive queries to get distinct elements from table

27. September 2021 - 9:18
I wrote about similar things couple of times, but recently found thread on pgsql-general mailing list that made me thing about it again. Summary of the problem from mail is: we have a table, ~ 800 million rows, with, at least 2 columns: station – 170 distinct values channel – generally 1-3 channels per station … Continue reading "Using recursive queries to get distinct elements from table"

Lukas Fittl: A better way to index your Postgres database: pganalyze Index Advisor

23. September 2021 - 14:00
When you run an application with a relational database attached, you will no doubt have encountered this question: Which indexes should I create? For some of us, indexing comes naturally, and B-tree, GIN and GIST are words of everyday use. And for some of us it’s more challenging to find out which index to create, taking a lot of time to get right. But what unites us is that creating and tweaking indexes is part of our job when we use a relational database such as Postgres in production. We need…

Nikolay Samokhvalov: Zero-downtime Postgres schema migrations need this: lock_timeout and retries

23. September 2021 - 11:06
Deploying DB schema changes in heavily loaded systems is challenging. In this article, we explore one of the challenges - how to avoid situations when DDLs get blocked, wait to acquire a lock, and during that, start blocking other sessions.

Luca Ferrari: Restarting a sequence: how hard could it be? (PostgreSQL and Oracle)

23. September 2021 - 2:00

How hard could it be to reset a sequence?

Restarting a sequence: how hard could it be? (PostgreSQL and Oracle)

One reason I like PostgreSQL so much is that it makes me feel at home: it has a very consistent and coherent interface to its objects. An example of this, is the management of sequences: ALTER SEQUENCE allows you to modify pretty much every detail about a sequence, in particular to restart it from its initial value.
Let’s see this in action: