Using Lua in PostgreSQL – PLLua

Posted on by Areski Belaid
voice broadcasting

Like many before us, we’ve spent time and effort trying to improve our application performance, mostly by tweaking our PostgreSQL database indexes and SQL queries but then we wondered what more could be done, so we built Stored Procedures in PLLua with excellent results. The following article aims to introduce PLLua [http://pllua.projects.pgfoundry.org/] and make a brief comparison with other procedural languages.

At Newfies-Dialer [www.newfies-dialer.org], we build high capacity Voice Broadcasting systems  and in our current stack, we use Celery [http://www.celeryproject.org/] to do post-processing after calls are hung-up. Our Celery tasks mainly log and aggregate the information from the call and do the decision-making required by our application. I’m not going to talk about Voice Broadcasting or telephony, but I do hope to explain how we succeeded in dramatically improving our application performance.

We saw the potential for improvement if we replaced some Celery tasks by procedures and triggers built directly in PostgreSQL.  We didn’t want to write PL/SQL as may fit well for basic function and triggers but once the logic is more complex it’s not a nice language to code. So after discovering PLLua I decided to experiment with it.

Is Lua a good fit in PostgreSQL?

Lua is a popular scripting language, often using in C and C++ applications, it’s easily embedded into your application and Lua really shines in C applications like FreeSWITCH, Redis and Nginx as you can program parts of it without needing to recompile the program. It’s known for being very fast, but I will let you read more about Lua at http://www.lua.org/about.html

Before deciding, I compared Lua with the other options, so if you prefer a Javascript flavor you should look at PLV8 [https://github.com/plv8/plv8], but for us, if Lua shows good performance I would definitely choose LUA over Javascript.

First let’s compare community/usage around PLLua & PLV8.

PLLua:

PLV8:

Both projects are active, with an obvious and expected bigger community in PLV8, not surprising as Javascript is a more popular programming language. You can find debian packages for both PostgreSQL extensions which make it easy to get started.

Get started with PLLua or PLV8

Here are the instructions to install PLLua for Debian/Ubuntu:

$ apt-get install postgresql-9.5-pllua
then,
$ psql
> CREATE EXTENSION pllua;
> CREATE OR REPLACE FUNCTION hello_pllua(name text) RETURNS text AS $$
return string.format(“Hello %s From PLLUA!”, name)
$$ LANGUAGE pllua;
> SELECT hello_pllua(‘PostgreSQL’);

To get started with PLV8:

$ apt-get install postgresql-9.5-plv8
then,
$ psql
> CREATE EXTENSION plv8;
> CREATE FUNCTION hello_plv8(name text) RETURNS text AS $$
return “Hello ” + name + ” from PLV8!”;
$$ LANGUAGE plv8 IMMUTABLE STRICT;
> SELECT hello_plv8(‘PostgreSQL’);

Quick comparison of PLPGSQL, PLLua & PLV8 Performance

Let’s compare the performance of PLPGSQL, PLLua & PLV8, for this we will create the same function in each of the 3 languages and do some basic benchmarking on them. Our functions simply run an arbitrary amount of `Selects` and `Inserts` into a table.

PLLua Example : Accumulator

Gist benchmark_lua.sql:

Result on my local machine: ~6.069 seconds

PLV8 Example : Accumulator

Gist benchmark_v8.sql:

Result on my local machine: ~10.829 seconds

PLPGSQL Example : Accumulator

Gist benchmark_plpgsql.sql:

Result on my local machine: ~5.818 seconds

Compare results

We could have written these functions in one single SQL query but we wanted to compare how the Procedure Languages perform, so although our examples make little sense it gives us an insight into how a language will perform for a simple function.

The chart shows the elapsed time to perform 10.000 `Selects` and 10.000 `Inserts` using PLLua, PLV8 & PLPGSQL:

compare pllua plv8 & plpgsqlSo using PLLua seems to have very little overhead and it performs almost twice as fast as PLV8. ** Just a warning, benchmarks are evil, and every use case will be different. I recommend you stress test your own functions and procedures. **

Full Lua environment with PLLuau

PLLua is fun but PLLuau is even more fun. With PLLuau you have access to the full Lua environment, similar to the standard regular interpreter.

Let’s build an example to illustrate the use of PLLuau; for instance let’s imagine you are building an application that needs to send an SMS after Customer signup. So on `Insert` in your table `User`, we would like to trigger an API Call.

Before creating our trigger we will create a RequestBin (http://requestb.in/), which will make it easy to inspect and test our API requests that will be sent by our PostgreSQL PLLuau trigger.

Gist plluau_trigger_api.sql:

Let’s wrap it up!

We have been using PLLua in our Voice Broadcasting system for a few weeks now, and I am very pleased with it. With PLLua we are able to build much more complex logic inside our triggers, keeping procedures fast and at the same time keeping the code readable and easy to maintain.

It was easy to choose PLLua over PLV8, I was immediately convinced once we realised that the performance was good, and that PLLuau takes full advantage of Lua’s power inside postgreSQL. On the other hand I saw that PLV8 have a bigger community, so that might have an impact in the future. For instance, PLV8 has a remote debugger [http://pgxn.org/dist/plv8/doc/plv8.html] which is something I would love to see in PLLua.

I hope this article will encourage you to try Lua programming [http://www.lua.org/start.html] and use it inside your beloved PostgreSQL Database.

Leave comments or join the discussion on Hacker News [https://news.ycombinator.com/item?id=11352338].

/Areski
@areskib