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 🐘 on Twitter

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

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.

How I Made PostGraphile Faster Than Prisma: 1 Year Later was originally published in Graphile on Medium, where people are continuing the conversation by highlighting and responding to this story.

PostGraphile is now in production use by many companies, with applications in industries ranging from farming to aerospace. We’ve always had a strong focus on stability, and we do our utmost to ensure you don’t have to rewrite your stack as we introduce new features (assuming you stick to the official releases!)

One of the biggest aims for the release of version 4 was to maintain as much compatibility with version 3 as we possibly could whilst completely re-writing the core engine, and we were very successful at this. Another big aim was to stop trying to be a “one size fits all” GraphQL solution and instead focus on handling the repetitive tasks and allowing users to customise and extend the resulting schema as they saw fit. Our plugin system has been very successful at this and is proving very powerful, with community members making available a number of plugins that enhance PostGraphile, and businesses writing their own private plugins to handle their own specific needs.

With all these people relying on us, it’s critical that we balance the needs to improve PostGraphile with the needs to remain stable for our users, and I think we’ve done a great job of this since the release of v4.0.0. However, many of the great improvements we’ve made since the launch of v4 are not available to users by default — they have to “opt in” via flags or plugins — and many don’t get that far in our documentation.

With that in mind, the next major version of PostGraphile, v5, will aim to improve the out-of-the-box experience, whilst making the upgrade from v4 as painless as possible for existing users via the addition of a v4 compatibility plugin.

Below, you’ll find in detail a list of most of the planned breaking changes, but in summary:

Changes which (probably) won’t have backwards compatible solutions include:-

• Overhauling the introspection system (only affects plugin authors)
• Introducing plugin priorities (only affects plugin authors)
• Type generation during `build` will be banned (only affects a small number of plugin authors)
• Postgraphilerc will be changed to use library options (affects people using a postgraphilerc file)

The other planned changes will probably be solvable by using a — v4 flag or similar to revert to the previous behaviour. These changes include:

• Simpler generated names
• Will mount to the root path (“/”) by default in CLI
• GraphQL schema will be responsible for managing PostgreSQL connections/transactions
• NodeIDs will use GraphQL type names rather than database table names
• `pgSettings` will be upgraded to deal with JWTs
• `postgraphile` CLI will use first available port if port 5000 is busy

More detail on these changes can be read in detail below at your leisure, and they’re still in the planning stages so do come chat with us on Discord if you have any thoughts!

And so we come to the end of our Birthday Week celebrations. We have released a Medium article every day this week to celebrate, with a different focus each day. You can see them all in our new Graphile Medium Publication. We’ve had a fantastic time this past year, growing both PostGraphile the project and PostGraphile the community. I’m now pressing publish just as I am waiting for my plane to depart to Brussels for FOSDEM (so excuse typos!) and, as always, want to take this opportunity to send a HUGE THANK YOU to all our sponsors, contributors, evangelists and other supporters — this would not be possible without you!

Remember, if you don’t already, you can sponsor PostGraphile on Patreon, and join our community on Discord.

Backwards Incompatible Breaking Changes

Introspection

We’ll be changing the introspection system to be extensible via plugins; we’ll also probably switch to getter methods for retrieving things rather than (or in addition to) object traversal. A hook will probably be added to enable you to augment introspection results before they are “baked” (interlinked/cached/etc).

Hook priorities

Hooks will define priorities (or provides/depends — it’s not been decided yet) that allow you to control the order in which they are applied without the user having to reference plugins in a particular order.

Type generation during `build` will be banned

And type generation during `init` will be dissuaded. Sadly, I learned that building types during `build` was not optimal only after the release of 4.0.0. The issue is that it makes it a lot harder for other plugins to hook these objects since the build object may not be finalised (so not all the helpers are present yet). It can also result in building “orphaned” objects that are never used, wasting processing time and memory. In v5 we’ll probably switch to registering type generator functions that are executed “Just In Time” rather than generating types up front (we already do this with table, view and compound types and it works brilliantly).

Postgraphilerc will be changed to use library options

Currently postgraphilerc uses the options that come out of the CLI library that we use, which do not always use the names documented in the CLI or Library pages. We’ll change it so that it uses the library option names instead. This should also mean that a postgraphilerc could be shared between library and CLI usage which would be awesome!

Enhanced GraphiQL will be the only GraphiQL

GraphiQL, the GraphQL IDE, is an excellent tool for exploring your GraphQL API, and with recent enhancements such as GraphQL Explorer from OneGraph it’s better than ever. Since it’s easy to add your own bespoke GraphiQL route should you so desire, we’re going to focus on PostGraphiQL (our flavour) as a development tool, and as such we’ll be changing it and enhancing it over time. You can still use it in production if you want, but it will be PostGraphile branded 😉

Backwards Compatible Breaking Changes

Isn’t that an oxymoron? Well, thanks to our powerful plugin system: no. We’re able to make changes to defaults and the schema in v5 whilst offering a flag/plugin that restore the behaviours of v4 that your application uses.

Simpler names by default

