1. 69
  1.  

  2. 9

    This is an amazingly interesting read–some bits I knew about, but a lot I didn’t. Thank you so much for the writeup!

    1. 4

      I like unlogged tables, but you need to remember that they are not replicated to standby servers :(

      1. 3

        Another even shadier trick is to have temporary tables on unjournaled filesystems or just tmpfs.

        If they are really just for intermediate computations - there is no end to end loss in reliability, as on a rare reboot you start fresh and the computation can be restarted from scratch.

      2. 3

        Really nice article and well written, but I think the title is a bit misleading given that most of the discussed techniques depend on PostgreSQL features.

        I feel that a better title would be:

        Some PostgreSQL Tricks of an Application DBA

        1. 2

          That got really interesting. Also I feel stupid for not knowing about the WITH statement, will have to research if mariadb on debian is already old enough to support it.

          1. 2

            Good article. The acronym “DML” was new to me. It stands for Data Manipulation Language and basically refers to the subset of SQL commands that modify stored data. This contrasts with Data Query Language (DQL).

            1. 1

              Just want to point out, in SQL Server

              A CTE must be followed by a single SELECT, INSERT, UPDATE, or DELETE statement

              so which databases let you run multiple selects on a single CTE? Because that sounds great. Postgres docs talk about “one query” but I’ll just try it myself I guess

              1. 1

                I’m using PostgreSQL in the examples, and I’ve worked with Oracle in the past, and I don’t know of such a limit on neither.

                1. 1

                  I believe it’s a sql server limitation, note however that can define multiple ctes and a cte can reference a previously defined cte. This is the most common workaround, but sometimes you still need a temp table (rarely for me at least)

                  Just looked at the example again and that is what the author is doing, there’s 2 ctes there.

                  1. 1

                    Do you happen to know if you can do updates or deletes inside a CTE in SQL Server? I hadn’t ever seen that done until this article. Wasn’t sure if it was just a postgres thing.

                    1. 2

                      I don’t have this type of experience with SQLServer. Maybe someone else here can provide an answer.