Comment by sgarland

1 year ago

At low-medium scale, this will be fine. Even at higher scale, so long as you monitor autovacuum performance on the queue table.

At some point it may become practical to bring a dedicated queue system into the stack, sure, but this can massively simplify things when you don’t need or want the additional complexity.

5 comments

sgarland

Reply
arp242  1 year ago

Aside from that, the main advantage of this is transactions. I can do:

  begin;
    insert_row();
    schedule_job_for_elasticsearch();
  commit;

And it's guaranteed that both the row and job for Elasticsearch update are inserted.

If you use a dedicated queue system them this becomes a lot more tricky:

  begin;
    insert_row();
    schedule_job_for_elasticsearch();
  commit; // Can fail, and then we have a ES job but no SQL row.

  begin;
    insert_row();
  commit;
  schedule_job_for_elasticsearch(); // Can fail, and then we have a SQL row and no job.

There are of course also situations where this doesn't apply, but this "insert row(s) in SQL and then queue job to do more with that" is a fairly common use case for queues, and in those cases this is a great choice.

  • jashmatthews  1 year ago

    Transactional Outbox solves this. You use a table like in the first example but instead of actually doing the ElasticSearch update the Outbox table is piped into the dedicated queue.

  • nostrebored  1 year ago

    Most of these two phase problems can be solved by having separate queue consumers.

    And as far as I can tell, this is only a perk when your two actions are mutate the collocated database and do X. For all other situations this seems like a downgrade.

jeeybee  1 year ago

I agree, there is no need for FANG level infrastructure. Imo. in most cases, the simplicity / performance tradeoff for small/medium is worth it. There is also a statistics tooling that helps you monitor throughput and failure rats (aggregated on a per second basis)