-2

I currently have a fairly small (micro, but can upgrade if necessary) Postgres db instance on Amazon RDS. I think I have a max connections of about 85 (checked through max_connections value in console), though I could likely tune that a little.

I have a scheduler that will enqueue 1000 jobs every 1 minute, and these jobs will each make 1 read (single query, no joins), and then 1 write (single insert, one table) a few seconds later when the job is completed.

The jobs are run by two workers which have a connection pool of 25, totalling 50 between them, where the remainder of the connections are used by my application.

So the question is: would it be wise to hammer my db with 1000 reads followed by 1000 writes a few seconds later, every 1 minute, or, instead, spread that load to around 16 reads and writes every second?

The latter sounds much nicer, but it does involve much more complexity elsewhere.

EDIT: I am not AT ALL suggesting I do 1000 reads where a single read will suffice. I am running these queries in worker jobs (separate processes), so I cannot simply batch read these rows without using an intermediary store. Thought I should clarify as first answer appears to assume I don't understand how DBs work.

EDIT 2: Have decided to stream these rows from the db and pass them into a concurrent streamer in the language I am using, which then feeds chunks of the results to a process which batch inserts.

Thanks for all the answers.

2
  • I think your question lacks a lot of details. Those "1000 reads" are "1000 rows" or "1000 SELECT with an unknown number of rows red"? That changes a lot. Why do you need two workers? If you could downgrade to just one, could you use batching as other user suggested? Try to explain more about your infrastructure and why it is made like it is, so that others could understand more of your needs, and maybe suggest something different but better for you.
    – mordack550
    Commented Mar 5, 2018 at 8:25
  • 1
    @mordack550 You're right, it does lack a lot of details - primarily because it was during the brainstorm of a new piece of architecture -- I wouldn't have considered pushing such a naive approach into production, which is why I came here for some suggestions. I since gathered feedback from the community for the language I am using (Elixir) and found a great compromise using streams and chunking. I think this forum was probably the wrong place to start the question. Lesson learned, ey.
    – Damien
    Commented Mar 6, 2018 at 14:32

4 Answers 4

1

I believe if you are putting ~ 1000 jobs in a queue to be processed, you should be able to have an event subscribe to the completion of that job or an object returned from that job with a status. Take that event and have a single worker that either writes or batches requests for a few seconds and then writing several records together to the database. Have the results from the worker write to an object that is processed to write the records. Locking in memory on objects is much better than trying to do it with all the overhead of all those connections.

Depending on the environment and sensitivity to record loss if there is a crash, you could always write the status messages to a List object and have an event timer firing off every 1 to 5 seconds to write the updates to the database.

If you're wanting to using the same most recent sent of events for the read functions, you could always just keep them in memory for the working session and write them to the database as a persistent log.

1
  • I do like this approach, and is potentially something I'll run with in the future. At the moment, I've decided to stream the database rows into asynchronous tasks which send chunks of results to a function which batch inserts. Seems to be working very well, though does lack the durability. Having said that, the durability doesn't matter much in this case.
    – Damien
    Commented Mar 6, 2018 at 14:36
4

One of my favorite things about databases is that they can handle work in batches.

If you need to read 1,000 rows, don't get them one at a time, each with their own query. Use a WHERE clause with a list of values that you're looking for, or a range comparison (like greater than a particular ID, and less than another ID.)

If you need to write 1,000 rows, try working in a batch instead. Writing all 1,000 rows in a single statement can result in less blocking - whereas 1,000 statements trying to write to the same table, simultaneously, can be a blocking nightmare. (Especially when that table has multiple indexes.)

2
  • "One of my favorite things about databases is that they can handle work in batches." hardy ha :P ..but these queries are coming from separate worker jobs? How am I to batch query these rows and feed them to separate workers? Are you suggesting I have an intermediary store with these batch results? Same goes for writing.. schedule a bulk write using an intermediary store, too?
    – Damien
    Commented Feb 27, 2018 at 20:53
  • Fair point RE blocking. I'll have to look more into that and how Postgres handles it, and what might be an adequately safe and efficient solution given my architecture. I can't see how I can bulk write or read from separate processes without using an intermediary store that builds up the query.
    – Damien
    Commented Feb 27, 2018 at 21:12
0

You are almost certainly 'doing it wrong' in one way or the other. If your application truly needs to operate that way, you probably should be using some other sort of datastore. Maybe something like Hadoop or a key-value store. Alternately, you may be doing something that is inherently a batch operation and should be done on the server side rather than remotely. Your description sounds like load-test simulation software designed to find the limits of connection pooling.

1
  • I'm performing availability requests to many different services. What do you mean done on the server instead of remotely? Good point RE a K/V store, but I'm not sure that fits with my data model at all, which is relational. I could, however, potentially move data from the datastore to Postgres for long-term persistence. Also, this isn't really an answer, it's a comment. Probably worth leaving it as such.
    – Damien
    Commented Mar 3, 2018 at 14:11
0

Got to point out that batching up, whilst making the process of the batch probably quicker. your unit of work is now the batch, thus handling failures and retries is way way harder. Which one in the batch caused the failure.

Secondly co-ordinating all the inputs into a batch and then fanning back out is a level of complexity that you might not want.

Finally to your question, you could throw everything at the DB and have sessions wait, the first guy will probably be quick, and the last really really slow as its waiting for resource. Thus having things evening spread is more consistent and consistency is good.

One final thing is that you could just have a serial worker that processed the requests. You've only a small DB and thus one thread might consume all that. Don't go parallel unless you have to.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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