PostGIS users have a new option for viewing spatial data! DBeaver has joined the spatial database club with its geometry viewer, available since version 6.0.3. I previously wrote about PgAdmin4's geometry viewer, now we have two general purpose database GUI options supporting PostGIS spatial data. This post gives an overview of DBeaver's implementation and my initial thoughts.

This post was written using DBeaver v6.1.0, PostgreSQL 11.3, and PostGIS 2.5.

What it looks like

DBeaver's geometry viewer allows you to easily select and display a single row, multiple rows, or all rows with relative ease. This is handled by you selecting one or more rows of spatial data. Clicking the spatial column's header displays all returned rows.

Continue Reading

This post outlines my experience with becoming more involved with the PostgreSQL development process. Having even a tiny part in reporting and fixing a bug was exciting and rewarding. Turns out, though, there's quite a steep learning curve to get involved. The Postgres project isn't small and things move fast, making it easy to get lost. I have learned a lot and am looking forward to contributing more to the PostgreSQL project and community.

Getting involved

I got involved with the development of PostgreSQL after years of frustration with a particular headache related to QGIS layer styles and pg_dump (fixed!). Granted, the workaround wasn't that big of a deal but did add unnecessary complexity and a point of confusion. I dug into the source code to find why that workaround was required, and that led me to find a case where the workaround no longer worked. Now it wasn't just a hassle, it was a bug 🐛.

Continue Reading

A few months ago I submitted a bug to the PostgreSQL hackers mailing list... It is already fixed thanks to Chapman Flack and others! While the official bug was recently submitted, users in the PostGIS and QGIS communities have been long-plagued by this issue. If adding the following line of code to your pg_dump file is familiar, an upgrade to PostgreSQL is in your near future!

SET XML OPTION DOCUMENT;

Continue Reading

In a previous post I documented PostgreSQL's performance on the Raspberry Pi 3B over multiple versions of Postgres. There I used the standard TPC-B (sort of) tests included with pgbench that try to run as fast as possible. That's reasonable from a certain perspective, but falls short from helping to decide how much hardware you really need.

This post is part of the series PostgreSQL: From Idea to Database.

This post puts Postgres on the Pi under a more realistic workload (for me) and explains how I approached the task. For this post I'm using Postgres v11.2 for all tests and using three Raspberry Pi's for the testing. The configuration for Postgres is the same as the prior post and the only Postgres-specific tuning I do in this post is on max_parallel_workers_per_gather. That brings me to by secondary goal with this post, do a better job finding where PostgreSQL's parallel query feature works well, and where it can hurt performance. Parallel query was introduced in Pg 9.6 and has improved with each subsequent version.

My example workload

My Postgres-Pis are all disposable and easy to rebuild. I have a standard image created that I can flash to an SD card, run an Ansible deployment or two, and the Pi is setup for whatever I want it to do. Much of my querying is from a reporting/analytical perspective. This means I'm using wider tables with multiple data types, indexes, joins, and aggregations. Not tiny SELECT, UPDATE and INSERT queries.

Continue Reading

Have you ever wondered how well PostgreSQL performs on a Raspberry Pi 3B? Oh. You haven't? Oh well, I asked myself that question and put it to the test. This post covers how I used pgbench to answer this question and the results of what I found.

I love the Raspberry Pi, my post Low Power Computing with Raspberry Pi explains more.

This post is part of the series PostgreSQL: From Idea to Database.

TLDR;

Modern PostgreSQL (>=9.6), out-of-the-box, achieves around 200 TPS (TPC-B (sort of)) on a Raspberry Pi 3B. That's 17+ million transactions per day!

Note: These tests used non-app rated SD cards. See my post on SD cards for more details.

What was tested

I tested four PostgreSQL versions, 9.6.12, 10.7, 11.2, and 12-dev. Tests were ran using four Raspberry Pi 3Bs mounted as a Rack-o-Pi. The first round of testing used one version of Postgres installed on each Pi. For the rest of the testing I focused on Pg11 and Pg12.

Continue Reading