Neues vom PostgreSQL Planet
In my last post, I did a simple intro to foreign data wrappers in PostgreSQL. postgres_fdw is an extension available in Postgres core that allows you to issue queries against another Postgres database.
With the 10.1 release to the Citus extension to Postgres, you can now monitor the progress of an ongoing shard rebalance—plus you get performance optimizations, as well as some user experience improvements to the rebalancer, too.
The goal of this post is to help reading the index access path in a YugabyteDB execution plan.
The Google Summer of Code 2021 for the PostgreSQL Project is wrapped up. The timeline this year was shortened to half, compared to previous years. That’s good, because smaller projects can be worked on, and students have a chance to cope with a changing environment at home and university. On the other hand, the shorter time doesn’t allow diving into more complex projects.
An interesting question came up on the #postgresql IRC channel about how to use native PostgreSQL features to handle queuing behavior. There are existing solutions for queuing, both in PostgreSQL, with the venerable pgq project, or dedicated message queues like RabbitMQ, Kafka, etc.
After a short break, I’m back to writing technical articles about Postgres. This is also an opportunity for me to announce my change of activity. Since 2021, I’m a freelancer to give companies the opportunity to benefit from my experience on Postgres.
When an index is missing,
good performance won’t be kissing
a PostgreSQL user looking for efficiency
but instead feels like a legacy.
To satisfy a DBA’s desire and thirst,
let us load some data first.
pgbench is the tool of the day
but the next listing will explain that anyway:
This post is about postgres JDBC (PGJDBC) prepared statements, the setting prepareThreshold, and the performance implication of it.what is a prepared statement?
A prepared statement is statement that is defined so that it's variables can be set, and executed. An example:
One reason why people choose a UUID for the surrogate key is scalability. Because it generates unique values without having to synchronize with a central generator like a sequence. There are other reasons to use UUID but this post is about scalability. In most databases, sequences can be scalable with a cache: the central catalog object that manages the sequence doesn't have to be read each time we need a next value. Even when the database does not provide a sequence cache, it is very easy to manage from the application.
The data in the main OpenStreetMap database is constantly changing. Folks around the world are almost certainly saving changes via JOSM, iD, and other editors as you read these words. With change constantly occurring in the data, it is often desirable to have an idea of what has actually changed in the data. This post explores one approach to tracking changes to the tags attribute data once it has been loaded to Postgres.
Generally, one appreciates new features of PostgreSQL on the release date after anxious inspection of the release notes or having skimmed through the git logs.
I had an opportunity to sit down with Mark Porter, the CTO of MongoDB to discuss PostgreSQL, Aurora PostgreSQL and MongoDB. Mark is one of the creators of Aurora PostgreSQL and now enjoys a leadership role at MongoDB. There are two episodes:
Creating auto increment columns in PostgreSQL is easy. Simply use two pseudo data types serial and serial8, respectively, then PostgreSQL will automatically take care of your auto increment columns. However, once in a while problems can still occur. Let us take a look and see.Sequences: Avoid manual values
To understand the underlying problem, one has to understand how auto increment and sequences work in PostgreSQL:
PostgreSQL can provide high performance summaries over multi-million record tables, and supports some great SQL sugar to make it concise and readable, in particular aggregate filtering, a feature unique to PostgreSQL and SQLite.
A huge amount of reporting is about generating percentages: for a particular condition, what is a value relative to a baseline.