Few tips I gathered along the years:
- Configure Vacuum and maintenance_work_mem regularly if your DB size increases, if you allocate too much or too often it can clog up your memory.
- If you plan on deleting more than a 10000 rows regularly, maybe you should look at partition, it's surprisingly very slow to delete that "much" data. And even more with foreign key.
- Index on Boolean is useless, it's an easy mistake that will take memory and space disk for nothing.
- Broad indices are easier to maintain but if you can have multiple smaller indices with WHERE condition it will be much faster
- You can speed up, by a huge margin, big string indices with md5/hash index (only relevant for exact match)
- Postgres as a queue is definitely working and scales pretty far
- Related: be sure to understand the difference between transaction vs explicit locking, a lot of people assume too much from transaction and it will eventually breaks in prod.
- Configure Vacuum and maintenance_work_mem regularly if your DB size increases, if you allocate too much or too often it can clog up your memory.
- If you plan on deleting more than a 10000 rows regularly, maybe you should look at partition, it's surprisingly very slow to delete that "much" data. And even more with foreign key.
- Index on Boolean is useless, it's an easy mistake that will take memory and space disk for nothing.
- Broad indices are easier to maintain but if you can have multiple smaller indices with WHERE condition it will be much faster
- You can speed up, by a huge margin, big string indices with md5/hash index (only relevant for exact match)
- Postgres as a queue is definitely working and scales pretty far
- Related: be sure to understand the difference between transaction vs explicit locking, a lot of people assume too much from transaction and it will eventually breaks in prod.