TRAKTOR 2 is out!

Check TRAKTOR 2 - less complicated, more stable, cloud ready! Compatible with PostgreSQL 16 and 17.

TRAKTOR revisited

Working on TRAKTOR again. The good news is, it still works with PostgreSQL 17.x and I have completely replaced psycopg2 with pg8000 to remove all native dependencies. The bad news is, if you don't want to run the arbiter as superuser it's a complicated dance of permissions, some of which can only be GRANTed by a superuser, and I have to find a way to automate this as far as possible.

TRAKTOR: Towards the cloud

TRAKTOR now has the capability to analyze the PostgreSQL server logs through file_fdw or log_fdw, without requiring direct access to the server filesystem any more.

This enables it to work with managed PostgreSQL like AWS RDS and Aurora.

File_fdw and log_fdw seem to be lacking from Azure Database for PostgreSQL.

TRAKTOR, true multimaster replication for PostgreSQL

Hi all,

after the retirement of pgchem::tigress, I stumbled over this Blog post, that showed a method to break the vicious replication cycle when using PostgreSQL logical replication in a multimaster cluster, i.e. all nodes are readers and writers.

For PostgreSQL 16.x, this is not necessary anymore, since it introduced origin=NONE, but for pre-16.x,it works quite well.

Yet, setting up a multimaster cluster is tedious and error prone, so I decided to start a new endeavour and make it easier (that was well before the introduction of pgEdge or pgactive).

So without much furter ado, TRAKTOR was released today. With the help of TRAKTOR, you can set-up true multimaster replication clusters on top of vanilla PostgreSQL servers. Notable features are:

1. Does not need anything beyond a standard PostgreSQL server. No fork, no extension.
2. Shared nothing architecture. No single point of failure,
3. Can automatically heal conflicts in case of split brain situations.
4. REST API for control and monitoring.
5. Works with extensions and custom datataypes, PostGIS and rdkit have been tested.

It does NOT help with designing a robust logical multimaster model! No fancy CRDT datatypes, no vector clocks. And it lacks a comprehensive user's guide yet, but there is a tutorial to get you started.

The BfArM database of essential drugs in short supply is lacking an API. So I built one.

The BfArM database of essential drugs in short supply is lacking an API. So I implemented one in about 12 hours. The source code can be found on GitHub.

tl;dr 

The state of digitalization in Germany is in dire straits, especially so when looking at the healtcare system and govenment/administration. If APIs exist, they are often difficult to discover and/or undocumented (The private bund.dev project tries to collect and document them in a central repository).

Yet I was surprised when I, inspired by an an article about the shortage of essential drugs in Germany, took a closer look at the official governmental database on this matter.

This database is hosted by the "Bundesinstitut für Arzneimittel und Medizinprodukte (BfArM)", and can be found here.

It can be accessed in two ways:

1. As filterable dynamic HTML/JavaScript table rendered using JSF.
2. As CSV download.

No API for M2M communication or the like. At least I could not find one. There is also no documentation about the data itself. 

Regarding data quality, I noticed the following issues:

1. The CSV file is encoded in ISO-8859-1 (Latin1) and not UTF-8. While this is not uncommon it is a bit unexpected, since ISO-8859-1 only covers the first 256 Unicode characters. The file encoding is not documented.
2. The CSV is actually not comma, but semicolon separated.
3. Data not available is not only NULL, it is also encoded as "N/A", "n.a.", "-", and "'-", respectively. There might me more undocumented encodings.
4. "*" encodes "Altdatenübernahme war nicht möglich", meaning that older data could not be transferred. This is documented in the legend of the table, but not what it actually means.
5. The update frequency of the data on display is not documented.

Having worked with databases for about 30 years now, this looks like this data comes directly from some kind of manually curated data set to me. There is obviously no decent data standardization process in place.

But whining alone doesn't help, so I decided to implement the missing API on a tiny server hosted in Germany. It took me about 12 hours of my private time, including implementing basic data sanitization.

The most difficult part was to automate the CSV download, since the submit button calls some JavaScript function, and thus can't be called using a HTTP request or a scraping library like beautifulsoup. I'm now using a remote controlled headless Browser via selenium. That the HTML name attribute frequently changes does not especially help, either. This has been solved with an XPATH expression on the value.

The demo API is available under https://3.73.42.17:8443/docs. You will be warned because of the self-signed SSL certificate. This is ok, AWS did not want to register a domain, so no Let's Encrypt. But this just swaps SSL with SSH semantics anyway. Since it is for demonstration purposes only, and runs on a small server, there is a rate limiter in place. Resource names and data are in German, like in the source system.

The source code can be found on GitHub. Maybe somebody at BfArM realizes that it does not cost a fortune to implement an API on top of what they already have, and finds my example useful to build on.

Public data (I assume it is public, there is no need to register) is updated daily at 11:00 UTC. In complicance with Article 4 of the GDPR, personal information (Client IP address, Telephone number and E-Mail address) is not stored or displayed. As mentioned before, the API is hosted in Germany.

I'm not intending to run this forever, and outages are possible at any time. I have shut down the service by now.

Native (PostgreSQL only) streaming data tables

If you want to see (and analyze) only a window of data over some continuous data stream in PostgreSQL, one way is to use a specialized tool like the PipelineDB extension. But if you can't do that, e.g. because you are stuck with AWS RDS or for some other reason, streaming data tables, or continuous views, can be implemented with pretty much PostgreSQL alone.

