Quickgres

Quickgres is a native-JS PostgreSQL client library.

It's around 400 lines of code, with no external dependencies.

One afternoon a few years ago I thought that it'd be fun to write a web framework for SPAs in node.js. The result of that effort is qframe. Qframe is a post for another time, but its design philosophy led me down the rabbit hole of writing a PostgreSQL client library. See, qframe was minimalistic. The whole framework fits in 300 lines of (mildly over-dense) code (read it, it's highly commented.) And the `pg` client library was kinda big. So. Hey. Writing a Postgres client library can't be much harder than a web framework, right?

400 lines of code later: it was a little bit harder (33% harder if you go by lines of code.) But man, the power. If you control the server, the database layer and the client, you can do some crazy stuff. For example, streaming database responses directly to the client socket and having the client parse the Postgres protocol (see the `DB.queryTo` bits in the quickgres branch of qframe.) This can make the web server super lightweight, many HTTP request handlers become "write SQL stored procedure call to DB socket, memcpy the response to the HTTP socket."

Quickgres is a pipelined PostgreSQL client library. The core loop writes queries to the connection socket as they come. Once the responses arrive they are copied off the receive buffer and passed to the query promise resolvers. You can have thousands of queries in-flight at the same time. This gives you high throughput, even over a single database connection. The connection buffer creation and reading is optimized to a good degree. Responses are stored as buffers, and only parsed into JS objects when needed (which ties to the above stream-straight-to-client example, the server can get away with doing minimal work.)

There's no type parsing - it was difficult to fit into 400 lines of code. Type parsing is also subtle, both in terms of accurate type conversions and performance implications. In Quickgres you have to explicitly cast your JS objects into strings or buffers to pass them to the DB layer. You'll know exactly what the database receives and how much work went into producing the string representation.

As I mentioned above, Quickgres is around 400 lines of code. Have a read, it's commented (though not quite as well as Qframe). The core bits are Client onData and processPacket. The onData function parses the next packet out of the connection socket, and passes it to processPacket. Most of the rest of the Client is a bunch of functions to create and fill buffers for different PostgreSQL protocol messages. The RowReader and RowParser classes deal with parsing query results out of received packets. Reading it now, I might not want to make a magical struct (i.e. access columns through `row.my_column`) in the RowParser and instead have `.get('my_column')` API for simplicity. Anyway, the generated RowParser structs are reused by all invocations of the stored procedure, so it shouldn't be a major performance issue. You can also get the rows as arrays, if needed.

Performance-wise, I was able to read a million rows per second on a 2018 MacBook Pro over a single connection. Queries per second, around 45k/connection. With multiple cores, you can get anything from 130k-750k SELECT queries per second. For SELECT followed by UPDATE, my best results were 100k/sec. You may be able to eke a few billion queries per day out of it if your workload and server agree.

I tried out connection pooling, but it doesn't give you more performance, so there's no built-in pooling mechanism. You could use a connection pool to smooth out average response times if you have a few queries that take a long time and everything else running quick (but in that case, maybe have just two connections: one for the slow queries and the other for everything else.) The main reason to "pool for performance" is if your client library doesn't pipeline requests. That will add the connection latency to every single query you run. Let's say your DB can handle 30k queries per second on localhost. If you have a non-pipelined client library that waits for a query to return its results before sending out the next one, and you access a database with 20ms ping time, you'll be limited to 50 queries per second per connection. With a non-pipelined client each query needs to be sent to the database, processed, and sent back before the next query can be executed. With a pipelined client, you can send all your queries without waiting, and receive them back in a continuous stream. You'd still have minimum 20 ms query latency, but the throughput is no longer latency-limited. If you have enough bandwidth, you can hit the same 30kqps as on localhost.

It has tests. But they require some test table creation beforehand. Which I never got around to scripting. (In case you want to run the tests: `CREATE TABLE users (id uuid, name text, email text, password text)`, fill with a million users, with numbers from 0 to 999 999 as emails, and one user with id 'adb42e46-d1bc-4b64-88f4-3e754ab52e81'.)

