I recently completed an Endeca to Algolia migration. I spent quite a bit of time auditing the Endeca implementation, from the XML files to the Windows desktop application. It was pretty good for its era.
> The big difference is that duplicate index entries can still occur in B-tree indexes. GIN indexes have to integrate new entries into the posting list every time, which makes data modifications less efficient (this is mitigated by the pending list). In contrast, PostgreSQL deduplicates B-tree entries only when it would otherwise have to split the index page. So it has to do the extra work only occasionally, and only when it would have to do extra work anyway. This extra work is balanced by the reduced need for page splits and the resulting smaller index size.
I would be very hesitant to turn an ORM into a "smart" SQL generator. Depending on the type/distribution of data, there are sometimes very different paths to optimizing a query. The ORM as a "stupid" SQL generator (straightforward mapper) is a great way to allow for the flexibility to control how a query is generated.
The DB engine might be the place for those sort of improvements.
I'm in a one bedroom Brooklyn apt. Any time I open up my laptop, my 2yr old daughter climbs onto my lap and repetitively hits the caps lock key (it lights up on my laptop). Luckily, I've found alternative places to work from, albeit with less than ideal setups.
That book does cover many implemention details of a database. However, sometimes at a high level, and as you mention, specifically in the context of distributed systems.
I wrote up some tips/impressions on configuring Algolia for ecomm:
https://www.avikaminetzky.dev/posts/algolia-ecommerce-nextjs