PostgREST+

Here are the enhancements in the commercial version from subZero (PostgREST+).

Prepared Statements

The open source version inlines all the request parameters. For a request like:

GET /projects?select=id,name&id=eq.10
the resulting query (conceptually) looks like this

SELECT id, name FROM projects WHERE id = '10'

while PostgREST+ generated query looks like this

SELECT id, name FROM projects WHERE id = $1
parameters: $1 = '10'

This means for different parameters of the id column, queries generated by the OS version, will need to be parsed, analyzed, and rewritten by the database individually. The queries generated by PostgREST+ wil all be the same, hance they need to be parsed only once.

From PostgreSQL docs

Info

Prepared statements potentially have the largest performance advantage when a single session is being used to execute a large number of similar statements. The performance difference will be particularly significant if the statements are complex to plan or rewrite, e.g. if the query involves a join of many tables or requires the application of several rules.

While open source PostgREST generates a prepared statement for POST/PATCH/PUT requests, it only sends the body payload as a query parameter. All the other GET parameters and headers are still inlined similar to GET requests. Because of this, the advantage explained above holds true for all the HTTP methods (GET/POST/PATCH/PUT/DELETE).

GraphQL support

In order to support the requests coming from the GraphQL API (which is implemented in the proxy layer), PostgREST+ pack some additional logic that is missing from the open source version.

Custom Relations

Automatic relation detection is great and it has gotten a lot more robust over the years however it's still not perfect when it comes to complicated views. For this reason PostgREST+ provides a custom parameter:

# custom relations when auto-detection does not work
# (use "@filename" to load from a separate file)
# The json format is
# [{"schema":"api", "table":"projects", "fkColumns":["client_id"], "fSchema":"api", "fTable":"clients", "pkColumns":["id"]}]
# Which says api.projects.client_id references api.clients.id
custom-relations = "[{\"schema\":\"api\", \"table\":\"projects\", \"fkColumns\":[\"client_id\"], \"fSchema\":\"api\", \"fTable\":\"clients\", \"pkColumns\":[\"id\"]}]"

This additional config parameter will allow you to use the powerful embedding feature even with the most complicated views.