What is a "truly distributed UDF" ?
Tags: yugabytedb, postgres, udf, sql
Published: 2022-12-08 22:43:41

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_.

![https://web.archive.org/web/20220000000000*/https://www.cockroachlabs.com/blog/distributed-functions-udfs-cockroachdb/](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/h54mj4fx00rt4rvr4g72.png)

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/wz99dbw699bl2fu3lekx.png)
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](https://www.yugabyte.com/blog/distributed-sql-yugabytedb-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](https://www.linkedin.com/pulse/yugabytedb-distributed-sql-features-franck-pachot). 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:

```sql

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:

```sql

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](https://wiki.postgresql.org/wiki/Inlining_of_SQL_functions) 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:

```sql

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:
```sql

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] (https://github.com/cockroachdb/cockroach/issues/87699#issue-1367866124), how can it be qualified as "truly distributed"?