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