Sammlung von Newsfeeds

Gabriele Bartolini: CloudNativePG: The Most Popular Postgres Operator in 2023

Neues vom PostgreSQL Planet - 22. Dezember 2023 - 9:50
Timescale has just announced the results of its 2023 State of PostgreSQL survey, the fourth of its kind, with responses from almost 900 individuals. As proud partners of this initiative, EDB is pleased to share some key findings that shed light on the dynamic landscape of this database engine. [Continue reading...]

Andreas Scherbaum: PGConf.EU 2023 Review

Neues vom PostgreSQL Planet - 22. Dezember 2023 - 2:16

PGConf.EU 2023 is over, time for my personal review of the conference. Overall we got good and very good feedback, for most parts. Just the coffee …

oded valin: 5 Ways to implement NOT EXISTS in PostgreSQL

Neues vom PostgreSQL Planet - 21. Dezember 2023 - 16:48

When building an application, usually we query our database to retrieve the things we already know: what orders a client made or, more generally, what steps in the app flow have already been taken. However, to correctly address the user journey, it is often more important to query the database and retrieve data that informs us about what is missing: an item not yet in the basket, a missing email confirmation, or a pending payment. Understanding and addressing these missing steps is crucial to optimize the user experience and to drive app adoption.

Shayon Mukherjee: Do you really need Foreign Keys?

Neues vom PostgreSQL Planet - 21. Dezember 2023 - 15:25
Before we dive in, let’s set the stage with a few pointers: YMMV. There’s no one-size-fits-all. My insights are primarily drawn from my experience of handling large-scale, high-transaction Ruby on Rails / Go applications backed by PostgreSQL. I am not here to tell you Foreign Keys are bad. They are great. This post isn’t about that. If there’s one takeaway I’d love for you to have from this post, it’s this: Revisit your database schemas with a critical eye.

Christoph Berg: The 2023 PGconf.EU in Prague

Neues vom PostgreSQL Planet - 21. Dezember 2023 - 10:05

Most of CYBERTEC’s employees are spread over central and Eastern Europe, so we rarely get to see each other in person, except for the ubiquitous video calls that have become common nowadays. Going to conferences is the best way to meet, and among them, the annual PGconf.EU conference is the best and biggest opportunity.

Robert Haas: Praise, Criticism, and Dialogue

Neues vom PostgreSQL Planet - 20. Dezember 2023 - 19:05
When my children were little and I was trying to figure out how to be a parent, I read someplace that you need to have five positive interactions with your child for each negative one to maintain a good relationship. I don't know whether that is fact or myth; a quick Google search suggests that the origin of the idea was in a study about how married couples argue, the idea being that in a good marriage, positive things continue to happen even amidst disagreement.

Nickolay Ihalainen: Using Huge Pages with PostgreSQL Running Inside Kubernetes

Neues vom PostgreSQL Planet - 20. Dezember 2023 - 17:49

Huge pages make PostgreSQL faster; can we implement it in Kubernetes? Modern servers operate with terabytes of RAM, and by default, processors work with virtual memory address translation for each 4KB page. OS maintains a huge list of allocated and free pages to make slow but reliable address translation from virtual to physical.

Andreas 'ads' Scherbaum: FOSDEM PGDay 2024 - FOSDEM PGDay + FOSDEM Devroom Schedule and Registration

Neues vom PostgreSQL Planet - 20. Dezember 2023 - 17:44

We are pleased to announce that we have finalized the schedule for FOSDEM PGDay + FOSDEM Devroom!

Registration is open for the PGDay on Friday, you do not need a registration for the Devroom on Sunday. However based on experience, plan some time for the queue in front of the Devroom.

Christopher Winslett: Using acts_as_tenant for Multi-tenant Postgres with Rails

Neues vom PostgreSQL Planet - 20. Dezember 2023 - 14:00

Since its launch, Ruby on Rails has been a preferred open source framework for small-team B2B SaaS companies. Ruby on Rails uses a conventions-over-configuration mantra. This approach reduces common technical choices, thus elevating decisions. With this approach, the developers get an ORM (ActiveRecord), templating engine (ERB), helper methods (like number_to_currency), controller (ActiveController), directory setup defaults (app/{models,controllers,views}), authentication methods (has_secure_password), and more.

Laurenz Albe: Broken foreign keys: how can that happen in PostgreSQL?

Neues vom PostgreSQL Planet - 20. Dezember 2023 - 10:01

© Laurenz Albe 2023

