database indexes

[cch5ng]

look at indexes; try to better understand what is the significance of the ones predefined (later) scan some of the more advanced topics under the tutorial (later) scan some of the db admin topics in the docs

[cch5ng]

followup: add ON DELETE CASCADE, ON UPDATE CASCADE logic to ownerId (fkey) on job table

---

resc https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-foreign-key/

[cch5ng]

research question: was wondering why in Dodds' schema he appears to create a separate index on the column which is a foreign key?

finding: postgresql has to do a scan on each row for query like select * where foreign_key = 'zzz'; but creating an index on the fkey column only really improves performance on read (slightly makes performance worse for write - create, update, delete); in this app, expect the user to do a lot of read and write (possibly more write) but also I think for initial scale that the performance difference would not be significant...

action: create the separate index just for practice but keep the big picture goals in mind for future apps

resc https://backstage.payfit.com/of-postgresql-indexes-and-foreign-keys/#:~:text=Indexing%20Foreign%20Keys%20in%20PostgreSQL&text=A%20foreign%20key%20must%20reference,a%20match%20will%20be%20efficient. https://www.postgresqltutorial.com/postgresql-indexes/

[cch5ng]

followup questions: should more updatedAt columns have separate indexes? why do _PermissionToRole_B_index and _RoleToUser_B_index exist?

---

resc https://medium.com/@bengarvey/use-an-updated-at-column-in-your-mysql-table-and-make-it-update-automatically-6bf010873e6a#:~:text=Another%20benefit%20you%20get%20from,column%20and%20prevent%20redundant%20queries. https://stackoverflow.com/questions/67392742/should-i-create-index-on-created-at-column-on-database https://blog.logrocket.com/how-configure-indexes-prisma/