Also just want to add an oracle developers post that I find hilarious...
This was a gem. Really makes you appreciate not having to work on such a codebase.
The problem is, SQLite does have a few limitations that it doesn't make obvious: some of the more complex SQL syntax that Postgres supports are missing, but more importantly, it can only ever do one write or read to the database predictably. (Don't believe me? Try this: https://gist.github.com/mrnugget/0eda3b2b53a70fa4a894)
I know that SQLite supports concurrent writes and reads technically, but it predictably leads to 'database is locked' issues. In practice you have to use it in a fashion that it only ever does one thing, read, or write. No multiple writes. No single write and multiple readers, no single write and single reader. Just read — or write. (Edit: WAL doesn't help with this either.)
Or you're in a world of hurt. I'd love to be able to move to Postgres just to get away from that.
If you fix this (which is easy), then WAL works just fine. Note that transactions, as is standard, span from BEGIN/COMMIT to COMMIT/ROLLBACK.
See e.g. https://docs.sqlalchemy.org/en/latest/dialects/sqlite.html#t... and https://docs.sqlalchemy.org/en/latest/dialects/sqlite.html#p...
[1] Really it's more like "encoded": https://docs.python.org/3/library/sqlite3.html#controlling-t... -- you already need to know the issue to understand that this section specifies the root cause.
How many drivers have you actually tested? There are 2 comments from 2016 on that thread saying other drivers do work fine.
I don't know...it's recently added upsert compatible with Postgres syntax as well as support for window functions.
Additionally, sqlite has a sophisticated json extension.
> it can only ever do one write or read to the database predictably
WAL mode addresses this, allowing an arbitrary number of readers with a writer.
For concurrent applications just use a dedicated write thread and enqueue writes...your example is contrived. Connection management, transaction scope management, etc, can all work in your favor. Writes occur in a few milliseconds, meaning you only need to hold the exclusive lock very briefly in most situations.
More information here: http://charlesleifer.com/blog/going-fast-with-sqlite-and-pyt...
Not all writes are couple milliseconds. I regularly have transactions that take 15< seconds, and it's not because the data size that's being input is large, it's just that the SQL is recursive and complex enough to warrant recursion (storing graph data).
https://www.sqlite.org/src/tree?ci=754ad35cd26da361
https://www.sqlite.org/src/artifact/b98409c486d6f028
Haven't yet played around with it myself, and I tend to expect it'd be more experimental than something to use in production.
Do you mean your application actually throws the error "database is locked," and dies without simply waiting for the lock to release? This behavior definitely goes against the grain of the documentation. From the SQLite website:
"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." --- https://www.sqlite.org/whentouse.html
I have to wonder, with the others here, whether the bug is in the driver, or just anywhere but SQLite. With such fragility it would have blown up by now. SQLite is ubiquitous: the iPhone, iTunes, Android, Chrome, Windows 10, McAfee, the Redhat Package Manager (RPM), commercial flight software, and many other pieces of software --- https://www.sqlite.org/famous.html
An example for Oracle would be RAC. This is a mature shared data architecture to provide high availability and horizontal server scaling to a DB. Deploying this is a fairly standard Oracle DBA activity. Getting something similar with open source tools is a big lift and permanent support commitment and will be for a while. Meanwhile these three commercial DBs have provided continuity for decades for features like this.
There is a lot of that assumption. And then, there is the vendor lock-in licensing terms that commercial DB vendors utilize, in that database switching cost is just prohibitively high and impractical.
Like, imagine you have a few hundred terabytes of data you need to keep in-sync globally, between multiple datacenters, with many servers in each DC.
By cross-datacenter I mean datacenters far away from each other, in different geo regions.
It still doesn't have proper upsert or JSON columns, so PostgreSQL wins on small usability features, but SQL Server is a serious workhorse when it comes to large scale operational and analytical systems with unmatched performance. PG barely has parallel queries or decent connection management and is a decade away from catching up to SQL server in these features.
Upsert (MERGE) was added in SQL Server 2008 (although it's chock full of known bugs): https://www.mssqltips.com/sqlservertip/3074/use-caution-with...
JSON was added in SQL Server 2016, although indexing it is still tricky: https://docs.microsoft.com/en-us/sql/relational-databases/js...
Is the integrated encryption comparable to pgcrypto?
Stretch and Polybase are much more limited to Azure storage and Hadoop systems with deep integration into T-SQL, and scale-out using clustering.
I am a little torn about this - on the one hand it strikes me as laziness that all these Windows developers will just pick SQL Server because it is so convenient (good integration with Visual Studio, drivers are present by default in .Net Framework). On the other hand, in terms of performance and reliability, I cannot say a single bad word about SQL Server. Another thing I like is that one can use accounts from an Active Directory domain as database accounts, so you do not have to duplicate group structures and such, if you use elaborate database permissions.
I have come to like T-SQL, which integrates some procedural features into SQL, so SQL Server does not need a separate language for writing stored procedures, triggers, and so forth. Where T-SQL is not enough, one can use basically any .Net language, but there are some restrictions to it, and I have never used this feature.
Postgres offers many features SQL Server lacks, e.g. the array of data types available in Postgres is amazing, or the fact that it has enums. SQL Server has some features Postgres lacks, for example the Service Broker, which is very interesting, but apparently non-trivial to use correctly.
Embedded R in SQL Server as well.
Postgres is my default database choice, or SQLite if it's small, but SQL Server is a solid, high performance product and I'll gladly run it for the most demanding workloads.
https://en.wikipedia.org/wiki/Oracle_Database#Releases_and_v...