Anyone who has been following along with @graphile-contrib/pg-simplify-inflector will know that one of my obsessions is simplifying our generated GraphQL schemas down from having fields like `personByAuthorId` to simply `author`. It’s a challenge to do this in an auto-generated schema without causing field name conflicts, but pg-simplify-inflector has done a great job and I believe we can do even better… If we can, without causing a significant drop in compatibility with existing databases, then I definitely plan to bake this in as default behaviour in v5 (but it will be made backwards compatible via a plugin). For now, you can use @graphile-contrib/pg-simplify-inflector to get a feel for this, just beware of semver-major updates changing names of things.

Will mount to the root path (/) by default in CLI

We currently mount to /graphql and /graphiql for legacy reasons, this sometimes causes user confusion. We will change this to `/` by default for the CLI (and maybe also the library) but you can still use the relevant GraphQL route options to configure this back to its old behaviour.

PostgreSQL connections baked into the schema

In all previous versions of PostGraphile, when you run a query you first get a PG client from the pool, start a transaction, configure the transaction for the user, and then you pass that client to the GraphQL schema (via the `context` argument) for use during resolution. Once the GraphQL request has resolved you then commit the transaction and release the client back to the pool. This has worked really well for PostGraphile’s own server/middleware, but unfortunately it works less well with various other software in the GraphQL ecosystem that do not have support for guaranteed (and asynchronous) active releasing of the GraphQL context. This means that when you combine PostGraphile with other GraphQL tools you sometimes have to use it as a remote schema.

In version 5 we’re going to change how this behaves a little: if you pass a pgClient on `context` then all will work as before, but if you do not (which will be the default for the PostGraphile server) then the schema will manage allocating and configuring PG clients and transactions. This should greatly increase our compatibility with other GraphQL tooling such as Apollo Server and GraphQL Weaver.

Node IDs

Graphile Engine uses the GraphQL type name in the generated NodeIDs, but as part of maintaining compatibility with version 3, we had to add a plugin (PgNodeAliasPostGraphile) that overrode these NodeIDs to instead use the actual table names. This means if you base64 decode a NodeID in PostGraphile currently you can see the underlying table name (even if you’ve renamed the resource using smart comments or an inflector plugin).

Version 5 will using the Graphile Engine native NodeIDs by default, but you will be able to add a small plugin to restore the v3-flavoured NodeIDs.

pgSettings will be enhanced or replaced

We’re going to change how pgSettings interacts with JWTs slightly, and try and make it work better with other transports such as websockets. If necessary we’ll introduce a new interface, but we’re hoping to avoid this.

The CLI `postgraphile` will find first available port

If port 5000 is in use and you don’t specify a port number, we’ll find an available port. To stop this being a breaking change, ensure you explicitly set the port number on the command line: `-p 5000`

Wow, thanks for reading this far! If you have any feedback, come chat to me on Discord (but don’t expect fast responses this weekend as we’re at FOSDEM!)

Birthday Week: Looking to the future was originally published in Graphile on Medium, where people are continuing the conversation by highlighting and responding to this story.

I love hearing from PostGraphile users about how they use the software, especially when I hear stories like how PostGraphile enabled two developers to do a better job in three months than 8 developers did in a year. Just yesterday, user ‘@envoked’ popped into our Discord chat:

I’ve been using PostGraphile to prototype a GraphQL backend at my company and I’m super impressed. It brings me the same level of joy I had when I first discovered Rails/ActiveRecord 10+ years ago. Thanks Benjie and team.

This kind of feedback is only possible because we have been able to spend significant time on PostGraphile throughout the last year. Back in January 2018, we set up a Patreon sponsorship page and began asking for contributions towards our time working on the project. By September, we passed the $1000 /mo mark and with this growing support we’ve been able to devote more and more of our time to PostGraphile.

Among other things, Patreon support has enabled us to -

• Massively increase PostGraphile performance
• Implement the powerful Smart Comments system for customising your GraphQL schema
• Make PostGraphile smarter about PostgreSQL extensions, permissions and indexes
• Implement `graphile-utils` — a module that makes it much easier to customise your schema
• Overhaul the error system, including adding helpful hints on how to fix many issues
• Enhance the plugin API to better support community plugin developers
• Overhaul and massively improve our TypeScript support

Besides these new features, Patreon support has also enabled us to work hard to improve PostGraphile’s documentation—we’ve even received a lot of compliments on it recently! Of course, there’s always more to do, and along with the new learning resources at learn.graphile.org (see Tuesday’s post) we’re going to be having a special focus on documentation and examples over the coming months.

We want to take this opportunity to say a huge “thank you!” to everyone who has donated any amount to our Patreon over the past year.

Since December 2017, Patreon supporters on the “Sponsor” tier or above have been listed inside the PostGraphile project in a “SPONSORS.md” file. Back in November, we updated the PostGraphile command’s greeting message to feature a random sponsor.

Today, as part of our ongoing website overhaul, we are happy to announce we are now featuring sponsors on our website. PostGraphile is a community effort, so we are pleased to underline this on our website.

We also want to extend our sincere thanks to all our GitHub contributors over the lifetime of the project. All code and documentation contributions, no matter how large or small, whether you’re a seasoned open source contributor or this is your first pull request, are all welcomed.

If you would like to get involved with contributions, pop into our Discord chat and get to know our community. We are also building a thriving community of plugin authors and would love more people to come and join us to help make PostGraphile even more awesome.

