Tech - Dev - SQL notes

Dev DB DevOps

psql CLI

https://tomcam.github.io/postgres/

psql tips

https://psql-tips.org/psql_tips_all.html

schema

primary keys : use identity columns

https://maciejwalkowiak.com/blog/postgres-uuid-primary-key/

• use UUIDv7

https://www.cybertec-postgresql.com/en/uuid-serial-or-identity-columns-for-postgresql-auto-generated-primary-keys/

• faster than UUIDv4
• use UUIDv4 only if planning to shard database

Common Table Expressions instead of sub-queries

https://antonz.org/cte/

raw SQL and Python

https://joaodlf.com/python-just-write-sql

query optimization

https://www.foxhound.systems/blog/sql-performance-with-union/

performance debugging for postgres

• EXPLAIN
• GNI index bad for performance

https://iamsafts.com/posts/postgres-gin-performance/

performance and EXPLAIN

https://www.cybertec-postgresql.com/en/how-to-interpret-postgresql-explain-analyze-output/

advanced SQLite use

https://antonz.org/sqlite-is-not-a-toy-database/

best practices for writing SQL queries

https://www.metabase.com/learn/building-analytics/sql-templates/sql-best-practices

SQLite for small-medium scale web apps

https://unixsheikh.com/articles/sqlite-the-only-database-you-will-ever-need-in-most-cases.html