6 ms·

Thanks to these engines and extensions, Postgres has become that rare tool for me where I have to ask "why use ___ instead of Postgres?" If they got their clus

by atonse 8y ago

Thanks to these engines and extensions, Postgres has become that rare tool for me where I have to ask "why use ___ instead of Postgres?" If they got their clustering story to be as easy as MongoDB's was 5 years ago (From what I read, Citus does this well), it's yet another excuse to stick with it.

chmod775 8y ago
> If they got their clustering story to be as easy as MongoDB's was 5 years ago You must have been using a different mongodb than I did.
- sroussey 8y ago
  On paper. Add that to the end of that sentence, and it will be true again.
- Townley 8y ago
  One of the things I liked about Mongo (compared to Postgres) was the ability to configure a list of connected servers, an election pattern for deciding the master, and... that's it. With that, servers on multiple hosts were load-balancing read-only queries, propagating writes, and failing over in a way that worked for us (a few seconds of write failures of the master ever died was fine for the application we were making). Meanwhile, postgres has this replication scheme available ("Trigger-Based Standby Master Replication") but the steps to implement it were much harder, and required an evaluation of possible replication/failover needs that we didn't know enough to do. It also requires a dive through pages of the documentation at https://www.postgresql.org/docs/10/different-replication-solutions.html https://www.postgresql.org/docs/10/different-replication-sol... to understand how to implement any of the above strategies. Postgresql's availability of different replication methods, and the resources available for each, are impressive for sure. But there's something to be said for having Mongo's easily-understandable list of steps to get multiple servers talking to each other right away.
  - SahAssar 8y ago
    Out of interest, have you tried https://github.com/zalando/patroni https://github.com/zalando/patroni ? It seems to fill this niche. I've been meaning to try it out for the HA postgres use case, but haven't gotten around to it.
- atonse 8y ago
  I won't speak to data integrity, but as the other poster said, you just had to set up the replication, election mechanisms and you were done. I bet Citus is like this, but I haven't used it yet.
