PostgreSQL UDF (User Defined Functions) overhead

Disclaimer

The task may seem esoteric, but nevertheless I want to create a POC of some sorts.
The goal

My goal is to make PostgreSQL database (version 10) expose an API to an application that uses it.

An API needs to be in form of a set of UDFs: all functions belong to a public scheme which is the only one accessible to an application. The tables and other stuff are hidden in private scheme. Almost like, you know, an object-oriented database.

Here's why I'm trying to make it work:

• It would decouple database from application, so you can restructure/optimize/denormalize the former with lesser risk of breaking the latter. You can even delegate its maintenance to another team or department (oh my)

• An API formalizes the requirements for a service. A database surely is a service, but the traditional mechanics called migrations doesn't serve well in figuring out what's going on in there. Think of hundreds or thousands of migrations that collected over the years, and some of them are broken and never will work again, and

Well, nevermind.
The issue

So when I've tried to create some very simple functions (like getting all records from a table), I mentioned that they are always slower than the query it wraps. While this is totally acceptable and understandable by itself, the timing difference can be huge. Hence, unacceptable.
The example

I've got a table like this.

CREATE TABLE notifications (
    id SERIAL PRIMARY KEY,
    source_type INTEGER NOT NULL,
    content JSONB,
    created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(3)
)

And >120k records in it.

Imagine we want to get all of them.

Here we do it with a simple query. No indexes, JSONB data is almost 1kb for every record.

```
EXPLAIN (ANALYZE,VERBOSE,BUFFERS) SELECT * FROM private.notifications;
QUERY PLAN
----------------------------------------------------------------------

This is (almost) equivalent to your function in the question, but performs like a plain SELECT:

CREATE OR REPLACE FUNCTION notifications_get_faster()
  RETURNS SETOF private.notifications AS
$func$
SELECT * FROM private.notifications
$func$  LANGUAGE sql STABLE;

Almost, because it's not SECURITY DEFINER, which would prevent the desired effect.

Most notably, you will see a Seq Scan instead of the Function Scan in the query plan. That's what makes most of the difference.
Why?

Neither of your various attempts met all conditions for inlining of table functions. This function does. In particular:

-
the function is LANGUAGE SQL

-
the function is not SECURITY DEFINER

-
the function is declared STABLE or IMMUTABLE

So Postgres can take the functions body and execute it without function overhead ("function inlining"). Only adds very minor extra planning cost as compared to a plain SELECT.

Aside: don't quote the language name. It's an identifier.

StackExchange Database Administrators Q#220689, answer score: 4