[Study | OMEP] week 4: Celebrating SQLite’s 23rd birthday: why we love it

영어 발표 스터디를 하고 있다. (참고로 OMEP: Oh My English Present 는 사실 내가 그냥 지은 이름이다.)

내 발표들을 다시 정리해두면 좋을 것 같아, 블로그에도 업로드한다. (참고로, week3 가 없는 이유는 발표가 격주로 돌아가면서 한 주 발표를 건너뛰었기 때문이다!)

이번 주제는 twitter에서 본 흥미로운 스레드에 대해 공유한 것인데, NoSQL에 대해 깊게 생각해본적이 없었지만 이번 발표를 통해 생각해보게 되었다. 하지만 영어로 발표하기에는 너무 어려운 내용이었어서 아쉬움이 많이 남는다.

Celebrating SQLite’s 23rd birthday: why we love it

Intro

SQLite was realesed on August 17, 2000. so this year is its 23rd birthday!

SQLite Blessing

Before start this presentation..

When I started summarizing this, I thought it would be easy but it got so hard that I added a lot of references. so if you're interested, you can follow the links to find out more!

Actually, even at the time of this presentation, I don't really understand the hard stuff, but I think it would be fun for other people to talk about it, so I'm just try to share it with you :)

Hot Issue

1. The developer's VPN Tailscale is running with SQLite and PaaS platform
2. fly.io has acquired litestream, a standalone disaster recovery tool for SQLite.

Litestream - Streaming SQLite Replication

3. Cloudflare announce D1, a SQLite-based SQL DB

 

 

More recently..

4. Cloudflare has announced that D1 (SQL DB) is in public beta

Cloudflare D1 · Cloudflare D1 docs

 

D1: open beta is here

5. fly.io's LiteFS Cloud is now Generally Available!

LiteFS Cloud is Generally Available

 

6. this week is SQLite-on-Edge host, turso database’s Launch Week!

tweet from here

cf. SQLite and edge

Why SQLite is so great for the edge

 

What the heck is the edge anyway?

Despite being relatively simpler than other SQL DBs (such as MySQL), SQLite is one of the most popular and enduringly popular DBs, especially among conservative (or Traditional) developers those who have a deep understanding and use of existing technologies rather than unproven new ones. This is because it's simple structure makes it easy to use and operate.

• SQLite is more of a JSON/protobuf than a DBMS like MySQL: A single .sqlite file contains all the data and is read and written using the sqlite lib. File format+library is all you need.
• Behind the simple structure is a relatively lack of functionality. In particular, the write performance is limited, making it difficult to scale.

SQLite trends

So why is SQLite, with its obvious performance limitations, making a comeback in the cloud era?

Two trends stand out:

1. The thirst for simplicity in an increasingly complex cloud ecosystem
2. Emerging edge computing platforms (Cloudflare Workers, Deno Deploy)

1. Complex cloud ecosystem

Advances in software like Kubernetes are exciting, but they also come with a high degree of complexity.

Most services are not Google, why adopt their complex technology? is a common cry.

 

While it is inevitable that the difficulty of server development and operation will increase as the expectations of service users increase, it is important to be cautious about adopting trendy technologies without much thought.

For example, NoSQL technology is said to be more scalable than SQL DB, but most services do not encounter the limitations of SQL DB. (cf. it's not impossible to scale out of RDB like this: https://changhoi.kim/posts/database/rdb-scaling/ )

SQLite has added a lot of features over the years (e.g., concurrency, cte), and even low performance isn't that low given today's hardware advances. For many services, SQLite performance is likely to be sufficient for their needs.

 

| Ref for this:

Tailscale

https://github.com/tailscale/sqlite

expensify

Scaling SQLite to 4M QPS on a Single Server (EC2 vs Bare Metal)

These large services also revert to SQLite.

Using SQLite instead of MySQL/PostgreSQL may seem a bit stubborn, but for "conservative" developers who value the simplicity of the server stack and prioritize a deep understanding of the tools they use, SQLite is an attractive choice.

The SQLite-ecosystem is also full of interesting developments (litestream, dqlite, rqlite, etc.).

2. edge computing

Developer-friendly edge computing services like Cloudflare Worker and Deno Deploy are emerging, showing the promise of a truly serverless platform.

Cloudflare Workers · Cloudflare Workers docs

Deno Deploy | Deno

Running application logic without location constraints is an exciting development. But unlike compute, data is not yet location agnostic.

For most services, the DBs they use are tied to a specific location, which in turn removes a lot of the appeal of edge computing for services that need DBs.

Globally distributed DB is a really hard technical problem. AWS/GCP/Azure all have great technical solutions (Dynamo/Spanner/Cosmos).

Amazon DynamoDB 글로벌 테이블은 선택된 AWS 리전 간에 Amazon DynamoDB 테이블을 자동으로 복제

https://www.usenix.org/system/files/conference/osdi12/osdi12-final-16.pdf?mob=1 

https://salemal.medium.com/summary-of-spanner-googles-globally-distributed-database-f66a6ea25a81

Distribute data globally with Azure Cosmos DB

This is where SQLite comes in. As mentioned earlier, SQLite stores all data in a single file, and copying that file globally is easily solved by a CDN.

The technical teams at both Cloudflare/Fly platforms seem to have seen the potential of this property and have taken on the challenge of a SQLite-based global DB solution.

2-1. Things to think about

The question is how to support global writes.

Based on some of the comments I've seen, it's possible to move away from the traditional model of having all data on a single DB server and come up with an entirely new model that leverages the power of SQLite to have a DB file per user/account. The devil is in the details, but it's a technically interesting idea.

 

 

 

other Ref

https://use.expensify.com/blog/scaling-sqlite-to-4m-qps-on-a-single-server

https://tailscale.com/blog/database-for-2022/

https://fly.io/blog/all-in-on-sqlite-litestream/

https://news.hada.io/topic?id=6557