Skip to content

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.

Smart Views

One common requirement when creating an api through PostgREST is to Block Full-Table Operations (accidentally deleting/updating multiple rows with one request). The only way to accomplish this is either through code at the proxy level (that checks the request query string) or by custom PostgreSql extensions. For this reason, PostgREST+ makes available to the SQL context all the query string parameters which enables some powerful capabilities (we call them Smart Views).

With the aid of a small custom function

create or replace function validate(
  valid boolean, 
  err text,
  details text default '',
  hint text default '',
  errcode text default 'P0001'
) returns boolean as $$
begin
   if valid then
      return true;
   else
      RAISE EXCEPTION '%', err USING
      DETAIL = details, 
      HINT = hint, 
      ERRCODE = errcode;
   end if;
end
$$ stable language plpgsql;

It becomes possible to define views like these

create view api.books as 
  select id, title, publication_year, author_id 
  from private.books 
  where 
    validate(
        (current_setting('request.method', true) != 'DELETE') or
        (
            (current_setting('request.get.id', true) is not null) and
            (current_setting('request.method', true) = 'DELETE')
        )
      ,
      'Full table DELETE blocked',
      'Please provide the id of the book entry you want to delete'
    )

 ;

With a definition like this, when an api client performs a DELETE request without providing the id=eq.10 parameter, he will receive a 400 BAD REQUEST response.

This technique can also be employed when one wants to expose to the api views that contain millions of rows and it's not desirable to allow clients to issue request without specific filters applied. For example if one has a big time-series table with column create_time, a "smart view" can be defined to check that a parameter like ?create_time=ov.[2017-01-01,2017-01-30] was supplied and that the date range does not exceed a month.

Function calls in select

Often times it's very useful to apply some simple transformation to a column, for example joining first_name and last_name in a single column called name. This can be accomplished at the database level through a view or a computed column exposed in the api schema but depending on the requirement of the api clients, the number of these custom definitions can grow exponentially and will become a maintenance burden. By leveraging this feature, api clients can apply transformation to the data without requiring custom definitions at the database level. Here are some sample requests

/projects?select=id,name:$upper(name)
/clients?select=name:$concat('Client: ', first_name, ' ', last_name)
/projects?select=name:$substr(name, '2')
Of course exposing all database level functions like is a security risk so to mitigate that, PostgREST+ is be configured with a list of safe functions using db-safe-functions configuration option. By default these are the configured funtions:
avg, count, every, max, min, sum, array_agg, json_agg, jsonb_agg, json_object_agg, jsonb_object_agg, string_agg,
corr, covar_pop, covar_samp, regr_avgx, regr_avgy, regr_count, regr_intercept, regr_r2, regr_slope, regr_sxx, regr_sxy, regr_syy,
mode, percentile_cont, percentile_cont, percentile_disc, percentile_disc, 
row_number, rank,  dense_rank, cume_dist, percent_rank, first_value, last_value, nth_value,
lower, trim, upper, concat, concat_ws, format, substr

Aggregates / GROUP BY

A big part of why databases are so useful (besides storing data) is their ability to perform aggregate queries. By exposing the groupby query parameter coupled with "Function calls in select" capability, you can run flexible/custom aggregate queries against your tables/views without the need for additional views or stored procedures created specifically for a particular aggregation. Consider this request:

/product_orders?select=city,total_order_amount:$sum(order_amount)&groupby=city

{"city":"Arlington","total_order_amount":"$37,000.00"},
{"city":"GuildFord","total_order_amount":"$50,500.00"},
{"city":"Shalford","total_order_amount":"$13,000.00"}

Aggregates can be an expensive operation if performed over a big dataset (millions of rows), however if the size of the dataset is reasonable (tens of thousands), it is safe to expose this capability to the api. See "Smart Views" for how to guard against exposing groupby for big datasets by enforcing size limiting filters.

Window functions

Another powerful feature available in PostgreSql is window functions, A window function performs a calculation across a set of table rows that are somehow related to the current row. This is comparable to the type of calculation that can be done with an aggregate function. However, window functions do not cause rows to become grouped into a single output row like non-window aggregate calls would. Instead, the rows retain their separate identities.

PostgreSql manual provides this example

SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;

depname  | empno | salary |          avg          
-----------+-------+--------+-----------------------
 develop   |    11 |   5200 | 5020.0000000000000000
 develop   |     7 |   4200 | 5020.0000000000000000
 develop   |     9 |   4500 | 5020.0000000000000000
 develop   |     8 |   6000 | 5020.0000000000000000
 develop   |    10 |   5200 | 5020.0000000000000000
 personnel |     5 |   3500 | 3700.0000000000000000
 personnel |     2 |   3900 | 3700.0000000000000000
 sales     |     3 |   4800 | 4866.6666666666666667
 sales     |     1 |   5000 | 4866.6666666666666667
 sales     |     4 |   4800 | 4866.6666666666666667
(10 rows)

This particular query can be performed with PostgREST+ with a request like this

/empsalary?select=depname,empno,salary,avg:$avg(salary)-p(depname)

Another example:

SELECT depname, empno, salary,
       rank() OVER (PARTITION BY depname ORDER BY salary DESC)
FROM empsalary;

/empsalary?select=depname,empno,salary,rank:$rank()-p(depname)-o(salary.desc)