If you’d like to support our work and enable us to spend more time working on PostGraphile, please visit our Patreon page at patreon.com/benjie

Join us again tomorrow for our last post in this Birthday series, a look at what’s to come…

Birthday Week: Gratitude was originally published in Graphile on Medium, where people are continuing the conversation by highlighting and responding to this story.

I’m excited to announce PostGraphile now offers professional support contracts.

We can help you get started with PostGraphile, give you tailored advice when you need it, help fix performance issues, and give priority support on any issues you face whilst using PostGraphile. And the best thing is that while we’re supporting you, you’re supporting our continued open source work on PostGraphile! You can read more about our support offering here:

https://www.graphile.org/support/

We’ve been trialing this support offering for the last couple months with the fine folks over at learnlight.com, and in just one 30 minute phone call managed to save them days of work thanks to some significant performance optimisations to their security infrastructure. We’ve also shared relevant knowledge and experience to help the team form a bespoke set of best practices to guide their developers. Demian Martinez from Learnlight says:

We manage a web platform providing language and skills learning services to over 100k users worldwide. We wanted a GraphQL API server based on a PostgreSQL backend, and to focus on writing business code instead of data mapping, conversion and plumbing.

After almost a year of explorations and tests, we settled on PostGraphile to power our new platform. PostGraphile now replaces the entire API server and saves us from having to manually create GraphQL schemas, resolvers and connectors that add no value but take time, effort and bugs. This means faster prototyping and shorter sprints.

Benjie provides outstanding professional support and will even develop bespoke extensions—if you are going to bet the future of your platform on PostGraphile, becoming a professional customer is the first thing you should do.

Why professional support?

At Graphile we love open source because it means people can use our software in amazing places and interesting ways, can share their learnings with others, and can even contribute back features, fixes and documentation — it reduces the barrier to entry, and engenders trust and community.

For development on an open source project to be sustainable, there needs to be a source of income so the developers can continue to pay their mortgages, feed their kids, and once in a while take some well-earned time off. The absolute best way to support PostGraphile’s ongoing development efforts, the most bang-for-your-buck as it were, is to become a Patreon sponsor. Crowd-sourced funding enables us to spend the most time directly working on improving PostGraphile, which you and other PostGraphile users then benefit from.

Sometimes, however, you need something from us — some advice, to validate a potential bug, help figuring out how to achieve some goal — and you’ve got time pressures. This is where open source can fall down — no reasonable human would expect a stranger to help their business earn money on a tight deadline without some quid pro quo. This is where professional support comes in — it gives you guarantees that you’ll get a response to your issue in a timely fashion, and it gives us a steady income that we can rely on to dedicate more time to working on PostGraphile — mutual support.

Find out more about professional support at graphile.org/support, and join us again tomorrow for a celebration of our Patreon supporters!

Birthday Week: Mutual Support was originally published in Graphile on Medium, where people are continuing the conversation by highlighting and responding to this story.

Over the last 12 months of working on Graphile, I have learned a lot about both GraphQL and the open source community as a whole. It has been an incredible experience, and I can’t wait to see what the future holds.

To begin with, my focus was on the PostGraphile documentation. Benjie and I have both spent a lot of time improving and expanding our docs, adding features such as the various galleries, search, and advice sections sharing tips and best practices.

GraphQL is a fairly new technology, so it’s no surprise that a large number of people coming to PostGraphile are unfamiliar with GraphQL and are looking for a quick and easy way to get started. As time went on, we found that not only did we need good documentation about PostGraphile itself, but we also needed resources about GraphQL in general. To that end…

We’re proud to announce the launch of our learning resources website, covering GraphQL, PostgreSQL and PostGraphile:

https://learn.graphile.org

We have three learning resources ready for download:

• A GraphQL Operations cheatsheet
• A GraphQL Schema Language cheatsheet
• A PostgreSQL Row Level Security infosheet

They’re designed to be a quick reference for some of the technical language you come across when first using these technologies. There’s a nifty annotated diagram of a GraphQL query, plus there’s an entire infosheet about Row Level Security, something that is commonly misunderstood.

These were designed in collaboration with a local agency in Southampton, Hinge, and we are very happy with how they’d turned out. We have had some of these professionally printed which we want to share, so we’re sending some out this week to our Patreon Sponsors.

We are also taking these and some other goodies with us to FOSDEM, Europe’s biggest open source conference. If you’re in Brussels this weekend, come and visit us in the K building at the conference. We always love sharing our love of GraphQL, we can’t wait!

Join us again tomorrow to find out how you can help us to help you…

Birthday Week: Sharing was originally published in Graphile on Medium, where people are continuing the conversation by highlighting and responding to this story.

One year ago, I decided it was time to get serious about PostGraphile. Having worked on it as a side-project for over a year by that point, it was clear that to make development sustainable I’d have to find a way of earning a living from my work. Jem and I wanted to find a stream of income which complemented my work on PostGraphile, and enabled her to become more involved.

After much thought, we went into business together, creating a software development and training company using a portmanteau of our names: Jemjie Ltd. We were quickly successful in finding related work: helping companies to build their stack around PostGraphile in an advisory and/or development role, and a stint of GraphQL training.

