What's new in pg_graphql 1.5.7#

Since our last check-in on pg_graphql there have been a few quality of life improvements worth calling out. A quick roundup of the key differences includes:

• Pagination via First/Offset
• Schema based multi-tenancy
• Filtering on array typed columns with contains, containedBy and overlaps

First/Offset pagination#

Since the earliest days of pg_graphql, keyset pagination has been supported. Keyset pagination allows for paging forwards and backwards through a collection by specifying a number of records and the unique id of a record within the collection. For example:

to retrieve the first 2 records after the record with unique id Y3Vyc29yMQ== .

Starting in version 1.5.0 there is support for offset based pagination, which is based on skipping offset number of records before returning the results.

That is roughly equivalent to the SQL

In general as offset values increase, the performance of the query will decrease. For that reason its important to use keyset pagination where possible.

Performance schema based multi-tennancy#

pg_graphql caches the database schema on first query and rebuilds that cache any time the schema changes. The cache key is a combination of the postgres role and the database schema's version number. Initially, the structure of all schemas was loaded for all roles, and table/column visibility was filtered down within pg_graphql.

In multi-tenant environments with 1 schema per tenant, that meant every time a tenant updated their schema, all tenants had to rebuild the cache. When the number of tenants gets large, that burdens the database if its under heavy load.

Following version 1.5.2 each tenant's cache only loads the schemas that they have usage permission for, which greatly reduces the query time in multi-tenant environments and the size of the schema cache. At time of writing this solution powers a project with >2200 tenants.

Filtering array column types#

From 1.5.6 pg_graphql has added contains, containedBy, overlaps filter operators for scalar array fields like text[] or int[].

For example, given a table

the tags column with type text[] can be filtered on.

In this case, the result set is filtered to records where the tags column contains both tech and innovation.

Roadmap#

The headline features we aim to launch in coming releases of pg_graphql include support for:

• Insert on conflict / Upsert
• Nested inserts

If you want to get started with GraphQL today, check out the Docs or the source code.