The basic idea is to have a table that allows for fast INSERT operations, is aggressively VACUUMed, and has some key that can be used to prune outdated entries. This table is fed with the events from the data stream and regularly pruned. Voilà: a streaming data table.

We have done some testing with two approaches on an UNLOGGED table, prune on every INSERT, and pruning at reqular intervals. UNLOGGED is not a problem here, since a view on a data stream can be considered pretty much as ephemeral.

The timed variant is about 5x - 8x faster on INSERTs. And if you balance the timing and the pruning interval right, the window size is almost as stable.

The examples are implemented in Python3 with psycopg2. Putting an index on the table can help or hurt performance, INSERT might get slower but pruning with DELETE faster, depending on the size and structure of the data. Feel free to experiment. In our case, a vanilla BRIN index did just fine.

Instead of using an external scheduler for pruning, like the Python daemon thread in the stream_timed_cleanup.py example, other scheduling mechanisms can be of course used, e.g. pg_cron, or a scheduled Lambda on AWS, or similar.

Feel free to experiment and improve...

MQTT as transport for PostgreSQL events

MQTT has become a de-facto standard for the transport of messages between IoT devices. As a result, a plethora of libraries and MQTT message brokers have become available. Can we use this to transport messages originating from PostgreSQL?

Aa message broker we use Eclipse Mosquitto which is dead simple to set up if you don't have to change the default settings. Such a default installation is neither secure nor highly available, but for our demo it will do just fine. The event generators are written in Python3 with Eclipse paho mqtt for Python.

There are at least two ways to generate events from a PostgreSQL database, pg_recvlogical and NOTIFY / LISTEN. Both have their advantages and shortcomings.

pg_recvlogical:

• Configured on server and database level
• Generates comprehensive information about everything that happens in the database
• No additional programming neccessary
• Needs plugins to decode messages, e.g. into JSON
• Filtering has to be done later, e.g. by the decoder plugin

NOTIFY / LISTEN:

• Configured on DDL and table level
• Generates exactly the information and format you program into the triggers
• Filtering can be done before sending the message
• Needs trigger programming
• The message size is limited to 8000 bytes

Examples for both approaches can be found here. The NOTIFY / LISTEN example lacks a proper decoder but this makes be a good excercise to start with. The pg_recvlogical example needs the wal2json plugin, which can be found here and the proper setup, which is also explained in the Readme. Please note, that the slot used in the example is mqtt_slot, not test_slot:

pg_recvlogical -d postgres --slot mqtt_slot --create-slot -P wal2json

Otherwise, setup.sql should generate all objects to run both examples.

tcn again

In the previous post, I suggested that contrib/tcn for example could be used to selectively update caches.

OK, here is my try:


By touching the cache only when the database signals a change of the base table and using the passed information to touch the affected rows only, this should be much more efficient than other strategies like routinely invalidating the cache every n minutes or so.

Module Monday: tcn

The next installment of the series covers contrib/tcn.

tl;dr

Tcn provides a native trigger function to send notifications about INSERT, UPDATE and DELETEs on any table it is attached to, to subscribers of an arbitrarily named channel via PostgreSQL's NOTIFY/LISTEN mechanism. This is useful e.g. for refreshing caches automagically.

First, tcn must be installed in the database:

CREATE EXTENSION tcn;

Now, a test table is created:

CREATE TABLE public.cats
(
  id serial NOT NULL,
  face_name text NOT NULL,
  CONSTRAINT cats_pkey PRIMARY KEY (face_name)
)
WITH (
  OIDS=FALSE
);

And the trigger:

CREATE TRIGGER cats_tcn_trigger
   AFTER INSERT OR UPDATE OR DELETE ON cats
   FOR EACH ROW EXECUTE PROCEDURE triggered_change_notification('cats');

Here, the channel is named 'cats'. If the parameter is omitted, the channel is 'tcn' by default.

It's all set, now we can subscribe to channel 'cats':

LISTEN cats;

Now, when we INSERT some data, the following happens:

INSERT INTO cats (face_name) VALUES ('Tailchaser');

Query returned successfully: one row affected, 13 msec execution time.

Asynchronous notification of 'cats' received from backend pid 5457

Data: "cats",I,"face_name"='Tailchaser'

Multiple operations generate multiple notifications:

INSERT INTO cats (face_name) VALUES ('Hushpad'),('Pouncequick');

Query returned successfully: 2 rows affected, 11 msec execution time.

Asynchronous notification of 'cats' received from backend pid 5457
Data: "cats",I,"face_name"='Hushpad'
Asynchronous notification of 'cats' received from backend pid 5457
Data: "cats",I,"face_name"='Pouncequick'

But what is this good for?

Well, some PostgreSQL drivers support asynchronous notification, e.g. libpq for C, psycopg2 for Python or JDBC for Java. So this can be used for notifying all clients if the content of a table has been changed.

Then the client can react accordingly, e.g. refresh the cache of such a table, or display the changes in a (near) real-time information system, or whatever action is required in the context of the application if a change has occurred.

The following Python code shows an example matching the 'cats' table from above.

Start with python notify.py:


Waiting for notifications on channel 'cats'

Another INSERT

INSERT INTO cats (face_name) VALUES ('Roofshadow');

yields

Got NOTIFY: 5457 cats "cats",I,"face_name"='Roofshadow'
Must refresh cache

from Python.

As always, imagination is the limit.