Write route

  • The /code route generates a 500-character code and inserts it into PostgreSQL. 1:01:57
  • The table has an ID, a creation timestamp, and the random code. 1:02:17
  • A first attempt with 5,000 connections causes many timeout errors, so the uploader lowers the test to 300 connections. 1:03:47 1:05:14
  • At 300 connections, the database handles about 35,000 writes per second with no errors, processing about 700,000 requests. 1:05:14 1:05:31
  • The server CPU and network are mostly idle, so PostgreSQL is the limit, not Node. 1:06:17 1:06:41
  • The setup opens about 1,000 PostgreSQL connections because 128 Node instances each use a default pool of 10 connections. 1:07:09 1:07:30
  • Raising provisioned IOPS from 3,000 to 12,000 improves writes to about 66,000 per second, but adds about $1,000/month. 1:08:28 1:08:53 1:09:11
  • The suggested pattern is to buffer high-volume writes and batch them into PostgreSQL later instead of trying to write every request directly to storage. 1:09:56

Read routes

  • The database is seeded with 10 million records because a million-request-per-second service would likely have at least that much data. 1:11:16 1:11:43
  • Seeding 10 million records takes about 30 minutes. 1:12:26
  • Version 1 uses order by random(), which is O(n) because it scans the table before choosing a row. 1:14:11 1:14:26
  • With 10 million records, the order by random() route takes about 43 seconds for one response. 1:14:41
  • Version 2 still performs badly because count(*) is also O(n) in this setup. 1:15:50
  • Version 3 gets the max ID, generates an ID, and then looks up the row, reaching about 200,000 requests per second. 1:16:24 1:17:33
  • Version 4 does a simpler indexed ID lookup and reaches about 400,000 requests per second without errors. 1:18:21 1:18:58
  • Increasing disk speed again does not materially improve the best read route, because database CPU reaches 100%. 1:20:04 1:21:21

Cost conclusion

  • Scaling PostgreSQL toward 1 million reads and writes per second would likely require multiple or much larger database instances and could cost $20,000 to $30,000/month or more. 1:22:01 1:22:40 1:23:17