Neues vom PostgreSQL Planet
Joshua Tolley: On Shapefiles and PostGIS
Partial map of the voyage of the Endurance, from “South”, by Ernest Shackleton
The shapefile format is commonly used in geospatial vector data interchange, but as it’s managed by a commercial entity, Esri, and as GIS is a fairly specialized field, and perhaps because the format specification is only “mostly open”, these files can sometimes be confusing to the newcomer. Perhaps these notes can help clarify things.
Shaun M. Thomas: PG Phriday: Tidying Up With VACUUM
Hubert 'depesz' Lubaczewski: Waiting for PostgreSQL 15 – SQL/JSON query functions
Lukas Fittl: How Postgres Chooses Which Index To Use For A Query
Hubert 'depesz' Lubaczewski: Waiting for PostgreSQL 15 – IS JSON predicate
David Z: parallel commit in postgres fdw
PostgreSQL is one of the greatest open source databases, not only because of the extensibility and SQL compliance but also the evolution of new features. For example, in postgres_fdw, there is a new feature parallel commit has been added into the main branch and will be released in PG15. This blog is for a quick taste of this new feature.
Hubert 'depesz' Lubaczewski: Waiting for PostgreSQL 15 – SQL/JSON constructors
Hubert 'depesz' Lubaczewski: Waiting for PostgreSQL 15 – Add support for MERGE SQL command
Egor Rogov: Queries in PostgreSQL: 3. Sequential scan
In previous articles we discussed how the system plans a query execution and how it collects statistics to select the best plan. The following articles, starting with this one, will focus on what a plan actually is, what it consists of and how it is executed.
Regina Obe: Creating cumulative sums by combining aggregation with windowing
When you want to include a running sum for each line of data in your query, you generally use a window clause with SUM. What if you don't want to list all line items of your data. You want a report that gives you a weekly sum and another that gives you the running sum for the whole year. What do you do then? We'll demonstrate how to do that.
Devrim GÜNDÜZ: How To Build Your Own PostgreSQL (and related software) RPMs on RHEL/Rocky/Fedora
Ryan Booz: Identify PostgreSQL Performance Bottlenecks With pg_stat_statements
We continue our #AlwaysBeLaunching Cloud Week with MOAR features! 🐯☁️ Today, we’re introducing you to pg_stat_statements, a PostgreSQL extension now enabled by default in all Timescale Cloud services. pg_stat_statements allows you to quickly identify problematic queries, providing instant visibility into your database performance.
Claire Giordano: Ultimate Guide to Citus Con: An Event for Postgres
One of the good things with a virtual event like Citus Con is that you have a lot of flexibility about where and when to watch the talks. From your home office, or a café, or the beach—or even the car, while you wait to pick up your kids. As long as you have an internet connection, you’re in.
Bo Peng: Installing Crunchy Postgres Operator v5 on EKS
In my previous post I described how to deploy Crunchy Postgres Operator v4 on Kubernetes and use it to achieve disaster recovery and high availability. The new major version, v5 was release last year and the installation methods have significantly changed.
Oleg Bartunov: SQL/JSON in PG15 !
Slide from my talk Understanding Jsonb performance at PGCONF.NYC, Dec 2, 2021, with some corrections.
Laurenz Albe: How to DROP ROLE or DROP USER in PostgreSQL
© Laurenz Albe 2022
You might, at first glance, believe that DROP ROLE (or DROP USER, which is the same) is a simple matter. However, that’s not always the case. So I thought it might be a good idea to show you the problems involved and how to solve them.
Andreas 'ads' Scherbaum: Claire Giordano
Marco Slot: Test drive the Citus 11.0 beta for Postgres
Today we released Citus 11.0 beta, which is our first ever beta release of the Citus open source extension to Postgres. The reason we are releasing a beta version of 11.0 is that we are introducing a few fundamentally new capabilities, and we would like to get feedback from those of you who use Citus before we release Citus 11.0 to the world.
Alexander Nikitin: No UPDATE updates
Working with databases one can’t help but wonder what happens with data stored on the disk.
For example you need to know this to be able to save at least some of the information on damaged data blocks with help of hexedit.
Today we’ll try to find out how data types such as integer are kept on disk. To do that we will create a testing table, review its contents using hexedit and change data on disk. For the sake of this exercise I will be using PostgreSQL 14.
Let’s create a testing table: