relational models
learning sql
Postgres
MSSQL SQL Server local instance
MariaDB
NoSQL - MongoDB

Related: ORMs and ODMs

todo

• dive deeper into caching, I THINK I have a good basic understanding of caching but would like to implement a caching solution from scratch to cement this knowledge.
• process this video

Imagine I have a landing page with heavy traffic and a banner that has a real count of how many clients it has served a service.

• In theory a simple count query on a reasonably indexed table is not costly at all.
• I could cache the response for a day and the trigger to update the cached value could be a new request or a cron job.
• maintain a separate count table: I think too much extra work and maintenance for updating
• real time updates w websocket

ACID

Atomicity: transactions are all or nothing
Consistency: data moves from one valid state to another
Isolation: concurrent transactions do not interfere
Durability: once a transaction commits it will not be lost even after crashes

CAP

query optimization

https://www.acceldata.io/blog/query-optimization-in-sql-essential-techniques-tools-and-best-practices
refining queries for faster data retrieval.
!Pasted image 20250502111510.png

Normalization and Denormalization

DB normalization and denormalization

indexing

https://www.acceldata.io/blog/mastering-database-indexing-strategies-for-peak-performance#:~:text=When you run a query,much faster than scanning the

Serves as the foundation for optimizing query performance. An index is a separate data structure that stores a portion of a table's data (the indexed columns) in a format optimized for quick searching.
When you run a query with conditions that match the indexed cols, the db used the index to locate the relevant rows faster than scanning the entire table.

When a db engine executes a query one of the 1st steps is checking if there is an available index that matches the query pattern.

tradeoffs

Although indexes can improve read times: they can slow down write operations because the db also has to update the corresponding index entries.
in write-heavy workloads, the overhead of maintaining indexes can become significant. It's important to find balance between query performance and write efficiency.

Another consideration is storage space. In some cases, the size of an index can be as large as the table itself!. While storage relatively cheap now, it is a thing to consider.

Indexing is not a silver bullet for all problems, Sometimes if a table is small or if the query selects a large portion of the table's rows, a full table scan may be faster than using an index. DB engines have built-in heuristics to determine when it's more efficient to full scan or use an index.

types of indexes:

clustered

determines the physical order of data in a table.
like sorting the pages of a book based on a criteria like chapter number.
when you create a clustered index on a col (or set of cols) the db rearranges the table data to match the index order.
a good use case for this could be for example if you frequently query orders within a specific date range.

non-clustered

created a separate structure that contains a copy of the indexed cols along a pointer to the corresponding row in the table. It's like having a separate book index that lists keywords and their page numbers.
ideal for optimizing frequently queried columns that are not part of the clustered index. They allow you to have multiple indexes on a single table, each tailored to specific query patterns.

unique and composite clustered

Unique indexes ensure that the indexed column(s) contain no duplicate values, helping to enforce data integrity.

Composite indexes, also known as multi-column indexes, include multiple columns in a single index structure. They're useful for queries that frequently use multiple columns in their search conditions.

composite indexes takes up more storage space the more columns the index involves. the relation is directly proportional.

order of columns in the index matters.

Migrations

Migrations = versioned schema history. They are the audited, repeatable steps that take a database from state A → B (add column, backfill, rename, drop). Your entities describe the desired shape; migrations are the path to get there safely.

Entities defined in code alone are not enough for a shared DB. ORMs and ODMs can invent schema from entities (synchronize: true), and that can feel fine for a solo throwaway DB. It is not how you run a real app: it can drop/rename wrong, skips data transforms, and every machine/CI/prod would “guess” differently. Migrations make every environment apply the same ordered SQL.

A thousand migrations is normal and fine. They are cheap to run (TypeORM tracks what’s already applied). Pain is usually review/noise, not runtime.

You can squash later. When history gets noisy: dump the current schema as one “baseline” migration, mark it applied on existing DBs, delete the old files, and start fresh from there. That’s a deliberate ops step, not something you do casually—existing environments must agree on that baseline.

Rule of thumb: entities = current model in code; migrations = how every database got there (and stays in sync).