10 ms·

This is advertising of course. But if I had to select an SQL DB postgres is my only choice. Perhaps I don't know enough about databases and their differences.

by Jonnax 7y ago

This is advertising of course. But if I had to select an SQL DB postgres is my only choice. Perhaps I don't know enough about databases and their differences. Anyone have some pros and cons of others? Like why would I pick MySQL, Microsoft, Oracle, Maria etc over Postgres? Apart from support that you gotta pay for.

drdaeman 7y ago
Replication, failover and high availability are complicated matters. I believe there are other SQL databases that may be said to handle this better than PostgreSQL. At the very least, CockroachDB is much simpler to set up and manage. Cannot vouch for its stability and complexity of abnormal emergency recoveries, though - haven't used it long enough, only had simplest outages (that it had handled flawlessly).
- xdanger 7y ago
  I've been following YugabyteDB ( https://www.yugabyte.com/ https://www.yugabyte.com/ ) with interest for similar reasons. It's build on postgresql so it supports more features than CockroachDB. They just forked PG on top of their clustered document store. And the newest version (2.1) is production ready (their words). Just haven't had the reason to dive in yet, but seems good. Their PR department just isn't as good as CockroachDBs one.
- ksec 7y ago
  >Replication, failover and high availability are complicated matters. I wonder why Postgre hasn't improved in these areas and instead leaving it to third party solution. I mean every year these few points are still listed as something that flavours MySQL.
  - nicoburns 7y ago
    It might be partly because Postgres is largely developed by the providers of "3rd-party" solutions for features like this. You could see it as an open-core model of sorts.
  - yxhuvud 7y ago
    Why are you saying they have not improved? That is flat out false - postgres provides a lot better tools for this now than 10 years ago. They still leave a lot of the plumbing to third parties, but they are now at least giving those third parties a lot better tools than they used to.
    - ksec 7y ago
      I think a more accurate way to put it, why isn't there any default solution? But I guess the Open Core model another comment pointed out makes sense.
jabl 7y ago
Well, (unfortunately) plenty of applications support only one DB backend. For FOSS software, MySQL/MariaDB support is very common, and even if those apps support pgSQL it might be that pgSQL support is not as robust and well-tested as mysql. And in plenty of situations, sqlite is perfectly adequate, and saves you from the extra work of managing yet another service. But yes, I agree if I'd start from a blank sheet and need a network-accessible SQL DB, pgSQL would be my first choice. Now if I had some very high end requirements, maybe one of those big commercial DB's would have an advantage.
- m0skit0 7y ago
  Yahoo uses PostgreSQL. I think that's pretty high-end requirements an debunks the myth of "commercial software is better for high end requirements".
  - redis_mlc 7y ago
    First of all, Yahoo historically used Oracle Enterprise and MySQL for RDBMS, roughly 50% each. Large companies use some of everything, but I never saw PG in use there.
    - mulmen 7y ago
      https://www.computerworld.com/article/2535825/size-matters--yahoo-claims-2-petabyte-database-is-world-s-biggest--busiest.html https://www.computerworld.com/article/2535825/size-matters--... Yahoo’s use of Postgres was legendary.
      - redis_mlc 7y ago
        No, it wasn't legendary. That's just one data warehouse application. The one I administered, just as big, was Saturn. And it was MySQL. Source: worked at Yahoo, saw way more MySQL than PG.
        mulmen 7y ago
