Database Review 2021
bytebase.com
Database Review 2021
1–10 of 68 posts
Re: Database Review 2021
#2My prediction: SQLite will keep gaining popularity.
Especially among pragmatic software builders who run their own business and do not work for the man. A demographic that I expect to grow.
Talking about SQLite: Is there any downside to partitioning an SQLite db into multiple files?
For example one of my systems has a table 'details' which is not vital for the system to work. It's just a nice to have, to have data in this table. And it is pretty big, growing fast.
When I copy the DB over to another system, I don't need that table. So it would be nice to have like primary.db and secondary.db. With 'details' in secondary.db. Any downside to this approach? Are JOINS slower across two files than across two tables in the same file?
Re: Database Review 2021
#3This is a review of companies making databases, not the databases themselves.
Re: Database Review 2021
#4My prediction: SQLite will keep gaining popularity. Especially among pragmatic software builders who run their own business and do not work for the man. A demographic that I expect to grow. Talking about SQLite: Is there any downside to partitioning an SQLite db into multiple files? For example one of my systems has a table 'details' which is not vital for the system to work. It's just a nice to have, to have data in…
My prediction: Database gatekeeping will continue into next year.
Lots of X is a toy database, Y is all you need for every use case, nobody really needs scalability, high-availability etc and above all else never use an ORM. Real engineers write SQL by hand.
Re: Database Review 2021
#5My prediction: SQLite will keep gaining popularity. Especially among pragmatic software builders who run their own business and do not work for the man. A demographic that I expect to grow. Talking about SQLite: Is there any downside to partitioning an SQLite db into multiple files? For example one of my systems has a table 'details' which is not vital for the system to work. It's just a nice to have, to have data in…
> Any downside to this approach?
None that I can think of unless you need foreign key constraints between both.
> Are JOINS slower across two files than across two tables in the same file?
I was recently debugging as slow JOIN with ATTACH'ed databases and the query plan looked the same as when both tables were in the same database. I don't think it makes any difference.
But in these situations, the solution is measuring and benchmarking for your use case.
Re: Database Review 2021
#6My prediction: SQLite will keep gaining popularity. Especially among pragmatic software builders who run their own business and do not work for the man. A demographic that I expect to grow. Talking about SQLite: Is there any downside to partitioning an SQLite db into multiple files? For example one of my systems has a table 'details' which is not vital for the system to work. It's just a nice to have, to have data in…
I use an app with 3 different SQLite databases, but since I never have to join tables from different files, I haven't found a downside.
Re: Database Review 2021
#7My prediction: SQLite will keep gaining popularity. Especially among pragmatic software builders who run their own business and do not work for the man. A demographic that I expect to grow. Talking about SQLite: Is there any downside to partitioning an SQLite db into multiple files? For example one of my systems has a table 'details' which is not vital for the system to work. It's just a nice to have, to have data in…
The very problem of SQLite: Single user only. Although SQLite does have WAL but it still doesn't allow you to do concurrent write unless you want to see file corruption.
This means SQLite is very much locked to things that works with one specific purpose and almost nothing else. Sure, you can be read-only, but you have to run alongside the app in the specific node, too.
Another problem (although without solving the single user mindset this wouldn't be a problem at all) is high availability. You want to make sure that your database won't get lost do you.
Things like Litestream [1] attempts to solve the SQLite backup problem it by continuously saving the database state and pack t up to S3-compatibles or file system but its just half the story. You want to make sure your operation not stopping. This is where HA comes in to save you from an emergency fixup when you are enjoying your holiday.
It doesn't mean that nobody tried to solve both these problems though. Ahem, introducing rqlite [2]. Although my own experience is not very great because the memory usage is quite high and does not fit my need (because the embedded device only has 512MB on it, and every byte counts, sorry), I guess that's the price to pay if you want to turn a non-multiuser, non-concurrently acccess database into one...Another honorable mention would be LiteFS [3] but I haven't used it yet so I have no say on it.
Re: Database Review 2021
#8My prediction: SQLite will keep gaining popularity. Especially among pragmatic software builders who run their own business and do not work for the man. A demographic that I expect to grow. Talking about SQLite: Is there any downside to partitioning an SQLite db into multiple files? For example one of my systems has a table 'details' which is not vital for the system to work. It's just a nice to have, to have data in…
> Especially among pragmatic software builders who run their own business and do not work for the man. A demographic that I expect to grow.
From the FAQ; the are lots of caveats (especially, the last).
> Situations Where A Client/Server RDBMS May Work Better
> Client/Server Applications
> If there are many client programs sending SQL to the same database over a network, then use a client/server database engine instead of SQLite. SQLite will work over a network filesystem, but because of the latency associated with most network filesystems, performance will not be great. Also, file locking logic is buggy in many network filesystem implementations (on both Unix and Windows). If file locking does not work correctly, two or more clients might try to modify the same part of the same database at the same time, resulting in corruption. Because this problem results from bugs in the underlying filesystem implementation, there is nothing SQLite can do to prevent it.
> A good rule of thumb is to avoid using SQLite in situations where the same database will be accessed directly (without an intervening application server) and simultaneously from many computers over a network.
> High-volume Websites
> SQLite will normally work fine as the database backend to a website. But if the website is write-intensive or is so busy that it requires multiple servers, then consider using an enterprise-class client/server database engine instead of SQLite.
> Very large datasets
> An SQLite database is limited in size to 281 terabytes (248 bytes, 256 tibibytes). And even if it could handle larger databases, SQLite stores the entire database in a single disk file and many filesystems limit the maximum size of files to something less than this. So if you are contemplating databases of this magnitude, you would do well to consider using a client/server database engine that spreads its content across multiple disk files, and perhaps across multiple volumes.
> High Concurrency
> SQLite supports an unlimited number of simultaneous readers, but it will only allow one writer at any instant in time. For many situations, this is not a problem. Writers queue up. Each application does its database work quickly and moves on, and no lock lasts for more than a few dozen milliseconds. But there are some applications that require more concurrency, and those applications may need to seek a different solution.
Re: Database Review 2021
#9My prediction: SQLite will keep gaining popularity. Especially among pragmatic software builders who run their own business and do not work for the man. A demographic that I expect to grow. Talking about SQLite: Is there any downside to partitioning an SQLite db into multiple files? For example one of my systems has a table 'details' which is not vital for the system to work. It's just a nice to have, to have data in…
The very problem of SQLite: Single user only. Although SQLite does have WAL but it still doesn't allow you to do concurrent write unless you want to see file corruption. This means SQLite is very much locked to things that works with one specific purpose and almost nothing else. Sure, you can be read-only, but you have to run alongside the app in the specific node, too. Another problem (although without solving the s…
Many of us here writing web apps for enterprises can use SQLite with WAL with no issues.
Number of concurrent users range from tens and rarely hit hundreds. SQLite can handle that kind of traffic without any issues.
Re: Database Review 2021
#10> It already seems to be an afterthought after Gigapipe and Firebolt debuts.
This is referring to Clickhouse Inc. and is way off the mark. If you're looking for hosted Clickhouse, why would a company whose CTO is the creator of Clickhouse be an "afterthought"? Their cloud product offers separation of storage and compute, but you still have an escape hatch to self-hosting if you need it (which you wouldn't have with Firebolt).
Clickhouse has only one major flaw, joins. If Clickhouse improves join's, it will become the standard open source OLAP database.