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).
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
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
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:
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.
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.
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.
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.
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).
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.
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.
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.
64 comments
[ 0.29 ms ] story [ 95.8 ms ] thread> https://github.com/cmu-db/dbdb.io/blob/master/dbdb/settings....
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.
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
I wonder if that's how it is in production.
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
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
The traffic from a HN frontpage is relatively low per second tbh.
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.
> PRAGMA journal_mode=WAL;
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.
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.
Speaking of,
What would an interesting version of "database of databases" look like?
Screenshot that makes it easier to understand: https://www.collibra.com/wp-content/uploads/Blog-DataLineage...
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
http://howfuckedismydatabase.com/
The answers for access and Oracle are both brilliant and true.
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).
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.
https://github.com/sindresorhus/awesome
Let me download the sqlite database directly! Andy?
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.
x Supports Foreign Keys
x Supports Stored Procedures
x Supports SQL Query Interface
No Postgresql?