Skip to content

SQL Optimizations

Jump to bottom
Milan Miladinovic edited this page Jul 31, 2021 · 4 revisions

Note

It might be a good idea to check out the schema page first, to have an idea as to what each table actually stores.

This page will only cover writes, since most reads are performed from Redis anyways. We occasionally read from Postgres, though this mostly occurs on program startup.

Query Types

When we flush our buffers to disk, up to 7 different queries can execute.

  1. Insert new Orders
  2. Update old Orders
  3. Insert new Pending Orders
  4. Delete old Pending Orders
  5. Update the total number of orders on the exchange
  6. Update any modified Markets
  7. Insert new Trades

Queries 1-4 and 7 have required some form of optimization, while queries 5 & 6 have not. This is because query 5 only modifies a single row, and query 6 modifies x rows, where x <= # markets on the exchange. In short, these two queries operate on such a small number of rows that they have not needed any optimizations.

As for the other 5 queries, each can insert or modify a massive number of rows depending on the number of elements we store in the program's buffers. Take inserts for example; if we had to insert 100k new Orders, one approach would be to insert orders one-by-one using a different query each time: INSERT INTO Orders (...) VALUES (...);. In this case, each time we execute this statement, the index on the Orders table would need to be updated, the query planner would need to parse and develop and new plan, and it would require a request-response between the client (our application) and the server (postgres). Performing this for each of the 100k orders would make our writes extremely slow, and we would still have to execute queries 2-4 and 7.

Instead, we use a combination of Prepared Statements and Transactions for batch query execution. For example, we can prepare the following query: INSERT INTO Orders (...) Values (...);, which means Postgres only needs to run the query planner once before we insert 100k orders. The next part, executing in a single transaction, ensures that we only update the Order table's index once after the rows have been inserted. The only issue that remains unaddressed here is the large number of messages sent between the client and server, although this gets addressed in queries 3, 4, and 7.

Clone this wiki locally