4 ms·

A friend of mine is a Microsoft DBA for mid-sized companies and was proclaiming how you can't do anything serious with Postgres. He said he was shocked to disco

A friend of mine is a Microsoft DBA for mid-sized companies and was proclaiming how you can't do anything serious with Postgres. He said he was shocked to discover it didn't even have a query planner. Leaving mocking him to one side for a moment - is there any plausibility to his broader claim that MSSQL can handle things at a scale where Postgres would be a poor choice? My gut instinct is that this is nonsense but I'm not a DBA by a long stretch.

fabian2k 3y ago
Postgres has a query planner, I mean this entire post is about improvements to it. So I think there either was some miscommunication or your friend doesn't know anything about Postgres. There are very large Postges databases that seem to work fine, so Postgres can certainly scale. But SQL Server also has some features that Postgres doesn't, and if those are important for you it might work better for your use case. They are in the end different databases with different strengths and weaknesses.
danpalmer 3y ago
Yes there is. If what you need is a database that will do pretty much anything well enough, then MSSQL and Oracle are going to manage it. They solve this by throwing money and hardware (more money) at the problem until it works. There's some clever stuff happening in there of course, but fundamentally they've just had much more engineering work over a long time. They can scale out more than Postgres can reasonably do. That said, Postgres is catching up, and arguably MySQL/MariaDB has always had a good story here. Scale-out options are improving all the time. The landscape has also changed in other ways too, now you can easily have a multi-terabyte Postgres cluster on a small number of machines serving large traffic volumes, and then put your "big data" into a more specialist database. The old world of shoving everything on MSSQL/Oracle may be a bit old-school.
PartiallyTyped 3y ago
AWS' Aurora seems to be handling things pretty well tbh and is meant as a drop-in replacement for Postgresql and MySQL.
- fuy 3y ago
  Aurora is using native Postgres planner, I believe, probably with some minor enhancements.
  - CubsFan1060 3y ago
    There is this extension which gives you some flexibility: https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Optimize.overview.html https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide...
mmcgaha 3y ago
I have used both for OLTP and data warehousing and both are fine. I started writing this to say that I would recommend my company move to Postgres if it weren't for vendor provided applications that required SQL Server but then I realized how much work it would be for me to replace the things MS includes like reporting services, integration services, jobs, AD integration, service broker (notify/listen lacks message types). I don't use analysis services any more but when I did that would have been hard to replace too. This stuff is how they get you. I have no clue how long it would take me to replace all of this but it would not be a good ROI to spend a year replacing what you already have.
SigmundA 3y ago
I develop for MSSQL extensively and PG is missing some things that can be a little surprising. He might have been referring to the fact that PG doesn't cache query plans or have way to lock them in. PG replans for every statement unless you manually do prepared statements and that only works per connection. MSSQL will cache plans and reuse them and has done this for a very long time. Consequently the planner can take more time planning. Also MSSQL has hints and you can even lock a plan. PG really needs hints, optimizers are great and all but sometimes I know better and I want to make sure it listens to me. Also PG has no true clustered indexes all tables are heaps which is something most use all the time in MSSQL, usually your primary key is also set as the clustered index so that the table IS the index and any lookup on the key has no indirection. Interesting SQLite is the opposite tables always have clustered index whether you make one or not, MSSQL give you the choice for heap or index organized tables.
- sgarland 3y ago
  > Also PG has no true clustered indexes all tables are heaps which is something most use all the time in MSSQL You can rewrite a table in PG to be clustered [0], but it a. locks the table b. is a one-shot, so you have to periodically redo it > Interesting SQLite is the opposite tables always have clustered index whether you make one or not AFAICT [1] you have to explicitly make a table `WITHOUT ROWID` to get a Clustered Index in SQLite. > usually your primary key is also set as the clustered index so that the table IS the index and any lookup on the key has no indirection. The main problem with this is there is a tendency by people unfamiliar with proper schema design (so, most) to use a UUID – usually v4 – as the PK. This causes no end of performance issues for RDBMS with and without clustered index, but since InnoDB also uses a clustered index, and MySQL is the most-installed RDBMS (modulo SQLite), it happens a lot. This isn't the fault of the RDBMS; clustered index has some great advantages, as you point out. But it's the reality of the current situation. [0]: https://www.postgresql.org/docs/current/sql-cluster.html https://www.postgresql.org/docs/current/sql-cluster.html [1]: https://www.sqlite.org/withoutrowid.html https://www.sqlite.org/withoutrowid.html
  - SigmundA 3y ago
    >You can rewrite a table in PG to be clustered [0], but it a. locks the table b. is a one-shot, so you have to periodically redo it That is not a true clustered index or index organized table, the table is still a heap and there is still a secondary index that is the b-tree. With a real clustered index the table itself is the b-tree there is no heap and no secondary index unless you have other indexed fields as obviously there can be only one clustered index. This may be a terminology thing Oracle calls them index organized tables while MSSQL calls them clustered indexes while PG uses clustered for something that is not actually an index. >AFAICT [1] you have to explicitly make a table `WITHOUT ROWID` to get a Clustered Index in SQLite. The Row ID IS the clustered index unless you specifically say it's one of your columns. In SQLite there are no heaps only b-tree indexes. >The main problem with this is there is a tendency by people unfamiliar with proper schema design (so, most) to use a UUID – usually v4 – as the PK. This causes no end of performance issues for RDBMS with and without clustered index, but since InnoDB also uses a clustered index, and MySQL is the most-installed RDBMS (modulo SQLite), it happens a lot. Since most of your key are indexed typically this is an issue either way since that will cause fragmentation in clustered or non clustered indexes on a random UUID requiring page splits etc.
