Best way to update 1M records in DB

I’m new in phoenix elixir, I just created a collection of 1M users record, Now I want to apply/learn best practice to update all 1M collection, But I’m planning to use Stream for this purpose. can you Pl guide me on what to use to update all these, My goal is the solution should not have effects on machine and memory.
Thanks in advance.

What kind of update you want to perform? Maybe it would be possible to do so entirely in the DB instead of fetching all entries.

Clarity is important here. Are you looking to use these million as a test for streaming ingestion when the project goes live?

If not, you should use PostgreSQL COPY function. It’s about 100 times faster than anything else you can do to load data. If you have to preprocess it before it can be loaded then consider defining batch sizes that you can then load as a group with COPY.

If it is the streaming test case, I’ll leave that discussion for the rest of the thread.

Basically, this is for the testing purpose I want to know what If I wanna add a prefix for mobile number of each record. what are the best way to update all entries.

I want to update all record in efficient way, that It shouldn’t have affect on rest of services/process.

In that case, what you’re looking for is more of a DB concern than an Elixir concern. You’ll just need an UPDATE query, but to prevent stress to the database you should batch the updates so that it doesn’t try to do all 1 million at the same time. Either add a where clause to limit how many are update using an id range or if your database is partitioned, something based on that.

@brightball Thanks for your time, Basically, It’s my bad that I can’t explain my exact needs what I was looking for, I’ll create an other post with clear details. Thanks for your time. I apologize for taking your time.

1 Like