One of the fundamental requirements for a relational database is that the transaction system always maintains consistency. That means that database constraints must always be satisfied, even in the face of concurrent data modifications. PostgreSQL certainly strives to live up to this requirement. Still, you can end up with broken foreign key constraints if you perform certain dangerous operations. This article shows how broken foreign keys can come about, so that you know what you should avoid in order to maintain consistency.

Julien Rouhaud: Extracting SQL from WAL? (part 2)

Neues vom PostgreSQL Planet - 20. Dezember 2023 - 4:04

In the previous article of this series, we saw how to extract WAL records related to the exact SQL commands we want, INSERTs on heap tables, and what the structure of those records was. In this article we will focus on the heap specific information contained in those records and how to extract SQL queries from them.

Paul Ramsey: Data Science is Getting Ducky

Neues vom PostgreSQL Planet - 19. Dezember 2023 - 17:00

For a long time, a big constituency of users of PostGIS has been people with large data analytics problems that crush their desktop GIS systems. Or people who similarly find that their geospatial problems are too large to run in R. Or Python.

These are data scientists or adjacent people. And when they ran into those problems, the first course of action would be to move the data and parts of the workload to a “real database server”.

This all made sense to me.

Paul Ramsey: PostGIS Clustering with DBSCAN

Neues vom PostgreSQL Planet - 19. Dezember 2023 - 14:00

A common problem in geospatial analysis is extracting areas of density from point fields. PostGIS has four window clustering functions that take in geometries and return cluster numbers (or NULL for unclustered inputs), which apply different algorithms to the problem of grouping the geometries in the input partitions.

Karen Jex: Trying to be Barbie in Ken's Mojo Dojo Casa House

Neues vom PostgreSQL Planet - 19. Dezember 2023 - 9:11

Lætitia Avrot and I co-presented "Trying to be Barbie in Ken's Mojo Dojo Casa House" at PGConf EU in Prague this week. Lætita has published the amazing, very pink slides that she created and I'm publishing the transcript here.

David Z: Experiencing a specific table recovery using file-level backup

Neues vom PostgreSQL Planet - 19. Dezember 2023 - 1:00
1. Overview

In previous blog post, we discussed the process of performing backup and restore operations using pg_rman. pg_rman is a user-friendly tool that supports full and incremental backups and restores for PostgreSQL, operating at the file level. In this blog, I will guide you through a potential method for restoring a specific table using pg_rman.

Tristen Raab: New in PostgreSQL 16: Bi-Directional Logical Replication

Neues vom PostgreSQL Planet - 18. Dezember 2023 - 19:47

In this blog, we’ll be going over some more advanced topics new in Postgres 16. Having some experience with Linux, Postgres, and SQL is necessary as we’ll not only be going over these new features but also how to implement them. This blog was written using PostgreSQL 16 (Development Version) running on Ubuntu 23.04. First I’ll go over some background and a brief introduction to what Bi-Directional Replication is, and why it’s important, then finish off with how we implement Bi-Directional Logical Replication.

Ryan Lambert: Working with GPS data in PostGIS

Neues vom PostgreSQL Planet - 18. Dezember 2023 - 2:15

One of the key elements to using PostGIS is having spatial data to work with! Lucky for us, one big difference today compared to the not-so-distant past is that essentially everyone is carrying a GPS unit with them nearly everywhere. This makes it easy to create your own GPS data that you can then load into PostGIS! This post explores some basics of loading GPS data to PostGIS and cleaning it for use. It turns out, GPS data fr om nearly any GPS-enabled device comes with some... character. Getting from the raw input to usable spatial data takes a bit of effort.

Adam Hendel: Introducing pg_vectorize: Vector Search in 60 Seconds

Neues vom PostgreSQL Planet - 18. Dezember 2023 - 1:00

That’s right. You could have vector searches running on your existing Postgres database in less time than it takes to read this blog. Let’s see how it works:

Intrigued? Let’s take a look at the bigger story.

Laurenz Albe: PostgreSQL hash index performance

Neues vom PostgreSQL Planet - 17. Dezember 2023 - 9:19

© Laurenz Albe 2023

Among the many index types in PostgreSQL, the hash index is the most widely ignored. This came home to me when somebody asked me a question about hash index performance recently. High time to explore that little-known corner of PostgreSQL and run some benchmarks!

Laurenz Albe: What you should know about PostgreSQL minor upgrades

Neues vom PostgreSQL Planet - 17. Dezember 2023 - 9:09

© Laurenz Albe 2023

The PostgreSQL documentation is rather terse on the subject of minor upgrade. The reason is probably that the procedure is so simple. Still, I find that many people don’t understand this topic well, so I thought I should compile the relevant information in an article.

What is a PostgreSQL minor upgrade?

If you look at a PostgreSQL version number like 16.1, it consists of two parts: