What is a "truly distributed UDF" ?

You may love YugabyteDB and still read other distributed SQL database announcements (even if they are not truly PostgreSQL-compatible, and not truly Open Source 😉). I do the same: I love SQL databases and don't want to miss any true innovation when it happens.

The sneaky reference to YugabyteDB

I've read recently a mention of a vision of truly distributed UDFs with a stray bullet starting the sentence by: Instead of grafting legacy features onto a distributed database.

There's only one Distributed SQL Database that re-uses the PostgreSQL code as the base for its query layer: YugabyteDB.

This looks like a pejorative or dumb interpretation of Yugabyte's Two-Layer Architecture which reuses PostgreSQL code, to provide the best features and compatibility. This claim ignores all code enhancement that makes this SQL layer cluster-aware, with high-performance in mind, by pushing down some SQL processing to the distributed storage. There is no central node in YugabyteDB and it is easy to prove: any node can be stopped and the database is still available.

This is not new. Their "CockroachDB in Comparison" documentation is full of such misunderstanding which I debunked here. Note that I'm fully aware that this comes from marketing and not technical teams who know how distributed databases work.

The facts: YugabyteDB distributes UDFs thanks to the PostgreSQL code

There are too many empty words in this marketing post from Cockroach Labs, so let's go to the facts. In YugabyteDB, I create a one million rows table and create this simple UDF. An UDF, User Define Function, is just a function that can be called from SQL. Nothing fancy, it is a very old SQL feature:

yugabyte=# CREATE TABLE demo AS SELECT n a, n b 
           FROM generate_series(1,1000000) n;

yugabyte=# CREATE FUNCTION add(a INT, b INT) RETURNS INT
           IMMUTABLE LEAKPROOF LANGUAGE SQL AS 'SELECT a + b';

This example function comes from the competitor's blog about this feature. You will find it if you want to, but I don't want to increase their SEO with a link to marketing FUD. There is another example in their blog, but it is not PostgreSQL-compatible as it tries to cast a time interval to an integer (that's the risk when you re-write a query layer from scratch instead of re-using the proven code 😉).

If we were only grafting legacy features onto a distributed database we would observe this:

yugabyte=# set yb_enable_expression_pushdown=off;
SET

yugabyte=# explain (costs off, analyze) 
           select add(a,b) from demo where add(a,b) <= 42;

                            QUERY PLAN
------------------------------------------------------------------
 Seq Scan on demo (actual time=146.710..5552.085 rows=21 loops=1)
   Filter: ((a + b) <= 42)
   Rows Removed by Filter: 999979
 Planning Time: 0.051 ms
 Execution Time: 5552.156 ms
 Peak Memory Usage: 8 kB
(6 rows)

This has read 1 million rows from the distributed storage, and has removed 999979 in the SQL layer to apply the where add(a,b) <= 42 predicate. This is not distributed and not efficient: 5 seconds in this multi-region cluster with 10 millisecond latency between nodes.

I disabled yb_enable_expression_pushdown to run the PostgreSQL code without push-down enhancements for the demo, but you should have it enabled.

If you look at the execution plan, you can see that the function body has been inlined. We don't see the (add(a,b) <= 42) predicate but ((a + b) <= 42) with the function body applied to the function arguments. This is a great feature we have thanks to re-using the PostgreSQL code.

There is another feature in YugabyteDB, added to the PostgreSQL code, to pushdown expression filters. I've disabled it above to show you what would be the execution of the pure PostgreSQL code. Now, with this YugabyteDB feature:

yugabyte=# set yb_enable_expression_pushdown=on;
SET

yugabyte=# explain (costs off, analyze) 
           select add(a,b) from demo where add(a,b) <= 42;

                           QUERY PLAN
-----------------------------------------------------------------
 Seq Scan on demo (actual time=790.084..790.090 rows=21 loops=1)
   Remote Filter: ((a + b) <= 42)
 Planning Time: 0.049 ms
 Execution Time: 790.144 ms
 Peak Memory Usage: 8 kB
(5 rows)

Do you see the difference? It is faster of course and the reason is visible in the execution plan. Instead of Rows Removed by Filter in the SQL layer, we have a Remote Filter which is applied on each storage server.

I've taken this simple function from our competitor's announcement, without changing the code, and it is a great example of the perfect marriage between PostgreSQL and YugabyteDB: the features from each one combine to provide the best scalability with the all SQL features.

The real innovation: YugabyteDB can not only distribute UDFs, but without limitations on indexing, views, language...

By the way, the function can be indexed of course, another feature from PostgreSQL (expression index), and YugabyteDB distributes index scans:

yugabyte=# create index demo_udf on demo( add(a,b) asc ) include (a,b);
CREATE INDEX

yugabyte=# explain (costs off, analyze)
           select * from demo where add(a,b) <= 42;

                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Index Only Scan using demo_udf on demo (actual time=1.105..1.115 rows=21 loops=1)
   Index Cond: (((a + b)) <= 42)
   Heap Fetches: 0
 Planning Time: 3.731 ms
 Execution Time: 1.145 ms
 Peak Memory Usage: 8 kB
(6 rows)

That's what you can expect from a Distributed SQL database: single-digit milliseconds. You can't have that with full table scans.

That's the main reason for deterministic UDFs: use them in queries, indexes and views. You don't want to recalculate the same on each row in each query. And it is then distributed with the index sharding. If not supported in indexes, how can it be qualified as "truly distributed"?