0

Here is my situation:

  • I have a WebSocket connection that feeds me data real-time
  • I have an async callback function that gets the data and inserts into a queue
  • I have another thread that reads from the queue and stores it into the Postgres database, one row at a time (using python's psycopg2 library)

The problem is the real-time data comes in faster than the time it takes to insert into the database, and in a couple of hours, the server runs out of memory. (Is this because psycopg2 library is slow?)

A simple solution is to create more threads that insert into the database; however, this will cause the data to be out of order. Is there a database that sorts the data? Other suggestions would be very much appreciated.

  • I am planning on using timescaleDB at some point but for now, I wanted to test my current WebSocket client with Postgres. But if I'm not mistaken, the differences between Postgres and TimescaleDB are the fast insertion, deletion, retention policy, etc. I think the current problem I'm facing may be due to load balancing, not the DB itself. Do you have any suggestions on how to solve this kind of problem? – MoneyBall Feb 23 at 12:28
  • 1
    "cause the data to be out of order" - relational databases guarantee no inherent ordering to the data and, in general, do not guarantee to return the data in insert order. So what ordering are you concerned about and why? – Michael Green Feb 24 at 10:20
3

Generally speaking with relational databases, 1000 single-row inserts will be slower than a single 1000-row insert. Batching up your data in memory and loading to the database may be faster.

As you receive data into memory, you build a queue. Then, you pop the top item from your queue and insert it into the database. Instead, pop the top N items and insert them all in a single INSERT statement. The ideal value for N isn't universal, so you'll have to experiment. Try starting at 1000, 10000, and going from there based on the throughout results.

I've also seen (and supported) architectures where high volume data is streamed to a file, then asynchronously loaded to the DB. This helps ensure that your receiving service never runs out of memory and crashes (thus losing data in memory), but it does add some complexity, and doesn't guarantee that you'll always load data faster than it comes in. Rather, it just separates it into a separate problem to solve. In this configuration, your receiving service would write data to files until they are N MB/rows in size. Then, you roll over into the next file. (Personally, I like datetime stamps on file names to preserve file order.) Asynchronously, a separate service picks up the less-than-most-recent files and loads them to the database, (using COPY or whatever Python library you find for that).

  • 1
    Agree with @AMtwo here. The immediate thing I would suggest is to implement some "upstream batching". So if you have a queue -- then INSERT multiple rows/records into the database as one INSERT, rather than a single row per insert. Note I recently wrote up some other "best practices" for improving insert performance here: github.com/timescale/docs.timescale.com-content/issues/294 (TimescaleDB person here) – Mike Freedman Feb 23 at 18:20
0

This question is better suited to Stack Overflow. You're almost there with your architecture, I think. You want the web sockets to be as fast as possible and save the data to some non-persistent storage, maybe in memory is enough if you're not worried about losing data on a server crash. Then every so often you want a process to save data from that queue into Postgres. Make sure you bulk insert many rows in one round trip to the database. Use a prepared statement and pass it only the array of data that needs to be inserted. – Colin 't Hart

0

I have an async callback function that gets the data and inserts into a queue

What is the queue? Is it transactional and crash proof?

The problem is the real-time data comes in faster than the time it takes to insert into the database, and in a couple of hours, the server runs out of memory. (Is this because psycopg2 library is slow?)

This is an empirical question. A look at top can probably answer that. In my experience, psycopg2 is not noticeably slower than anything else. It also supports the COPY protocol which can remove some bottlenecks. If the table you are inserting into is indexed, then index maintenance might be the bottleneck.

A simple solution is to create more threads that insert into the database; however, this will cause the data to be out of order

It can be inserted with whatever timestamp the callback or queue assigned to it.

  • I see. Regarding the out of order problem, the data from the WebSocket come in order (e.g. data A at t=0, data B at t=1). If I used multiple threads, my concern is that a thread 2 handling data B will insert that data to the database before thread 1 does (which handles data A). – MoneyBall Feb 24 at 0:10

Not the answer you're looking for? Browse other questions tagged or ask your own question.