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

71–78 of 78 posts

Re: SuperSQLite: SQLite library for Python (2018)

#71

Post by rogerbinns »

nerdponx wrote: post #69
rogerbinns wrote: post #44

Earlier quoted context omitted.

I'm the APSW author. The binary builds for Windows are distributed with those extensions all compiled in, although my doc needs some updating. It is also only a single flag for other platforms to include all extensions during compilation. What can I do?

First of all thanks for producing APSW and providing good documentation. The problem for my case is that if I want to distribute anything with a Sqlite+JSON1 workflow I also have to host and distribute a version of sqlite3 or apsw with those flags enabled. If that's already done on Windows then great, but many people I'm sure (myself included) would like a cross-platform solution. It would be great if we could use th…

I'm very happy to take this general approach, but someone else needs to figure out exactly what needs to be done to get a package that way. Discussion is welcomed on python-sqlite mailing list.

Re: SuperSQLite: SQLite library for Python (2018)

#72

Post by _pgmf »

patelajay285 wrote: post #67
_pgmf wrote: post #5

Looks like it just bundles a full build of the sqlite shared library, along with some kind of gross hacks. It bundles pysqlite2, which notably is missing a ton of features and improvements from upstream. I'll just compile my own, thanks...

Hi @coleifer, I'll disagree, I'm the author. I don't think they are gross hacks, just a more sensible default configuration. In fact, I used a lot of your advice from your blog post https://charlesleifer.com/blog/going-fast-with-sqlite-and-py... while making this library. Fair enough if you want to compile your own version, but there's a lot of people and Python devs who don't know how / won't go through the effort a…

The advice in my post that you linked can be supplemented by this:

http://charlesleifer.com/blog/compiling-sqlite-for-use-with-...

Describes several handy ways to compile sqlite for use with python (2 or 3), as a system-wide shared library or compiled directly into the python module. This can also be applied to sqlcipher, etc.

If you don't know how to compile sqlite, I'd argue that you have no business trying to use it's more advanced features. How can you tune or optimize something you don't understand?

Furthermore, your library is stale. Anyone using it is dependent on you to merge in upstream changes constantly. And based on what I saw, it's already well out of date.

Re: SuperSQLite: SQLite library for Python (2018)

#73

Post by rogerbinns »

nbevans wrote: post #50
alberth wrote: post #48

Earlier quoted context omitted.

Dumb questions: if BedRockDB has all of these huge benefits over using stock-SQLite, why hasn't SQLite merged in all of the changes into SQLite? Why does BedRockDB have to exist as a separate fork?

I think it is because the changes are, currently, considered esoteric and slightly experimental in the sense they want to reserve the right to make breaking changes to them. SQLite has a relatively slow cadence when it comes to things like this. Other reasons may be that they're still working on the tests to validate and support those features - as well as documentation. I am hopeful BEGIN CONCURRENT and WAL2 will ma…

> SQLite has a relatively slow cadence

They intend to support it till 2050 - https://sqlite.org/lts.html - which includes keeping file format and API backwards compatibility. That accounts for the more careful decision making since things added now have to be supported for another 30 years.

Re: SuperSQLite: SQLite library for Python (2018)

#74

Post by patelajay285 »

_pgmf wrote: post #72
patelajay285 wrote: post #67

Earlier quoted context omitted.

Hi @coleifer, I'll disagree, I'm the author. I don't think they are gross hacks, just a more sensible default configuration. In fact, I used a lot of your advice from your blog post https://charlesleifer.com/blog/going-fast-with-sqlite-and-py... while making this library. Fair enough if you want to compile your own version, but there's a lot of people and Python devs who don't know how / won't go through the effort a…

The advice in my post that you linked can be supplemented by this: http://charlesleifer.com/blog/compiling-sqlite-for-use-with-... Describes several handy ways to compile sqlite for use with python (2 or 3), as a system-wide shared library or compiled directly into the python module. This can also be applied to sqlcipher, etc. If you don't know how to compile sqlite, I'd argue that you have no business trying to use…

Yes, I've seen that post as well. Thanks! It's good advice.

>> If you don't know how to compile sqlite, I'd argue that you have no business trying to use it's more advanced features. How can you tune or optimize something you don't understand?

I'm not sure I agree with that, some people understand the advanced features but have a miserable time with compilers and compiling something like SQLite. There's a lot of great engineers with Python and SQL expertise that just don't know how to compile a C project. That might sound contradictory, but it's just something a lot of engineers still don't do day to day since people are using a lot of scripted languages (Node/Python/Ruby).

For example, there's probably a lot of people who want to use the JSON1 or FTS5 modules with SQLite in Python. That doesn't require advanced knowledge, but requires them to re-compile SQLite!

>> Furthermore, your library is stale. Anyone using it is dependent on you to merge in upstream changes constantly. And based on what I saw, it's already well out of date.

It is stale, I agree. But it's still a WIP. This was posted here early. My plan is to pull in changes from the upstream sqlite module with the latest CPython 2.7 tag and latest CPython 3 tag in the source.

Re: SuperSQLite: SQLite library for Python (2018)

#75

Post by NilsIRL »

patelajay285 wrote: post #68
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…

Author of SuperSQLite here. SQLite is under-utilized in my opinion! Because of the name, people seem to have the misconception that it can't be used for production workloads. It's true that it generally works well for embedded use cases, but it can be used for so much more and has an extremely simple codebase without a lot of bloat.

Would you recommand it on the server side as well?

For example to store user credentials.

Re: SuperSQLite: SQLite library for Python (2018)

#76

Post by agumonkey »

patelajay285 wrote: post #68
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…

Author of SuperSQLite here. SQLite is under-utilized in my opinion! Because of the name, people seem to have the misconception that it can't be used for production workloads. It's true that it generally works well for embedded use cases, but it can be used for so much more and has an extremely simple codebase without a lot of bloat.

Some guy on IRC told me they made a world scale DNS server work because of sqlite.

Re: SuperSQLite: SQLite library for Python (2018)

#77

Post by jgalt212 »

patelajay285 wrote: post #64
jgalt212 wrote: post #45

> SQLite is extremely reliable and durable for large amounts of data (up to 140TB). This has not been our experience. Our experience is that it sort of bogs down around 12GB in file size. linux box, ext3/4, spinning disk (not SSD)

Hi @jgalt, I'm the author of this library, and we've been using it to serve extremely large files over ~130GB at Plasticity (YCS17) for AI model data! Shoot me an email ajay@plasticityai.com and if you'd like to chat, but it should remain extremely responsive even at that file size.

Thanks may take you up on that after running some new tests. It was years ago we had performance issues (approx 3-4), so will see if time has healed all wounds.

Re: SuperSQLite: SQLite library for Python (2018)

#78

Post by patelajay285 »

NilsIRL wrote: post #75
patelajay285 wrote: post #68

Earlier quoted context omitted.

Author of SuperSQLite here. SQLite is under-utilized in my opinion! Because of the name, people seem to have the misconception that it can't be used for production workloads. It's true that it generally works well for embedded use cases, but it can be used for so much more and has an extremely simple codebase without a lot of bloat.

Would you recommand it on the server side as well? For example to store user credentials.

You could, plenty of sites do! It really depends on your write workload, but I would say it would scale pretty well for most small to medium size SaaS companies unless your product is Google scale.
Post reply on HN