endymi0n 7y ago
Having worked with dozens of different databases over lots of years, I can certainly say that Postgres is my go-to _default_ database over all the other relational ones. In the same way that Golang claims it's the 90% language ( https://talks.golang.org/2014/gocon-tokyo.slide#1 https://talks.golang.org/2014/gocon-tokyo.slide#1 ), I'd say Postgres is the perfect 90% database. However, we're still using at least four other databases in production, and the reason why is that because it's doing something of everything, there are lots of niches that it doesn't fill well. It's all about tradeoffs. If you've got a Full Text Search problem, PG is "good enough", that is until you need to index more challenging languages like Korean or need more exotic scoring functions. For that, Elastic is better. You can do small scale analytics on PG, but at larger sizes, the transactional setup gets in the way and you're better off with BigQuery/Redshift/Snowflake. You can scale vertical pretty big these days, but if you need linear scalability while still guaranteeing single digit ms access, you're better off with ScyllaDB. However, there isn't a single project I wouldn't not start on a Postgres these days, that's how much I love it.
- guiriduro 7y ago
  I wouldn't say PostgreSQL was the analogue of golang: go's initial popular growth for being 'good enough' while being arguably oversimplified, where simplicity masks problems, under the pretence that YANGNI always applies (initial N emphasizing the Not of Aren't), seems much closer to MySQL's history. PG was driven by engineering correctness, by considering what DBAs 'Are Going to Need'. Sometimes that strictness worked against popular growth but in the end it has worked out well, as many programmers figured out they also needed it. I'd say the programming language analogue would likely be Rust, lets see in 10 years where the 90% case lies.
  - KptMarchewa 7y ago
    Rust is too complicated for 90% cases. 90% of cases are simpler with GC.
    - ansible 7y ago
      Rust is more complicated than some languages with GC. However, if you are writing some code with multi-threading (who isn't these days), then you may eventually want the help Rust gives you there as far as correctness goes.
timmy-turner 7y ago
I did use MySQL/MariaDB in production for a couple of years, and now I'm using Postgres. PG sure seems to have better foundations and generally a more engineered approach to everything. This leads to some noteworthy outcomes in the tooling or the developer UX, for example: PG won't allow a union type (like String or Integer) in query arguments. PGs query argument handling is rather simple (at least the stuff provided with libpq), it only allows positional arguments. Sometimes, MySQL by extending or bending SQL here and there allows for some queries to be written terser, like an update+join over multiple tables. Also I did actually enjoy that MySQL had less advanced features, so it seemed to be easier to reason about performance and overall behaviour.
onion2k 7y ago
One of Postgres's biggest pros (over other commonly used dbs in web dev) is Row Level Security. Controlling what data a user can access at the database rather than the application or as part of a query makes the application logic a lot simpler. That's a big win.
- BiteCode_dev 7y ago
  But that also means that: - the code is not reusable outside of a database setting. So not cacheable. - the code is not reusable accross different storage layers. So not portable. - the code may needs updating if the schema change, you can't abstract that - changing the logic means a db migration - testing the code requires a DB - tooling support to check that code si limited to SQL tooling, which is very weak, especially for code completion, refactor and debugging. That's a lot of constraints for just making the application logic a lot simpler.
  - Joeri 7y ago
    I agree, but I also see the benefits from the other side, so I’ll play devil’s advocate: - it is cached, in the database’s memory, where the cache can be invalidated automatically. It is better to cache views than data anyway. - it is portable to every platform postgres runs, which in practice means it will run everywhere. Portability between databases is overrated because it rarely happens in practice. - the access control logic evolves together with the schema, guaranteeing they have an exact correspondence. This is a good thing. - integration tests should involve a live database - have you looked at jetbrains datagrip?
    - sscarduzio 7y ago
      The tooling is just fine for me, DataGrip or not: I wrote a ton of PostgreSQL triggers and utility functions in Javascript thanks to the excellent plv8 (free) extension. Development and unit tests for these functions is as easy as plain Javascript.
      - BiteCode_dev 7y ago
juped 7y ago
SQLite is also extremely useful, but it fills a different niche. (One of SQLite's slogans is "SQLite does not compete with client/server databases. SQLite competes with fopen().")
- yjftsjthsd-h 7y ago
  > SQLite competes with fopen() ... and wins, which surprised me when I found out.
mrweasel 7y ago
I think the only reason I wouldn't choose Postgresql is if I need master-master replication. It's not that you can't create a master-master setup using Postgresql, I believe 2ndquadrant have and add-on that allows this. It just feels like it's messing with the fundamentals of the database on such a low level that I really only trust it, if it's part of Postgresql it self.
- yrro 7y ago
  Logical replication has been in PostgreSQL itself for a while now. There are naturally caveats: sequences don't get replicated, so you need to configure each replica with a non-overlapping range for each sequence. And DDL statements are also not replicated, so you have to migrate database schemas by hand on each replica.
  - jeremyjh 7y ago
    Logical changeset extraction itself does not in itself provide a replication feature from what I can see of the wiki [1]. 2ndQuadrant sells BDR which is built on top of it. [1] https://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling https://wiki.postgresql.org/wiki/Replication,_Clustering,_an...
    - anarazel 7y ago
      > Logical changeset extraction itself does not in itself provide a replication feature It is however a building block for logical replication, and used by https://www.postgresql.org/docs/current/logical-replication.html https://www.postgresql.org/docs/current/logical-replication.... > from what I can see of the wiki [1 I really wish we'd just shut off the wiki. There's random pages starting to be maintained by someone that then stops at some point. Leads to completely outdated content, as in this case.
willvarfar 7y ago
The main reason I use MySQL over Postgres is storage engines. MySQL has storage engines with transparent compression, which allow me to keep an order of magnitude more data than using Postgres. These days its MyRocks, in the past it was TokuDB. Historically Postgres has thought that the job of the file system, which is basically a bad choice for dbs. MyRocks wipes the floor with it. TimescaleDB is an interesting new Postgres storage choice. I'm evaluating it. But the free version doesn't do compression on-demand nor transparently. Anyway, it can only get better.
- wlll 7y ago
  Do these satisfy the compression requirement? https://stackoverflow.com/questions/1369864/does-postgresql-support-transparent-compressing-of-tables-fragments https://stackoverflow.com/questions/1369864/does-postgresql-...
  - ddorian43 7y ago
    No. LSM/Rocksdb databases do "block compressions". Even better is dynamic dictionary compression: https://github.com/facebook/rocksdb/wiki/Dictionary-Compression https://github.com/facebook/rocksdb/wiki/Dictionary-Compress...
  - willvarfar 7y ago
    No, that's a compressed row format, not a compressed block format. MySQL's innodb, for example, supports per-row-compression. Its completely ineffective. Block compression in tokdub and myrocks etc is a completely different class. MySQL's innodb also supporta kind of 'page compression' using the file-system's sparse pages. Its also naff. The closest postgres can get is using zfs with compression. Its a lot better than nothing.
    - wlll 7y ago
      Yeah, one of the suggestions on that page was a compressed filesystem.
- rrickardt 7y ago
JacKTrocinskI 7y ago
Oracle is currently moving in the direction of autonomous databases. It's no longer just about having great automation features but about the database being able to do things for you like create or drop an index in order to improve ETL performance, and it's all done autonomously.
- redwood 7y ago
  Oracle has not managed the shift to cloud so if you look at where the puck is going, it's moving away from Oracle.
  - JacKTrocinskI 7y ago
    Oracle has their own cloud platform and a great database to go along with it, if anything history has shown us that Oracle is able to adapt their database offerings to cater to current enterprise needs and I think this time will be no different.
    - redwood 7y ago
      The database is not the cloud platform. There is a lot of gravity there but it's not enough.
  - kube-system 7y ago
    The largest enterprise SaaS application in the world runs on Oracle.
    - redwood 7y ago
      Is that application built and sold by Oracle as well?
      - kube-system 7y ago
        No
deleted 7y ago
[deleted]
DeathArrow 7y ago
We picked SQL server by Microsoft for one of our projects because it was easier to integrate it when we started (ASP.NET core 2.0) and it has very nice tools like SSMS, better than DBeaver. For a new project we picked Postgres. We might in the future use any of the two. We also have other DB tech in the company: Teradata, Oracle, Redis, Cassandra.
pjc50 7y ago
Microsoft SQL Server is an obvious default choice if you're an all-Microsoft shop; it has all the Azure and Visual Studio integrations, etc. and comes with your MSDN. Oracle is the choice of organisations with money to burn who don't believe that free products can be good. An Oracle installation nearly always comes with some consultants to write your application for you, and define your application for you, and write your invoices for you ...
neop1x 7y ago
I am quite surprised MySQL or MariaDB is not mentioned more. It seems like a standard to me. Almost every serious software supports it. Of course it doesn't have some extra features like GIS but for generic RDB it performs well. And there is also Galera cluster btw...

by Jonnax 7y ago

drdaeman 7y ago

Replication, failover and high availability are complicated matters. I believe there are other SQL databases that may be said to handle this better than PostgreSQL. At the very least, CockroachDB is much simpler to set up and manage. Cannot vouch for its stability and complexity of abnormal emergency recoveries, though - haven't used it long enough, only had simplest outages (that it had handled flawlessly).

xdanger 7y ago
I've been following YugabyteDB ( https://www.yugabyte.com/ https://www.yugabyte.com/ ) with interest for similar reasons. It's build on postgresql so it supports more features than CockroachDB. They just forked PG on top of their clustered document store. And the newest version (2.1) is production ready (their words). Just haven't had the reason to dive in yet, but seems good. Their PR department just isn't as good as CockroachDBs one.
ksec 7y ago
>Replication, failover and high availability are complicated matters. I wonder why Postgre hasn't improved in these areas and instead leaving it to third party solution. I mean every year these few points are still listed as something that flavours MySQL.
- nicoburns 7y ago
  It might be partly because Postgres is largely developed by the providers of "3rd-party" solutions for features like this. You could see it as an open-core model of sorts.
- yxhuvud 7y ago
  Why are you saying they have not improved? That is flat out false - postgres provides a lot better tools for this now than 10 years ago. They still leave a lot of the plumbing to third parties, but they are now at least giving those third parties a lot better tools than they used to.
  - ksec 7y ago
    I think a more accurate way to put it, why isn't there any default solution? But I guess the Open Core model another comment pointed out makes sense.

jabl 7y ago

Well, (unfortunately) plenty of applications support only one DB backend. For FOSS software, MySQL/MariaDB support is very common, and even if those apps support pgSQL it might be that pgSQL support is not as robust and well-tested as mysql. And in plenty of situations, sqlite is perfectly adequate, and saves you from the extra work of managing yet another service. But yes, I agree if I'd start from a blank sheet and need a network-accessible SQL DB, pgSQL would be my first choice. Now if I had some very high end requirements, maybe one of those big commercial DB's would have an advantage.

m0skit0 7y ago
Yahoo uses PostgreSQL. I think that's pretty high-end requirements an debunks the myth of "commercial software is better for high end requirements".
- redis_mlc 7y ago
  First of all, Yahoo historically used Oracle Enterprise and MySQL for RDBMS, roughly 50% each. Large companies use some of everything, but I never saw PG in use there.
  - mulmen 7y ago
    https://www.computerworld.com/article/2535825/size-matters--yahoo-claims-2-petabyte-database-is-world-s-biggest--busiest.html https://www.computerworld.com/article/2535825/size-matters--... Yahoo’s use of Postgres was legendary.
    - redis_mlc 7y ago
      No, it wasn't legendary. That's just one data warehouse application. The one I administered, just as big, was Saturn. And it was MySQL. Source: worked at Yahoo, saw way more MySQL than PG.
      - mulmen 7y ago

endymi0n 7y ago

Having worked with dozens of different databases over lots of years, I can certainly say that Postgres is my go-to _default_ database over all the other relational ones. In the same way that Golang claims it's the 90% language ( https://talks.golang.org/2014/gocon-tokyo.slide#1 https://talks.golang.org/2014/gocon-tokyo.slide#1 ), I'd say Postgres is the perfect 90% database. However, we're still using at least four other databases in production, and the reason why is that because it's doing something of everything, there are lots of niches that it doesn't fill well. It's all about tradeoffs. If you've got a Full Text Search problem, PG is "good enough", that is until you need to index more challenging languages like Korean or need more exotic scoring functions. For that, Elastic is better. You can do small scale analytics on PG, but at larger sizes, the transactional setup gets in the way and you're better off with BigQuery/Redshift/Snowflake. You can scale vertical pretty big these days, but if you need linear scalability while still guaranteeing single digit ms access, you're better off with ScyllaDB. However, there isn't a single project I wouldn't not start on a Postgres these days, that's how much I love it.

guiriduro 7y ago
I wouldn't say PostgreSQL was the analogue of golang: go's initial popular growth for being 'good enough' while being arguably oversimplified, where simplicity masks problems, under the pretence that YANGNI always applies (initial N emphasizing the Not of Aren't), seems much closer to MySQL's history. PG was driven by engineering correctness, by considering what DBAs 'Are Going to Need'. Sometimes that strictness worked against popular growth but in the end it has worked out well, as many programmers figured out they also needed it. I'd say the programming language analogue would likely be Rust, lets see in 10 years where the 90% case lies.
- KptMarchewa 7y ago
  Rust is too complicated for 90% cases. 90% of cases are simpler with GC.
  - ansible 7y ago
    Rust is more complicated than some languages with GC. However, if you are writing some code with multi-threading (who isn't these days), then you may eventually want the help Rust gives you there as far as correctness goes.

timmy-turner 7y ago

I did use MySQL/MariaDB in production for a couple of years, and now I'm using Postgres. PG sure seems to have better foundations and generally a more engineered approach to everything. This leads to some noteworthy outcomes in the tooling or the developer UX, for example: PG won't allow a union type (like String or Integer) in query arguments. PGs query argument handling is rather simple (at least the stuff provided with libpq), it only allows positional arguments. Sometimes, MySQL by extending or bending SQL here and there allows for some queries to be written terser, like an update+join over multiple tables. Also I did actually enjoy that MySQL had less advanced features, so it seemed to be easier to reason about performance and overall behaviour.

onion2k 7y ago

One of Postgres's biggest pros (over other commonly used dbs in web dev) is Row Level Security. Controlling what data a user can access at the database rather than the application or as part of a query makes the application logic a lot simpler. That's a big win.

BiteCode_dev 7y ago
But that also means that: - the code is not reusable outside of a database setting. So not cacheable. - the code is not reusable accross different storage layers. So not portable. - the code may needs updating if the schema change, you can't abstract that - changing the logic means a db migration - testing the code requires a DB - tooling support to check that code si limited to SQL tooling, which is very weak, especially for code completion, refactor and debugging. That's a lot of constraints for just making the application logic a lot simpler.
- Joeri 7y ago
  I agree, but I also see the benefits from the other side, so I’ll play devil’s advocate: - it is cached, in the database’s memory, where the cache can be invalidated automatically. It is better to cache views than data anyway. - it is portable to every platform postgres runs, which in practice means it will run everywhere. Portability between databases is overrated because it rarely happens in practice. - the access control logic evolves together with the schema, guaranteeing they have an exact correspondence. This is a good thing. - integration tests should involve a live database - have you looked at jetbrains datagrip?
  - sscarduzio 7y ago
    The tooling is just fine for me, DataGrip or not: I wrote a ton of PostgreSQL triggers and utility functions in Javascript thanks to the excellent plv8 (free) extension. Development and unit tests for these functions is as easy as plain Javascript.
    - BiteCode_dev 7y ago

juped 7y ago

SQLite is also extremely useful, but it fills a different niche. (One of SQLite's slogans is "SQLite does not compete with client/server databases. SQLite competes with fopen().")

yjftsjthsd-h 7y ago
> SQLite competes with fopen() ... and wins, which surprised me when I found out.

mrweasel 7y ago

I think the only reason I wouldn't choose Postgresql is if I need master-master replication. It's not that you can't create a master-master setup using Postgresql, I believe 2ndquadrant have and add-on that allows this. It just feels like it's messing with the fundamentals of the database on such a low level that I really only trust it, if it's part of Postgresql it self.

yrro 7y ago
Logical replication has been in PostgreSQL itself for a while now. There are naturally caveats: sequences don't get replicated, so you need to configure each replica with a non-overlapping range for each sequence. And DDL statements are also not replicated, so you have to migrate database schemas by hand on each replica.
- jeremyjh 7y ago
  Logical changeset extraction itself does not in itself provide a replication feature from what I can see of the wiki [1]. 2ndQuadrant sells BDR which is built on top of it. [1] https://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling https://wiki.postgresql.org/wiki/Replication,_Clustering,_an...
  - anarazel 7y ago
    > Logical changeset extraction itself does not in itself provide a replication feature It is however a building block for logical replication, and used by https://www.postgresql.org/docs/current/logical-replication.html https://www.postgresql.org/docs/current/logical-replication.... > from what I can see of the wiki [1 I really wish we'd just shut off the wiki. There's random pages starting to be maintained by someone that then stops at some point. Leads to completely outdated content, as in this case.

willvarfar 7y ago

The main reason I use MySQL over Postgres is storage engines. MySQL has storage engines with transparent compression, which allow me to keep an order of magnitude more data than using Postgres. These days its MyRocks, in the past it was TokuDB. Historically Postgres has thought that the job of the file system, which is basically a bad choice for dbs. MyRocks wipes the floor with it. TimescaleDB is an interesting new Postgres storage choice. I'm evaluating it. But the free version doesn't do compression on-demand nor transparently. Anyway, it can only get better.

wlll 7y ago
Do these satisfy the compression requirement? https://stackoverflow.com/questions/1369864/does-postgresql-support-transparent-compressing-of-tables-fragments https://stackoverflow.com/questions/1369864/does-postgresql-...
- ddorian43 7y ago
  No. LSM/Rocksdb databases do "block compressions". Even better is dynamic dictionary compression: https://github.com/facebook/rocksdb/wiki/Dictionary-Compression https://github.com/facebook/rocksdb/wiki/Dictionary-Compress...
- willvarfar 7y ago
  No, that's a compressed row format, not a compressed block format. MySQL's innodb, for example, supports per-row-compression. Its completely ineffective. Block compression in tokdub and myrocks etc is a completely different class. MySQL's innodb also supporta kind of 'page compression' using the file-system's sparse pages. Its also naff. The closest postgres can get is using zfs with compression. Its a lot better than nothing.
  - wlll 7y ago
    Yeah, one of the suggestions on that page was a compressed filesystem.
rrickardt 7y ago

JacKTrocinskI 7y ago

Oracle is currently moving in the direction of autonomous databases. It's no longer just about having great automation features but about the database being able to do things for you like create or drop an index in order to improve ETL performance, and it's all done autonomously.

redwood 7y ago
Oracle has not managed the shift to cloud so if you look at where the puck is going, it's moving away from Oracle.
- JacKTrocinskI 7y ago
  Oracle has their own cloud platform and a great database to go along with it, if anything history has shown us that Oracle is able to adapt their database offerings to cater to current enterprise needs and I think this time will be no different.
  - redwood 7y ago
    The database is not the cloud platform. There is a lot of gravity there but it's not enough.
- kube-system 7y ago
  The largest enterprise SaaS application in the world runs on Oracle.
  - redwood 7y ago
    Is that application built and sold by Oracle as well?
    - kube-system 7y ago
      No

deleted 7y ago

[deleted]

DeathArrow 7y ago

We picked SQL server by Microsoft for one of our projects because it was easier to integrate it when we started (ASP.NET core 2.0) and it has very nice tools like SSMS, better than DBeaver. For a new project we picked Postgres. We might in the future use any of the two. We also have other DB tech in the company: Teradata, Oracle, Redis, Cassandra.

pjc50 7y ago

Microsoft SQL Server is an obvious default choice if you're an all-Microsoft shop; it has all the Azure and Visual Studio integrations, etc. and comes with your MSDN. Oracle is the choice of organisations with money to burn who don't believe that free products can be good. An Oracle installation nearly always comes with some consultants to write your application for you, and define your application for you, and write your invoices for you ...

neop1x 7y ago

I am quite surprised MySQL or MariaDB is not mentioned more. It seems like a standard to me. Almost every serious software supports it. Of course it doesn't have some extra features like GIS but for generic RDB it performs well. And there is also Galera cluster btw...