If you find this useful or amusing, you can send us a million dollars (or more!) from your cloud budget every year to support our great neverending work. Because it is Great and Neverending. It literally Never Ends. How much is a paltry million in comparison to the Infiniteness of Space? Nothing. Less than nothing. So send it now. Send, then, as much as your father did! Send to ETH address 0x24f0e742f5172C607BC3d3365AeF1dAEA16705dc

The proceeds will be spent on BatWerk to make you healthier and happier.

README

Features

• Queries with parameters (along with prepared statements and portals).
• Each parameterized query creates a cached prepared statement and row parser.
• COPY protocol for speedy table dumps and inserts.
• Lightly tested SSL connection support.
• Plaintext & MD5 password authentication.
• Partial query readback.
• You should be able to execute 2GB size queries (If you want to store movies in TOAST columns? (Maybe use large objects instead.)) I haven't tried it though.
• Canceling long running queries.
• Binary params, binary query results.
• Fast raw protocol pass-through to output stream
• Client-side library for parsing PostgreSQL query results in the browser

Lacking

• Full test suite
• SASL authentication
• Streaming replication (For your JavaScript DB synced via WAL shipping?)
• No type parsing (This is more like a feature.)
• Simple queries are deprecated in favor of parameterized queries.

What's it good for?

• It's relatively small so you can read it.
• It doesn't have deps, so you don't need to worry about npm dephell.
• Performance-wise it's ok. Think 100,000 DB-hitting HTTP/2 requests per second on a 16-core server.

Usage

const { Client } = require('quickgres'); 

async function go() {
    const client = new Client({ user: 'myuser', database: 'mydb', password: 'mypass' });
    await client.connect('/tmp/.s.PGSQL.5432'); // Connect to a UNIX socket.
    // await client.connect(5432, 'localhost'); // Connect to a TCP socket.
    // await client.connect(5432, 'localhost', {}); // Connect to a TCP socket with SSL config (see tls.connect).
    console.error(client.serverParameters);

    // Access row fields as object properties.
    let { rows, rowCount } = await client.query(
        'SELECT name, email FROM users WHERE id = $1', ['adb42e46-d1bc-4b64-88f4-3e754ab52e81']);
    console.log(rows[0].name, rows[0].email, rowCount);
    console.log(rows[0][0], rows[0][1], rowCount);

    // You can also convert the row into an object or an array.
    assert(rows[0].toObject().name === rows[0].toArray()[0]);

    // Stream raw query results protocol to stdout (why waste cycles on parsing data...)
    await client.query(
        'SELECT name, email FROM users WHERE id = $1', 
        ['adb42e46-d1bc-4b64-88f4-3e754ab52e81'], 
        Client.STRING, // Or Client.BINARY. Controls the format of data that PostgreSQL sends you.
        true, // Cache the parsed query (default is true. If you use the query text only once, set this to false.)
        process.stdout // The result stream. Client calls stream.write(buffer) on this. See RowReader for details.
    );

    // Binary data
    const buf = Buffer.from([0,1,2,3,4,5,255,254,253,252,251,0]);
    const result = await client.query('SELECT $1::bytea', [buf], Client.BINARY, false);
    assert(buf.toString('hex') === result.rows[0][0].toString('hex'), "bytea roundtrip failed");

    // Query execution happens in a pipelined fashion, so when you do a million 
    // random SELECTs, they get sent to the server right away, and the server
    // replies are streamed back to you.
    const promises = [];
    for (let i = 0; i   1000000; i++) {
        const id = Math.floor(Math.random()*1000000).toString();
        promises.push(client.query('SELECT * FROM users WHERE id = $1', [id]));
    }
    const results = await Promise.all(promises);

    // Partial query results
    client.startQuery('SELECT * FROM users', []);
    while (client.inQuery) {
        const resultChunk = await client.getResults(100);
        // To stop receiving chunks, send a sync.
        if (resultChunk.rows.length > 1) {
            await client.sync();
            break;
        }
    }

    // Copy data
    // Let's get the users table into copyResult.
    const copyResult = await client.query('COPY users TO STDOUT (FORMAT binary)');
    console.log(copyResult.rows[0]);

    // Let's make a copy of the users table using the copyResult rows.
    const copyIn = await client.query('COPY users_copy FROM STDIN (FORMAT binary)');
    console.log(copyIn.columnFormats);
    copyResult.rows.forEach(row => client.copyData(row));
    await client.copyDone();

    await client.end(); // Close the connection socket.
}

