Database of Databases | Hacker News Reader

Database of Databases

dbdb.io

156 pointskiyanwang61 comments

Open articleView on HN

Well I guess that answers it, SQLite is the database of all databases.

> https://github.com/cmu-db/dbdb.io/blob/master/dbdb/settings....

Archived page (5days ago): https://web.archive.org/web/20200911140514/https://dbdb.io/

Are these websites just completely unable to handle a 1000 simultaneous requests?

They should have used a JSON file as the backend.

This is more in the range of 10s of requests per second at this position on HN

Someone should make a database of caches or CDNs. Then combine knowledge! /s

There you go https://www.splitgraph.com

Writing a simple query engine can be accomplished with a trie and a hash map to be a bit like Dynamo DB and SQL is straightforward to parse (but a pain to implement).

https://github.com/samsquire/hash-db https://github.com/samsquire/sql-database

These are my very straightforward codebases where I've been experimenting with SQL parsing (and execution) and dynamo db style querying.

You need an efficient range operator to implement a database.

Damn, I don't think I've read about most of the site top database... Maybe I should try to use something different than PostgreSQL, at least in some side projects.

Any good learning resources for implementing your own DBMS, and DB internals? (not DB theory, not SQL, not intro).

You might find useful guidance here: http://www.aosabook.org/en/

https://github.com/rqlite/rqlite

https://github.com/rqlite/rqlite/blob/master/DOC/DESIGN.md

I built a distributed database using Raft and SQLite, and I put a lot of thought into its design and implementation -- to make it as simple (but not simplistic) and clear as possible. One goal was clear separation between the consensus layer, the database access, and the HTTP API. I think it's a good resource to study (even if I do say so myself).

CMU DB Group has all their lectures on youtube. Checkout 15445 and 15721 https://www.youtube.com/channel/UCHnBsf2rH-K7pn09rb3qvkA

Yes, the Postgresql source and associated docs. It's a well-structured code base that is easy enough to dive into, if you don't try to understand the whole thing at once. I'm slowly reading it in bits and pieces.

Here's a Github mirror so you can browse around: https://github.com/postgres/postgres

Note that the offical repo is at git.postgresql.org.

Should mention the newer OReilly "Database Internals" book here: https://www.oreilly.com/library/view/database-internals/9781...

https://github.com/speakeasy-engine/contentdb

https://dbdb.io/browse?foreign-keys=supported&query-interfac...

x Supports Foreign Keys

x Supports Stored Procedures

x Supports SQL Query Interface

No Postgresql?

But does it include itself?

Also reminded me of the awesome list of awesome lists:

https://github.com/sindresorhus/awesome

They're using SQLite which is single threaded and single user by design. So this website will not be able to service that much traffic.

Should be fine for reads. Sqlite.org is dynamic, pulling from sqlite data for ~20% of the pages, and it does fine with HN piling on. The single threaded would be an issue for writes, but I don't see why they would be doing writes for a page view. See https://www.sqlite.org/whentouse.html

Also, caching layers.

Presumably a service uses SQLite to simplify their ops. So long as the caching layer is equally simple to maintain, then SQLite continues to make sense to me

Their repo shows the "DummyCache" (no caching) as the default for the Python/Django setup: https://github.com/cmu-db/dbdb.io/blob/master/dbdb/settings....

I wonder if that's how it is in production.

For a mostly static site you would want to cache "over" Django, probably in a caching proxy or CDN. Of course there are a lot of details, such as many CDNs will always go to the origin on a edge miss instead of locating another copy in the CDN. Of course running a caching nginx on the same box as the Django is probably way more performant than caching "under" Django

It's intermittently working now. Shows the server as Apache 2.4.18 / Ubuntu. Apache can page cache, but I assume they aren't using it. I don't see any typical CDN headers either.

Also, somewhat odd, they are bounding the browser-side cache to 10 minutes:

Date: Wed, 16 Sep 2020 18:19:13 GMT

Expires: Wed, 16 Sep 2020 18:29:14 GMT

For this kind of content site, I'd convert the Django site to a static site with django-bakery [1] and stick it on Netlify.

I suppose there would be a bunch of rewriting to do searching (lunr.js maybe) and filtering client side.

Buuuuuut, at this point, just turning on caching would be easier.

[1]: https://github.com/datadesk/django-bakery

Well it looks down now.

