Please enable JavaScript.
Coggle requires JavaScript to display documents.
Bases de datos relacionales, Each database system has its own use cases,…
Bases de datos relacionales
ACID / Transactions
Consistency
In data
In reads
Atomicity
Isolation
Tipos
Serializable
Non repetable reads
Repetable reads
Durability
Eventual consistency
En sistemas con horizontal scaling y caches
Internals
Heap data structure
Page (fixed size memory/disk location) (contain rows)
IO (try to minimize as much as possible) (fetches pages)
Index data structure (tells you the pages that contains what your querying)
Types
Non-clustered index
Cluster index (don't use a UUID for this)
B-tree
B+Tree
LSM tree
Single column vs multi-column
Notes
Every primary key has an index by default, and it is a clustered index by default
Expressions in queries make indexes useless, because indexes are meant to hold and search a whole value
Row id
Scan types
Full table scan / seq scan (can be done in parallel)
Index only scan
Bitmap index scan (postgres)
Index scan
Tipos
Row oriented
Column oriented
Update is painful
Notes
Database hinting is a way to instruct the database to behave a certain way, like force the use of some indexes. Sintax is similar to code comments.
Creating an index blocks writes by default. But in postgres we can create an index concurrently with the cost of lasting longer to create.
Bloom filters
Do not use offset when doing pagination as it is slowwww. Offset will fetch the # rows in the offset from disk anyway!!!!
Clean dead rows after roolback
Eagerly
Lazily
Partitioning (partitions are tables in the same db, client is agnostic)
Types
Horizontal (split by rows or range of rows)
Vertical(split and move columns to different table, usually to move less frequent columns to another storage disk)
By range, list or hash (consistent hashing)
Sharding
table spaces
Locks
Exclusive lock (only one is allow to read/write to some column, row, table)
Shared lock (many are allow to read a value but that value won't change, if change is attempted by anyone an error is raised)
Replication
Single master replication (useful to scale reads)
Synchronous vs asynchronous replication
Multi master replication (useful to scale writes)
Cursors
Each database system has its own use cases, advantages and disadvantages
Clustered index (primary keys) (Index organized table) vs non-clustered index (secondary keys)
All indexes in postgres are secondary indexes
A secondary index is an index that lives in its own datastructure