September 26, 2024
Summary: In this tutorial, you will learn how to tune network latency for your application server.
Table of Contents
Latency matters
Typical client/server round-trip network latencies can range from 0.01ms (localhost) through the ~0.5ms of a switched network, 5ms of WiFi, 20ms of ADSL, 300ms of intercontinental routing, and even more for things like satellite and WWAN links.
A trivial SELECT can take in the order of 0.1ms to execute server-side. A trivial INSERT can take 0.5ms.
Every time your application runs a query it has to wait for the server to respond with success/failure and possibly a result set, query metadata, etc. This incurs at least one network round trip delay.
When you’re working with small, simple queries network latency can be significant relative to the execution time of your queries if your database isn’t on the same host as your application.
Many applictions, particularly ORMs, are very prone to running lots of quite simple queries. For example, if your Hibernate app is fetching an entity with a lazily fetched @OneToMany relationship to 1000 child items it’s probably going to do 1001 queries thanks to the n+1 select problem, if not more. That means it’s probably spending 1000 times your network round trip latency just waiting. You can left join fetch to avoid that, but then you transfer the parent entity 1000 times in the join and have to deduplicate it.
Similarly, if you’re populating the database from an ORM, you’re probably doing hundreds of thousands of trivial INSERTs… and waiting after each and every one for the server to confirm it’s OK.
It’s easy to try to focus on query execution time and try to optimise that, but there’s only so much you can do with a trivial INSERT INTO ...VALUES .... Drop some indexes and constraints, make sure it’s batched into a transaction, and you’re pretty much done.
What about getting rid of all the network waits? Even on a LAN they start to add up over thousands of queries.
COPY
One way to avoid latency is to use COPY. To use PostgreSQL’s COPY support your application or driver has to produce a CSV-like set of rows and stream them to the server in a continuous sequence. Or the server can be asked to send your application a CSV-like stream.
Either way, the app can’t interleave a COPY with other queries, and copy-inserts must be loaded directly into a destination table. A common approach is to COPY into a temporary table, then from there do an INSERT INTO ... SELECT ..., UPDATE ... FROM ...., DELETE FROM ... USING..., etc to use the copied data to modify the main tables in a single operation.
That’s handy if you’re writing your own SQL directly, but many application frameworks and ORMs don’t support it, plus it can only directly replace simple INSERT. Your application, framework or client driver has to deal with conversion for the special representation needed by COPY, look up any required type metadata its self, etc.
(Notable drivers that do support COPY include libpq, PgJDBC, psycopg2, and the Pg gem… but not necessarily the frameworks and ORMs built on top of them.)
PL/pgSQL functions
A PL/pgSQL function is a set of SQL statements created in advance and stored on the database server with a specified name. Frequent or complex operations are written and stored in advance with SQL statements. When the database is required to provide the same service in the future, Just execute the stored function again.
Complex business logic requires multiple SQL statements. When there are many operations between the client and the server, a large amount of network transmission will be generated. If these operations are placed in a stored function, the network transmission between the client and the server will be reduced, reducing the network load.
PgJDBC – batch mode
PostgreSQL’s JDBC driver has a solution for this problem. It relies on support present in PostgreSQL servers since 8.4 and on the JDBC API’s batching features to send a batch of queries to the server then wait only once for confirmation that the entire batch ran OK.
Well, in theory. In reality some implementation challenges limit this so that batches can only be done in chunks of a few hundred queries at best. The driver can also only run queries that return result rows in batched chunks if it can figure out how big the results will be ahead of time. Despite those limitations, use of Statement.executeBatch() can offer a huge performance boost to applications that are doing tasks like bulk data loading remote database instances.
Because it’s a standard API it can be used by applications that work across multiple database engines. Hibernate, for example, can use JDBC batching though it doesn’t do so by default.
It is unfortunately not possible to automatically enable batching for existing applications. Apps have to use a slightly different interface where they send a series of queries and only then ask for the results.
Performance
I thought a hosted database service like RDS Postgres would be a good example of where this kind of functionality would be useful. In particular, accessing them from ourside their own networks really shows how much latency can hurt.
At ~320ms network latency:
- 500 inserts without batching: 167.0s
- 500 inserts with batching: 1.2s
… which is over 120x faster.
You won’t usually be running your app over an intercontinental link between the app server and the database, but this serves to highlight the impact of latency. Even over a unix socket to localhost I saw over a 50% performance improvement for 10000 inserts.
COPY is fastest
Where practical clients should still favour COPY. Here are some results from my laptop:
inserting 1000000 rows batched, unbatched and with COPY
batch insert elapsed: 23.715315s
sequential insert elapsed: 36.150162s
COPY elapsed: 1.743593s
Done.
Batching the work provides a surprisingly large performance boost even on a local unix socket connection, but COPY leaves both individual insert approaches far behind it in the dust.
Use COPY.