Well it clearly wasn't :) Site is down already

There's several layers. It's Python/Django. I don't think we know what the issue really is. They could be, for example, logging visits to Sqlite. Or other issues unrelated to Sqlite.

I've survived a few HN front pages on SQlite + $5 DO droplet. Don't disparage my primary tech stack like this ^_^

The traffic from a HN frontpage is relatively low per second tbh.

SQLite isn't single threaded - and it can support multiple readers very well.

The limitation with SQLite is that it doesn't support concurrent writes well - it needs to take a lock on the entire database to perform a write.

Writes are crazy fast (a few ms) so this often isn't a problem - but it does mean you wouldn't want to use it to build a site that has many people writing at once, like Hacker News for example.

For a site that has low (or no) writes, SQLite works really well even at a much larger scale - 100s of requests a second.

Enabling the write ahead log makes sqlite behaviour much, much better under (write) contention:

> PRAGMA journal_mode=WAL;

Yeah I was getting occasional "database is locked" read errors on a project that had crons writing to the SQLite file which I solved by switching on WAL mode.

It still doesn't let you have concurrent writes but it does mean that reads won't error if a write is going on at the same time.

There is zero reason a carefully engineered application utilizing SQLite cannot completely saturate the IO capabilities of the host it resides on.

Going even further, there are no competing technologies (i.e. hosted SQL solutions) which, when running in single node/instance mode, are competitive with the performance of well-tuned SQLite.

PRAGMA journal_mode=WAL makes all the difference in the universe.

.. and it does appear to be very slow at the moment.

The amount of databases I'd like to play with and explore greatly outweighs the amount of time or reasons I have to explore databases.

Did anyone else pronounce this like dibby-dibby-yo?

So this isn’t in fact instances of databases inside databases? Disappointed! Surely possible with SQLite, right? Has it been done?

I was really hoping it was a SQL interface for querying data stored in multiple databases; something like Presto.

Thought it was some groundbreaking new database technology....

Speaking of,

What would an interesting version of "database of databases" look like?

The closest I can think of would be the "Data Governance" space. Tools like Collibra: https://www.collibra.com/

Screenshot that makes it easier to understand: https://www.collibra.com/wp-content/uploads/Blog-DataLineage...

Apparently SQLite. Who would have thought?

TerminusHub is a database filled with databases (for collaboration): https://terminusdb.com/hub/

Probably CosmosDB or MarkLogic.

This is basically what we're building at Splitgraph [0]. We're calling it a "data delivery network." You connect to one SQL endpoint and can query (and join across) 40k+ different datasets. It's built on Postgres, and as far as your SQL client is concerned, it's talking to a Postgres database with 40k tables in it. Right now we forward queries to public data portals, but eventually you'll be able to connect live data sources to the DDN without writing any code. We want it to be as easy as configuring Cloudflare; you just upload a set of read-only credentials in the web UI and we take care of the rest. For more private use cases, we're planning to offer private deployments to AWS/GCP/Azure.

Technically, this is database virtualization, which isn't really a new concept. We're implementing it as a database proxy, using PgBouncer instances to intercept queries and route them to Splitgraph engines. Within a Splitgraph engine (which is Postgres + some custom code), each "table" is either a "mounted" live database via a foreign database wrapper (FDW), or part of a point-in-time, versioned database snapshot called a "data image" that you can build with sgr.

[0] https://www.splitgraph.com

The lulz thing about the site is that you can't query it with SQL.

Let me download the sqlite database directly! Andy?

pretty cool. learned alot of new databases beyond the big 6.

this site could benefit greatly from not running on a database and being statically generated. even the browse section could just be a vuejs app powered by a json collection.

So this is being reposted over and over again. Not saying it's not useful, but still..

Website is down.

HN Hug of death.

upvote!

Reminds me of my favorite Wikipedia article: https://en.wikipedia.org/wiki/List_of_lists_of_lists

My god that is a rabbit hole. Thanks for this! (But there goes my evening..)

https://youtu.be/vvp0r1T5ers

This article itself is a list of lists, so it contains itself.

Found the Russell Paradox.

This article reminded me of this site:

http://howfuckedismydatabase.com/

I don't understand the goal of this site. :|

It's satire written by a disgruntled engineer :P

Amazon affiliate links

What's not to understand?

The answers for access and Oracle are both brilliant and true.

Reply on news.ycombinator.com