databases
learning sql
Postgres
MSSQL SQL Server local instance
MariaDB
todo
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
indexing
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.