TrackFootball | Track, Analyse and Improve | Democratizing Football Science

💡

This was an experiment, the idea “worked” but it “failed” for performance reasons. Details below.

For the eager folks, this is the repo that converts typescript files into PLV8 enabled Postgres functions: https://github.com/divyenduz/plv8ify

Context

TrackFootball’s sprint detection algorithm is written in TypeScript. When a new activity is created in TrackFootball, the server computes the sprints and that information to the database.

The Problem

The algorithm is still evolving and changes occasionally and when that happens, I have a script to update older activities.

While running this script is okay, I thought about serving the sprints dynamically from the database as an SQL query with Postgres functions.

I had the option to do this in server, but I wanted to experiment with trying to make the algorithm declarative (SQL is declarative).

The Solution

I wanted to use a Postgres function, but I didn’t want to rewrite the algorithm or change my workflow of developing it, most of which happens in TypeScript.

Being already aware of https://plv8.github.io/, I know that v8 can be run in Postgres. PLV8 is a trusted extension, so it doesn’t have access to the file system or network (so, patching require is needed).

That’s also the approach suggested by folks on the internet https://rymc.io/blog/2016/a-deep-dive-into-plv8/

While I like the way patching require works, I didn’t want to manage modules in the database. Updating the modules from NPM etc. would be a lot of overhead in this approach.

I decided to write a thin CLI utility that bundles TypeScript code and wraps it in a PLV8 function. This way, I can continue my current developer workflow but generate Postgres+PLV8 SQL files.

https://github.com/divyenduz/plv8ify

It Works

With this approach, I can generate PLV8 functions based on TypeScript, even bundling Turf.JS in the process and I can query, in my case, Football sprints from GPS data in SQL with a query like


SELECT
	jsonb_array_length(plv8ify_sprints("geoJson"))
FROM
	"Post"
WHERE
	"id" = 358;

What doesn’t work

Some queries are slow and take around 1-2 seconds in PLV8, while the respective Node.js function only takes around 100ms.

I don’t fully understand what is the cause of this (if you are good with Postgres function optimization, I am happy to provide a reproduction and work with you to fix it).

Here is what I did so far:

Instead of inlining the bundled code, I tried using plv8.start_proc which is a way to bootstrap code when PLV8 is booting. This improved the performance a bit, but still around 1 sec. This is available in plv8ify CLI as a flag called --mode

The function is marked as IMMUTABLE, so Postgres should cache it aggressively based on https://www.postgresql.org/docs/current/xfunc-volatility.html.

Postgres functions in User land are PARALLEL RESTRICT by default and I attempted it after marking them PARALLEL SAFE (This was for a case where I want to use the function in an aggregate query).

Future Steps

This was a good experiment. I am slowly working on a project to make Turf.JS available in Postgres via plv8ify.

Besides, I had an interesting idea that I want to try in the coming weeks. By using plv8ify with React. I believe it should be possible to write your components using existing setup (storybooks etc.) but render them in the database.

Practically, I am not sure how this would be used besides my specific use cases, but doing this was sure is a lot of fun. If you have any thoughts, please reach out to me at https://twitter.com/divyenduz.

Engineering - TypeScript in Postgres with PLV8