As time went on, it became clear we couldn’t take on too much of this work or we would not have time left to work on PostGraphile itself. This is where our Patreon supporters made a huge difference — by funding time for us to work directly on PostGraphile, we could take a break from the consultancy work and spend time closing issues, reviewing pull requests, writing documentation, and advancing the project with new features.

Throughout the year, more and more community members stepped forward to help support the project financially and just last week we passed the $1500/mo goal allowing us to dedicate the equivalent of one day per week entirely to development on PostGraphile (and that’s not including the constant attention to GitHub issues and PRs, or supporting people via our Discord chat).

Another source of great support came in the form of sponsored development for specific PostGraphile features. These have included:

• Massively improved Lambda support thanks to better webpackability, which included decreasing bundle size by 10 times, and decreasing cold start times to under 0.5 seconds.
• Enhancements to PostGraphile’s support for PostgreSQL VIEWs and MATERIALIZED VIEWs; through adding support for defining constraints (e.g. foreign keys, primary keys, not null, etc) using smart comments
• An operation hooks plugin which enables you to add additional checks and send additional metadata back to the client when you attempt mutations — e.g. custom validation messages, authorisation checks and assertions, or even warning the user their credit card is expiring soon

In addition to thanking all our generous Patreon supporters, our thanks go out to CoGo, Ulta, and Learnlight for sponsoring the development of the features they needed, and then sharing these features with the community.

Thanks to all this support from PostGraphile users, we’ve been able to spend a lot more time on PostGraphile than we expected — I’m extremely proud of the advancements we’ve made, and am very excited for the future! PostGraphile is now in production use by many companies, with applications in industries ranging from farming to aerospace.

Reflecting on the successes of the past year, Jem and I are doubling down on our commitment to PostGraphile. We’re happy to announce that we’ve renamed our company to Graphile Ltd to reflect our focus, we’re only taking on PostGraphile related contracts, and we’ve got more announcements to come about how we intend to support increasing development time on PostGraphile.

This would not be possible without you, PostGraphile users and supporters, so a huge thank you to you all!

Join us again tomorrow for Jem on sharing our knowledge…

Birthday Week: Doubling Down was originally published in Graphile on Medium, where people are continuing the conversation by highlighting and responding to this story.

TL;DR: PostGraphile was already highly performant for complex GraphQL queries, but the next release is faster still, and now it’s great at the very simple queries found in Prisma’s benchmarks too!

UPDATE: postgraphile@next is now released.

UPDATE 2: a year has passed, see our 2019 update here.

As PostGraphile v4 gets towards the end of it’s beta period, the time has been coming to start thinking about performance optimisations. And as luck would have it, both Hasura and Prisma have recently released benchmarks comparing their GraphQL offerings to PostGraphile! This sudden interest in comparative benchmarking was sufficient excuse for me to dedicate some time to finding the low-hanging fruit of PostGraphile performance optimisations.

One of the main reasons behind the PostGraphile v4 rewrite was to achieve significantly greater performance than v3 (which was known as “PostGraphQL”). This has been very successful for complex queries of the type that PostGraphile typically deals, where you’re requesting all the data required to render a web page in one single GraphQL query.

What I realised with the chart on Prisma’s shiny new marketing site is that their benchmark wasn’t against the large nested queries I had been focussed on optimising. Instead, they had focussed on the other end of the scale — extremely small simple queries — which makes sense given the way that they intend Prisma to be used. (PostGraphile is a front-line GraphQL server intended to serve requests direct from end clients such as web browsers, whereas Prisma is intended to be the backend to another GraphQL schema.)

Still, the performance difference for these small queries shouldn’t be as great as Prisma’s chart implied… Grateful for the wake-up call I realised it was time to look at PostGraphile’s per-request performance — how quickly could we serve trivial queries that just request a couple of fields from an individual database row like the following?

{
  artistById(id: 3) {
    id
    name
  }
}

Prisma gave me the heads up about the benchmarks the night before they launched their site (Tuesday last week), and by Friday I’d managed to allocate about 6 hours on the task and had achieved quite substantial performance gains. On Monday, whilst writing up this post, I got distracted with another performance thread and spent another couple hours on it. Here’s some of the strategies I applied to improve performance:

Simple list-based collections

First, I intuited that Prisma were likely benchmarking simple GraphQL lists, whereas PostGraphile only supported best-practices Relay-compatible connections (which are more expensive to execute). I’ve seen chatter here and there that people would like to see simpler collections in PostGraphile so this seemed a “two drones¹ with one stone” opportunity.

You may be happy to hear that, as a direct result of this, PostGraphile now has support for simple list-based collections as well as Relay connections! When the next release of PostGraphile goes out you’ll be able to use them with the CLI flag--simple-collection both. This will probably become the default before PostGraphile v4 is officially released.

¹ no birds were harmed in the writing of this post

Disable expensive query logging

Second, I realised that our logging is unnecessarily expensive — by default we print a reformatted single-line version of every single GraphQL query that comes in — great for debugging but not so good for performance benchmarks! When you use PostGraphile as an Express middleware you can turn this off via the disableQueryLog option, but there wasn’t a way to disable it on the PostGraphile CLI; until now! Just add --disable-query-log to skip logging entirely; at some point we may add more efficient and conventional logging as an option but I didn’t have time this week!

