How I Made PostGraphile Faster Than Prisma: 1 Year Later

Thanks, in part, to the incredible performance increases in Node 12

In May last year I released How I Made PostGraphile Faster Than Prisma In 8 Hours to debunk the extremely misleading graph Prisma had integrated into their marketing website.

PostGraphile focusses on performance for the kind of GraphQL queries you’d see when building a web application following best practices—single GraphQL queries that pull all the required data for an individual web page. Prisma benchmarked an unrealistically small query (byArtistId, see below), which effectively meant they were benchmarking the HTTP layer rather than the GraphQL resolution itself. A little friendly competition is good for the ecosystem, and I hadn’t yet optimised the HTTP layer in PostGraphile, so this was good justification to set aside a day later that week to doing some performance work. It didn’t take long for PostGraphile to beat Prisma even at this trivially small query—I do love optimisation!

Benjie 🐘

@benjie

So we're playing the synthetic benchmarks game are we? 😈😁

10:40 AM - 17 May 2018

Six months later, Prisma let me know they’d taken the graph down, and had improved their own performance significantly. They requested that I re-run the benchmarks. As a crowd-funded open source developer, it took a while to find more time to allocate to performance and benchmarking work.

Following the release of PostGraphile 4.4, and as a celebration of the release of Node 12, I allowed myself to spend some time deep in the developer tools for Node, finding where our performance could be further improved. chrome://inspect is incredibly useful for this purpose.

Node 12 itself brought some impressive performance gains, and it also opened wider support for modern JavaScript features, allowing us to tell TypeScript to compile to a newer ECMAScript target and leverage various performance increases from not having to poly-fill expressive syntax. To maintain backwards compatibility, these optimisations are opt-in via the GRAPHILE_TURBO environmental variable. Node 12 also brought with it a new HTTP parser, llhttp, which apparently is a little faster also. All in all, this gave us some great performance gains just from changing some compiler flags and using a newer Node.js version!

In PostGraphile’s codebase itself, there were a few places that we managed to squeeze out some more performance. I’ll release a post soon for Node.js developers explaining exactly what we did (sign up to our mailing list to be notified about this and other Graphile news), but the main things were to reduce our code’s garbage collection overhead, perform more ahead-of-time computation, and to automatically track and reuse PostgreSQL prepared statements.

Following these optimisations I re-ran the benchmarks, testing the latest version of Prisma (1.32), PostGraphile 4.0.0 running on Node 10, and the alpha of PostGraphile 4.4.1 running on Node 12 with GRAPHILE_TURBO enabled. The only significant change we made to the benchmarks was to reduce the warmup concurrency (see albums_tracks_genre_all below for reasoning).

Enough with the story — show us the numbers!

In last year’s graphs, the latest version of PostGraphile (labelled postgraphile-next, which was actually v4.0.0-beta.10) was in pink. PostGraphile v4.0.0 had a similar performance profile to this version, so we’ve made that pink in the new graphs for reference. We’ve added a new line, in green, for the latest version:postgraphile@alpha (v4.4.1-alpha.4).

I also added crosses to the latency charts to indicate when 0.1% or more of the requests failed (and have labelled the crosses with the percentage of failed requests) because this is an important metric that wasn’t previously visible without cross-referencing the relevant “Successful requests” chart. Further, the Y axis has been extended to show a slightly higher range of latencies.

What follows is a section for each of the 5 queries benchmarked. The benchmark setup is almost exactly the same as last year, so I won’t go into it again (see the “Benchmarking” section from last year’s post).

prisma_deeplyNested

This query shows how the various softwares handle a query that touches a number of database tables, relations and columns. Prisma named this request “deeply nested” but it’s not uncommon for a frontend-facing GraphQL API to have to handle a query similar to this.