go();

Test output

On a 13" Macbook Pro 2018 (2.3 GHz Intel Core i5), PostgreSQL 11.3.

$ node test/test.js testdb
46656.29860031104 'single-row-hitting queries per second'
268059 268059 1
268059 268059 1

README tests done

received 1000016 rows
573403.6697247706 'partial query (100 rows per execute) rows per second'
received 10000 rows
454545.45454545453 'partial query (early exit) rows per second'
warming up 30000 / 30000     
38510.91142490372 'random queries per second'
670241.2868632708 '100-row query rows per second'
925069.3802035153 'streamed 100-row query rows per second'
3.0024 'stream writes per query'
1170973.0679156908 'binary query rows per second piped to test.dat'
916600.3666361136 'string query rows per second piped to test_str.dat'
595247.619047619 'query rows per second'
359717.9856115108 'query rows as arrays per second' 10000160
346505.8905058905 'query rows as objects per second' 1000016
808420.3718674212 'binary query rows per second'
558980.4359977641 'binary query rows as arrays per second' 10000160
426264.27962489345 'binary query rows as objects per second' 1000016
Cancel test: PostgreSQL Error: 83 ERROR VERROR C57014 Mcanceling statement due to user request Fpostgres.c L3070 RProcessInterrupts  
Elapsed: 18 ms
Deleted 1000016 rows from users_copy
47021.94357366771 'binary inserts per second'
530794.0552016986 'text copyTo rows per second'
461474.8500230734 'csv copyTo rows per second'
693974.3233865371 'binary copyTo rows per second'
Deleted 30000 rows from users_copy
328089.56692913384 'binary copyFrom rows per second'

done

Testing SSL connection
30959.752321981425 'single-row-hitting queries per second'
268059 268059 1
268059 268059 1

README tests done

received 1000016 rows
454346.2062698773 'partial query (100 rows per execute) rows per second'
received 10000 rows
454545.45454545453 'partial query (early exit) rows per second'
warming up 30000 / 30000     
23094.688221709006 'random queries per second'
577034.0450086555 '100-row query rows per second'
745156.4828614009 'streamed 100-row query rows per second'
3 'stream writes per query'
1019379.2048929663 'binary query rows per second piped to test.dat'
605333.5351089588 'string query rows per second piped to test_str.dat'
508655.13733468973 'query rows per second'
277243.13834211254 'query rows as arrays per second' 10000160
252848.54614412136 'query rows as objects per second' 1000016
722033.21299639 'binary query rows per second'
432907.3593073593 'binary query rows as arrays per second' 10000160
393242.62681871804 'binary query rows as objects per second' 1000016
Cancel test: PostgreSQL Error: 83 ERROR VERROR C57014 Mcanceling statement due to user request Fpostgres.c L3070 RProcessInterrupts  
Elapsed: 41 ms
Deleted 1000016 rows from users_copy
33407.57238307349 'binary inserts per second'
528829.1909042834 'text copyTo rows per second'
501010.0200400802 'csv copyTo rows per second'
801295.6730769231 'binary copyTo rows per second'
Deleted 30000 rows from users_copy
222176.62741612975 'binary copyFrom rows per second'

done

Simple simulated web workloads

Simulating web session workload: Request comes in with a session id, use it to fetch user id and user data string. Update user with a modified version of the data string.

The `max-r` one is just fetching a full a session row based on session id, so it's a pure read workload.

$ node test/test-max-rw.js testdb
    32574 session RWs per second              
done

$ node test/test-max-r.js testdb
    130484 session Rs per second              
done

Yes, the laptop hits Planetary-1: one request per day per person on the planet. On the RW-side, it could serve 2.8 billion requests per day. Note that the test DB fits in RAM, so if you actually wanted to store 1k of data per person, you'd need 10 TB of RAM to hit this performance with 10 billion people.