thaumasiotes 3y ago
> He said he was shocked to discover it didn't even have a query planner. How did he discover that?

fabian2k 3y ago

Postgres has a query planner, I mean this entire post is about improvements to it. So I think there either was some miscommunication or your friend doesn't know anything about Postgres. There are very large Postges databases that seem to work fine, so Postgres can certainly scale. But SQL Server also has some features that Postgres doesn't, and if those are important for you it might work better for your use case. They are in the end different databases with different strengths and weaknesses.

danpalmer 3y ago

Yes there is. If what you need is a database that will do pretty much anything well enough, then MSSQL and Oracle are going to manage it. They solve this by throwing money and hardware (more money) at the problem until it works. There's some clever stuff happening in there of course, but fundamentally they've just had much more engineering work over a long time. They can scale out more than Postgres can reasonably do. That said, Postgres is catching up, and arguably MySQL/MariaDB has always had a good story here. Scale-out options are improving all the time. The landscape has also changed in other ways too, now you can easily have a multi-terabyte Postgres cluster on a small number of machines serving large traffic volumes, and then put your "big data" into a more specialist database. The old world of shoving everything on MSSQL/Oracle may be a bit old-school.

PartiallyTyped 3y ago

AWS' Aurora seems to be handling things pretty well tbh and is meant as a drop-in replacement for Postgresql and MySQL.

fuy 3y ago
Aurora is using native Postgres planner, I believe, probably with some minor enhancements.
- CubsFan1060 3y ago
  There is this extension which gives you some flexibility: https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Optimize.overview.html https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide...

mmcgaha 3y ago

I have used both for OLTP and data warehousing and both are fine. I started writing this to say that I would recommend my company move to Postgres if it weren't for vendor provided applications that required SQL Server but then I realized how much work it would be for me to replace the things MS includes like reporting services, integration services, jobs, AD integration, service broker (notify/listen lacks message types). I don't use analysis services any more but when I did that would have been hard to replace too. This stuff is how they get you. I have no clue how long it would take me to replace all of this but it would not be a good ROI to spend a year replacing what you already have.

SigmundA 3y ago

I develop for MSSQL extensively and PG is missing some things that can be a little surprising. He might have been referring to the fact that PG doesn't cache query plans or have way to lock them in. PG replans for every statement unless you manually do prepared statements and that only works per connection. MSSQL will cache plans and reuse them and has done this for a very long time. Consequently the planner can take more time planning. Also MSSQL has hints and you can even lock a plan. PG really needs hints, optimizers are great and all but sometimes I know better and I want to make sure it listens to me. Also PG has no true clustered indexes all tables are heaps which is something most use all the time in MSSQL, usually your primary key is also set as the clustered index so that the table IS the index and any lookup on the key has no indirection. Interesting SQLite is the opposite tables always have clustered index whether you make one or not, MSSQL give you the choice for heap or index organized tables.

sgarland 3y ago
> Also PG has no true clustered indexes all tables are heaps which is something most use all the time in MSSQL You can rewrite a table in PG to be clustered [0], but it a. locks the table b. is a one-shot, so you have to periodically redo it > Interesting SQLite is the opposite tables always have clustered index whether you make one or not AFAICT [1] you have to explicitly make a table `WITHOUT ROWID` to get a Clustered Index in SQLite. > usually your primary key is also set as the clustered index so that the table IS the index and any lookup on the key has no indirection. The main problem with this is there is a tendency by people unfamiliar with proper schema design (so, most) to use a UUID – usually v4 – as the PK. This causes no end of performance issues for RDBMS with and without clustered index, but since InnoDB also uses a clustered index, and MySQL is the most-installed RDBMS (modulo SQLite), it happens a lot. This isn't the fault of the RDBMS; clustered index has some great advantages, as you point out. But it's the reality of the current situation. [0]: https://www.postgresql.org/docs/current/sql-cluster.html https://www.postgresql.org/docs/current/sql-cluster.html [1]: https://www.sqlite.org/withoutrowid.html https://www.sqlite.org/withoutrowid.html
- SigmundA 3y ago
  >You can rewrite a table in PG to be clustered [0], but it a. locks the table b. is a one-shot, so you have to periodically redo it That is not a true clustered index or index organized table, the table is still a heap and there is still a secondary index that is the b-tree. With a real clustered index the table itself is the b-tree there is no heap and no secondary index unless you have other indexed fields as obviously there can be only one clustered index. This may be a terminology thing Oracle calls them index organized tables while MSSQL calls them clustered indexes while PG uses clustered for something that is not actually an index. >AFAICT [1] you have to explicitly make a table `WITHOUT ROWID` to get a Clustered Index in SQLite. The Row ID IS the clustered index unless you specifically say it's one of your columns. In SQLite there are no heaps only b-tree indexes. >The main problem with this is there is a tendency by people unfamiliar with proper schema design (so, most) to use a UUID – usually v4 – as the PK. This causes no end of performance issues for RDBMS with and without clustered index, but since InnoDB also uses a clustered index, and MySQL is the most-installed RDBMS (modulo SQLite), it happens a lot. Since most of your key are indexed typically this is an issue either way since that will cause fragmentation in clustered or non clustered indexes on a random UUID requiring page splits etc.

thaumasiotes 3y ago

> He said he was shocked to discover it didn't even have a query planner. How did he discover that?