query prisma_deeplyNested {
  allAlbumsList(condition: {artistId: 127}) {
    albumId
    title
    tracksByAlbumIdList {
      trackId
      name
      genreByGenreId { name }
    }
    artistByArtistId {
      albumsByArtistIdList {
        tracksByAlbumIdList {
          mediaTypeByMediaTypeId { name }
          genreByGenreId { name }
        }
      }
    }
  }
}

albums_tracks_genre_all

Last year we had to exclude this query as we didn’t get any results from Prisma and weren’t sure why. This year we figured it out: Prisma had become overwhelmed during the warmup period and could not respond when the main benchmarks started. The solution was to reduce the concurrency during the 5 minute warmup period from 100rps to 10rps (you can read about why warmup is necessary in last year’s post).

This query shows fetching all the rows from a particular collection in the database, and some of the related records. Typically a frontend GraphQL request like this should have pagination at the root level (e.g. limiting to 50 albums at a time), but since there’s only 347 rows in the albums table it’s not too bad. This query better represents a GraphQL query you might make from your backend rather than one from your web frontend.

query albums_tracks_genre_all {
  allAlbumsList {
    albumId
    title
    tracksByAlbumIdList {
      trackId
      name
      genreByGenreId {
        name
      }
    }
  }
}

albums_tracks_genre_some

This query is almost identical to the previous one, except it reduces the number of results (from 347 down to just 3) by filtering against a specific artist. This is a reasonably good example of a simple frontend GraphQL query.

query albums_tracks_genre_some {
  allAlbumsList(condition: {artistId: 127}) {
    artistId
    title
    tracksByAlbumIdList {
      trackId
      name
      genreByGenreId {
        name
      }
    }
  }
}

byArtistId

This query is extremely simple and light, just requesting two fields from a single row in the database. It’s rare that you’d have a GraphQL request this simple in the web frontend of a non-trivial application—it shows more about the underlying performance of the HTTP layer than the GraphQL resolution itself.

query artistByArtistId {
  artistByArtistId(artistId: 3) {
    artistId
    name
  }
}

tracks_media_first_20

Included for completeness, this query requests 2 columns from 20 rows in a single database table—like a slightly heavier version of byArtistId. GraphQL requests from webpages are rarely this simple.

query tracks_media_first_20 {
  allTracksList(first: 20) {
    trackId
    name
  }
}

Is speed really that important?

Yes and no. I do optimisations because it’s a fun challenge to see how far I can push the computer in an interpreted language without having to make my code too messy. PostGraphile’s users will now benefit from faster performance and happier endusers just from updating to the latest version — they don’t need to change any of their code at all. I think that’s really cool✨

But performance isn’t everything—one of the things we focus on at PostGraphile is extensibility. Our job isn’t to simply convert your database from SQL to GraphQL. Our job is to help you build your ideal GraphQL API as quickly as possible. To help with that, we do as much of the boilerplate for you as we can, but then we give you ways to add to, customise and otherwise make the GraphQL schema your own. We fundamentally do not believe that our job is to expose all the functionality of the database to your end users; instead we believe that we should allow you to leverage the functionality of the database to build the GraphQL API that your frontend developers need, without them having to worry about the complexities of joins, subqueries, common-table expressions, ON CONFLICT DO UPDATE, indexes, SQL query optimisation, and other such things. Despite PostGraphile’s extensibility and flexibility it achieves incredibly good performance, thanks in part to the choice of Node.js as the development platform.

So what’s next?

You can take the new PostGraphile for a spin right now with yarn install postgraphile@alpha. It passes all the tests, but hasn’t been fully vetted by the community yet, hence the “alpha” label—if you try it out, drop us a line on our Discord chat to let us know how you got on!

yarn install postgraphile@alpha

If you appreciate our work, please sponsor us—we’re hugely thankful to our Patreon sponsors who help us to keep moving things forward.

If you appreciate our work, please sponsor us.

Thanks for reading, I’ll be releasing another post soon on the Node.js performance optimisations that I used to make this possible—sign up to our mailing list to be notified about this and other Graphile news.

Sign up to our mailing list