Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

SuperSQLite: SQLite library for Python (2018)

github.com

Post reply on HN
Print view View original

31–40 of 78 posts

Re: SuperSQLite: SQLite library for Python (2018)

#31

Post by smush »

firebacon wrote: post #12
johnisgood wrote: post #4

> SQLite is extremely reliable and durable for large amounts of data (up to 140TB). It is considered one of the most well-engineered and well-tested software solutions today, with 711x more test code than implementation code. I keep seeing this statement. Why is it considered one of the most well-engineered software?

About the well-tested bit: Per it's own documentation, SQLite has a massive test suite. [*Not all of] the test suite is actually open source though, so the overlap between commenters selling you on how well tested SQLite is and those that have seen how the sausage is made is probably [close to] zero. However, pointing this or any of the other practical shortcomings of SQLite out on hacker news is blasphemy and will i…

What are some other shortcomings of SQLite? I'm genuinely curious even though the below probably sounds like some text from the ~Rust~ Sqlite evangelism strike force.

With the default settings which I semi-affectionately refer to as paranoid mode, an untuned database can start to have worse performance after getting 500,000+ records going. Then things like indexes, RediSQL, and WAL mode start being more necessary rather than just best-practices.

But if you set your pragmas correctly and so on, SQLite scales up just fine. I haven't done a large scale Sqlite base simply due to caution around needing to use a 'real' database in production like SQL server or Postgres, Maria etc. Sqlite is excellent for ephemeral databases to be created, seeded with test data, run tests against, and deleted in repeatable automated testing.

Based on the link to the Expensify article, it sounds like Sqlite can scale up even better than Sql server under some circumstances.

But I have barely tried using it in production because of that aforementioned caution. What are some pitfalls to watch out for?

Re: SuperSQLite: SQLite library for Python (2018)

#32

Post by pdw »

dest wrote: post #9

Interesting pick from one of the links in the article: "SQLite has fantastic write performance as well. By default SQLite uses database-level locking (minimal concurrency), and there is an “out of the box” option to enable WAL mode to get fantastic read concurrency — as shown by this test. But lesser known is that there is a branch of SQLite that has page locking, which enables for fantastic concurrent write performa…

I knew Oracle offered something like that (implemented by grafting the SQLite frontend/VM on top of Berkeley DB). But it'd be really cool to see page-level locking appear in standard SQLite version.

Re: SuperSQLite: SQLite library for Python (2018)

#33

Post by nbevans »

dest wrote: post #9

Interesting pick from one of the links in the article: "SQLite has fantastic write performance as well. By default SQLite uses database-level locking (minimal concurrency), and there is an “out of the box” option to enable WAL mode to get fantastic read concurrency — as shown by this test. But lesser known is that there is a branch of SQLite that has page locking, which enables for fantastic concurrent write performa…

In case anyone is curious "BEGIN CONCURRENT" is what is being discussed here (page level locks). There is also a WAL2 mode which is basically the WAL mode operating in a A/B hot swap mode - to facilitate checkpointing without holding up writes.

SQLite has progressed a lot in the last few years. It is no longer advisable to mock it over SQLITE_BUSY et al; you will come unstuck on the Internet very quickly ;-)

Re: SuperSQLite: SQLite library for Python (2018)

#35

Post by niea_11 »

Gys wrote: post #2

Its supports 'Remote Streaming over HTTP' without explaining what that means. Maybe someone here knows?

From what i saw in the source code, it's a feature that lets sqlite open a database file stored on a web server. There is a class in the code HTTPVFS [1] that proxies sqlite's filesystem operations to http requests.

[1]:https://github.com/plasticityai/supersqlite/blob/01e54bbb829...

Re: SuperSQLite: SQLite library for Python (2018)

#36

Post by firebacon »

smush wrote: post #31
firebacon wrote: post #12

Earlier quoted context omitted.

About the well-tested bit: Per it's own documentation, SQLite has a massive test suite. [*Not all of] the test suite is actually open source though, so the overlap between commenters selling you on how well tested SQLite is and those that have seen how the sausage is made is probably [close to] zero. However, pointing this or any of the other practical shortcomings of SQLite out on hacker news is blasphemy and will i…

What are some other shortcomings of SQLite? I'm genuinely curious even though the below probably sounds like some text from the ~Rust~ Sqlite evangelism strike force. With the default settings which I semi-affectionately refer to as paranoid mode, an untuned database can start to have worse performance after getting 500,000+ records going. Then things like indexes, RediSQL, and WAL mode start being more necessary rat…

It's hard to point those out without coming across as a hater, and because they might seem so obvious. Trying...:

With the way transactions and durability work in SQLite, a normal setup will not do more than on the order of ~100 transactions per second without adding additional layers of complexity. Which is completely fine for it's intended usecase of being an embedded, lightweight database and of course is not due to some bad engineering decisions, but due to inherent tradeoffs in how transactions are handled.

So basically it's all fine until you try to use SQLite for something for which it is not a good fit, like a large volume of inserts. Other solutions exist that are conceptually better equipped for this usecase out of the box (Postgres/MySQL/Elastic/etc). Not a shortcoming in SQLite per-se, but rather a practical shortcoming that comes up when you try to use it for the wrong job.

Case in point is TFA which takes the theoretical SQLite database size (140TB) limit and runs with claiming that is the useful limit on how much data you can store in SQLite. LOL! That value is when SQLite page IDs start overflowing and not an estimate on how much data can be usefully handled in a single SQLite database.

SQLite is, by the nature of what it is, not a good fit for big data volumes. Try loading 100TB of data into SQLite and try to run even a single query. Even assuming that you have a hyper-fast SSD, a single query will take days to complete! But it still get's mentioned as a potential solution for that problem every so often. In this post even...

So you could consider those practical shortcomings or ill-advised usage. It depends on the definition/perspective I guess. At any rate, SQLite, even if generally being excellent, is not the panacea as which it sometimes gets sold here on HN. No database is, there are just too many tradeoffs involved.

Re: SuperSQLite: SQLite library for Python (2018)

#37

Post by nerdponx »

tony wrote: post #26

Any features in this library you'd like to see standard library's sqlite3 [1]? Maybe a PEP [2, python enhancement proposal] could do it. [1] https://docs.python.org/3/library/sqlite3.html [2] https://www.python.org/dev/peps/pep-0001/

In the standard library? Probably nothing.

But if someone published an alternative APSW wheel with JSON1, ICU, and FTS5 enabled, I'd be happy.

Re: SuperSQLite: SQLite library for Python (2018)

#39

Post by marktangotango »

dest wrote: post #9

Interesting pick from one of the links in the article: "SQLite has fantastic write performance as well. By default SQLite uses database-level locking (minimal concurrency), and there is an “out of the box” option to enable WAL mode to get fantastic read concurrency — as shown by this test. But lesser known is that there is a branch of SQLite that has page locking, which enables for fantastic concurrent write performa…

When you have a write-heavy workload with multiple servers that need to write concurrently to a shared database (backend to a website), you would probably want to choose something that has a client-server model instead like PostgreSQL

It's easy to get really stellar concurrent performance out of SQLite using a many reader, single writer model (ie many threads, single process). In testing we did it easily surpassed Postgres.

Re: SuperSQLite: SQLite library for Python (2018)

#40

Post by dragonsh »

tony wrote: post #26

Any features in this library you'd like to see standard library's sqlite3 [1]? Maybe a PEP [2, python enhancement proposal] could do it. [1] https://docs.python.org/3/library/sqlite3.html [2] https://www.python.org/dev/peps/pep-0001/

May be just create a new PEP to replace sqlite3 with it.
Post reply on HN