EP20: How SQL works beneath the hood. Additionally…

On this publication, we’ll cowl the next subjects:

• How is a SQL assertion executed within the database?

• How does Netflix scale push messaging for hundreds of thousands of gadgets?

• Amazon DynamoDB

• HTTP/1 → HTTP/2 → HTTP/3 (YouTube video)

• What’s API?

The diagram beneath exhibits the method. Observe that the architectures for various databases are completely different, the diagram demonstrates some widespread designs.

Step 1 – A SQL assertion is shipped to the database through a transport layer protocol (e.g.TCP).

Step 2 – The SQL assertion is shipped to the command parser, the place it goes by syntactic and semantic evaluation, and a question tree is generated afterward.

Step 3 – The question tree is shipped to the optimizer. The optimizer creates an execution plan. 

Step 4 – The execution plan is shipped to the executor. The executor retrieves information from the execution.

Step 5 – Entry strategies present the information fetching logic required for execution, retrieving information from the storage engine. 

Step 6 – Entry strategies resolve whether or not the SQL assertion is read-only. If the question is read-only (SELECT assertion), it’s handed to the buffer supervisor for additional processing. The buffer supervisor appears for the information within the cache or information recordsdata.

Step 7 – If the assertion is an UPDATE or INSERT, it’s handed to the transaction supervisor for additional processing.

Step 8 – Throughout a transaction, the information is in lock mode. That is assured by the lock supervisor. It additionally ensures the transaction’s ACID properties. 

This put up attracts from an article revealed on Netflix’s engineering weblog. Right here’s my understanding of how the net streaming large’s system works.

Necessities & scale

– 220 million customers

– Close to real-time

–  Backend programs have to ship notifications to varied purchasers

– Supported purchasers: iOS, Android, sensible TVs, Roku, Amazon FireStick, net browser

The lifetime of a push notification

1. Push notification occasions are triggered by the clock, consumer actions, or by programs.

2. Occasions are despatched to the occasion administration engine.

3. The occasion administration engine listens to particular occasions and ahead occasions to completely different queues. The queues are populated by priority-based occasion forwarding guidelines.

4. The “occasion priority-based processing cluster” processes occasions and generates push notifications information for gadgets.

5. A Cassandra database is used to retailer the notification information.

6. A push notification is shipped to outbound messaging programs.

7. For Android, FCM is used to ship push notifications. For Apple gadgets, APNs are used. For net, TV, and different streaming gadgets, Netflix’s homegrown resolution known as ‘Zuul Push’ is used.

Amazon DynamoDB powered 89 million requests per second throughout prime days. How does it do this?

The diagram beneath is my try to attract the newest structure of DynamoDB based mostly on the 2022 paper. I additionally attempt to clarify why sure selections had been made. Please go away a remark in the event you spot any errors.

YouTube Video:

Thanks for making it this far!

If you wish to study extra about System Design, try our books:

Paperback version: https://geni.us/XxCd

Digital version: https://bit.ly/3lg41jK