PostgreSQL Finally Lands Support For "REINDEX CONCURRENTLY"

[phoronix]

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?page=news_item&px=PostgreSQL-Reindex-Concurrently

[waxhead]

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!

[uid313]

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/Comparison_of_relational_database_management_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/amd64/postgresql-client-11/filelist

[bpetty]

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.

[boxie]

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!

[oiaohm]

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/Todo#Features_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.

[bpetty]

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.

[oiaohm]

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

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/scaling-connections-postgresql-using-connection-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 .

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-id/flat/[email protected]

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.