41–50 of 76 posts
Friendly reminder that this post is published on the PlanetScale blog, a company that sells a database SaaS. Beware of the bias. I personally would argue with every single point this article makes, except scalability.
Earlier quoted context omitted.
To be fair, relational algebra is hard. That being said: going back to 1970 to read the original "A Relational Model of Data for Large Shared Data Banks" by Codd (the paper which started the relational-database + normalization model) is incredibly useful. But yeah, all of this debate about "how data should be modeled" was the same in 1970 as it is today. ----- SQL doesn't quite fit 100% into the relational model, but…
I had a specific class on relational algebra in uni and it is up there with algorithm design and analysis in the realm of classes that actually provided me the most long term value. Relational algebra is a lot easier once you start viewing it as relational algebra - a declarative expression of intent that can be manipulated and re-expressed similar to other purely mathematical statements. Then, when performance tunin…
From this perspective, the study of relations is more about solving these NP-hard problems. For example, coloring a graph. You can solve things "locally", such as:
Texas | New Mexico | Oklahoma
-----------------------------
Red | Blue | Green
Red | Blue | Yellow
Red | Green | Blue
Red | Green | Yellow
Red | Yellow | Blue
Red | Yellow | Green
Blue | Red | Green
Blue | Red | Yellow
Blue | Green | Red
Blue | Green | Yellow
...
(etc. etc. for all other valid combinations)
We can see that "solving" a 4-coloring problem is as simple as a cross-join over all these relations (!!!). That is: Texas_NewMexico_Okalhoma cross-join Texas_Oklahoma_Louisiana cross-join Louisiana_Mississippi_Arkansas cross-join ...
We can see immediately that "Texas_NewMexico_Okalhoma cross-join Texas_Oklahoma_Louisiana" will create a new relation, a "Texas_NewMexico_Oklahoma_Louisiana" table, though with the information from just two earlier tables, this new table is "incomplete" so to speak (you'll need to join this table with many other Texas, NewMexico, Oklahoma, and Louisiana tables to ensure global consistency).
We can imagine a big 48-wide table consisting of the 48 states of USA as the final solution to a graph coloring problem. Successful calculation of this massive table will enumerate all possible solutions (!!) of the graph coloring problem.
----------------
Somehow, I find it more obvious to understand relations from this perspective. If anything, learning about constraint programming has made my relational algebra better (and as a result, has made my database skills probably better too)
Its also a funny corner where if you "study relations" hard enough, you eventually reach NP complete problems. 3SAT is easily written in the form of database relations after all :-) (But using a database as a 3SAT solver is probably a bad idea, despite being mathematically connected)
Earlier quoted context omitted.
Is there a wrapper to treat a database like its an sqlite database (a file) to simplify to the same level as Sqlite? This may at least simplify some issues. Doesn't matter whether its backed by a local database running in a container or by a remote one.
The biggest database unification effort tends to be focused around ODBC compatibility from what I've seen - and the operations laid out by ODBC are quite trivial and easy to comprehend I think.
ODBC might enable the creation of such a cli wrapper in a database-agnostic way. An authentication library retrieving the correct credentials based on the local file path may make the local use as seamless as with Sqlite. To get the best from both worlds.
As a developer, I have to say that sqlite gives me the best experience. Everything else pales in comparison. Create a database? sqlite3 mydata.db Where is the database? In the current directory How is it structured on disk? It's a single file How do I backup the DB? cp mydata.db /my/backups/mydata.db Do I have to install a server? No Do I have to configure anything? No During setup and deployment I usually I dabble a…
Unless you need to do something crazy, like run two nodes of your application. But only Google needs to do that, right?
You could be a single-node 4-core $5/month VPS instance and run into this issue. SQLite requires "exclusive" access to a table to handle writes (meaning when writing, no other thread can be reading the table). Especially if your transactions start to become complex.
In contrast, MySQL and PostgreSQL allow for simultaneous reads while writes are occurring.
As a developer, I have to say that sqlite gives me the best experience. Everything else pales in comparison. Create a database? sqlite3 mydata.db Where is the database? In the current directory How is it structured on disk? It's a single file How do I backup the DB? cp mydata.db /my/backups/mydata.db Do I have to install a server? No Do I have to configure anything? No During setup and deployment I usually I dabble a…
Also, it support more stuff (like stored procedures!) that I miss a lot on sqlite.
My only complain with firebird is that is not easy to embed into iso/android or it will my main db.
Earlier quoted context omitted.
> It was my impression that everyone picked (and still picks) MySQL, MongoDB, and Firebase _because_ they were the easiest to use. I've found that to be the case, except for enterprise development, which has different concerns than how quickly code gets written to use a database.
Are you saying that enterprise developers pick MySQL, MongoDB, and Firebase for other reasons?
Earlier quoted context omitted.
Are you saying that enterprise developers pick MySQL, MongoDB, and Firebase for other reasons?
I think GP was pulling apart "everyone".
Earlier quoted context omitted.
calibre uses sqlite and it doesn't support hosting the db on a network drive. https://manual.calibre-ebook.com/faq.html#i-am-getting-error... i guess that's a bad -dev- user experience?
You can certainly host it on a network drive if the network filesystem has the right features and behaviour. The same goes for a local filesystem. Sqlite has certain features it requires the filesystem to have. That is independent of how that filesystem stores the data physically.
Which network filesystems are those?
Earlier quoted context omitted.
Unless you need to do something crazy, like run two nodes of your application. But only Google needs to do that, right?
You don't even need to reach two nodes before SQLite becomes grossly inadequate. Even on a single node: SQLite's paradigm of global locks leads to poor performance when multiple threads write to the same table. You could be a single-node 4-core $5/month VPS instance and run into this issue. SQLite requires "exclusive" access to a table to handle writes (meaning when writing, no other thread can be reading the table).…
It is not as if a 4-core machine can do 4 times the DB work if you only allow it.
Memory access, disk access .. they all have their specific behaviour when you try to do things simultaneously. In the worst case, things will just get serialized on a lower level, even if multiple CPU cores send and/or request data simultaneously.