Lighter resolution

I took a quick scan over the most high-trafficked areas of the GraphQL schema: the resolvers. It turned out there was a couple of places where I was performing chained .filter(…) calls inside the GraphQL resolvers. A simple V8 optimisation here would have been to roll these up into a single filter call; but I realised… these don’t need to be in the resolvers at all! I moved the calculations to the parent contexts so the resolvers could use the pre-computed results and thus had less work to do.

Optimising the HTTP layer

Another realisation was that I’d spent much of my time optimising the actual GraphQL schema and SQL execution (through the invention of the graphile-build suite). However, the HTTP server/middleware portion of PostGraphile has barely been touched since I took over as maintainer from the ever-inspirational Caleb (who has now moved from the Flow team to the Oculus(!) team at Facebook). Well, the time had finally come! I broke out the Chrome DevTools, hooked them up to PostGraphile and got profiling.

(Aside: to everyone in the Chrome, V8 and Node.js projects who have made debugging and profiling the latest versions of Node such a delight, my heartfelt thanks go out to you. These tools are incredible and it has been a joy to experience the frequent advancements over the last 5 years! Thank you so much, and keep up the great work!)

Long story short, we now perform more of the computations during startup rather than at request processing time; we don’t recalculate things over and over if we can avoid it¹; we do standard V8 optimisations such as storing repeatedly referenced deep object properties to a variable to avoid re-incurring object traversal costs² and avoiding unnecessary function calls³; and we avoid fancy new things like Map in performance-critical paths for more performant alternatives such as plain objects and arrays.

¹ no more multiple calls to parseUrl(req) for the same request!
² and making the code more readable as a bonus
³ for example by turning array.forEach(…) calls into traditional for loops, and in certain cases moving function guards outside of the functions

No unnecessary transactions

When I first wrote this article yesterday, Prisma were still beating PostGraphile on the tiny query byArtistId, and I enumerated some reasons that might be. Then I realised that one of them was relatively easy to fix — I intuited that Prisma might not be using transactions for their queries because they don’t need to (saving them for mutations only). Turns out that PostGraphile doesn’t need transactions around queries either, unless it is configured to set some PostgreSQL settings e.g. via pgSettings or from a JWT or similar.

I quickly edited PostGraphile to only use transactions when it needs to, and that was sufficient for us to squeeze the last bit of performance out to beat Prisma even on the simplest queries. Seems that even the begin/commit network packets were weighing down PostGraphile’s performance, not including the additional work the database had to do to track the transaction! Fixing the tests to not expect these statements took longer than implementing the feature itself 😅

Benchmarking

On Thursday I asked Prisma for their test suite, and they happily provided it to me the next day along with some guidance on how to get it up and running. The suite was based on Hasura’s graphql-bench suite, but quite heavily modified and with the benchmarking tool wrk2 replaced by vegeta. It took quite a lot of time and effort to get it running reliably unattended, but with Prisma’s permission I’ve published my heavily modified version of it on GitHub.

To make the results reproducible I decided to run it on a Digital Ocean 8GB RAM compute-optimised droplet. I’ve provided detailed instructions in the README on how to set up a similar environment for yourself so that you can validate my findings.

I’ve provided detailed instructions in the README on how to set up a similar environment

The test suite as configured takes about 15 hours to run on the Digital Ocean Droplet. You can easily shorten that by simply editing bench.json to not record so many data points — but beware that according to @do4gr, the Prisma engineer responsible for the benchmarks, because Prisma is written in Scala and runs on the JVM it needs more time to get the JIT up to speed than PostGraphile (which is Node.js-based). @do4gr stated that “Prisma was only fully warmed up after more than 100k requests” and suggested that warming up slowly is important to an accurate benchmark on Prisma’s performance.

To accommodate Prisma’s preferences I give each environment a full 5 minute warmup period, then let it rest for 15 seconds before jumping into the testing. Each test performs requests at a constant rate lasting 60 seconds and then the service gets a 15 second respite before moving onto the next test. We use different rps (requests per second) values for the different GraphQL queries because some are significantly heavier than others, and thus need a smaller range and higher resolution (not even PostGraphile can resolve prisma_deeplyNested at 3000rps!).

When I set up the suite I followed the instructions @do4gr provided for benchmarking Prisma (using the Docker image prismagraphql/prisma:1.8-beta), and also benchmarked the current npm version of PostGraphile (v4.0.0-beta.9), and the next version of PostGraphile which will include the optimisations and features listed above (available via npm as postgraphile@next, or v4.0.0-beta.9.3).

Show me the results!

Okay, enough chit-chat — time to show some numbers!

Importantly I was able to reproduce the effect from Prisma’s website — this was on a simple query that requests just 2 fields from the first 20 rows in a database table. On the test environment outlined above, Prisma reaches an impressive 2000rps for this simple query! The current version of PostGraphile starts struggling after 1700rps. However, thanks to the improvements listed above, the next version of PostGraphile takes a strong lead hitting 3000rps with sub-50ms latency — more than 50% more requests per second than Prisma — and even gets past 3250rps before it starts dropping connections.

the next version of PostGraphile takes a strong lead hitting 3000rps with sub-50ms latency — more than 50% more requests per second than Prisma

# PostGraphile@next
query tracks_media_first_20 {
  allTracksList(first: 20) {
    trackId
    name
  }
}
# Prisma
query tracks_media_first_20 {
  tracks(first:20)
  {
    id
    Name
  }
}

Despite PostGraphile’s strong lead, it’s important to keep in mind that this synthetic benchmark is not the kind of query that most users would be issuing to a PostGraphile server — they’re likely to want to traverse the graph at least a little bit!

Once you get into queries that actually do use graph traversal, even the current version of PostGraphile takes a clear lead immediately. But we’ve widened the gap with postgraphile@next anyway 😁

This next query requests all the albums from a particular artist, the tracks within that album and the genre name of each track. (Note: this query was from Hasura’s and hence Prisma’s test suite, I didn’t invent any of the queries in this post.) PostGraphile can serve over 4x as many of these queries per second as Prisma while staying below the 50ms line in the 95th percentile latency chart!

# PostGraphile@next
query albums_tracks_genre_some {
  allAlbumsList(condition: {artistId: 127}) {
    artistId
    title
    tracksByAlbumIdList {
      trackId
      name
      genreByGenreId {
        name
      }
    }
  }
}
# Prisma
query albums_tracks_genre_some {
  albums(where: {Artist: {ArtistId: 127}}) {
    AlbumId
    Title
    Tracks {
      TrackId
      Name
      Genre {
        Name
      }
    }
  }
}

Next, we look at a more complex query prisma_deeplyNested that Prisma added to their test suite (note the formatting is verbatim from the test suite):

# Postgraphile@next
query prisma_deeplyNested {
  allAlbumsList(condition: {artistId: 127}) {
    albumId
    title
    tracksByAlbumIdList {
      trackId
      name
      genreByGenreId {
        name
      }
    }
    artistByArtistId {
      albumsByArtistIdList {
        tracksByAlbumIdList {
          mediaTypeByMediaTypeId {
            name
          }
          genreByGenreId {
            name
          }
        }
      }
    }
  }
}
# Prisma
query prisma_deeplyNested {albums(where: {Artist: {ArtistId: 127}}) { AlbumId Title  Tracks {TrackId Name Genre { Name } }  Artist{ Albums{ Tracks{ MediaType{   Name } Genre{ Name } } }  }}}

Another important way to look at the data is to look at the number of successful responses completed, rather than the latency.

Browse the data for yourself

The graphs above are screenshots of an open source webpage I built to browse the results, hosted by now.sh. Check it out, here.

The webpage allows you to browse all 4 queries that I profiled¹, and choose from the stats: average/max latency, 50th/95th/99th percentile latency, successful request count and failed request count.

I apologise for the lack of pretty styles; I don’t have the design resources that Prisma have! (The source can be found within the benchmark repository on GitHub here.)

¹ Technically I also profiled albums_tracks_genre_all but the results came back with Prisma reporting no successes at even 1 rps, so I discounted it from the results since I don’t have time to look into it.

Conclusion

As you can see from the graphs, the next version of PostGraphile outperforms Prisma for even simple queries, and the more complex a query becomes the wider PostGraphile’s lead. PostGraphile is designed to serve both simple and complex queries extremely rapidly without requiring complex setup.

These performance results don’t mean that we’re resting on our laurels — there’s still a huge number of things we can do to improve PostGraphile performance further— if you want to support this then please give generously to my Patreon! The results in this post came from just 8 hours work on the low hanging fruit; think what I could do with a month!

Couldn’t have done it without my Patreon supporters ❤️

I’d like to take this opportunity to send more heart-felt thanks to my Patreon supporters. Knowing that these 14 wonderful folks value my work enough to put their hand in their pocket and support it makes a huge difference to my motivation — they really keep me going. It’s easy to become weighed down in Open Source — especially because most of the time you have no idea who is even using your software until they have an issue — but this concrete support means the world to me! Thank you all 🙏

If you or your company would like to support my work on PostGraphile or hear what it can do for you, please get in touch on via @Benjie on Twitter or just head straight to my Patreon 😉

I’m also starting to build some commercial features around PostGraphile, so if this interests you please get in touch — I’m interested to hear what your company would like to see in a professional or enterprise PostGraphile offering!

Say “hi” at GraphQL Europe in June!

Coming up in just under a month, on June 15th, is the GraphQL Europe conference in Berlin (tickets are still available). I’ll be there among the crowd, do come and say “hi!” I’ll be hanging around for a few days afterwards, so if you happen to be in the area, get in touch via my Twitter.

Benchmark limitations / further plans

There’s a lot of issues with this benchmark process.

I’d like to think that I’ve improved a number of issues over Hasura’s and Prisma’s benchmarks, but there are still some serious shortcomings. Prisma are keen to set up a benchmarking framework that different providers in the GraphQL ecosystem can use to benchmark their own software, I would definitely like to see this go ahead.

There’s quite a lot that needs doing to make these tests more fair. Here are some of the inadequacies of the benchmarking I performed in this post:

1. The databases differed. I ran Prisma against their stable MySQL implementation (as advised by them) rather than against PostgreSQL, so we were effectively benchmarking the databases along with the GraphQL software.
2. The GraphQL queries differed. They don’t need to — both PostGraphile and Prisma support renaming GraphQL fields—it would be good to be sending exactly the same JSON blob to both servers so that differences in performance cannot be blamed on the difference in field names or whitespace.
3. Loading the sample dataset into the databases differed. Though the tests should use the same dataset, because one was loaded via SQL file into PostgreSQL whereas the other was loaded with a proprietary tool into the Prisma cluster it’s hard to verify that the datasets are equivalent, including checking that they have the same indexes, etc. It would be best if we could have a single database that all the benchmarks run against.
4. Everything ran on the same machine. The benchmarking tool, GraphQL software, and databases should all run on independent hardware such that they do not compete for resources and interfere with each other.
5. Everything ran on a virtual machine. The benchmarks should run on dedicated physical hardware so that they’re unaffected by fluctuations in resource usage from other tenants on the same box. (This is why I chose the compute-optimise droplets, but dedicated hardware would be better still.)
6. The recorded metrics should be widened — for example it would be great to monitor CPU usage, memory usage, or even energy usage of the different solutions when resolving the same queries.
7. Consistent configurations should be used for each solution — how many connections can it make to the database? Must it use transactions? How many cores can it use? Should it produce logs? Is it okay to configure the environments differently because their databases have different characteristics?
8. Testing should be performed against a larger data set. This test was ran against the chinook data set that Hasura used, however it might be better to run tests against much larger (e.g. many-gigabyte) databases, this will give a much better impression of how each software treats the database when the entire data set can’t fit in RAM.
9. I’m not aware of any performance throttling, but if you run a benchmark long enough and hard enough the hypervisor may throttle it, or the hardware may do so automatically if there are any thermal issues. There’s no detection of this in the current suite.
10. GraphQL-specific response validation. I noticed quite early on that Prisma will sometimes fail with a GraphQL error but still produce a 200 status code. This is acceptable GraphQL behaviour (“GraphQL successfully resolved to this error”), but the benchmarking tool vegeta is only set up in the suite to monitor for status codes. It’s possible (if not likely!) that things are going wrong under the hood without us knowing. Any response validation that did take place, however, would have to be very careful to avoid contaminating the benchmark results with different processing times…
11. Error logging: when Prisma was overwhelmed it was writing stack traces to Docker’s logs. This may have had a noticeable negative impact on performance, causing a cascading failure. But on top of this, it also filled up the hard drive on the server with Docker logs, which broke the first benchmark after about 5 hours. All docker-compose.yml files are now set up with a log limit to prevent this flooding, but we should agree on what the systems being benchmarked should do in the case of errors to make comparisons fair.
12. Garbage collection. Both Prisma and PostGraphile use garbage-collected languages; I believe this contributes to the “bumps” in the charts — e.g. where the system appears to suddenly go slower and then speed up again. Seeing how the system deals with GC is an important metric, but when you’re only benchmarking 60 second windows you’re sometimes going to hit a GC run, and sometimes not. One way to solve this is to force GC before each test. Another might be to extend the test period so that the GC results get absorbed in.
13. Unnatural load. Most public-facing servers on the internet deal with varying load from second to second; these synthetic benchmarks do not model that well at all — once the system stumbles there’s no chance of it getting back up again because the load never lets up, even for a few milliseconds, which can allow GC pauses and JIT changes to allow a queue to build up that it cannot then clear.
14. The tests only ran once. If a system process kicked in at one point, that may have interfered with one of the tests. It would be good to run the tests multiple times and average the results (perhaps even excluding outliers); however with the suite already taking 15 hours to execute that was unachievable for me.
15. I’m not a Prisma engineer. Benchmarking third party code is challenging — only they know how best to set up that software, and the incentives are not aligned for you to go out of your way to research how to set the tests up right for them, or even how to write sensible queries. I sought a lot of guidance from Prisma on running these tests (and use the queries that they provided), but still there could be issues with what I have done or mistakes that I have made during setup.
16. Doesn’t include Hasura in the tests. This is simply because I used Prisma’s test suite, and I’ve absolutely no familiarity with Hasura and didn’t have time to re-introduce benchmarking on it in a way that would be meaningful.

Thanks for reading! Check out PostGraphile, follow me on Twitter: @Benjie or GitHub: @Benjie, and consider giving to to my Patreon.

How I Made PostGraphile Faster Than Prisma GraphQL Server In 8 Hours was originally published in Graphile on Medium, where people are continuing the conversation by highlighting and responding to this story.

Despite 80% of the codebase having been replaced with the new Graphile GraphQL Engine, v4 manages to achieve very high compatibility with v3, so most users should find the upgrade fairly painless.

The main focusses of v4 have been performance, customisability and stability.

Performance

PostGraphile performance has been greatly improved thanks to the new Graphile Engine which enables us to compile an entire GraphQL query tree into just one SQL statement. This then passes the task of efficiently executing your query over to the heavily optimised query planner in PostgreSQL, which uses advanced techniques such as relational algebra and genetic algorithms to find the fastest plan quickly. We’ve also performed a large number of optimisations in JavaScript-land, which has lead to code that is more easily optimised by the JIT, and reduced work for the garbage collector — resulting in a 90%+ reduction in peak memory usage. We see typical performance improvements for webpage queries at around 17×, but the more complex the query is, the better PostGraphile v4’s performance — for a 4-level deep query we hit 55× the performance of v3!