craigkerstiens 8y ago
Couldn't agree more on the extensions part. We wrote a post recently highlighting how extensions have been key into what Postgres has become and is becoming. It got some pickup here on HN, in case you missed it in the initial post you can find it at https://www.citusdata.com/blog/2018/11/27/postgres-more-than-a-relational-database/ https://www.citusdata.com/blog/2018/11/27/postgres-more-than....
zamalek 8y ago
> why use ___ instead of Postgres? MSSQL has DataDude. You write your database as CREATE statements. This is then parsed into an AST and semantic model, diffed against a live database (or another script) and you get an ALTER script. You also get working intellisense, build errors, and everything you'd expect from something like C++. It completely changes how you develop databases into something a whole lot more modern. Especially in source control: migrations are storing history on top of the history which source control already provides, which is nonsensical. I still want to develop something for Postgres someday that replicates this.
- garblegarble 8y ago
  Liquibase is quite nice for doing this in a cross-database way - you describe the changes you want to make to the schema and it applies them as needed to the db (and because you're properly describing the changes it can also do rollback for a change). We use the same migrations across hsqldb, postgresql and sql server and it just works
- weberc2 8y ago
  > MSSQL has DataDude. You write your database as CREATE statements. This is then parsed into an AST and semantic model, diffed against a live database (or another script) and you get an ALTER script. Oh wow, I've wanted exactly this for Postgres. Never knew how to search for it though.
- atonse 8y ago
  I used to use RedGate's SQL Compare (and SQL Delta) for this for years. Compare two databases and spit out an alter script to get you from one state to another (for schemas and data). The tooling is definitely lacking in the Postgres world. I like using Postico on the mac for basic data tasks, since it's rather polished. But doing anything else is a slog using an ugly app.
- jakobegger 8y ago
  Is the product name really DataDude, or is that a typo? Do you have a link to that product?
outworlder 8y ago
And even if you have to use some other tool (Redis, Cassandra, MySQL!), you can access it from Postgres – https://wiki.postgresql.org/wiki/Foreign_data_wrappers https://wiki.postgresql.org/wiki/Foreign_data_wrappers PG has some warts, and the config defaults are really more suitable for a RaspberryPI rather than production use(so people sometimes get the wrong impression out of the box), but it is rock solid and can support so many different use-cases. It's one of the few pieces of software I could feel confident that, due to the way it's designed, my data will still be there even if someone yanks a server power cord.
nickjj 8y ago
Are you running at a scale where a single master DB isn't sufficient? Because nowadays you can get servers with about 192GB of RAM and 32 CPU cores. Clustering seems like one of those "what if"[0] scenarios where maybe if you were operating at roflcopter scale you might need it but for 99.9999999999999% of cases, a single master DB is more than enough -- at least with a well designed SQL db like Postgres. [0]: https://nickjanetakis.com/blog/optimize-your-programming-decisions-for-the-95-percent-not-the-5-percent https://nickjanetakis.com/blog/optimize-your-programming-dec...
- chrisfosterelli 8y ago
  Or maybe you just want to have a hiccup on a single machine not take down your entire application?
  - vvillena 8y ago
    Hot standbys work well in Postgres. As OP said, the issue is clustering, not replication.
- threeseed 8y ago
  It’s not about scale it’s reliability. Servers especially in the cloud can go down pretty quickly. Not just for hardware reasons either. I can’t imagine any company crazy enough to run one server like you’re describing. Definitely wouldn’t pass any enterprise service continuity testing/review that’s for sure.
  - marcosdumay 8y ago
    Clusters for reliability are very different from clusters for performance, and very well supported by Postgres.
- atonse 8y ago
  As others said, it isn't about scale, but reliability, and even things like patching. With a load balancer, you can easily patch your web servers, but very often, DB servers go unpatched and un-upgraded for years since they aren't in a self-healing cluster. To add some detail, I worked on an e-commerce system that was like this. We would've lost something like $60k an hour if we rebooted the database server (yes, one server, nobody expected this sort of growth). So we didn't touch it for 3 years until it was about to run out of disk space. Then we had no choice, but then we set it up as a fully replicated system with a ton of disk space, making future patching easier.
wenc 8y ago
SSMS is a really good free database management IDE/GUI/tool for MS SQL Server. It can do graphical live query profiling, index management, management of views/stored procedures/triggers/constraints/functions etc. Postgres currently has nothing comparable. DataGrip, though proprietary, is the closest in terms of functionality, but even it doesn't have the tight integration with the database that SSMS has. If you've used SSMS, other database GUIs feel underpowered. (Oracle SQL Developer is ugh)
- atonse 8y ago
  Agreed, I actually really like SQL server especially because of SSMS.
  - wenc 8y ago
    One of SSMS's strengths, as opposed to a generic database GUI IDE, is that it only works with one database: SQL Server (and variants like Azure SQL). This is important for accessing the specific features that distinguish that database from others, e.g. indexed views, clustered columnar indices, user-defined types, functions, linked servers, replication, HA, etc. There are so many cool and interesting features in MS SQL Server. I don't go for Microsoft products in general, Microsoft really got SQL Server right.
- AlfeG 8y ago
  I use Dbeaver. Its not a ssms, but very handy tool
  - wenc 8y ago
    I've used it too. It's one of the best free tools of its ilk. But yes, it's not SSMS.

by atonse 8y ago

chmod775 8y ago

> If they got their clustering story to be as easy as MongoDB's was 5 years ago You must have been using a different mongodb than I did.

sroussey 8y ago
On paper. Add that to the end of that sentence, and it will be true again.
Townley 8y ago
One of the things I liked about Mongo (compared to Postgres) was the ability to configure a list of connected servers, an election pattern for deciding the master, and... that's it. With that, servers on multiple hosts were load-balancing read-only queries, propagating writes, and failing over in a way that worked for us (a few seconds of write failures of the master ever died was fine for the application we were making). Meanwhile, postgres has this replication scheme available ("Trigger-Based Standby Master Replication") but the steps to implement it were much harder, and required an evaluation of possible replication/failover needs that we didn't know enough to do. It also requires a dive through pages of the documentation at https://www.postgresql.org/docs/10/different-replication-solutions.html https://www.postgresql.org/docs/10/different-replication-sol... to understand how to implement any of the above strategies. Postgresql's availability of different replication methods, and the resources available for each, are impressive for sure. But there's something to be said for having Mongo's easily-understandable list of steps to get multiple servers talking to each other right away.
- SahAssar 8y ago
  Out of interest, have you tried https://github.com/zalando/patroni https://github.com/zalando/patroni ? It seems to fill this niche. I've been meaning to try it out for the HA postgres use case, but haven't gotten around to it.
atonse 8y ago
I won't speak to data integrity, but as the other poster said, you just had to set up the replication, election mechanisms and you were done. I bet Citus is like this, but I haven't used it yet.

craigkerstiens 8y ago

Couldn't agree more on the extensions part. We wrote a post recently highlighting how extensions have been key into what Postgres has become and is becoming. It got some pickup here on HN, in case you missed it in the initial post you can find it at https://www.citusdata.com/blog/2018/11/27/postgres-more-than-a-relational-database/ https://www.citusdata.com/blog/2018/11/27/postgres-more-than....

zamalek 8y ago

> why use ___ instead of Postgres? MSSQL has DataDude. You write your database as CREATE statements. This is then parsed into an AST and semantic model, diffed against a live database (or another script) and you get an ALTER script. You also get working intellisense, build errors, and everything you'd expect from something like C++. It completely changes how you develop databases into something a whole lot more modern. Especially in source control: migrations are storing history on top of the history which source control already provides, which is nonsensical. I still want to develop something for Postgres someday that replicates this.

garblegarble 8y ago
Liquibase is quite nice for doing this in a cross-database way - you describe the changes you want to make to the schema and it applies them as needed to the db (and because you're properly describing the changes it can also do rollback for a change). We use the same migrations across hsqldb, postgresql and sql server and it just works
weberc2 8y ago
> MSSQL has DataDude. You write your database as CREATE statements. This is then parsed into an AST and semantic model, diffed against a live database (or another script) and you get an ALTER script. Oh wow, I've wanted exactly this for Postgres. Never knew how to search for it though.
atonse 8y ago
I used to use RedGate's SQL Compare (and SQL Delta) for this for years. Compare two databases and spit out an alter script to get you from one state to another (for schemas and data). The tooling is definitely lacking in the Postgres world. I like using Postico on the mac for basic data tasks, since it's rather polished. But doing anything else is a slog using an ugly app.
jakobegger 8y ago
Is the product name really DataDude, or is that a typo? Do you have a link to that product?

outworlder 8y ago

And even if you have to use some other tool (Redis, Cassandra, MySQL!), you can access it from Postgres – https://wiki.postgresql.org/wiki/Foreign_data_wrappers https://wiki.postgresql.org/wiki/Foreign_data_wrappers PG has some warts, and the config defaults are really more suitable for a RaspberryPI rather than production use(so people sometimes get the wrong impression out of the box), but it is rock solid and can support so many different use-cases. It's one of the few pieces of software I could feel confident that, due to the way it's designed, my data will still be there even if someone yanks a server power cord.

nickjj 8y ago

Are you running at a scale where a single master DB isn't sufficient? Because nowadays you can get servers with about 192GB of RAM and 32 CPU cores. Clustering seems like one of those "what if"[0] scenarios where maybe if you were operating at roflcopter scale you might need it but for 99.9999999999999% of cases, a single master DB is more than enough -- at least with a well designed SQL db like Postgres. [0]: https://nickjanetakis.com/blog/optimize-your-programming-decisions-for-the-95-percent-not-the-5-percent https://nickjanetakis.com/blog/optimize-your-programming-dec...

chrisfosterelli 8y ago
Or maybe you just want to have a hiccup on a single machine not take down your entire application?
- vvillena 8y ago
  Hot standbys work well in Postgres. As OP said, the issue is clustering, not replication.
threeseed 8y ago
It’s not about scale it’s reliability. Servers especially in the cloud can go down pretty quickly. Not just for hardware reasons either. I can’t imagine any company crazy enough to run one server like you’re describing. Definitely wouldn’t pass any enterprise service continuity testing/review that’s for sure.
- marcosdumay 8y ago
  Clusters for reliability are very different from clusters for performance, and very well supported by Postgres.
atonse 8y ago
As others said, it isn't about scale, but reliability, and even things like patching. With a load balancer, you can easily patch your web servers, but very often, DB servers go unpatched and un-upgraded for years since they aren't in a self-healing cluster. To add some detail, I worked on an e-commerce system that was like this. We would've lost something like $60k an hour if we rebooted the database server (yes, one server, nobody expected this sort of growth). So we didn't touch it for 3 years until it was about to run out of disk space. Then we had no choice, but then we set it up as a fully replicated system with a ton of disk space, making future patching easier.

wenc 8y ago

SSMS is a really good free database management IDE/GUI/tool for MS SQL Server. It can do graphical live query profiling, index management, management of views/stored procedures/triggers/constraints/functions etc. Postgres currently has nothing comparable. DataGrip, though proprietary, is the closest in terms of functionality, but even it doesn't have the tight integration with the database that SSMS has. If you've used SSMS, other database GUIs feel underpowered. (Oracle SQL Developer is ugh)

atonse 8y ago
Agreed, I actually really like SQL server especially because of SSMS.
- wenc 8y ago
  One of SSMS's strengths, as opposed to a generic database GUI IDE, is that it only works with one database: SQL Server (and variants like Azure SQL). This is important for accessing the specific features that distinguish that database from others, e.g. indexed views, clustered columnar indices, user-defined types, functions, linked servers, replication, HA, etc. There are so many cool and interesting features in MS SQL Server. I don't go for Microsoft products in general, Microsoft really got SQL Server right.
AlfeG 8y ago
I use Dbeaver. Its not a ssms, but very handy tool
- wenc 8y ago
  I've used it too. It's one of the best free tools of its ilk. But yes, it's not SSMS.