Announcement

Collapse
No announcement yet.

SQLite 3.24 Released With UPSERT Support

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQLite 3.24 Released With UPSERT Support

    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...

    Phoronix, Linux Hardware Reviews, Linux hardware benchmarks, Linux server benchmarks, Linux benchmarking, Desktop Linux, Linux performance, Open Source graphics, Linux How To, Ubuntu benchmarks, Ubuntu hardware, Phoronix Test Suite

  • #2
    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.

    Comment


    • #3
      Originally posted by Chewi View Post
      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.

      Comment


      • #4
        Originally posted by Aleksei View Post

        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.

        Comment


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

          Comment


          • #6
            Originally posted by Chewi View Post

            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.

            Comment


            • #7
              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?

              Comment


              • #8
                Originally posted by Chewi View Post
                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
                )

                Comment


                • #9
                  Originally posted by andrei_me View Post

                  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?

                  Comment


                  • #10
                    Originally posted by ssokolow View Post

                    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

                    Comment

                    Working...
                    X