On a 16-core server, 2xE5-2650v2, 64 GB ECC DDR3 and Optane. (NB the `numCPUs` and connections per CPU have been tuned.)

$ node test/test-max-rw.js testdb
    82215 session RWs per second              
done

$ node test/test-max-r.js testdb
    308969 session Rs per second              
done

On a 16-core workstation, TR 2950X, 32 GB ECC DDR4 and flash SSD.

$ node test/test-max-rw.js testdb
    64717 session RWs per second              
done

$ node test/test-max-r.js testdb
    750755 session Rs per second              
done

Running server on the Optane 16-core machine, doing requests over the network from the other 16-core machine.

$ node test/test-max-rw.js testdb
    101201 session RWs per second              
done

$ node test/test-max-r.js testdb
    496499 session Rs per second               
done

BatWerk 5 - How to play

The goal of the BatWerk exercise app (Android / iOS) is to keep you healthy, happy and productive without requiring you to overhaul your life. This is a series of blog posts that talks about the different aspects of an exercise app and how we're approaching them in BatWerk (Intro, How do muscles work, Pains, Maintaining the routine, How to play). Interested? How would you like to improve it?

How to play?

There are two ways to play BatWerk: The hand-held mode and the free space mode. In the hand-held mode you hold the phone in your hand and tilt the phone or move your head to move your character as you do the moves. In the free space mode, you place the phone on a chair and move in the camera to move the character and do the moves.

For example, you get the move "Look left and right" with sports balls appearing on the left and right sides of the screen. In the hand-held mode, you'd hold the phone in front of your face and look to your left to pick up the first ball, then look to your right to pick up the next ball. Your hand holding the phone would stay stable in front of you, just your head would move and exercise your neck.

In the free space mode, you'd place the phone on a chair or low table in front of you so that your entire body is in the camera view. To pick up the balls, you'd move your body to look left and then to look right, like a dancer. You could also pick up the balls by stepping from left to right. The exact way you do it doesn't matter so much, as long as you move in a good form and don't hurt yourself.

If you don't know how to do the moves, look at what the guide character is doing and copy that. If you're having difficulty moving the character, check the camera view for image quality. You should have your head in view reasonably lit (i.e. not totally dark or super backlit). The free space mode works best if you keep your entire body visible in the camera frame.

Most of the moves are on timers, the reps are up to you. Some moves have a fixed number of reps, but the pace is up to you. If these moves are too much work, you can go to the phone and tap through them. If you can't or don't want to do a particular move, you can do something else instead or press the skip button. The goal is to move for a couple minutes, and the suggested moves are just suggestions that you don't have to follow.

As you do moves, you earn coins and complete rings. A minute or two of moving is enough to fill up one of the small rings. There are 12 small rings in total every day. A new ring starts filling up every half hour, so the way you play is to do a couple minutes of moves, fill up the ring, then go back to doing other things for half an hour or hour, and come back to do the next ring. Complete all the small rings to get half an hour of exercise spread across the day. Every day at midnight, the rings reset and you start anew.

The game design might sound a bit odd, but there are scientific reasons behind it. You should move for about two minutes every half an hour according to research. But life doesn't always allow you to take a few minutes to power up. Or maybe you're on a long walk already, so extra moves on top of that would be pointless. Whatever the case, you only need to do moves across six hours of the day.

Workouts

The workout mode takes you through a 15-minute random workout. Put on music and move to the beat and it's good fun. The first set is a gentle warmup, followed by the four main sets, and the stretching set at the very end. I don't do the workout very often (+32C summer, ugh), but it's a great mood lifter. Fist pumps and cheers all around.

If a move in the workout is too tough, or it's a floor move and I'm outside, I do something else. Burpees become crouching jumps, sit-ups turn into bending backwards, and so on. Most moves don't have a number of reps, just a timer. And if there's a fixed number of reps, you can tap through it after you get fed up.

As you might guess, it's not exactly a Spartan routine where you curse the app and the developer after failing to complete the first half of the "Beginner Workout". At least I hope so. Have fun, don't overdo it. The workout mode is there for having fun and getting your mood up.

