The promise of digital mapping is to provide a shared and real-time view of the state of the underlying system.

pg_eventserv is a free and open-source component that helps fulfill the promise of real-time event modeling and shared views in PostgreSQL.

By connecting to PostgreSQL and listening on specified channels, pg_eventserv captures database notifications and forwards them to web clients, enabling real-time updates and synchronization of data displayed on maps or other web interfaces. It does one thing and one thing only: take events generated by the PostgreSQL NOTIFY command and pass the payload along to waiting WebSocket clients.

pg_eventserv is free and easy to install, and you can find it here: https://github.com/CrunchyData/pg_eventserv. Here is a link to a YouTube video demonstration of pg_eventserv in action: https://youtu.be/UakRtYmoWow

I will let Paul Ramsey, the creator of pg_eventserv, explain all this in more detail in this episode. If you want to reach out to Paul, the best place to do that is http://blog.cleverelephant.ca/

Related podcast episodes

• Rasters In A Database?
• Dynamic Vector Tiles Straight From The Database
• Spatial SQL – GIS Without The GIS
• Distributing Geospatial Data

In Conversation

Who Paul Ramsey Is

Daniel: Paul, welcome back to the podcast. For listeners who haven’t heard you before, would you mind introducing yourself briefly?

Paul: I am a developer of the PostGIS spatial database, and thus someone who knows a little bit about geospatial, a little bit about databases, and a little bit about the uses people put those technologies to.

Daniel: A little bit about everything.

Paul: Yes — I am the ultimate dangerous man.

What pg_eventserv Does

Daniel: What is pg_eventserv, and what does it do?

Paul: pg_eventserv watches for LISTEN/NOTIFY events coming out of the PostgreSQL database and converts those events — which are native to the database world — into events on WebSockets, which are native to the web development world. And with that explanation I have, for most users, explained absolutely nothing.

Daniel: So what problem are we trying to solve?

Paul: The problem is more native to maps than to non-mappy interfaces. Information in a database can change, and the people looking at that information gain a great deal of utility from being able to see that change. All the facilities to do that trick exist in the database and in the web technology world, but there has been a gap in between them that needed to be filled — and that’s what pg_eventserv does. For a banking app, the amount of data someone is looking at is practically none, so there’s low utility in telling clients something changed. But a web map panel might be showing the end user every single record in a table — so if there’s more than one user changing things, it makes a lot of sense for that change to propagate immediately.

LISTEN/NOTIFY and Database Triggers

Daniel: Tell me about LISTEN/NOTIFY.

Paul: LISTEN/NOTIFY is a PostgreSQL-only feature — it doesn’t exist in SQL Server or Oracle. A database client can say “I would like to listen to channel sailboat” — it’s a text string, so it could be any name. Any other client, or the database itself, can say “I have a notification for channel sailboat,” and every client listening on that channel gets the notification. The magic comes when you hook up things that happen in the database to this notification procedure. The easiest thing in the world is to add a trigger to a table that fires every time a row is updated, inserted, or deleted, throwing a notification so every client watching that channel gets an update that the table has changed.

Daniel: When you say “client,” what do you mean?

Paul: A database client is a piece of software attached to the database using the database’s specific communication protocol. QGIS, it turns out, is a PostgreSQL client in that narrow sense — it connects directly using the PostgreSQL connection protocol. I’ve not said Chrome or Firefox, because web interfaces never connect directly to the database — they connect to a web server, and the software inside that web server may or may not connect to the database. So to get a web client to update when the table changes, we need to take that NOTIFY signal and convert it from something database clients can hear into something web clients can hear. pg_eventserv just sits in between, using WebSockets, where unlike most web tech the server can be in control and say “hey web client, here’s something new.”

Wiring It Up to a Web Map

Daniel: If I’m using something like Leaflet, will this just work?

Paul: There’s going to be some plumbing — there’s no standard “moving object layer,” so you’ll have to write a little JavaScript to connect to the WebSocket and plumb between the events coming off it and your layer. The good news is the plumbing is quite small, and you can subsume a lot of the complexity into the database side, because the NOTIFY payload is just a hunk of text. It’s easy to build a JSON payload inside the database and fire it out through the piping. My favorite trick when demonstrating pg_eventserv is to get someone looking at a web map and a QGIS session, write a little SQL that updates the map object, hit return, and — boom — all their maps immediately update. It means any client that can talk directly to the Postgres tables can participate in this live data.

Paul: Even better, the notification can be much more precise than “the table changed” — it can say “this object has moved, and here are its new coordinates,” so the map can upgrade itself in a very granular way, moving objects one at a time. One thing I learned after a blog post: QGIS already has some facilities to do live response to LISTEN/NOTIFY too, so you can tell it to watch for notifications and redraw itself — which means all your clients can be completely live.

Installing and Using pg_eventserv

Daniel: How difficult is it to install pg_eventserv?

Paul: It takes almost nothing. pg_eventserv is written in Go, which is a surprisingly multiplatform language where it’s easy to build self-contained binaries. You go to the pg_eventserv page on GitHub, download a build for Linux, macOS, or Windows, and inside the zip is a single executable. You set an environment variable to tell it which database to connect to, and you run it — that’s it. If you’re into containers, there’s also a container.

Daniel: Does it require a special data model or specific timestamp fields?

Paul: Because the NOTIFY payload is just a hunk of text, there is no preset data model. It takes the text you generate in the database and hands it to the web client watching that channel — so it’s contingent on you as the app designer to format that text in the way most useful to your client. Almost always that ends up being JSON, but it could be anything.

Use Cases Beyond Maps

Daniel: This might be the dumbest question ever on a geospatial podcast — is this only for spatial events?

Paul: No — one of the demonstrations that ships with it is a little chat app. You can write your own PostgreSQL chat bot. It seems pointless, but it’s not a bad example. The real power is thresholding: geofencing is just spatial thresholding — “this thing has moved into this area” — but you can set limits on any kind of measurement, or fit a linear model to a time series and throw an alert when residuals fall outside an expected range. PostgreSQL ships with standard functions for least-squares analyses, so you can do statistical analysis right inside the database. The triggers themselves are just three lines of code, but the function they call can be any length, call any number of SQL commands, and can even be written in Python — so you can do machine learning inside your trigger functions. Anything you can program, you can attach to a trigger.

When Real-Time Is Worth It

Daniel: When is alerting the right answer, and when is polling fine?

Paul: Alerting is the right answer when it truly is a real-time problem — but you can get a long way with polling. Five-minute-old data is usually incredibly good. There’s a fetishism around true real-time-ness, which I admit to pandering to with this tool. You can see the weird bifurcation in apps like Uber: most of the time people’s requirement for real-time data is way lower than they think — giving them five-minute data wouldn’t change their decision process. But things flip when you look at cars on phones: people want to see the vehicle smoothly transition along the road network, when the actual GPS stream is less accurate than they expect and only comes in every 30 seconds. So they actually want to look at data that doesn’t exist — higher resolution than the most real-time data available.

Daniel: Could I take pg_eventserv and use it in production today?

Paul: You could — it’s a wonderfully small project, the most useful tiny thing I think I’ve ever written. I’ve shoved it through high load testing, found my mistakes, and now it doesn’t break. The main caveat for production is that it depends on maintaining the state of your system inside the database model. In the geospatial world you can have enough continuously moving things — say 10,000 objects all reporting every five seconds — that maintaining model state inside the database is just too much transactional churn. But for lots of practical purposes, it’s simple, it doesn’t break — I hear far more complaints about my tile service than I do about this thing.