Neues vom PostgreSQL Planet

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

Magnus Hagander: Getting random rows faster. Very much faster.

7. Mai 2021 - 20:30

Getting a single random row, or a few rows, from a table in order to get representative data for example is a frequent need. The most common way to do this in PostgreSQL is using ORDER BY random() like:

Bruce Momjian: Postgres on Big Iron

7. Mai 2021 - 16:15

Postgres has always run well on commodity hardware, and works well on Raspberry Pi and consumer devices too. However, we have always lagged in optimizing Postgres on very larger hardware, mostly because our community has limited access to such hardware. In recent years, Postgres support companies have given the community access to large hardware, and run performance test themselves, which has allowed the community to enhance Postgres for such hardware.

Alexander Korotkov: Jsonpath: ** Operator and Lax Mode Are't Meant to Be Together.

6. Mai 2021 - 17:10

PostgreSQL has an extension to jsonpath: ** operator, which explores arbitrary depth finding your values everywhere. At the same time, there is a lax mode, defined by the standard, providing a “relaxed” way for working with json. In the lax mode, accessors automatically unwrap arrays; missing keys don’t trigger errors; etc. In short, it appears that the ** operator and lax mode aren’t designed to be together :)

Luca Ferrari: To WAL or not to WAL? When unlogged becomes logged...

6. Mai 2021 - 12:30

What happens to table that are not logged into WALs when a physical replication is in place?

To WAL or not to WAL? When unlogged becomes logged… Creating and populating a database to test

First of all, let’s create a clean database just to keep the test environment separated from other databases:


Now let’s create and populate three tables (one temporary, one unlogged and one normal):

Florian Nadler: Publishing maps with Geoserver and PostGIS

6. Mai 2021 - 10:00

Analysing data within PostGIS is just one side of the coin. What about publishing our datasets as maps that various clients can consume and profit from? Let’s have a look at how this can be accomplished with Geoserver. Geoserver acts in this regard as a full-fledged open source mapping server, supporting several OGC compliant services, such as OGC Web Mapping (WMS) or Web Feature Service (WFS). OGC is an abbreviation for Open GIS Consortium, which is a community working on improving access to spatial information by developing standards in this area.

Paul Ramsey: (The Many) Spatial Indexes of PostGIS

5. Mai 2021 - 22:43
Spatial indexes are used in PostGIS to quickly search for objects in space. Practically, this means very quickly answering questions of the form:

Bruce Momjian: SET OF RECORD

5. Mai 2021 - 17:00

I previously covered the use of jsonb_to_record(), which uses record types to cast multiple JSON values. Record types are effectively special SQL rows.

Devrim GÜNDÜZ: How to install PostgreSQL 13, 12, 11, 10 and 9.6 to Rocky Linux 8

5. Mai 2021 - 11:33
Rocky Linux 8 RC is out. This is a great step for the final release. Rocky Linux is a "community enterprise operating system designed to be 100% bug-for-bug compatible with Red Hat Enterprise Linux". Thus, PostgreSQL packages built for RHEL will also work on Rocky Linux 8 (and already verified this!) Continue reading "How to install PostgreSQL 13, 12, 11, 10 and 9.6 to Rocky Linux 8"

Paul Ramsey: Spatial Indexes and Bad Queries

4. Mai 2021 - 10:00

So, this happened:

Basically a GeoDjango user posted some workarounds to some poor performance in spatial queries, and the original query was truly awful and the workaround not a lot better, so I snarked, and the GeoDjango maintainer reacted in kind.

Sometimes a guy just wants to be a prick on the internet, you know? But still, I did raise the red flag of snarkiness, so it it seems right and proper to pay the fine.

Paul Laurence : Using Kubernetes? Chances Are You Need a Database

3. Mai 2021 - 22:45

Whether you are starting a new development project, launching an application modernization effort, or engaging in digital transformation, chances are you are evaluating Kubernetes.  If you selected Kubernetes, chances are you will ultimately need a database

Halil Ozan Akgul: Citus Tips for Postgres: How to alter distribution key, shard count, & more

3. Mai 2021 - 18:22

Citus is an extension to Postgres that lets you distribute your application’s workload across multiple nodes. Whether you are using Citus open source or using Citus as part of a managed Postgres service in the cloud, one of the first things you do when you start using Citus is to distribute your tables.

Bruce Momjian: JSONB Multi-Column Type Casting

3. Mai 2021 - 18:15

I already covered JSONB type casting of single values. However, if you are extracting multiple values from JSONB that casting method can be cumbersome, and it also has limitations. Fortunately, there is another way — a way to retrieve multiple JSONB or JSON values and cast everything in one location.

Here is a simple table taken from my previous blog post:

Andreas 'ads' Scherbaum: Dave Page

3. Mai 2021 - 16:00
PostgreSQL Person of the Week Interview with Dave Page: I’m Dave Page, also known as pgSnake on IRC, Twitter and so on. I grew up and continue to live near Oxford in the UK. I’ve been working on PostgreSQL for over 20 years, contributing (or hindering, depending on your point of view!) in many different areas of the project. I currently serve on the project’s Core and Sysadmin teams, and on the board of directors for PostgreSQL Europe and the PostgreSQL Community Association of Canada.

Hans-Juergen Schoenig: Terminating database connections in PostgreSQL

3. Mai 2021 - 15:30

In PostgreSQL, every database connection is a server-side process. This makes PostgreSQL a robust multi-process rather than a multi-threaded solution. However, occasionally people want to terminate database connections. Maybe something has gone wrong, maybe some kind of query is taking too long, or maybe there is a maintenance window approaching.

In this blog you will learn how to terminate queries and database connections in PostgreSQL.

Ryan Lambert: OpenStreetMap to PostGIS is getting lighter

1. Mai 2021 - 7:01

If you have ever wanted OpenStreetMap data in Postgres/PostGIS, you are probably familiar with the osm2pgsql tool. Lately I have been writing about the osm2pgsql developments with the new Flex output and how it is enabling improved data quality.

Bruce Momjian: Separating Cluster and Database Attributes

30. April 2021 - 16:30

I have already covered the use of clusters, databases, and schemas, but a recent email thread highlighted the complexity of restoring database attributes and the objects inside of databases.

Bo Peng: Pgpool-II Logging and Debugging

30. April 2021 - 16:00

Logging and debugging help to monitor and identify issues or problems occurring in your program. Sometimes we need to log debug information to figure out the problems during software development and testing. However, if debug is enabled,  a large number of debug messages are generated and it is hard to read. Proper logging and debugging configurations are important.

There are a number of ways to retrieve debug information from Pgpool-II. In this post, I will describe the various ways for logging and debugging Pgpool-II. 

Alexander Korotkov: Dramatical Effect of LSE Instructions for PostgreSQL on Graviton2 Instances

30. April 2021 - 2:10

The world changes. ARM architecture breaks into new areas of computing. An only decade ago, only your mobile, router, or another specialized device could be ARM-based, while your desktop and server were typically x86-based. Nowadays, your new MacBook is ARM-based, and your EC2 instance could be ARM as well.

Luca Ferrari: pg_dump and inserts

30. April 2021 - 2:00

pg_dump supports a few useful options to export data as a list of INSERTs

pg_dump and inserts

pg_dump(1) is the default tool for doing backups of a PostgreSQL database.
I often got answers about how to produce a more portable output of the database dump, with portable meaning truly “loadable into another PostgreSQL version or even a different database”.
In fact, pg_dump defaults to use COPY for bulkd loading data: