It happens pretty much real time. While using cURL or the .NET client it doesn't return a response until the indexing is finished. You can verify this by querying your index for some basic info. For example you could even just put the following in the browser:
The bulk index API is very fast, I initially tried converting all the records into 7 separate JSON files with ~400k records each and passed them to elastic using cURL and the bulk api. Once the file finished uploading to the server where Elastic was hosted, the indexing of 400k records only took about 20 seconds on a pretty trivial dev VM. It's about the same using the .NET client, the majority of time is spent round-trip to the DB and organizing the data the way you want it to pass it to the bulk API. The indexing is super fast once you've got that all together. I don't know if it actually is optimized on the ES side of things for sure, but ES definitely isn't a bottleneck from what I've observed.
Edit: According to the official ES documentation: "The bulk API makes it possible to perform many index/delete operations in a single API call. This can greatly increase the indexing speed."
They're fairly vague in their documentation about what's going on under the hood. I'm sure you could go dig through the source and find out more about whether it would really speed things up or not for a specific use case.
Yeah I don't disagree with you there. I tested the memory consumption on one chunk before I went whole hog and did the whole data set. You certainly don't need to do 50k at a time, but it might be worth pulling at least 1k at a time and indexing (depending on how large your objects are of course). Mostly I was implying that in most cases it shouldn't take 4 queries to the DB per record, you can likely setup things to pull a decently large number of records at a time, and then pass them with the bulk API, the number of network requests that are needed both to go round trip to the DB and to go to Elastic would be significantly reduced.
Elasticsearch is awesome. It may be a good idea to use the bulk API that is built into elasticsearch, use some joins in your SQL query, and index more than just one record at a time. In the implementation I have, I batched my query to 50,000 records at a time that then index into elasticsearch. For the 2.7 million records I indexed this week, it took a total of 54 queries to the database (50,000 records returned at a time). Just one more idea to streamline your indexing without slamming your DB quite so hard.