Skip to content


Comparing fairly different data access libraries would be a bit complicated as the performances may vary for a wide range of reasons, mainly related to the database itself: the size of the tables, the indexes created, etc. However ship hold repository contains an example folder in which you can find some real use cases for our publishing platform with implementations for various libraries. This is not a benchmark, it is meant to give you a toy application to compare the different user experiences and test how (in)efficient queries with associations can be. Therefore queries are monitored and execution time is measured.

The listed pretty common use cases are the following:


With a data set of 100 000 users, 20 000 posts, 120 000 comments and around 200 tags, I get on my machine this kind of result. No index has been added, which is somehow unrealistic, but as the nature of queries generated by the different libraries is very different, that would make it even more complicated to compare. Of course you are invited to run your tests with indexes.

execution time of listed use cases with different libraries
latest posts55ms149ms111ms
user profile53ms102ms81ms
posts by tag164msERROR338ms

Why ?

Why does ship hold appear to perform so well compared to well established libraries ?

First of all, ship-hold remains a quite low level abstraction and does not do much other than building a SQL query, handing it to pg driver, and forward the response to you application code. That's why if you run a query directly with the driver or through ship-hold, you will get quite similar result:

Get five last articles including their authors' data
pg.query(`SELECT "posts".*, to_json("users".*) as "author"
            (SELECT * FROM "posts" ORDER BY "published_at" DESC LIMIT 5) as "posts" 
                JOIN "users" 
        ON "posts"."user_id" = "users"."user_id"

// > 18ms'posts.*', {
    value: toJson('"users".*'),
    as: 'author'
            .orderBy('published_at', 'desc')
            .limit(5), as: 'posts'
    .on('posts.user_id', '"users"."user_id"')

// > 23ms

// This one will actually generate different type of query but will get the same result
    .orderBy('published_at', 'desc')

// > 26ms

// As indication, with Sequelize I get something like 76ms

On the other hand, ORM usually do few things such as creating model instances from the rows, etc. That is why you would use them after all !

Second point, is that ship-hold only works with one database engine (Postgres) and tries a much as possible to rely on the database features rather than implementing an equivalent on the Javascript side. SQL is very powerful !(and often under used). For example, in ship-hold, queries with associations would result in a unique query to the database, and even the aggregation is done in that query. Other libraries would send various queries, buffer the result and aggregate the result in Javascript.

I see many advantages in proceeding that way:

Where would ship hold perform not so well ?

Even though I am trying to sell ship-hold to you, I must be honest. When I built ship-hold, I did not have performances particularly in mind. I tend to favor first user experience, simplicity of the code and extensible abstractions. The relative good performances is more or less an accident. As any software, at some point you have to make some trade-offs. This section will explain when ship-hold might not perform well.

When you perform a query including associations, ship-hold uses behind the scene Common Table Expressions. CTE are useful in many ways but their main advantage is the readability they provide. They are eagerly evaluated (once only) and can be referenced in other part of the query (a bit like variable). This is very useful when you want to build a query recursively which is the case when you have many associations fetched at many levels of depth.

On the other hand, CTE might not be simplified by Postgres query planner as their inline equivalent (at least for now). The fact that CTE are eagerly evaluated can also be a disadvantage

If you run the following code

    .where('user_id', 42)
    .include(Comments, Users)

You will get the following SQL

    "Posts" AS (SELECT * FROM "posts" WHERE "user_id" = 42 ORDER BY "published_at" DESC LIMIT 5),
    "comments" AS (SELECT * FROM "comments" WHERE "comments"."post_id" IN (SELECT "post_id" FROM "Posts")),
    "author" AS (SELECT * FROM "users" WHERE "users"."user_id" IN (SELECT "user_id" FROM "Posts"))
    (SELECT (COALESCE(json_agg("comments".*),'[]'::json)) AS "comments"
        FROM (SELECT * FROM "comments" WHERE "comments"."post_id" = "Posts"."post_id") AS "comments"),
    (SELECT (to_json("author".*)) AS "author"
        FROM "author" WHERE "author"."user_id" = "Posts"."user_id") AS "author"
FROM "Posts" ORDER BY "published_at" DESC

This query will have a cost relatively low. The first CTE "Posts" is referenced quite few times but only evaluated once. Moreover the scope of the query (posts whose author's id is 42) narrows down a lot the scope of the others dependant CTEs and sub queries.

To sum up, when you quickly narrows down the scope of a select with include (by applying a pagination on the main builder for example), ship-hold's query will perform quite well.

Now let's go back to the example where we wanted to create a dump files of all our posts. We had an impressive result there. But let's write our code with inclusions now

    .stream(function* () {
        let first = true;
        try {
            while (true) {
                const r = yield;
                if (first) {
                } else {
                    console.log(',' + JSON.stringify(r));
                first = false;
        } catch (e) {
            throw e;
        } finally {

This time, it took us 52 seconds to create the dump file (50x slower) ! Let's have a look at the SQL

    "Posts" AS (SELECT * FROM "posts"),
    "author" AS (SELECT * FROM "users" WHERE "users"."user_id" IN (SELECT "user_id" FROM "Posts"))
    (SELECT (to_json("author".*)) AS "author" FROM "author" WHERE "author"."user_id" = "Posts"."user_id") AS "author"
FROM "Posts"

The first CTE is useless: it basically returns the whole posts table and worst, the second evaluates whether an id is in an array of 20 000 items. In fact it will returns all the users who are also authors (about 8000). Finally our sub query (the last select) will have to match 20 000 posts to those 8000 users. No wonder it takes time !

To sum up, if your main query (the main builder) would probably returns a big subset, avoid query with inclusions. Luckily for us, ship-hold remains flexible and you are not out of tools when you spot such a case and need to workaround the issue.