Tips for a healthier Postgres database (2021)
>There seems to be a common lifecycle of indexes within applications. First you start off with almost none, maybe a few on primary keys. Then you start adding them, one by one, two by two, until you've got quite a few indexes for most any query you can run. Something is slow? Throw an index at it. What you end up with is some contention on overall throughput of your database, and well a lot of indexes that became a tangled ball of yarn over time.I spent years doing index maintenance on a large corporate database. Creating and rebuilding indexes was a hassle. So I set about building a new kind of database engine where every column in a table organizes its data for fast access (e.g. a columnar store with no separate indexing structure).I expected my system to be substantially faster than a Postgres table without indexes for a broad range of queries. What I didn't expect was for it to also be much faster for tables that were highly indexed in Postgres. I ran tons of queries on both systems and almost all of them were like this video:https://www.youtube.com/watch?v=OVICKCkWMZE