Announcement

Collapse
No announcement yet.

PostgreSQL Finally Lands Support For "REINDEX CONCURRENTLY"

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

  • PostgreSQL Finally Lands Support For "REINDEX CONCURRENTLY"

    Phoronix: PostgreSQL Finally Lands Support For "REINDEX CONCURRENTLY"

    It's been on the project's TODO list for more than one decade but finally support for the "REINDEX CONCURRENTLY" command was added today to the PostgreSQL database server...

    http://www.phoronix.com/scan.php?pag...x-Concurrently

  • #2
    Originally posted by phoronix View Post
    Phoronix: PostgreSQL Finally Lands Support For "REINDEX CONCURRENTLY"
    ...while waking up today it was a surprise to see...
    http://www.phoronix.com/scan.php?pag...x-Concurrently
    FAKE NEWS!!!!! I don't think you sleep at all Michael - "when waking up" .... yeah right!

    http://www.dirtcellar.net

    Comment


    • #3
      Here you can see how PostgreSQL compares to other databases in terms of functionality and SQL compliance.
      https://en.wikipedia.org/wiki/SQL_compliance
      https://en.wikipedia.org/wiki/Compar...gement_systems

      I like PostgreSQL but things I don't like about is that it has its own vanity license instead of a common, wide-spread, established open source license. Also that it pollutes the file system with executables such as clusterdb, createuser, createdb, dropdb, etc. Most commands are prefixed with pg_, but unfortunately not all. 😢
      https://packages.ubuntu.com/disco/am...nt-11/filelist

      Comment


      • #4
        uid313, agreed. I think early on someone was heavily influenced by Oracle (sadly). Example, and I could be wrong about this, but doesn't PostgreSQL still spawn a process for every connection? I think Oracle recently caved on that design philosophy because they were seeing the performance benefits MSSql had been enjoying for some time. It kind or reminds me of the old Apache vs NginX thing. I also hate how Oracle has been holding back MySQL... but that has been to PostgreSQL's benefit. PostgreSQL now has a much more mature locking system, was maybe the first to offer searchable JSON data types (that are also indexable!). Its interesting. I just wish that, at some level, it was architected more like MySQL.

        Comment


        • #5
          Originally posted by bpetty View Post
          uid313, agreed. I think early on someone was heavily influenced by Oracle (sadly). Example, and I could be wrong about this, but doesn't PostgreSQL still spawn a process for every connection? I think Oracle recently caved on that design philosophy because they were seeing the performance benefits MSSql had been enjoying for some time. It kind or reminds me of the old Apache vs NginX thing. I also hate how Oracle has been holding back MySQL... but that has been to PostgreSQL's benefit. PostgreSQL now has a much more mature locking system, was maybe the first to offer searchable JSON data types (that are also indexable!). Its interesting. I just wish that, at some level, it was architected more like MySQL.
          Having used Postgres for the last 5 years, going back to mysql feels... clunky but that is probably more me not being used to it any more

          I am also one of these people that don't really care how many files are put onto my filesystem - it's kind of the filesystems job to handle them - it's not fragile!

          Comment


          • #6
            Originally posted by bpetty View Post
            uid313, agreed. I think early on someone was heavily influenced by Oracle (sadly). Example, and I could be wrong about this, but doesn't PostgreSQL still spawn a process for every connection? I think Oracle recently caved on that design philosophy because they were seeing the performance benefits MSSql had been enjoying for some time. It kind or reminds me of the old Apache vs NginX thing. I also hate how Oracle has been holding back MySQL... but that has been to PostgreSQL's benefit. PostgreSQL now has a much more mature locking system, was maybe the first to offer searchable JSON data types (that are also indexable!). Its interesting. I just wish that, at some level, it was architected more like MySQL.
            Pays to read here.
            https://wiki.postgresql.org/wiki/Tod...We_Do_Not_Want

            Architecture like Mysql does not exactly fly. People note that Mysql is more fragile than PostgreSQL and some of this is the heavy usage of threading. Its simple to forget a thread crash can take complete process with it. Postgresql has been historically process heavy but this path is the less fragile path that a single process dies recovery can work. Targeted multi threading where it sane while keeping the multi process is the route Postgresql is on. Postgresql is going to look way different to mysql as postgresql is always going to be a mix of processes and threads going forwards.

            There comes a question what one is more important

            Also you have timeline badly, What Postgresql original name is gets forgotten "Postgres (Post Ingres)".
            https://en.wikipedia.org/wiki/Ingres_(database).
            Yes Ingres is older than Oracle db with first release in 1974

            https://en.wikipedia.org/wiki/Oracle_Database
            You can see here Oracle started in 1977.

            Ingres is started by NSF funding and Oracle is CIA funding. Guess what NSF and CIA have to work with each other at times due to being USA government agencies.

            http://bnrg.cs.berkeley.edu/~adj/cs262/Lec_8_27a.html
            Gets worse when you read history Oracle lead developer was reading all the white papers around Ingres and IBM databases.

            Basically both Oracle and PostgresSQL take functionality from older ingres design. Heck even Microsoft SQL server has some functionality from the Ingres design still in there.

            Interesting enough is mysql and relations is one of the few SQL servers who blood line does not lead back to Ingres by either direct code of Ingres in the program or white papers of Ingres. This kind of explains why you find so much similarity between different SQL servers in behaviours at times not define by standard then that behaviour does not exist on mysql and relations because those SQL servers can have the common blood line of Ingres showing through.


            Comment


            • #7
              oiaohm, thanks for the history lesson. I was not aware of Sybase's tie, and hence MS SQL Server's tie, to Ingres, so I found that interesting.
              On a different note, if you reread my statements you will see that I said nothing about threads. What I did reference, however, was the process model of NginX.

              You may want to read this:
              https://www.nginx.com/blog/inside-nginx-how-we-designed-for-performance-scale

              What I liked about MySQL, and not explicitly stated, was its ability to abstract away the database engine, for example: MyISAM and InnoDB.

              Comment


              • #8
                Originally posted by bpetty View Post
                This does not exactly work for a database when you have to have security.

                https://en.wikibooks.org/wiki/PostgreSQL/Architecture

                There is a lot in common with nginx design. The per connection process that postgresql has is more of a security thing. The Nginx where a worker can be processing multi different connections does make doing security trickier.

                Now the per connection to the database spinning up a new process is this exactly a problem.

                https://severalnines.com/blog/scalin...ection-pooling

                Interesting enough no. Connection pooling means clients using the same user login to database can use the same connection. Is now threading that connection.

                I would say from my point of view postgresql implementing like https://en.wikipedia.org/wiki/QUIC to allow connection pools to send independent request to be performed by the same database user down the same connection would be more beneficial than the nginx model. Of course QUIC is not exactly ideal as it mandates encrypted and there are many cases with databases where encryption is not required..

                The problem with postgresql is not that it spins a process per connection its more that per connection you cannot do parallel request effectively on a single connection and this is a tcp problem.

                Basically Nginx has worked around the TCP problem instead of fixing it. Grand-master playing chess against multi players is not the right model. The model you want to look at is front of house office person who is highly skilled. Yes these people can answer requests of their privilege level but they will be redirecting those with higher privilege back into a office/conference room out the back. Connection pooling solutions on databases do this job. Now if you are attempting to get 100 people though a single person door quickly you are going to have a problem this is the TCP problem with HTTP and Database.

                Adding 100 locations to answer those 100 people kind of works and is the nginx solution. Note I said kind of works. You still are resulting in needing to send authentication information multi times why quic has been invented for http open connection do stack of parallel request down 1 connection then have the process down the other end thread/multi process the heck out the requests and send results back when ready down the single connection.. We need the same thing with databases .

                Originally posted by bpetty View Post
                What I liked about MySQL, and not explicitly stated, was its ability to abstract away the database engine, for example: MyISAM and InnoDB.
                https://www.postgresql.org/message-i...p3.anarazel.de
                Plug-able storage in Postgresql is still a work in Progress. Postgresql plug-able storage will do most of the same jobs as the mysql database engine idea.

                https://wiki.postgresql.org/wiki/Future_of_storage
                This is also a very interesting read. Mysql has a lot of database engines that turn out to be the same as either the core postgresql engine or Foreign data wrappers(FWD)

                4 points of interest in Mysql from the postgresql side InnoDB, Archive, Write optimised(TokuDB, RocksDB) and memory. MyISAM is not interest from the postgresql side because core postgresql engine covers MyISAM.

                Comment

                Working...
                X