Notes on Postgres
- Hash joins are always bad, they write a temp table to disk/RAM (?). So if a big query has a hash join. Then you will
run out of disk space.
- If any query is taking more than 10 seconds, start profiling.
EXPLAIN ANALYZE is your friend, understand what it is trying to say.
- pev2 helps you understand the query plan of a query using beautiful visualizations.
- If you are adding a column to a table, always first make the column nullable and add the column and perform all
other ops later.
- Any ORM is slower than raw SQL, when you have to make any ops on DB at scale. always do the SQL version.
- Postgres does something called WAL (write ahead logging), before making an insert/update. This log will automatically
clear up once the need is over. The default max size is 1GB.