I would make a distinction between adding an index to sqlite/postgres and adding one to elastic. The ANN algorithms usually require the index fits into RAM which can lead to high memory requirements on large datasets. Something like elastic will do a lot better because of its ability to horizontally scale

I assume that is the rationale behind a dedicated database but I generally feel the same way as you