SQLite: Past, Present, and Future • Hacker News Board

Re: SQLite: Past, Present, and Future

#31

Post by ac2u » Thu, Sep 01, 2022, 4:25 PM UTC

electroly wrote: post #20

RedShift1 wrote: post #4

Earlier quoted context omitted.

SQLite is always going to win in that category just from the fact that there are less layers of code to be worked through to execute a query.

As long as you turn it into a throughput race instead of a latency race, PostgreSQL can definitely win. SQLite has a primitive query builder and a limited selection of query execution steps to choose from. For instance, all joins in SQLite are inner loop joins. It can't do hash or merge joins. It can't do GIN or columnstore indexes. If a query needs those things, PostgreSQL can provide them and can beat SQLite.

out of interest, what columnstore indexes are available to postgres? Would be happy to find out that I'm missing something.

I know citus can provide columnar tables but I can't find columnar indexes for regular row-based tables in their docs. (use case of keeping an OLTP table but wanting to speed up a tiny subset of queries)

Closest thing I could find was Swarm64 for columnar indexes but it doesn't seem to be available anymore.

Re: SQLite: Past, Present, and Future

#32

Post by samatman » Thu, Sep 01, 2022, 4:25 PM UTC

ledgerdev wrote: post #18

samatman wrote: post #8

Earlier quoted context omitted.

Postgres obviously. Sorry, just thought I'd buck the trend and assume a very write-heavy workload with like 64 cores. If you don't have significant write contention, SQLite every time.

Here's sqlite doing 100 million inserts in 33 seconds which should fit into nearly every workload, though it is batched. https://avi.im/blag/2021/fast-sqlite-inserts/ So write contention from multiple connections is what you're talking about, versus a single process using sqlite?

That's correct, I meant the many cores to allude to many processes.

Re: SQLite: Past, Present, and Future

#33

Post by polyrand » Thu, Sep 01, 2022, 4:32 PM UTC

ergocoder wrote: post #29

simonw wrote: post #27

Earlier quoted context omitted.

Is it the SQL dialect there lacking or is it the built-in functions? I agree that SQLite default functionality is very thin compared to PostgreSQL - especially with respect to things like date manipulation - but you can extend it with more SQL functions (and table-valued functions) very easily.

Depends on what easily means. Sqlite can't do custom format date parsing and regex extract. How do we extend something like this? If we go beyond a simple function to window function, I imagine it would be even harder. At this point, we nlmight as well use postgres.

Adding user-defined functions to SQLite is not difficult, and the mechanism is quite flexible. You can create extensions and load them when you create the SQLite connection to have the functions available in queries. I wrote a blog post explaining how to do that using Rust, and the example is precisely a `regex_extract` function [0].

If you need them, you also have a "stdlib" implemented for Go [1] and a pretty extensive collection of extensions [2]

[0]: https://ricardoanderegg.com/posts/extending-sqlite-with-rust...

[1]: https://github.com/multiprocessio/go-sqlite3-stdlib

[2]: https://github.com/nalgeon/sqlean

Re: SQLite: Past, Present, and Future

#34

Post by [deleted] » Thu, Sep 01, 2022, 4:33 PM UTC

manimino wrote: post #15

TFA appears to be about adapting SQLite for OLAP workloads. I do not understand the rationale. Why try to adapt a row-based storage system for OLAP? Why not just use a column store?

[deleted]

Re: SQLite: Past, Present, and Future

#35

Post by bob1029 » Thu, Sep 01, 2022, 4:33 PM UTC

rafale wrote: post #2

SQLite vs Postgres for a local database (on disk, not over the network): who wins? (Each in their most performance oriented configuration)

>most performance oriented configuration

I am 99% sure SQLite is going to win unless you actually care about data durability at power loss time. Even if you do, I feel I could defeat Postgres on equal terms if you permit me access to certain ring-buffer-style, micro-batching, inter-thread communication primitives.

Sqlite is not great at dealing with a gigantic wall of concurrent requests out of the box, but using a little bit of innovation in front of SQLite can solve this problem quite well. The key is resolve the write contention outside of the lock that is baked into the SQLite connection. Writing batches to SQLite on a single connection with WAL turned on and Sync set to normal is pretty much like operating at line speed with your IO subsystem.

Re: SQLite: Past, Present, and Future

#36

