PostgREST 12 is out. In this post, we'll focus on a few of the major features. For the complete list, check out the release on GitHub.
Performance: JWT Caching
Until now, PostgREST has validated JWTs on every request. As of PostgREST 12, the JWT is cached on the first request using the exp claim to set the cache entry's lifetime.
Why is that a big deal? Well, it turns out decoding JWTs is expensive. Very expensive.
_10## before_10$ curl 'localhost:3000/authors_only' -H "Authorization: Bearer $JWT" -D -_10HTTP/1.1 200 OK_10Server-Timing: jwt;dur=147.7_10_10## after, with JWT caching_10$ curl 'localhost:3000/authors_only' -H "Authorization: Bearer $JWT" -D -_10HTTP/1.1 200 OK_10Server-Timing: jwt;dur=14.1
The JWT cache shaves over 130ms off the server side timing. For projects with a high volume of API calls, upgrading to PostgREST 12 gives you faster responses, higher throughput, and lower resource consumption.
Server Timing Header
Did you notice the Server-Timing header in the last example? That's new too and it does more than measure JWT decoding duration.
Here's a complete reference to what you can extract from your responses:
_10Server-Timing:_10 jwt;dur=14.9,_10 parse;dur=71.1,_10 plan;dur=109.0,_10 transaction;dur=353.2,_10 response;dur=4.4
Where the information from each phase is internally timed by PostgREST for better visibility into server side performance.
Aggregate Functions
Support for aggregate functions has been much requested feature that went through multiple iterations of design and review.
Currently, PostgREST supports avg, count, max, min, sum. Here's a minimal example using count:
_10$ curl 'http://postgrest/blog_post?select=id.count()'_10_10[_10 {_10 "count": 51,_10 }_10]
We can also add a “group by” simply by adding another element to the select clause.
_12$ curl 'http://postgrest/blog_post?select=title,id.count()'_12_12[_12 {_12 "title": "Supabase Blog",_12 "count": 40_12 },_12 {_12 "title": "Contributors Blog",_12 "count": 11_12 },_12 ...
This example only scratches the surface. Aggregates are fully-compatible with resource embedding which yields an extremely versatile interface. We'll explore this feature more in a deep-dive coming soon.
Media Type Handlers
PostgREST now gives you the flexibility to handle your custom media types and override the built-in ones. Among other things, that enables serving HTML, javascript, or whatever you can think of, straight from your database.
_26create domain "text/html" as text;_26_26create or replace function api.index()_26returns "text/html"_26language sql_26as $$_26 select $html$_26 <!DOCTYPE html>_26 <html>_26 <head>_26 <meta charset="utf-8">_26 <meta name="viewport" content="width=device-width, initial-scale=1">_26 <title>PostgREST + HTMX To-Do List</title>_26 <!-- Tailwind for CSS styling -->_26 <link href="https://unpkg.com/tailwindcss@2.2.19/dist/tailwind.min.css" rel="stylesheet">_26 </head>_26 <body class="bg-gray-900">_26 <div class="flex justify-center">_26 <div class="max-w-lg mt-5 p-6 bg-gray-800 border border-gray-800 rounded-lg shadow-xl">_26 <h5 class="mb-3 text-2xl font-bold tracking-tight text-white">PostgREST + HTMX To-Do List</h5>_26 </div>_26 </div>_26 </body>_26 </html>_26 $html$;_26$$;
With PostgREST running locally we can then navigate to localhost:3000/rpc/index to see
We're still working through the full implications of this feature, but we're very excited internally about the possibilities it unlocks! Similar to aggregate functions, there's a dedicated post for this feature on the way.
Availability
For self-hosting, check out the PostgREST release on GitHub.
The latest version will be rolled out across all projects on the managed platform soon. Keep an eye out for notifications inside Supabase Studio.
