Database Engineering Udemy

Indexes

Another data stucture that pinpoints where to check on the heap (has pointers to heap) it is part of data used to quickly search can index one or more than one column uses b tress once you find the value of the index, you go to the heap to fetch more info index tells you EXACTLY which page to fetch in the heap rather than scanning whole heap index also stored as pages and has cost 90 to pull the pointers/entries of the index the smaller in the index, the more it can fit in the memory and faster a fetch example: Index on EMP_ID --> will store empl_id and tuple(of rownumber,page number) corresponding to the emp_id IO1 will be done on the above index to find the page and row number IO2 will be done on heap to pull exactly the page(s) we found in the index.. Some times heap table can be organized around a single index called clustered index or Index organized Table (IOT - Oracle) Primary key - usually a clustered index unless otherwise specified.. Every other index points to PK in Oracle/mysql Postgres ony have secondary indexes and all indexes point to the row_id which lives in the heap (all indexes might have to be altered if one index is altered)

Database Permissions and Building of REST API

Built 3 users in through postgres admin portal - within only login privileges then we further linked these users with certain priviledges on the table as required by the UI. - ex dbdelete user getting only delete (and read with grant option) table -security - privileges + sign to grant action/priviledge for each user. Then create three pool of connections for each user --> and these pool will be used for each user and function example createtodo for create action on the create table though app.post("/todos", async (req, res) +> { let result = {} try{ const reqJson = req.body; resultsuccess = await createToDo(reqJson.todoText) } catch(e){ result.success=false' finally { ... } // basically createtodo function uses the createpOOL async function createToDo(todoText){ try { dbCreatePool.query.query(insert into ...) ....... } catch (e) {...} Act of isnerting a new row in the todo table will trigger the sequence and it will read from this sequence to get the next dequence and the createuser will also need read permission on this sequence.

Database Sharding

Consistent Hashing, Horizontal Partitioning Vs Sharding traditionally query one big database.. query becomes slower as indexes become larger and slowly need more data. Partition table using sharding key and distribute this data to different databases -- can build a function to consistenly hash your requests to differerent database instances..

Durability - WAL

DBMSs persisit a compressed version of changes knows as wal (write ahead long segments) since writing to disk is slow and expensive! OS Cache -- > OS writes (write requests) to the os cache --> to batch these caches and write to the disk at once.. (less IO) when you write to os cache, and os crashes, then you can loose data which is not persisted and only in RAM Fsync OS command forces the write to always go the disk going to be very slow

Understanding Database Internals

Depending on storage model (row vs columnar) the rows are stored and read in logical pages database does not read a single row but page or more in a single IO and a lot of rows are fetched each page has a size of 8KB in posgres and 16KB in MYSQL

Homophoric Encryption

Encryption symmetric - same key encrypts and decrypts asym decryption - for communicating in network architecture between two parties.

Concurrency Control

Exclusive vs Shared Lock - ensure consistency in the system exclusive lock --> only my connection can edit a vlaue by exclusively obtaining a lock on the value --> no shared lock should be acquired on that value shared lock --> while reading a long transaction (think a long read/reporting transaction) --> then if any other account tries to make a deposit it will fail to open an exclusive lock on the account. once shared lock is gone then can have exclusive lock

Working with a Billion row Table

First level --> Huge Map and Reduce jobs running on a multi node cluster ----------------------------------------------------------- second level --> process only subset ofd a table (indexing) create a structure on the disk that reduces the subset that you search (which is the index) --> to further narrow search subset, use partitioning rows from 1 to n in this location of the disk (example) Partition key determines the partition/smaller piece of the and indexing will work on each partition then.. --------------------------------------------------------------- third level -sharding (breaking down the database into smaller databases -- adds client side complexity though!) --> basically partitioning horizontally on multiple hosts ---------------------------------------------------------------- Alternative --> do not have such a big table!! put a list, json field that basically encapsulates having multiple rows --> think of a followers table where all followers are now aa json field for each user rather than a never ending a follows b b follows c denormalized table)

Isolation Levels

For Inflight transactions no isolation, any change from outside is visible to the transaction, committed or not - opens door to dirty reads read-committed - each query in the transaction only sees committed changes by other transactions.. (default isolation for many database systems) repeatable reads - the transaction will make sure then when a query reads a row, that row will remain unchanged while transaction is running!! snapshot -- each query in a transaction sees only changes that have been committed up to the start of the transaction.. so if new changes are being updated then the snapshot will filter them out and not appear in the result set.. Serializable - effectively slows things down, but insures very high isolation each dmbs implements isolation levels differently DBMS Implementation of Isolation pessimistic - row level locks, table locks, page locks to avoid transactions (very expensive to track level of locks (especially row level locks) optimistic -- no pending transacitons and no locks -- if things change then fail the transaction repeatable read locks the rows that are read but it could be expensive if you read a lot of rows -- postgres implements this with a snapshot (so no phantom reads)

B Trees

Full Tables Scan have a large table and want to find a row so need to read entire table (cann pull various amount of pages/io depending on dbms implementation B trees BALANCED DATA STRUCTURE FOR FAST TRAVERSAL B tree - "m" degree means some nodes can have "m" child nodes node has upto m-1 elements element has a key and value the value is a data pointer to the row data pointer can point to the PK or tuple root nodes | internal nodes | leaf nodes a node = disk page (example 8kb worth of elements populating the node) each element is in the form of n1(key/ID/PK):n2(value:Tuple ID in postgres or page#/rowID in mysql/ oracle child nodes with lower key go to the left in the middle of the two elements go in the middle and key more than node elements go to the right. B Tree Limitations ------------------------------ storing both key and value make internal ndoes take more space thus requiring more IO - example primary key on a uuid or string field range queries are slow because of random access - since these pk's do not necessary preserve order of values and thus we could be requiring 5 different traversals (worst case) for a range query 1 to n B+ Trees ------------------------------ internal nodes only stores keys.. values stored in key nodes.. internal nodes thus can fit more elements (keys) leaf nodes are linked to keys - great for range queries leaf nodes are all linked together (holding values) so can do a very efficient iO to capture many closely situated index values B tree and DBMS Considerations .Cost of leaf pointer (cheap) 1 Node gits a DBMS Page can fit internal nodes in easily in memory for fast traversal (not as busy as leaf nodes) leaf nodes can live in data files in the heap. internal nodes indexes are tiny (a lot compressed in a page) so should fit in memory (cache) for fast traversals.. depending on the index size and pointer size the leaf nodes could also be in memory or forced to be stored in heap (disk) B+ Tree storage cost in PostGre vs MySQL B+ trees secondary indexes can either point to the tuple directly (postgres) or PK (mysql) If PK datatype is expensive then this can bloat all the secondary indexes for databases such as MSQL (innoDB) Leaf nodes in MYSQL contain the full row since its an Index organized table/ clustered index. (all row items tightly packaged with PK)

Horizontal paritioning vs sharding

HP splits tables into tables and client is agnostic (DB decides which partition to hit according to where clause) Sharding splits big tables in multiple tables across different/multiple database servers (client is aware of the shards as the server serving requests according to client traffic/ ip level requests)

Consistent Sharding

Hash("Input") --> hash will return you the instance (postgres:5432) make sure same input is consistenly hashed to the same instance example --> num("Input2) % 3 goes to an instance in the hashing ring

H Partitioning vs Sharding

Hp splits big table into multiple tables in same DB sharding splits big table into multiple tables across multiple database servers in HP table changes(sometimes schema)

IO'S

IO operation --> read request to disk ( we try to minimize this as much) IO can fetch a page or more depending on disk partitions and other factors cannot read a single row - fetched pages only some IO's go the operation system cache and not disk. lest io's --> faster query performance.. we want to be as efficient/targeted in our IO!

MyIsam

Index sequential access methods B - tree Balanced Tree that points to the row directly to offset on disk no transactional support open source and owned by oracle inserts (end of disk) are fast and updates and deletes are problematic deleting and updating rows in the middle - change the structure of the file and this tight coupling index structures database crashes and corrupts tables (cannot be out of sequence) table level locking only

Database Connection Pooling

LOT CLIENTS AND FEW SERVERS - WORKS WELL want to execute this before the get request const {Pool} = require("pg") const {Pool} = new Pool({ "max" : 20 max no of tcp connections (default is 10) "connectionTmeoutMillis" : 0 "idleTimeoutMillis": 0 - if connection is not being used wwhenhrn to drop it in app.get() { const results = await pool.query("select * ...") no overhead of closing and opening all connections again and again can ask the pool to give a client and lock that in case need to run atomic, serialized transaction.

Database Engines

Low level code libraries that take care of disk storage and CRUD can be key value store can be rich and complex to support acid TRANSACTIONS and this logic is take care in the database engine DBMS can use the database engine to build features on top (server replication, isloation etc) embedded database - import in your code and use in local laptop (no server client communcation needed) some databases / DBMS gives you flexibility to change engine (mysql) but some are fixed such as Postgres

Mongo DB Architecture

MMAPV1_idIndex search for ID --> find diskloc and read directly from disk (filename 32 byte + offset 32 byte) where offset will tell what subrange of bytes to fetch for that specific document. --> finally frontend will parse these bytes to shown at the front end hopefully indexes fit in memory from disk.. changing the offesets/ updates makes it a big problem.. Storage Engine - Wired_Tiger first document level locking document level locking -- means you can update two documents on the same collection concurrently.. only way two update two fields in same row object you'd need column/key level locking locks also stored in memory remember that.. second - wired tiger compresses the json document.. --> one page fits more document and there fore one IO gives more documents per hit... --> doing decompression on the client side.. third - uses clustered b tree index.. based on key can search the value/ documents.. and the documents are ordered next to each other so the page you land on retrieves the data situationally relevant (how clustered indexes are sorted in mysql) all leaves are a linked list of documents ordered/sequentially placed so order by/ range queries are very efficient.. in 4.2-5.2 versions.. secondary index/ search for id (visible to client/ user) will then find the record_ids(leaves) pK and these indexes would in turn be mapped to the DOCUMENTS in hidden clustered index (secondary index structure) where leaves would be documents) hence there were essentially 2 indexes that need to be loaded to memory and scanned.. > 5.3 WiredTiger _id index (clustered) 1) search for single clustered index (id) --->> find BSON Document the id is user controlled and can be large (12 bytes) snd this is because mongo db wanted to make these id's universally unique (across machines for scaling) now secondary indexes have to store these large values for these large indexes which make it very large and hard to find on disk..

Database Replication

Master/Backup Replication one master/leader db node that accepts writes/ddls one or more backup/standby nodes that receive those writes from the master. (do not write to the standby nodes directly) reads done/ routed to local slave nodes (can be done according to region) and conflict resolution/consistency need to be dealt here with. multi master replication multi leader nodes accepting writes/ddl one-or more slaves get those writes from masters. (each node will follow a different master but need to resolve writes between masters that can be complex.

sql Pagination

Offset is very slow - keep in mind when want to use for pagination. explain analyze select title from news order by id desc offset 10000 limit 10; --> Index scan Backward using id_idx on news --> will pull 10000 rows when actually only need 10 due to limit in offset query! to solver this do not use limit 1000, 10 or offset limit together as shown above rather, use id to do a backward scan and then limit the number of rows fetched for fast performance.. Explain analyze select title, id from news where id < 100999993 (last iD seen in previous request) order by id desc limit 10;

Durability

Process of persisting the writes to the disk in case of client or database crash/ outage.. or persist the changes when the transaction commits -- the data must be there even if slows down OS disk inherently slow.. you decide tradeoff between write to memory and write to disk (non -volatile) commited trans must be persisted into a durable on-volatile storage. WAL -write ahead log -- do not rely on write to disk that can be slow async snapshots -- REDIS AOL (Append only file) -- keeps track of the changes that happen and then writes these things eventually.. REDIS - gives options to be eventually durable -- slight a few seconds delay might be there

Pros and Cons of Database replication

Pros and Cons pros horizonatal scaling region based queries - DB [per region] your database cons eventual consistency and slow writes (sync) also complext with multi master architecture database protocols very chatty and top of tcp makes it very latent in nature especially when big queries are going through the internet

Row Store vs Column Store (Row oriented vs Column Store)

Row Oriented DB Tables are stored as rows in disk a single block io read to the table fetches miltiple rows with all their columns More IO's required to find the tow in table scan but once you find the row all colmns are taken in Block could have multiple rows depending on the block size once you find the specefic column value getting extra columns is not a problem.. (all columns already retrieved existing in memory) row can span 7 blocks also - depends on size of row items select sum(salary) from emp --> row store will work inefficiently for this. optimal for read/writes and OLTP workloads but compression and aggregation is not efficient. ---------------------------------------------- Column oriented Database Tables stored as columns first in disk single col fetches multiple cols with all (many) matching roles Less IO required to get more values of a given columns (working with multiple columns require more IO'S (directly proprtionate) OLAP - Great for OLAP!!! All columns arranged in blocks one over the other (row of columns) in case of select * from emp where id = 1 --> may jumps need to be made to find all other row values (arranged across columnar blocks) for the matching id -- id will be retrieved easily select sum(salary) from emp --> this works greatly columnar databases and only one columnar row(all blocks) needs to be summed up where things great for warehouses and data lakes (large scale OLAP uses cases) and compression is made easier of values that are similar in same column - example a lot of common names (John) names together..

Solving the Double Booking Problem

SELECT * FROM seats where id = $! and isbooked = 0 FOR UPDATE (for update here is n exclusive row level lock) makes this race serve (wait will be very minimal according which dataabse you use), however oracle gives a timeout option! -------------------------------------------------------- database internal lock when a client initiates a lock update seats set isbooked = 1, u = 'hussein' where id = 1 and isbooked = 0; UPDATE 1 tuple iD will be locked (given there is PK set on id) and the other client will not be able to execute the commit.. (under the assumption that this is a postgre sql transaction and isolation level is on read committed).. remember isolation level matters a lot here! once original transaction commits, the read will be executed based on the

No SQL Architecture

Schema less store of data based on document store Frontend |. Storage Engine API - Get/Set. Storage Data Format (JSON) Indexes/Data Files, WAL'S, transactions No SQL is mostly changes we see in the front end such as API's and what data flashed to disk (JSON data converted to byye data and flushed to disk)

BitMap Index Scan VS Index Scan VS Table (Seq) Index

Seq Scan --> in cases that have to fetch/ make to many hops to heap, it will just scan healp.. example: select * name from grades where id > 100 ------------------------------------------------------ explain select name from grades where g > 95 [bitmap heap scan] --> will jump to the heap once and fetch all those pages (and then rechecks for garbage rows pulled in the pages that does not meet the check] [bitmap index scan] --> will set all appropriate pages where the index is fetched on the bit map ---------------------------------------------------- last case select name from grades where g > 95 and id < 1000; [finally recheck and bitmap heap scan] [BitmapAND] Essentially inly takes the intersection and drops the rest [bitmap index on grades_pkey(id field) [Bitmap index scan on g]

Serializable vs Non Repeatable Read

Serialazable - > be ready for commit failures - could not serialize access due to read/write dependencies amongst transactions..

Server vs Client Side Cursor

Server Side Cursor will be created on the database, server side, and only when required will fetch that cursor set as per calls from the client application -- result is executed on the server side! # client side cursor: cursor = con.cursor() cursor.execute("select * from table") -- this takes a long time for the client side cursor since we are fetching everything into a tuple.. (read only) rows = cursor.fetchmany(50) conn.close() # server side cursor: cursor = con.cursor(jaivir) -- only different in code for client side server is naming the cursor. cursor.execute("select * from table") -- this takes a long time for the client side cursor since we are fetching everything into a tuple.. (read only) rows = cursor.fetchmany(50) -- fetching rows slightly slower but just fetching 50 rows so round trip to server is not costly at all conn.close() PROS AND CONS Client side server takes memory allocation on itself and the disadvantage is that the bandwith of the network is very heavy and maybe client does notnahve that much to process this data request.. -- dont make unbounded queries!! Server side puts overhead on the server side and opening all these client connections to the server can be an issue leaking data can be an issue.. good for some map reduce use cases where we want to perform transformations on a subset of data.

Sharding With Postgress

Spun up docker postgress shards (exposing three differenrt ports ) and also spun up pgadmin on our local host Writing to a shard (node js code) urls = [] for (let i = 0; i < 100; i++) urls.push(`https://google.com.in?q=test${1}`) generate a bunch of urls above using for loop and then make a async call to hit a bunch of these url's urls.forEach( u => fetch("https://localhost:8081/?url=${url}`, {"method": "POST"}).then(a => a.json()).then(console.log))

Deep look into Postgres Wire Protocol with Wireshark

Time Source Dest Protocol Length Info notes first three entries will be tcp connections (initial establishment) 5432 -> 5602 [SYN, ACK] three way handshake from server to client and then back and final ack back from server > client information here <E - server from client >X means exit conenction (client to server) >X --> singalling query from client to server

Consistency

Traded off in different platforms -- sacrificed for performance and scalability Consistency in Data - Consistency in Data/Disk defined by dfa -- referential integrity (foreign keys) atomicity --> for example debited and crediting scenarios isolation --> example repeatable reads (consistently changing data) constraints can be performed in database level or application level. Consistence in Reads if a transaction committed a change will a new transaction immediately see the change? affects the system as a whole both sql and no sql eventual consistency? not same as corruption to your data (more references the read integrity of your data will be eventually consistent) -- database heals eventually as replication process is completed.. can be achieved with async replication.

Isolation

Transaction Isolation - run. a transaction in complete isolation from the other concurrent transactions. Many read phenomenas -- undesirable isolation levels --> to solve above dirty read --> write not fully slushed to memory basically read a dirty value that has not been committed.. non repeatable reads --> read something and the database reads the same value that changes.. read. a committed value but it gave inconsistent results based on our prior value post gre contains versoining table , though myswl etc need go to versioning vault to ensure right value is read from data which can be expensive.. phantom reads --> similar to non repeatable reads but not caught on tables can / results.. but basically some entry is added to the result set before the query returns with the data. cant grab the newly inserted row elsewhere in the search query.. Lost updates --> what you updates is reverted by another connection can be helped with row level locks..

Building a Short URL System Database Backend

URL Shortener Design ID - serialized field grows by one per increase of row. (PK) SO Automatically indexed. URL - Long uRL stored B- tree index - writes might become a bit slower due o reshuffling LSM Engine (mysql) - great for this rebalancing Custom URL Designer PK on Short uRL but now a string PK Sha() 32 byte --> convert this to 8 chars base64 Have logic on client side to handle retires for inserts in case of using a salt algorithm Read Logic GET/james --> web server --> select on table (URL/SURL(index) Scaling --------------------- master + backups (writes pushed to backups (read replicas) POST --> Main Web server --> master node GET --> REVERSE PROXY (Load balancer (layer 7 looks at request data at TCP level) satellite servers --> reads/ backup database servers and master node syncs writes to read replica (eventual consistency and lag comes into place here) If you cannot handle writes in master slave, then can revert to Sharding

Other DB Engines

XtraDB Fork of InnoDB default for mariaDB TILL 10.1 --------- Sqlite - embedded database over sqlite storage engine database locally stores (local data -- everyone is using it) B-tree(tried to use lsm as an extension) postgre like syntax supports FULL ACID does not support low level locking (but not needed since it is a local instance) concurrent reads and writes Web SQL in browsers use it ------------------ Aria - USED BY SYSTEM TABLES IN MARIADB crash safe like MyISAM designed specifically for Maria db A FORK of MySql ------------------- BERKELEY DB key value embedded database supports ACID transactions, locks, replications Used Memcachedb ------------- Level DB log structures merge tree (LSM) great for high insert and SSD no transactions all updates made as inserts Inspired by Google BigTable Good for histroy keeping level of files MemTable - also write to write ahead logs that write to memory level shifts as they reach size as levels grow large, they will be merged and flushed to disk --------------- RocksDB - forked levelDB transactional high performance, multi threaded compaction for write and read Myrocks FOR mysql MongoRocks for MongoDB

Atomicity

all queries in a transaction must succeed (atom cannot be split) if one query fails/ database goes down all prior queries in trans should be rollbacked fast rollback/slow commit all 100 queries/ successful queries should be rolled back even if database fails

What is the largest sql statement that you need to send to your database

as the query becomes larger, the no. of tcp packets become larger and all of these need to be separately processed and handled and need to be reorganzied when order is lost..

InnoDB

b + trees - indexes point to the PK and PK points the row secondary index another jump to the PK (great if only selecting PK AND SK) default for Mysql and MariaDB ACID compliant Foreign Keys row level locking tablaspaces row level locking spatial operations owned by oracle

Hands on - ACID Transactions

begin transaction isolation level repeatable read; select ..... now any select query within this transaction will enforce repeatable reads no matter what is happening in other transactions (snapshot is maintained)

Partitioning

break table down into smaller tables (collection of rows) called partitions (same schema) select name from customer_table where id = 701,00 will inly quwery the relevant table (customers_800k) horizontal partitioning -- by range or list splitting rows vertical partitioning -- large column blob you can store in slow access drive in its own table space

Partition type

by range dates/ id's (eg logdates in years or customerid from to) by list discrete values - ex state or zip codes by hash hash functions -- consistent hashing

Sharding Advantages/Disa

client aware which instance to hit - scalability in data and memory also security (uses can access certain shards) optimal and smaller indexes! Disadvanatges complex client transactions across a shards a problem rollbacks are hard schema changes are hard -- uniformity joins hard across databases cross query sharding

Primary key/Index Vs Secondary

clustered index based on primary key in heap, Primary key clustered in MySQL --> Index organized Table (IOT - MysQL) oracle PK is optional. Secondary Indexes is like a look up table for the table to jump on a set of row no./page no. no tables and then another jump is made to the final table (heap) --> so a lot of jumps!!

Transaction

collection of queries that are treated a single unit of work -- can change data or be read only . can be user defined or system defined

Create Index Concurrently - Avoid Blocking Prod DatBse Writes

create index concurrently g on grades(g);

key vs non key column indexes (index packing)

create index g_indx on students(g); explain (analyze,buffers) shared hit=20 means whatever we queried is cached how can we do a pure index only scan? Adding non-key indexes create index g_indx on students(g) include (id); finally the query planner will only execute a "index only scan backward] or Heap Fetches: 0 Buffers: Shared hit = 2323 read=602 (meaning had to go to i/o (disk) only 602 times which is always smaller if previous query is cached) -- > index still lives on disk! vacuum (verbos) table_name --> helps to update all pages and visibility map especially after inserts/update remember when disk is very big then index also has to live on disk!!

Preparing Postgres Partition

create table grades_parts (id serial not null, g int not null) partition by range(g); create table g0035 (like grades_parts including indexes) similiar create g3560 and g6080 and g80100 tables alter table grades_parts attach partition g0035 for values from (0) to (35) similarly create for all other tables.. populate partitions and create indexes -------------------------------------------- insert into grades_parts select * from grades_org; now can do select count(*) from g003f (individual partitions tables) create index ---------------------- create index grades_parts_idx on grades_parts(g); \d g0035; --> noticed that indexes created automatically on partition tables (same index replicated on partition tables) Querying and Checking the size of partitions size wont matter if hitting a relatively small index that can fit in memory of laptop -- since no io/memory bound on docker container to use the host memory (so no need to hit disk) explain analyze select count(*) from grades_parts where g = 30; --> index only scan on g0035_g_idx on g0025 grades_parts select * pg_relation_size(oid), relname from pg_class order by pg_relation_size(oid) desc; show ENABLE_PARTITION_PRUNING (make sure set to on)

Heap

data structure where the table is stored with all its pages one after the other traversing the heap is expensive while doing filter queries hence we need indexes to tell us what part of the heap to read,, (What pages of the heap do we need to pull)

Cant Always Encrypt

database queries are on plain text analysis, indexing and tuning applications must read data to process it. TLS termination layer 7 reverse proxy and Load Balancing needs to decrypt data to know how to orchestrate as an alternative people do layer 4 load balancing

Database Cursors

declare c cursor for select * in tablename fetch c ; -- fetches rows one by one you save memory in your client/ backend that establishes/ talks to the database. pull up a subset of memories on the client side and filter/sort on the cursor subset.. good for streaming to another connection cursor can be easily cancelled and pagination but not that easy since cursor is stateful cons: it is stateful and therefore not easily scalable - the cursor data is stored internally., natively in the transaction itself.. also long running transaction is another issue

Switching Databses engines with mysql

docker exec -it ms1 bash mysql -u root -pppassword create database test use test create table employees_myisam (id int primary key auto_increment, name text) engine = myisam show engines; underlying storage completely different accordinfgto storage

Database Security

docker run -e PGADMIN_DEFAULT_EMAIL="hussein" -e PGADMIN_DEFAULT_PASSWORD="password" -p 5555:80 --name pgadmin dpage/pgadmin4 to enable ssl connection on postgre container docker run -it pg bash (bash into container) apt-get update && apt-get install vim cd var/lib/posgresql/ cd data vim postgressql.conf ssl = on ssl_cert_file = 'cert.pem' ssl_key_file = 'private.pem' opessl req -x509 -newkey rsa:4096 -nodes -keyout private.pem -out cert.pem (do not encrypt/ password protect this file since postgres needs to read it) chmod 600 private.pem chown postgres private.pem exit docker stop pg docker start pg can still communicate unencrypted with the server..

Combining Databases iNDEXES FOR better Perfomance

explain analyze select c from test where a = 70 limit 2; --> will not build bitmap since we are only fetching 2 rows and there is associated overhead with this explain analyze select c from table where a = 100 and b = 100 follows bitmap index scan on a and b in parallel (bitmapAnd(merge) which is fast and finally bitmap heap scan on test explain analyze select c from table where a = 100 or b = 100 follows Same execution but uses BitmapOr instead of and ------------------------------------------------------- composite index create index on test(a,b); // composite index remember index is built left to write so you can scan values from left but now right explain analyze select c fom test where a = 70; will usea_and_b composite indexz! explain analyze select c fom test where b= 70; parallel seq scan! -- cannot order by right index (b) in comoisite index since table is scanned left to right explain analyze select c fom test where a= 80 b= 70; then great performance using composite index wont work fast on or though --> will execute a parallel seq scan in table

Indexing

explain analyze select id from employees where name = 'Zs' ; /d employees; parelle Scan on employees (table) --> full table scan since we are not using the pk (id) which index is based on. will do sequential scan in parallel with 2 workers create index employee_name on employees(name); bitmap heap scan (bitmap index scan on employees_name (on our created like '%ZA%' --> parallel sequential scan despite the name index because exact values cannot be found from index and the planner will not be able to use the index. Try to add as much info in index (multi column index) so we reduce IO to disk!

Understanding the SQL Qeury Planner and Optimizer with Explain.

explain select * from grades; Query plan Seq Scan on grades | cost = 0.0 (work the precedes fetching the first page (pre fetinching work) .. 13234 (estimation on time it thinks query will take) rows = 314234234234 (not accurate but approx no. of rows it will fetch) width (sum of all bytes for the cols) select * from grades order by name; ---> Gather Merge Workers planned: 2 --> Sort (cost=.. ... Parellel Seq sCAN ON GRADES/ the table ( .. ... ) other things you might see: Seq scan on grades (explain select col from table) Index scan using id_idx on grades --> select * from table where id = (only one jump to heap to retrieve all other block data)

Best Practices working with REST And Databases

have different owners doing the ddl and dml operations.. create script with a different user and different user for application consumer (app-user) and different and set u pa different pool of connections for each route of application - ex read only/ update etc

Advantages of partitioning

improves query performance when accessing a single partition sequential scan vs scattered index scan --> seq scan might be better if yo are going to retrieve many rows and smaller partition makes this decision easier. easy bulk loading (attach partition) can also make csv to be the storage engine for a given table can use those tables to attach to a master table archive old data that is barely used to old/cheap/slow storage

Index scan vs Index only scan

index scan --> scan index then i/o request to heap on disk index only scan - package non key indexes (include (col)) to package col detail in index itself so that query executor only fetches from index (much smaller than Heap and therefore faster!) remember - tradeoff with increasing the size of the index!

Eventual Consistency

nosql databases began to emerge and implement this Consistency - in data and reads Consistency in Data --> pictures and picture_likes (normalized view of data consistent amongst fact tables/views) defined by user referential integrity (foreign keys) --> having cascading set on foreign keys) ensured by atomicity and isolation tenents ------------------------------------------ consistency in reads --> updates made to table should reflect in other reads in the database However when you have many reads/nodes then this concept becomes much harder to implement if a trans commits a change will a new trans. immediately see this? Both sql AND NONsql databases suffer from this when introducing horizontal scaling and caching! To get around this in nosql databases - the concept of eventual consistency has come about.

Homomorphic Encryption

perform arithmetic operations on encrypted data no need to decrypt can query a database that is encrypted client application does not look at data and is routed to end elient.. layer 7 proxies does not have to terminate tls anymore so can route traffic =without needing to decrypt databases can index and optimize without decrypting data.. END TO END ENCRYPTION GOOD FOR ASYNC USE CASES but too slow for commercial use yet..

Bloom Filters (prevents necessary expensive query to db)

post /username?q=Ali ---> Express Server (64 bit bloom filter sets bit) using conversion function Hash(name) % 64 = 2 (then set second bit with 2) and finally write to database.. basically saves the cases where a bit is not set and we can guarantee that the username does not exist in the DB and do not have to query it. If the bit is set (then username might be there, so query the DB!)

MongoDB Wire Protocol and Wireshark

require("sslkeylog").HookAll() and then add this config of wireshark tls --> pre master-secret log filename --> allows wireshark to decrypt the keys protocol MONGO WIRE will see extensible message format Request: extensible message format --> you will see the toArray, next() queries infromation in these logs (mongo wire protocol) all the other queries even db.collection() and client.db("thunderbolt") and also .find() will not do anything since these are all client side logic and the mongo client is a lazy evaluator Request : qUERY Response: Reply [from server] will all take place client side and regardless if we have any eval/ forced eval expressions such as .next() --> means there is significant overhead in establishing connections and we should pool as much,

Dead Lock

rolls back the transaction that enters/ update a shared lock value last.. insert into a table with constrained PK -- insert will not write because (because the value will be in locked state).. however if the other client rollbacks the insert on the same OK, we saw the PK will succeed..

Database System Design - Twitter System Design

specs post tweer 140 chars follow people home timeline post tweet arch --------------- client --> /post Server (HTTP/REST) -----> insert can add complexity such as message queue and retries --> technically update failed but have another process that posts tweet to the queue and have another process [kafka] that picks messages from the queue and writes to the database and does the retries (adds more complexity and another hop) though it ensures that fails to the backend are handled in the server/ backend logic by introducing the mq and distributed event store another consideration => pooling (pre heated tcp connections between client and DB) sqlite --> parallel thread where acting of posting a tweet is posted to sql lite database (locally on client side) in case of failure of post request to server. can add auto retry or tell user to retry Load Balancer between client and server(s) can be done on the client side double tcp(http protocol) will obtain a list from hardcoding into the device (AWARNESSS GIVEN TO THE CLIENT SIDE) ---------------------------------------- Introduction of the Load Balancer (layer 7 most of the times) establish tcp connection with load balancer (encrypted data) sent to load balancer and then lb BEHAVES A REVERSE PROXY AND MAKES THE REQUEST ON BEHALF OF THE CLIENT (DISTRIBUTING THEM TO DIFFERENT SERVERS/INSTANCES) http 1 - only one request per tcp connection at a given time HTTP 2 - one tcp connection that you shove multiple streams multiple channels in ----------------------------------------- Layer 4 Load balancing transparent proxy - any request is always gone to the same server b- certificates will be at server and certificate will be stored a part of handshake (tls handshake directly with backend) --------------------------------------------------------- Feature 2 - follow people profile table (id, name, pic, bio) following table (SID/DID) - source/follower and destination/following (vice versa) - b balanced tree using post gres (quick inserts) and 2 way index on sid and did list followers/ follwing either two seperate count queries using sid/ pid or have dome client side (database client executing the query) handle this - build A LOCAL client

DEMO - replication with postgres

spin up two postgre instances with docker make one master another standby connect standby to master make master aware of standby ------------------ docker run --nme p-master -p 5432:5432 -v /Users/jvpuri241/Desktop/Db_eng/rep/p-master_data: /var/lib/postgressql/data -e POSTGRES_PASSWORD=postgres-d postgres docker run --nme p-master -p 5433:5432 -v /Users/jvpuri241/Desktop/Db_eng/rep/p-standby_data: /var/lib/postgressql/data -e POSTGRES_PASSWORD=postgres-d postgres docker stop pmaster pstandby mv pstandby_data pstandby_data_bk cp -R pmaster_data pstandby+data docker start pmaster pstandby vim pga_hba.conf host replication postgres all md5 - allows master to connect to standby in replication mode IDEALLY DO NOT USE ADMIN USER (POSTGRES) now go to stanfby folder vim postgresql.conf *edit the file* primary_conninfo = 'application_name=standby1 host=jvpuri port=5432 user=postgres password=postgres' in the same standby_data dire: touch standby.signal -- have to create this instance to make it read only, standby instance go back to pmaster_data vim postgresql.conf *edit the file* synchronous_standby_names = 'first 2 (standby1)' ideally in multiple standby nodes it will be something like 'first 2 (standby1, standby2, standby3)' or use any instead of first -- basically can edit the sync nature of your app. ------ docker start pmaster pstandby docker logs pstandby you will see started sreaming 'WAL' from primary at ..... (write ahead lock)

Synchronization vs async

sync re -- a write transaction to the master will be blocked until written to the backup/standby nodes -- options to wait for x no. of nodes or priority (or just wait for 1) --> a lot of flexibility async rep once you write to the master then done, no need to wait for transaction to commit - so a write transaction is successful if written to the master, then async writes are applied to the back up nodes (background process that writes backup nodes might take up space so not necessarily improves throughput)

Cons of Partitioning

updates that moves rows from a partition to the other (slow or fails sometimes) inefficient queries that could accidentally scan all the partitions that might be slower on s seq scan on master table schema changes can be slow sometimes (could be handled by dictionary though)

When to Shard a Database

use partitioning on the same database as much as your load can handle.. But if you have too many tcp requests to server, then can have master slave architecture (satellite databases are full backups and reads can be distributed amongst them).. Write will still go to the master instance though. To deal with a lot of writes can segregate the data on two master nodes by region and then have some type of async balance between them. Finally, Sharding -- partitions distributed between multiple instances and logic is implemented in client (becomes complex).. most importantly ACID properties are heavily sacrificed for things like eventual consistency (add tight coupling also)

How Databases optimizers Decide to uSE inDEXES

various Cases: Very large no., of rows returned from index -- will just scan whole table and condition (one table returning fewer rows) --> will go directly to the table scan and fetch results and then filter out those that are not common with the other index values table satistics --> analyze in psql gather schema statistics in oracle example. here the stats come clutch to force use certain index during search (or omit) according to powerful stats (editable by engineer) on table - basically numerical metadata values on the table data)

phantom read

when you see row updated/inserted in another transaction begin transaction isolation level serializable; postgres ensures no phantom reads in other isolation levels also MVCC - MULTI VERSION CONCURRENCY CONTROL