Neues vom PostgreSQL Planet

Yaroslav Schekin (ysch) reported on irc that Index Scans Backward do not display properly. After checking I found out that if explain is in JSON/YAML/XML – node type is changed to “Index Scan" (or “Index Only Scan" if it was originally “Index Only Scan Backward"). Some more debugging/diagnosing later and I found the problem. Basically, … Continue reading "Fixed display of Backward scans on explain.depesz.com"

Recently landed in PostgreSQL, jsonb subscripting support doesn't look as exciting as some other improvements around jsonb. But it's user visible changes are only tip of the iceberg. How many people were involved to make it, and what decisions choices were made? How long did it take, and what are the good/bad ideas to work on a patch?

In this post I will show you how I discovered that the backend was putting data into a new table (generate_plist) but the website was still using the ports.pkg_plist column. The result was stale data being shown on the website (for older ports) or no data at all (for newer ports). How long? Since Oct [...]

To make it easier for our readers to install PostgreSQL on Ubuntu, we have compiled a quick guide to getting started. Don’t worry, it’s not too hard. You will learn:

PostgreSQL Person of the Week Interview with David E. Wheeler: I’m a long-time open-source hacker in his early fifties. I’ve lived and worked in San Francisco, Portland OR, and now New York City.

Previously I wrote about locating config files. The thing is – postgresql.conf is not the only place you can set your configuration in. In here, I'll describe all the places that can be used, why do we even have more than one place, and finally – how to find out where given value comes from. … Continue reading "Starting with Pg – where/how can I set configuration parameters?"

While migrating from Oracle to PostgreSQL, most of the developers ask about the options available in Handling Global associative arrays in PostgreSQL. It might get challenging if the variables need to be accessible in the Exception block. To avoid developers from finding it difficult while performing conversions of PL/SQL from Oracle to PostgreSQL, we are writing this blog post to demonstrate the workarounds available with not much of additional programming effort.

Pgpool-II has been released more than 15 years. When the version 1.0 was released, Pgpool-II had only replication mode. With the increase of new features (e.g. automatic failover, support for logical replication, etc.), the parameters for configuring cluster mode have increased. 

Yes, this post is about connection queueing, not just pooling. Because “connection pooling” – pre-created connections as a pool – is a much-celebrated feature. Almost every discussion on connection pool/pgBouncer starts with the overhead of establishing a new connection to PostgreSQL… and how pre-created connections in the pool can save the world.

This is something a lot of people want: being able to track changes happening on their database when and who ordered them. Well, I have good news, my friends, there is a native feature in Postgres that can help you achieve that exact goal! DDL and DML First things first, what are DDL and DML? Both DDL and DML are a subset of the SQL language. DDL means Data Definition Language (or Data Description Language).

A few weeks ago I decided to seriously consider Postgres' declarative table partitioning for our OpenStreetMap data. Once the decision was made to investigate this option, I outlined our use case with requirements to keep multiple versions of OpenStreetMap data over time. That process helped draft my initial plan for how to create and manage the partitioned data.

I released new version of pspg. Now, there is an possibility to select rows, columns or block (by mouse or by keys) for export.

© Laurenz Albe 2021

PostgreSQL v14 has new connection statistics in pg_stat_database. In this article, I want to explore one application for them: estimating the correct size for a connection pool.

New connection statistics in v14

Commit 960869da080 introduced some new statistics to pg_stat_database:

In the previous article, we explored GitOps and how to apply GitOps concepts to PostgreSQL in a Kubernetes environment with the Postgres Operator and custom resources. The article went on to mention additional tooling that has been created to help employ GitOps principles within an environment, including Helm.

PostgreSQL is one of the most advanced open source databases globally with a lot of great features. One of them is Streaming Replication which was introduced in PostgreSQL 9.0—known as Physical Replication. It works by sending the WAL segments generated by the primary PostgreSQL to another standby server also running PostgreSQL. However, if we only want to replicate a portion of the database tables, then Physical Replication in this case is not ideal.

Just like in most databases, in PostgreSQL a trigger is a way to automatically respond to events. Maybe you want to run a function if data is inserted into a table. Maybe you want to audit the deletion of data, or simply respond to some UPDATE statement. That is exactly what a trigger is good for. This post is a general introduction to triggers in PostgreSQL. It is meant to be a simple tutorial for people who want to get started programming them.

A surprisingly common problem in both application development and analysis is: given an input name, find the database record it most likely refers to. It's common because databases of names and people are common, and it's a problem because names are a very irregular identifying token.

PostgreSQL Person of the Week Interview with Takayuki Tsunakawa: I’m a Japanese man who was born and lives in Japan, near Tokyo. I am visually impaired by nature, and I have only enough eyesight to feel the light now. But I had somewhat better vision when I was a child, and enjoyed playing video games with friends or alone. I can’t see the screen, so I use screen reader software that reads characters by synthesized voice.

My previous two posts set the stage to evaluate declarative Postgres partitioning for OpenStreetMap data. This post outlines what I found when I tested my plan and outlines my next steps. The goal with this series is to determine if partitioning is a path worth going down, or if the additional complexity outweighs any benefits. The first post on partitioning outlined my use case and why I thought partitioning would be a potential benefit.

In my day to day, I get to work with many customers migrating their data to Postgres. I work with customers migrating from homogenous sources (PostgreSQL) and also from heterogenous database sources such as Oracle and Redshift. Why do people pick Postgres?