Customisability

Reading over the GitHub issues when I became maintainer of the (then) PostGraphQL project over a year ago, I realised that quite a few people desired the ability to customise their GraphQL schema. There were even a small number of people maintaining public forks of PostGraphQL so that they could add that one feature that was important to them; but these forks would quickly become out of date as the effort required to keep them in sync was too great. PostGraphQL also exposed too much by default, making schemas seem huge and unwieldy at times. It was clear to me that PostGraphile needed to solve these issues — doing more for the user out of the box, giving them more control over the generated GraphQL schema, and allowing users to enhance their schemas as they saw fit; so I set out to address these issues in version 4.

Out of the box, PostGraphile now ignores resources that come from PostgreSQL extensions, so you can now use the default public schema in PostgreSQL without your GraphQL being flooded with helper functions from extensions such as citext and pgcrypto(you can opt out of this should you so desire).

Version 4 also introduces the --no-ignore-rbac toggle which inspects the database permissions and omits things that the user has not been granted access to. This means that if you don’t grant SELECT on a table to your PostGraphile role, it will not appear in the generated schema. If you only grant UPDATE on two columns in your table, then only those two columns will be available in the update mutation. We highly recommend that all users enable this flag, it will probably be enabled by default in v5.

Another feature v4 introduces is smart comments. Smart comments enable you to add tags to tables, columns, functions, constraints, etc. so that the system can handle them slightly differently. For example, you can use the @omit all tag to prevent the generation of the allFoos root query field, or the @deprecated tag to mark a field as deprecated. You can use @name and variants to give a different name to a field or type in the generated GraphQL schema. And this functionality is not limited to these specific tags — plugins can also be influenced by smart comments.

And speaking of plugins — v4 introduces a powerful plugin system powered by Graphile Engine. This GraphQL engine was built from the ground up to enable incredible performance and extensibility. And we’re not kidding — in fact the entire schema generation in PostGraphile is powered by over 35 plugins, and you can add, remove or replace plugins to get the schema that you desire without having to maintain a fork of the entire project. A community of plugin builders has formed during the testing period of PostGraphile v4 and has built some really amazing plugins — one to check out in particular is Matt’s connection filter plugin which adds powerful filtering capabilities to PostGraphile connections.

The naming system (“inflector”) in PostGraphile is also pluggable, so if you don’t like how we manipulate the names of your database fields, or if you don’t like our allRows naming, you can change it easily. You can check out my inflector simplifier plugin to see this in action (I definitely recommend using this once you’re familiar with PostGraphile), or make your own with our handy makeAddInflectorsPlugin helper.

Stability

Version 4 fixes a large number of bugs and edge cases that were present in v3. It also focusses on reducing memory usage, helping your servers run faster for longer. One of the reasons for the long development and testing phase has been that we wanted to make sure that v4 was a solid foundation on which to build, ensuring that we do not cause any breaking changes in the future. Thanks largely to the community testing it and filing issues, writing powerful plugins, and pushing the boundaries of the software, I’m very confident in today’s release.

We’ve also made it a lot easier for users to figure out what’s going wrong — the errors that PostGraphile outputs now are no longer cryptic errors as they have been in the alpha and beta periods, but instead helpful errors detailing not only what went wrong, but in many cases hinting at how you might fix it. If you come across any cryptic error messages, please do file an issue so that we can fix it.

Getting Started

We’ve dedicated a lot of time to the PostGraphile documentation, and this will be continually improved over the coming weeks and months. We’ve included a full Version 4 Feature Guide and Version 3 to Version 4 Migration Guide — so do check them out whilst you upgrade.

Getting started with PostGraphile is as easy as giving it your PostgreSQL database connection string, e.g. for a local database called my_db, assuming you already have Node.js 8.6 or higher installed you can use the bundled npx utility and run:

npx postgraphile -c my_db --watch

The watch mode will monitor your PostgreSQL database for changes (added or removed tables, columns, constraints, comments, functions, etc) and automatically update the GraphQL schema. Our built in GraphiQL UI will also automatically update the documentation browser, without losing your state.

Please Support PostGraphile Development

PostGraphile version 4 has been a labour of love, but sadly love doesn’t pay the bills! Sponsorship is now close to funding a day per week dedicated to PostGraphile development, and this support is growing every month which is already leading to more features and faster releases. Please consider if you can support PostGraphile development via Patreon — a regular donation means more time spent on PostGraphile.

Whether you can sponsor PostGraphile or not, there are other ways you can help:

• ⭐ Star the repository on GitHub
• Talk about PostGraphile on social media
• Write blog posts about using PostGraphile
• Demonstrate your projects at local meet ups

And follow our new Twitter account @GraphileHQ for more updates.

The more people that use and support PostGraphile, the faster fixes and features can be added, and everyone benefits!

I want to take this opportunity to say a huge THANK YOU to the community for supporting my work on PostGraphile, without you version 4 would not be nearly as exciting a release!

PostGraphile v4 Released was originally published in Graphile on Medium, where people are continuing the conversation by highlighting and responding to this story.