Skip to content
academia.sh
tr
Log in

Course Advanced

Relational Database Administration

By the end of this course

Start course

01

Engine Architecture

Process and memory layout, how data settles into files as pages, how the write-ahead log provides durability, how checkpoints sync the log with the data file, multi-version concurrency control, dead row cleanup, and querying the system catalog.

  1. 01 Process and Memory Architecture The difference between server-based and in-process engine setups, the division of labor among background processes, the split between the shared buffer pool and per-connection memory, the measured effect of pool size on the hit rate, and the shared pool's advantage over private caches.
  2. 02 Physical Storage Layout The page as the engine's smallest unit of read and write, how page size and page count determine file size exactly, measuring in-page fullness and rows per page, overflow pages, and the tablespace concept.
  3. 03 Write-Ahead Log The cost of tying a commit to writing data pages to disk, the definition of the write-ahead rule, the log file growing at commit while the data file stays unchanged, showing that committed data lives only in the log, crash recovery, and the measured effect of log mode and the sync setting.
  4. 04 Checkpoints Changes in the log being applied to the data file, how a checkpoint changes both the data file and the log file, the difference between reusing the log and truncating it, the trade-off between checkpoint frequency and recovery work, and how readers delay a checkpoint.
  5. 05 Multi-Version Concurrency Control The difference between lock-based and version-based concurrency control, measuring whether the same read blocks the writer under two log modes, building the version chain and visibility rule through a model, when old versions can be removed, and the conflicts multi-versioning does not remove.
  6. 06 Dead Row Cleanup The dead versions left behind by deletes and updates, deleted space not returning to the file, why space is not always reusable, the cost of a full rewrite, and the bloat ratio's dependence on cleanup frequency and open transaction duration.
  7. 07 System Catalog Storing schema definitions in queryable tables, turning inventory and administrative rules into queries, measuring schema drift between two copies, and why the catalog is read but not written.

02

Indexes and Partitioning

Tree-based, hash-based, and special-purpose index types, column order in composite indexes, partial and covering indexes, index bloat and maintenance cost, range and hash table partitioning, and the effect of horizontal sharding on the application.

  1. 01 Index Types The measured cost of an unindexed foreign key, the tree-based index's ability to answer range and order questions, the hash-based approach's equality-only operation, and the inverted index for word-level search.
  2. 02 Composite and Partial Indexes The effect of column order in a composite index across three separate queries, the leftmost prefix rule, the page-count size gain of a partial index, and what happens on a query it does not cover.
  3. 03 Covering Indexes The real cost of returning from the index to the table, how a covering index removes that return, how the gain grows with the number of matching rows, and how the index approaches a copy of the table as columns are added.
  4. 04 Index Maintenance The gap that deletion and update leave in index pages, how index order determines whether that gap comes back, the difference between reindexing and a full rewrite, and how write cost grows with the number of indexes.
  5. 05 Table Partitioning Splitting a table into ranges, lists, and hashes; showing by measurement that a query touches only the relevant partition; dropping old data instead of deleting it; and computing the distribution of a partition key.
  6. 06 Sharding Patterns Distributing partitions across separate machines, the limits a shard key imposes on the application, the queueing delay of a cross-shard query, the join and uniqueness problem across shards, and the amount of data moved during rebalancing.

03

Operations

Roles and row-level security, backup types and point-in-time recovery, backup validation, physical and logical replication, failover, connection poolers, monitoring, bulk loading, and version upgrades.

  1. 01 Roles and Privileges Applying the principle of least privilege in the database: role-based access control, counting the excess privilege produced by role inheritance, a second line of defense with a read-only connection, and separating the application account from the maintenance account.
  2. 02 Row-Level Security Showing a different row set on the same table depending on the account: the policy expression, the session variable, counting the rows a policy leaks, auditing the write side, and the policy's interaction with the access path.
  3. 03 Backup Types The measured costs of logical, physical, and incremental backups: comparing file size and time to take, proving a restored copy's equality with the source through a content hash, and the limits of a watermark-based incremental backup.
  4. 04 Point-in-Time Recovery Using a base backup together with a log archive: the recovery window, choosing the target moment, fast-forwarding the log to that target, the work lost after an incident, and computing a partial recovery.
  5. 05 Backup Validation Knowing a backup's validity before the moment of restore: integrity checking with a file hash, catching silent corruption with a content hash, the scope of a structural check, and setting up a regular recovery drill.
  6. 06 Physical Replication Continuously streaming log frames to a standby server: the second copy that a base copy and the stream build together, measuring replication lag, lag showing up as stale reads and data loss, and the cost of synchronous commit.
  7. 07 Logical Replication Moving row-level changes for selected tables: publication and subscription, comparing the data volume sent against physical replication, writing to a target with a different schema, how conflict stops the stream, and the order in which schema changes apply.
  8. 08 High Availability and Failover The service continuing when the primary server is lost: the cluster manager's decision, how the majority rule prevents split brain, the data conflict two primaries produce, and the trade-off between detection time and false failover.
  9. 09 Connection Poolers Managing connection count: the measured cost of opening a connection, the difference between session-level and transaction-level pooling, how session state inherited from the pool can breach row-level security, and how pool size relates to queue time.
  10. 10 Monitoring and Alerting Knowing the database's state through numbers: what the difference between wall-clock time and CPU time means, the distribution of query times and percentiles, what a threshold-based slow query log misses, measuring lock wait, and when an alert is meaningful.
  11. 11 Bulk Data Loading The cost of high-volume import and export: the effect of the transaction boundary on load speed, whether indexes are built during or after loading, extracting bad rows with a staging table, and the shape of export.
  12. 12 Version Upgrades Replacing the layer underneath a running system: the outage window of each upgrade path, a compatibility check done by pulling the schema from the catalog and comparing it, which application version runs at each step of an expand-contract migration, and the rollback plan.

Start typing to search.

↑↓ ↵ Esc navigate · open · close