How I Made PostGraphile Faster Than Prisma: 1 Year Later
Tags: graphql, sql, node, typescript
Published: 2019-05-22 13:59:41


### 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](https://medium.com/graphile/how-i-made-postgraphile-faster-than-prisma-graphql-server-in-8-hours-e66b4c511160) to debunk the extremely misleading graph Prisma had integrated into their marketing website.

![](https://thepracticaldev.s3.amazonaws.com/i/a14p95pgd0g1pprwmozy.gif)<figcaption>I wish I had the budget to debunk this chart with equivalent pretty design and animation.</figcaption>

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!

{% twitter 997064259220770816 %}

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](https://www.graphile.org/sponsor/), it took a while to find more time to allocate to performance and benchmarking work.

Following [the release of PostGraphile 4.4](https://www.graphile.org/news/postgraphile-version-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](http://eepurl.com/gfV9LD) 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.

![](https://thepracticaldev.s3.amazonaws.com/i/aq2tpiyw09ni2d1yt4ay.png)<figcaption>From ~6:28:33 to ~6:30:18 we ran 200,000 requests against an instrumented instance of PostGraphile, and tracked the memory usage using [node-clinic](https://github.com/nearform/node-clinic) (which is pretty cool, by the way). This “saw tooth” graph shows where Node periodically runs garbage collection (GC) to free up memory that’s no longer in use.</figcaption>

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](https://graphql-bench-visualizer.now.sh/), the latest version of PostGraphile (labelled `postgraphile-next`, which was actually [v4.0.0-beta.10](https://github.com/graphile/postgraphile/releases/tag/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](https://graphql-bench-visualizer2.now.sh/) 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](https://medium.com/graphile/how-i-made-postgraphile-faster-than-prisma-graphql-server-in-8-hours-e66b4c511160)).

####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 }
        }
      }
    }
  }
}
```

![](https://thepracticaldev.s3.amazonaws.com/i/0z4co0litdiwwfa4ysf6.png)<figcaption>This chart shows how many requests per second the various softwares can handle before they start dropping requests (or not meeting the 5s latency cut-off). Prisma have gone from 100rps last year to 225rps — a significant increase. PostGraphile handles higher loads more gracefully now—though we start dropping requests a little after 700rps, even at 800rps we’re only dropping 2.26%.</figcaption>

![](https://thepracticaldev.s3.amazonaws.com/i/w3vmlqu5kwlaylpmtgoc.png)<figcaption>This 95th percentile latency chart shows how quickly the various softwares respond to requests under different loads. The latest PostGraphile has reduced latency across the board, and still performs well past its peak of 600rps.</figcaption>

####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
      }
    }
  }
}
```

![](https://thepracticaldev.s3.amazonaws.com/i/az5qznbw7qr5usnb09d9.png)<figcaption>PostGraphile improves from 70rps to 80rps without dropping any requests.</figcaption>

![](https://thepracticaldev.s3.amazonaws.com/i/zqigo3kprmp4eermdofg.png)<figcaption>Even at 80rps, PostGraphile handles 95% of requests in under 250ms. The latest postgraphile@alpha shows a significant reduction in latency and increase in throughput.</figcaption>

####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
      }
    }
  }
}
```

![](https://thepracticaldev.s3.amazonaws.com/i/18n4dp9smdh365nx0vrf.png)<figcaption>Prisma improved significantly—from 300rps last year to 800rps this year. PostGraphile increased from 1500rps to 1700rps, and handles going over its maximum more gracefully: at 1800rps PostGraphile drops only 0.7% of incoming requests.</figcaption>

![](https://thepracticaldev.s3.amazonaws.com/i/v4rysbafborvjyzjoogx.png)<figcaption>PostGraphile’s latency is lower than ever, serving 95% of requests at 1700rps in under 50ms.</figcaption>

####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
  }
}
```

![](https://thepracticaldev.s3.amazonaws.com/i/hiucjbszte10r2xi9fvn.png)<figcaption>This graph looks surprisingly similar to last years, except everything has moved to the right! PostGraphile can now handle an impressive 3700rps (vs last year’s 3000rps) without dropping any requests, and gets to 4000rps (vs last years 3200rps) whilst dropping only 0.8% of requests. Prisma have improved too, from 2750rps without dropping a request to 3600rps.</figcaption>

![](https://thepracticaldev.s3.amazonaws.com/i/1xg3uboe4fkrhib5ban3.png)<figcaption>The crosses indicating failure percentages really help to interpret the latency results here; after 3000rps Prisma manages to achieve marginally lower latency than PostGraphile for a short while until they start dropping requests at 3700rps. This is likely due to the differences in how Scala (which runs on the JVM) and Node.js handle garbage collection.</figcaption>

####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
  }
}
```

![](https://thepracticaldev.s3.amazonaws.com/i/wt069fda2w77p1aklcal.png)<figcaption>Prisma have improved from 2000rps last year to 3000rps this year. PostGraphile increases its lead at 3500rps.</figcaption>

![](https://thepracticaldev.s3.amazonaws.com/i/r21g8j1632tn9tyvzu2v.png)<figcaption>Again, this graph shows the difference in garbage collection, HTTP overhead, and other related costs in Node.js vs Scala, with Node achieving greater throughput but with marginally higher latency at high concurrency.
</figcaption>

####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](http://discord.gg/graphile) to let us know how you got on!

> `yarn install postgraphile@alpha`

If you appreciate our work, please [sponsor us](https://www.graphile.org/sponsor/)—we’re hugely thankful to our Patreon sponsors who help us to keep moving things forward.

>If you appreciate our work, please [sponsor us](https://www.graphile.org/sponsor/).

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](http://eepurl.com/gfV9LD) to be notified about this and other Graphile news.

> [Sign up to our mailing list](http://eepurl.com/gfV9LD)