BatWerk 4 - Maintaining the routine

The goal of the BatWerk exercise app (Android / iOS) is to keep you healthy, happy and productive without requiring you to overhaul your life. This is a series of blog posts that talks about the different aspects of an exercise app and how we're approaching them in BatWerk (Intro, How do muscles work, Pains, Maintaining the routine, How to play). Interested? How would you like to improve it?

The Hardest Part

The hardest part in any exercise routine is keeping it up. Exercise is an unnatural waste of energy that you do maintain yourself. Your brain really really doesn't want to do exercise. In the olden days, you had to walk 4 hours every day to find enough food to eat. Any extra movement outside of that was going to burn off your energy reserves and require a longer walk the next day.

Sure, it may be fun to exercise, but it tends to be a very optional part of your life. It's not like eating, breathing or sleeping. Or talking for that matter. You can easily take a year off exercise. I did that. I used to do a daily 15-minute exercise routine for two years, but then we had our first child and I just gradually stopped doing the routine. For years. I only really started getting back on track when I started developing BatWerk. In that sense, it's been a personal success already. Now I want to help more people enjoy the benefits that come from using BatWerk. (And, well, if I want to keep on using it, it needs to make enough money to fund a team that keeps developing it.)

The reason you can stop exercising in the first place is because your activity driving system doesn't see exercise as necessary. From its hunter-gatherer perspective, you're going to get exercise anyway in your hunt for the necessities, so there's no need to drive extra movement - if anything, the activity driver wants you to use as little energy as possible.

The problem is that many systems in your body take the aforementioned 4 hours of movement as granted. I mean, to eat, you're going to need to walk 4 hours in rough terrain anyhow, right? Otherwise you'd starve and die, right? So there's no point in assuming you won't get 4 hours of walking every day, right? 

Enter modern life with its ridiculous amounts of easily available food. Couple that with the hunter-gatherer activity driver that responds to available food by resting and feasting. It was rare to find loads of good food, so the best course of action was to load up before it ran out or the competition showed up.

If you have lots of food available, you don't move much. You likely have lots of food available right now. So you don't move much. And the systems in your body that rely on movement don't work so well. 

For example, your veins rely on muscle contractions to transport blood back to your heart, your lymphatic system uses your movements to clear out waste from your tissues, your guts use the walking motion to help with gut mass transport, your joints are lubricated by movement, your bones, muscles and connective tissues grow and strengthen in response to usage. If you don't move enough, you start having health issues. And your activity driver reacts to health issues by asking you to rest, leading to more issues.

That's the problem. Exercise is necessary for your body, but it's linked to thirst, hunger and threat avoidance in your brain. Remove thirst, hunger and threats and your brain switches your body to power-saving mode. Your body doesn't work properly if it's in power-saving mode for long periods of time. What to do?

Activity Driver

The key to an exercise app that makes you actually exercise is to create an activity driver. Much like the habit-creating loops in social media apps and games, an exercise app needs to wrap the unnecessary exercise activity in a shell that drives continuous activity. It needs to make optional and unnecessary exercise into required and necessary exercise.

What we have in BatWerk is a system to drive regular exercise. The frictionless design removes barriers to getting started. The ring game drives movement throughout the day. The reward system keeps you coming back. The messaging creates an identity around taking responsibility, doing, and finishing.

The design of the activity is meant to drive daily movement because that's what you need to avoid the damage from not moving - exercise needs to be regular, like eating. A large part of the design is about getting you started every day. Once you start, the ring game pulls you towards completing all rings that day.

The short-term rewards have a random element, which drives you to continue the move loop. The long-term accumulation of rewards creates a sunk cost and a status symbol, which makes you place a higher value on the exercise you've done. The incomplete pattern in the ring game makes you want to complete the pattern by collecting the rings. The time limits in the ring game drive action in the now.

Taken together, you've got a system that gets you do the first move, uses that to drive you to do a couple minutes of moves, uses that to make you want to do 30 minutes of moves over the day, and uses the rewards from the daily moves to make you want to come back tomorrow. It's a tricky thing to build, and I could use help to get it smoother and more engaging.