SQLite 3.24 Released With UPSERT Support

[phoronix]

Phoronix: SQLite 3.24 Released With UPSERT Support

SQLite 3.24.0 was quietly released earlier this week as the newest version of this widely-used embedded database library...

http://www.phoronix.com/scan.php?pag...eleased-UPSERT

[Chewi]

I usually regret using SQLite every time I try it but this is a nice feature. The PostgreSQL version has made our bulk data loads a hell of a lot faster.

[Aleksei]

I usually regret using SQLite every time I try it but this is a nice feature. The PostgreSQL version has made our bulk data loads a hell of a lot faster.

Can you share any details? I regularly think of switching to SQLite from Derby for embedded DB in a desktop app, what spooks me about SQLite is dynamic typing. Sounds like it could go messy very quickly with one dirty data load.

[Chewi]

Can you share any details? I regularly think of switching to SQLite from Derby for embedded DB in a desktop app, what spooks me about SQLite is dynamic typing. Sounds like it could go messy very quickly with one dirty data load.

Nothing too concrete, I just found the performance lacking, but for a desktop app, it's probably fine.

[ksec]

If you use it together with an layer on top like bedrockdb , it is very powerful.

[GrayShade]

Nothing too concrete, I just found the performance lacking, but for a desktop app, it's probably fine.

With write-heavy workloads you'll want to enable the WAL mode. Otherwise, in some cases (e.g. with https://gitea.io/ ) I've seen SQLite performing better than Postgres.

The.. flexible typing didn't bother me too much.

[hansg]

Ok, there are two things I don't understand:

1. PostgreSQL does not have UPSERT. It only has INSERT ... ON CONFLICT, and that only since 9.5, if memory serves.

2. SQLite already had INSERT OR REPLACE for a very long time, which is functionally identical.

So what gives?

[andrei_me]

I usually regret using SQLite every time I try it but this is a nice feature. The PostgreSQL version has made our bulk data loads a hell of a lot faster.

I've found out that in some scenarios at work that using :

INSERT INTO XXX
SELECT YYY FROM ZZZ
ON CONFLICT DO NOTHING


Is slower than:

INSERT INTO XXX
SELECT YYY FROM ZZZ
WHERE NOT EXISTS (
SELECT 1 FROM XXX
WHERE XXX.primary_key = ZZZ.primary_key
)

[ssokolow]

I've found out that in some scenarios at work that using :

INSERT INTO XXX
SELECT YYY FROM ZZZ
ON CONFLICT DO NOTHING


Is slower than:

INSERT INTO XXX
SELECT YYY FROM ZZZ
WHERE NOT EXISTS (
SELECT 1 FROM XXX
WHERE XXX.primary_key = ZZZ.primary_key
)

Have you tried prefixing both with EXPLAIN and EXPLAIN QUERY PLAN to see what's going on?

[andrei_me]

Have you tried prefixing both with EXPLAIN and EXPLAIN QUERY PLAN to see what's going on?

Forgot to check, but I assume it is related to how many records have to be discarded on the ON CONFLICT part, avoiding the primary key conflict using the WHERE instead of DO NOTHING was faster