Post by ergocoder » Thu, Sep 01, 2022, 4:56 PM UTC

polyrand wrote: post #33

ergocoder wrote: post #29

Earlier quoted context omitted.

Depends on what easily means. Sqlite can't do custom format date parsing and regex extract. How do we extend something like this? If we go beyond a simple function to window function, I imagine it would be even harder. At this point, we nlmight as well use postgres.

Adding user-defined functions to SQLite is not difficult, and the mechanism is quite flexible. You can create extensions and load them when you create the SQLite connection to have the functions available in queries. I wrote a blog post explaining how to do that using Rust, and the example is precisely a `regex_extract` function [0]. If you need them, you also have a "stdlib" implemented for Go [1] and a pretty exten…

Wow this is helpful. I'm using sqlite for some of my projects and always bothered that some functions are missing. WITH RECURSIVE is too mind bending.

This seems like I can add a lot more functions to it, not just regex extract.

Came here to complain and learned something useful.

Re: SQLite: Past, Present, and Future

#37

Post by naikrovek » Thu, Sep 01, 2022, 4:57 PM UTC

my ipad won’t let me search through the PDF, but i couldn’t find where “SSB” was defined, if anywhere. i did not see it defined in the first paragraph, which is where it is first used.

everyone: not all of your readers are domain experts. omissions like this are infuriating.

Re: SQLite: Past, Present, and Future

#38

Post by ryanworl » Thu, Sep 01, 2022, 5:03 PM UTC

naikrovek wrote: post #37

my ipad won’t let me search through the PDF, but i couldn’t find where “SSB” was defined, if anywhere. i did not see it defined in the first paragraph, which is where it is first used. everyone: not all of your readers are domain experts. omissions like this are infuriating.

Star Schema Benchmark https://www.cs.umb.edu/~poneil/StarSchemaB.PDF

Re: SQLite: Past, Present, and Future

#39

Post by fuckstick » Thu, Sep 01, 2022, 5:30 PM UTC

RedShift1 wrote: post #28

sophacles wrote: post #19

Earlier quoted context omitted.

> just from the fact that there are less layers of code to be worked through This is not an invariant. I've seen be true, and I've seen it be false. Sometimes that extra code is just cruft yes. Other times though it is worth it to set up your data (or whatever) to take advantage of mechanical sympathies in hot paths, or filter the data before the expensive processing step, etc.

I'm not talking about extra code, I'm talking about _layers_ of code. With PostgreSQL you're still sending data over TCP/IP or a UNIX socket, and are copying things around in memory. Compare that to SQLite that runs in the memory space of the program, thus no need for copying and socket traffic. There's just less middlemen (middlepersons?) with SQLite that are unavoidable with PostgreSQL. So less layers = less interp…

> less interpreting/serialization/deserialization/copying/... = higher performance

Unfortunately for many database workloads you are overestimating the relative cost of this factor.

> even if the SQLite query engine is slightly less efficient than PostgreSQL

And this is absurd - the postgresql query engine isn't just "slightly" more efficient. It is tremendously more sophisticated. People using a SQL datastore as a glorified key-value store are not going to notice - which seems to be a large percentage of the sqlite install base. It's not really a fair comparison.

Re: SQLite: Past, Present, and Future

#40

Post by eatonphil » Thu, Sep 01, 2022, 5:37 PM UTC

simonw wrote: post #27

ergocoder wrote: post #11

Earlier quoted context omitted.

Functionality-wise, SQLite's dialect is really lacking...

Is it the SQL dialect there lacking or is it the built-in functions? I agree that SQLite default functionality is very thin compared to PostgreSQL - especially with respect to things like date manipulation - but you can extend it with more SQL functions (and table-valued functions) very easily.

I like SQLite (qualifying not for you, simonw, but for others). But I hate that I can't be lazy by using arrays in SQLite... because they don't exist. group_concat is a poor approximation.

Also, I genuinely dislike how loose SQLite is with allowed syntax. Probably it's preference. But even interactively I prefer to know immediately that I messed up a query. SQLite is so forgiving I've often wasted time trying to understand why my results are nonsense (because I typoed in the query and SQLite didn't fail the query).

But I also strongly dislike Python for that reason and I know where you stand there. Maybe SQLite/PostgreSQL is similar to the dynamic/static language preference divide.