9 ms·

Postgres is one of those pieces of software that’s so much better than anything else, it’s really incredible. I wonder if it’s even possible for competitors to

Postgres is one of those pieces of software that’s so much better than anything else, it’s really incredible. I wonder if it’s even possible for competitors to catch up at this point - there’s not a lot of room for improvement in architecture of relational databases any more. I’m starting to think that Postgres is going to be with us for decades maybe even centuries. Do any other entrenched software projects come to mind? The only thing comparable I can think of are Git and Linux.

jjeaff 5y ago
MySQL 8 is not that far behind in feature parity. And is ahead when it comes to scalability. So I don't see postgres as necessarily standing alone.
- paozac 5y ago
  MySQL's lack of DDL transactions is a serious shortcoming.
- ezekiel68 5y ago
  You claim that MySQL 8 is ahead when it comes to scalability. What are the bases of this claim? When I see comparisons or entire systems that rely on a database (that is, not micro-benchmarks) such as the TechEmpower web framework benchmarks [0] , I notice that the 'Pg' results cluster near the top, with the "My" results showing up further down the rankings. I understand this isn't version 14 of the former versus version 8 of the latter. But it makes me wonder what the basis of your claims is. [0] https://www.techempower.com/benchmarks/ https://www.techempower.com/benchmarks/
  - isbvhodnvemrwvn 5y ago
    Aren't those run on a single node DB server? And the queries don't really seem realistic at all, e.g. single query test fetches 1 out of 10 000 rows, with no joins at all. Fortunes fetches 1 out of 10 rows. This seems extremely trivial.
  - merb 5y ago
    well if you need more than one server, mysql has vitess, which is huge. postgres has citus, but that is way more complex to setup than vitess. I still would never use mysql, just because of vitess.
    - deleted 5y ago
      [deleted]
  - the_duke 5y ago
    Techempower is not a database benchmark. The tests that involve a DB exist to include a DB client in the request flow, not to put any serious load on the database.
- purerandomness 5y ago
  No DDL transactions, no materialized views, the list is endless. There's almost no reason to pick MySQL for a new project.
  - tfigment 5y ago
    MySQL and mariadb have first class temporal tables. Pg has compile requirement and so cannot use in AWS RDS.
    - dragonwriter 5y ago
      > MySQL and mariadb have first class temporal tables. Pg has compile requirement and so cannot use in AWS RDS. There’s a pl/pgsql reimplementation of temporal tables specifically for that use case.
      - phonon 5y ago
        https://news.ycombinator.com/item?id=26768220 https://news.ycombinator.com/item?id=26768220
    - lowercased 5y ago
      I was aware maria had temporary tables, but not mysql proper. Any links you can point me to? Every search is coming up with 'temporary' table info, not temporal.
    - lowercased 5y ago
      mysql8 has gis/spatial stuff built in now. may not quite be on par with postgis, but... i also don't have to futz with "doesn't come baked in". Dealt with someone who wrote a whole bunch of lat/lon/spatial stuff in client code because we're on postgres but ... he couldn't get postgis installed (then even if he could, figuring out how to convince the ops people to add a new 'thing' in production would have been a delay). having stuff baked in is often a win.
  - aseipp 5y ago
    MySQL has transactions for DDL changes since 8.0.
- ksec 5y ago
  Are there any Roadmap for MySQL 9 ?
yakubin 5y ago
Fortran for linear algebra software. Excel for business spreadsheets. Java for enterprise server software.
- Supermancho 5y ago
  > Java for enterprise server software. Big corporations are horribly inefficient and Enterprise Software necessarily so from that...if you're saying Java is terrible by nature of it being the goto for enterprise, then that makes sense. It took 20 years for it to swap places with COBOL and I expect it will be something else in 20 more.
  - yakubin 5y ago
    I don't work with Java, but I can think of a few advantages off the top of my head: - appreciation of backwards-compatibility (here it wins with Python); - great debuggers and performance tools (e.g. Java Flight Recorder or Eclipse Memory Analyzer); - easy deployment - you can just give someone a fat JAR (here it wins with all scripting languages, so Python, Ruby, PHP, or any other flavour of the month); - industry-grade garbage collectors; - publicly-available standard spec (here it wins with all the defined-by-implementation languages such as Python, PHP, Rust, basically most languages, and with languages which are standardized, but their specs aren't public: C, C++, Ruby); - kind of like the previous point, but anyway: multiple implementations to choose from; - I've been told it has good performance. I've never seen a real-world Java application which felt fast, but I've heard people put it at the pedestal and the Debian programming languages benchmarks game seems to corroborate that story; Besides, the question wasn't about which technologies we like, but which we believe are entrenched so much, they aren't going to go away for a very long time. I don't see Java going away for another 100 years, no matter how much I would or wouldn't like to work with it.
    - radicalbyte 5y ago
      - Fantastic battle tested ecosystem of libraries. - Stable cross platform (kills Python, Node here). - Lingua franca. Now I personally don't like Java - it feels crusty vs C# - but the libraries are amazing. You can also use something nice like Kotlin and you have all of the platform benefits with non of the crusty language issues.
sweeneyrod 5y ago
I think many mercurial users would disagree with you about git.
- jayd16 5y ago
  Are we talking about market dominance, mind share or the idea that there's no real competition? MySQL and Oracle exist. Mercurial and perforce exist. I'm not sure it's a terrible stretch to compare git and postures.
  - aidenn0 5y ago
    I think the point is that git isn't "so much better" than mercurial, while pgsql has had a lead on mysql for quite some time on a lot of technical measurements.
    - polskibus 5y ago
      Postgresql does not have real, maintained with each change, clustered index. That itself makes it worse for many workloads than MySQL
      - glogla 5y ago
        "Is table a heap with indexes on the side or is table a tree with other indexes on the side (i.e. 'clustered index')" is a more complicated discussion. The former makes it possible to have MVCC (and thus gives you snapshot isolation and serializability) and makes secondary indexes perform faster, at the cost of vacuum or Oracle-style redo/undo/rollback segments with associated "Snapshot too old" issues. The latter pretty much forces use of locking even for read so queries block each other (but don't require vacuum or something), makes clustering key selective queries perform faster than secondary index ones and makes you think really hard about the clustering key. It's not really a feature you would have, but a complicated design tradeoff.
      - petergeoghegan 5y ago
        I would say that that's pretty dubious claim with modern versions of Postgres and MySQL/InnoDB, running on modern hardware. See for example this recent comparative Benchmark from Mark Callaghan, a well known member of the MySQL community: https://smalldatum.blogspot.com/2021/01/sysbench-postgres-vs-mysql-and-impact.html https://smalldatum.blogspot.com/2021/01/sysbench-postgres-vs... I'm not claiming that this benchmark justifies the claim that Postgres broadly performs better than MySQL/InnoDB these days -- that would be highly simplistic. Just as it would be simplistic to claim that MySQL is clearly well ahead with OLTP stuff in some kind of broad and entrenched way. It's highly dependent on workload. Note that Postgres really comes out ahead on a test called "update-index", which involves updates that modify indexed columns -- the write amplification is much worse on MySQL there. This is precisely the opposite of what most commentators would have predicted. Including (and perhaps even especially) Postgres community people.
autodeadmehaha 5y ago
Anyone whose ever had to upgrade postgres ever knows postgres can't fail fast enough. They must fix their upgrade paths and it's endless means to completely fuck you if they want to be taken seriously.
- yjftsjthsd-h 5y ago
  ? What's wrong with pg_upgrade?
ranit 5y ago
> Do any other entrenched software projects come to mind? SQLite.
- IshKebab 5y ago
  I'm pretty hopeful that DuckDB will replace some of the use of SQLite. SQLite is great but it sucks that it's entirely dynamically typed (the types specified for columns are completely ignored).
  - dragonwriter 5y ago
    > the types specified for columns are completely ignored They aren’t constraints (except in the case of “INTEGER PRIMARY KEY”), but they also aren’t “completely ignored”, because of type affinity.
fibers 5y ago
i had to roll back to 9.6 on windows because \COPY is fundamentally broken for large cvs
- CapriciousCptl 5y ago
  What's the issue? Just on Windows? Mac OS X with 13.2 has no issue for me with the 1.1gigabyte 20million record csv just imported last week, or some bigger ones I did a few months back.
  - tpxl 5y ago
    Same here, 500MB, 10 million row csv file with no issues on Postgres 11.8.
dilyevsky 5y ago
Kubernetes when it comes to clustering.
eterm 5y ago
I think anyone who has worked a lot with MSSQL would disagree with Postgres being "so much better". It's only really in the last few years that postgres has pulled ahead, MSSQL was lot more feature rich and performant for a decade.
- harikb 5y ago
  MSSQL ? As in Microsoft SQL Server? I have heard this argument a lot and all the comparisons I have seen are specific benchmarks on specialized hardware. My own personal experience wasn’t anything like the benchmarks
  - sigzero 5y ago
    By "few years" he has to mean 10 to 15 years. ;)
  - HideousKojima 5y ago
    MSSQL still has a few features that set it apart from Postgres. Off the top of my head are Filestream (basically storing files in the database while still having them accessible as files on the filesystem) and temporal tables without the need for extensions. Personally if I were choosing the tech stack for my company I'd still go for Postgres though
    - johnthescott 5y ago
      pg has had file fw for years: https://www.postgresql.org/docs/13/file-fdw.html https://www.postgresql.org/docs/13/file-fdw.html
dimgl 5y ago
Postgres is good, even great, but this is hyperbole. Postgres has its downsides, autovacuum being one of them.
- petergeoghegan 5y ago
  Although the article doesn't mention it, index bloat will be far better controlled in Postgres 14: https://www.postgresql.org/docs/devel/btree-implementation.html#BTREE-DELETION https://www.postgresql.org/docs/devel/btree-implementation.h... One benchmark involving a mix of queue-like inserts, updates, and deletes showed that it was practically 100% effective at controlling index bloat: https://www.postgresql.org/message-id/CAGnEbogATZS1mWMVX8FzZHMXzuDEcb10AnVwwhCtXtiBpg3XLQ@mail.gmail.com https://www.postgresql.org/message-id/CAGnEbogATZS1mWMVX8FzZ... The Postgres 13 baseline for the benchmark/test case (actually HEAD before the patch was committed, but close enough to 13) showed that certain indexes grew by 20% - 60% over several hours. That went down to 0.5% growth over the same period. The index growth much more predictable in that it matches what you'd expect for this workload if you thought about it from first principles. In other words, you'd expect about the same low amount of index growth if you were using a traditional two-phase locking database that doesn't use MVCC at all. Full disclosure: I am the author of this feature.
  - edoceo 5y ago
    Thank you!!
  - dimgl 5y ago
    Wow, this is actually incredible. One of my biggest gripes with Postgres is going to be solved. Thank you for sending this over!
    - petergeoghegan 5y ago
      Thanks. I forgot to mention that the test case had constant long-running transactions, each lasting 5 minutes. Over a 4 hour period for each tested configuration. This level of improvement was possible by adding a relatively simple mechanism because the costs are incredibly nonlinear once you think about them holistically, and consider how things change over time. The general idea behind bottom-up index deletion is that we let the workload figure out what cleanup is required on its own, in an incremental fashion. Another interesting detail is that there is synergy with the deduplication stuff -- again, very nonlinear behavior. Kind of organic, even. Deduplication was a feature that I coauthored with Anastasia Lubennikova that appeared in Postgres 13.
vosper 5y ago
> Do any other entrenched software projects come to mind? Elasticsearch is underrated here, IMO. Yes, there are alternatives for simple fulltext search. But there’s a lot more it can do (adhoc aggregations incorporating complex fulltext searches, with custom scripted components; geospatial; index lifecycle management) and if you’re using those features, there’s nothing else comparable. It’s pretty stable, too, once you’ve got the cluster configured. We don’t have outages due to problems with Elasticsearch.
- jeff-davis 5y ago
  I don't know about elasticsearch specifically, but I'm skeptical of special-purpose systems for databases. They are great in some cases and terrible in others, and over time, use cases push database systems into their worst cases. Use cases rarely stay in the sweet spot of a special-purpose system. That being said, if the integration is great, and/or the special system is a secondary one (fed from a general-purpose system), then it's often fine.
  - vosper 5y ago
    I’m not sure I fully understand your comment (databases that are special-purpose and evolve out of a sweet spot, or special-purpose systems using databases in worst-case ways?). I certainly wouldn’t say ES is the former. We use it for some conplex things that (AFAIK) no other (publicly available; I don’t what eg Twitter or Google has going on) system could provide at the scale we need. Everything we’re doing is well within the realm of what ES is built for, and it’s the only system built for it. It’s not perfect, but most of our performance issues could be solved by scaling out, where query or index optimization isn’t tractable.
    - jeff-davis 5y ago
      I interpreted (misinterpreted?) your comment to be suggesting ES for wider use cases.
- bradleyjg 5y ago
  It’s frustrating to need a run-time team for a piece of infrastructure, especially one sold as IaaS. It’s totally understandable that you’d need developers to have expertise in patterns and anti-patterns, as well as needing an expert to set things up in the first place, but you shouldn’t have to have a dedicated ES monitoring / tuning / babysitting team like Oracle DBAs of yore. That you do, means it isn’t there yet as a product.
stickfigure 5y ago
I'm an enormous fan of Postgres, it's my default go-to RDBMS. But the memory expense of connections is a huge issue and this article doesn't convince me that it's solved. The machine being used for this benchmark has 96 vCPUs, 192G of RAM, and costs $3k/mo. My business runs just fine on a 3.75G, 1 vCPU instance. But idle connections eat up a huge amount of RAM and I sometimes find myself hitting the limits when a load spike spins up extra frontend instances. Sure I could probably setup pgbouncer and some other tools but that's a lot of headache. I'm acutely aware that MySQL (which I dislike because no transactional DDL) does not suffer from this issue. I also don't see this being solved without a major rewrite, which seems unlikely. So Postgres has at least one very serious fault that makes room in the marketplace. The poor replication story is another.
- btbuilder 5y ago
  I agree - the disparity between the cost of idle connections in Postgres vs MSSQL is hampering our ability to migrate.
  - pgaddict 5y ago
    Can you elaborate / quantify the memory requirements a bit? I don't have much experience with MSQQL in this respect, so I'm curious how big the difference is.
    - btbuilder 5y ago
      Sure, SQL Server supports a maximum of 32767 connections each of which use around 128kB. Meaning that if you use the max connections you’ll need 4GB for the connection overhead. We see no noticeable drop in performance with increased idle connection with our workload.
  - gher-shyu3i 5y ago
    Why are you migrating out of curiosity? Price reasons?
    - 5y ago
jeff-davis 5y ago
I like to say that "Postgres is a great default". It's generally very good, and also very adaptable to special purposes, so it covers a wide range of use cases. But saying "so much better" is too strong.
- threeseed 5y ago
  It's the inevitable circlejerk we get with every PostgreSQL post on HN. Which is a shame because it means the legitimate and serious faults (i.e. lack of native HA/clustering) just get waved away.
jasonwatkinspdx 5y ago
There's a ton of room for improvement in the architecture of relational databases. This isn't a dig against Postgres, or ignoring how difficult it will be to get a new system to the same level of maturity. But databases designed natively for cloud/clustering, SSDs, (pmem soon perhaps), etc are quite a bit different. There's enormous simplifications and performance gains possible. There's been a lot of exciting work in this area over the last decade or so. Andy Pavlo's classes are great surveys of the latest work: https://15721.courses.cs.cmu.edu/spring2020/ https://15721.courses.cs.cmu.edu/spring2020/ CosmosDB is an example of a relational (multi paradigm properly) database with a quite different architecture vs the classic design, that's moved into production status quite rapidly. FaunaDB and CockroachDB are moving with solid momentum too.
- gogopuppygogo 5y ago
  Cockroach is the worst brand for a database ever. Even Croach would be a massive branding improvement. This is similar to how gimp is a terrible brand.
  - meesterdude 5y ago
    I mean... the WORST? For me Mongo takes the cake, but oracle is up there too.
    - zdragnar 5y ago
      Really? Oracle actually makes a lot of sense to me for a database name (in the 'source of truth' sense, not in the prophet sense). Mongo, on the other hand, has definitely always had the racist/ablist slur as the first connotation for me.
      - whatshisface 5y ago
        I've learned almost all the slurs I know from comments or media sources complaining about them. It's the only place they're used in polite society.
jpeter 5y ago
What exactly makes Postgres better than MySql? There seem to be certain design decisions like WAL or process per connection that cause problems at scale https://eng.uber.com/postgres-to-mysql-migration/ https://eng.uber.com/postgres-to-mysql-migration/
- Tostino 5y ago
  That article really isn't a good critique of Postgres.

jjeaff 5y ago

MySQL 8 is not that far behind in feature parity. And is ahead when it comes to scalability. So I don't see postgres as necessarily standing alone.

paozac 5y ago
MySQL's lack of DDL transactions is a serious shortcoming.
ezekiel68 5y ago
You claim that MySQL 8 is ahead when it comes to scalability. What are the bases of this claim? When I see comparisons or entire systems that rely on a database (that is, not micro-benchmarks) such as the TechEmpower web framework benchmarks [0] , I notice that the 'Pg' results cluster near the top, with the "My" results showing up further down the rankings. I understand this isn't version 14 of the former versus version 8 of the latter. But it makes me wonder what the basis of your claims is. [0] https://www.techempower.com/benchmarks/ https://www.techempower.com/benchmarks/
- isbvhodnvemrwvn 5y ago
  Aren't those run on a single node DB server? And the queries don't really seem realistic at all, e.g. single query test fetches 1 out of 10 000 rows, with no joins at all. Fortunes fetches 1 out of 10 rows. This seems extremely trivial.
- merb 5y ago
  well if you need more than one server, mysql has vitess, which is huge. postgres has citus, but that is way more complex to setup than vitess. I still would never use mysql, just because of vitess.
  - deleted 5y ago
    [deleted]
- the_duke 5y ago
  Techempower is not a database benchmark. The tests that involve a DB exist to include a DB client in the request flow, not to put any serious load on the database.
purerandomness 5y ago
No DDL transactions, no materialized views, the list is endless. There's almost no reason to pick MySQL for a new project.
- tfigment 5y ago
  MySQL and mariadb have first class temporal tables. Pg has compile requirement and so cannot use in AWS RDS.
  - dragonwriter 5y ago
    > MySQL and mariadb have first class temporal tables. Pg has compile requirement and so cannot use in AWS RDS. There’s a pl/pgsql reimplementation of temporal tables specifically for that use case.
    - phonon 5y ago
      https://news.ycombinator.com/item?id=26768220 https://news.ycombinator.com/item?id=26768220
  - lowercased 5y ago
    I was aware maria had temporary tables, but not mysql proper. Any links you can point me to? Every search is coming up with 'temporary' table info, not temporal.
  - lowercased 5y ago
    mysql8 has gis/spatial stuff built in now. may not quite be on par with postgis, but... i also don't have to futz with "doesn't come baked in". Dealt with someone who wrote a whole bunch of lat/lon/spatial stuff in client code because we're on postgres but ... he couldn't get postgis installed (then even if he could, figuring out how to convince the ops people to add a new 'thing' in production would have been a delay). having stuff baked in is often a win.
- aseipp 5y ago
  MySQL has transactions for DDL changes since 8.0.
ksec 5y ago
Are there any Roadmap for MySQL 9 ?

yakubin 5y ago

Fortran for linear algebra software. Excel for business spreadsheets. Java for enterprise server software.

Supermancho 5y ago
> Java for enterprise server software. Big corporations are horribly inefficient and Enterprise Software necessarily so from that...if you're saying Java is terrible by nature of it being the goto for enterprise, then that makes sense. It took 20 years for it to swap places with COBOL and I expect it will be something else in 20 more.
- yakubin 5y ago
  I don't work with Java, but I can think of a few advantages off the top of my head: - appreciation of backwards-compatibility (here it wins with Python); - great debuggers and performance tools (e.g. Java Flight Recorder or Eclipse Memory Analyzer); - easy deployment - you can just give someone a fat JAR (here it wins with all scripting languages, so Python, Ruby, PHP, or any other flavour of the month); - industry-grade garbage collectors; - publicly-available standard spec (here it wins with all the defined-by-implementation languages such as Python, PHP, Rust, basically most languages, and with languages which are standardized, but their specs aren't public: C, C++, Ruby); - kind of like the previous point, but anyway: multiple implementations to choose from; - I've been told it has good performance. I've never seen a real-world Java application which felt fast, but I've heard people put it at the pedestal and the Debian programming languages benchmarks game seems to corroborate that story; Besides, the question wasn't about which technologies we like, but which we believe are entrenched so much, they aren't going to go away for a very long time. I don't see Java going away for another 100 years, no matter how much I would or wouldn't like to work with it.
  - radicalbyte 5y ago
    - Fantastic battle tested ecosystem of libraries. - Stable cross platform (kills Python, Node here). - Lingua franca. Now I personally don't like Java - it feels crusty vs C# - but the libraries are amazing. You can also use something nice like Kotlin and you have all of the platform benefits with non of the crusty language issues.

sweeneyrod 5y ago

I think many mercurial users would disagree with you about git.

jayd16 5y ago
Are we talking about market dominance, mind share or the idea that there's no real competition? MySQL and Oracle exist. Mercurial and perforce exist. I'm not sure it's a terrible stretch to compare git and postures.
- aidenn0 5y ago
  I think the point is that git isn't "so much better" than mercurial, while pgsql has had a lead on mysql for quite some time on a lot of technical measurements.
  - polskibus 5y ago
    Postgresql does not have real, maintained with each change, clustered index. That itself makes it worse for many workloads than MySQL
    - glogla 5y ago
      "Is table a heap with indexes on the side or is table a tree with other indexes on the side (i.e. 'clustered index')" is a more complicated discussion. The former makes it possible to have MVCC (and thus gives you snapshot isolation and serializability) and makes secondary indexes perform faster, at the cost of vacuum or Oracle-style redo/undo/rollback segments with associated "Snapshot too old" issues. The latter pretty much forces use of locking even for read so queries block each other (but don't require vacuum or something), makes clustering key selective queries perform faster than secondary index ones and makes you think really hard about the clustering key. It's not really a feature you would have, but a complicated design tradeoff.
    - petergeoghegan 5y ago
      I would say that that's pretty dubious claim with modern versions of Postgres and MySQL/InnoDB, running on modern hardware. See for example this recent comparative Benchmark from Mark Callaghan, a well known member of the MySQL community: https://smalldatum.blogspot.com/2021/01/sysbench-postgres-vs-mysql-and-impact.html https://smalldatum.blogspot.com/2021/01/sysbench-postgres-vs... I'm not claiming that this benchmark justifies the claim that Postgres broadly performs better than MySQL/InnoDB these days -- that would be highly simplistic. Just as it would be simplistic to claim that MySQL is clearly well ahead with OLTP stuff in some kind of broad and entrenched way. It's highly dependent on workload. Note that Postgres really comes out ahead on a test called "update-index", which involves updates that modify indexed columns -- the write amplification is much worse on MySQL there. This is precisely the opposite of what most commentators would have predicted. Including (and perhaps even especially) Postgres community people.

autodeadmehaha 5y ago

Anyone whose ever had to upgrade postgres ever knows postgres can't fail fast enough. They must fix their upgrade paths and it's endless means to completely fuck you if they want to be taken seriously.

yjftsjthsd-h 5y ago
? What's wrong with pg_upgrade?

ranit 5y ago

> Do any other entrenched software projects come to mind? SQLite.

IshKebab 5y ago
I'm pretty hopeful that DuckDB will replace some of the use of SQLite. SQLite is great but it sucks that it's entirely dynamically typed (the types specified for columns are completely ignored).
- dragonwriter 5y ago
  > the types specified for columns are completely ignored They aren’t constraints (except in the case of “INTEGER PRIMARY KEY”), but they also aren’t “completely ignored”, because of type affinity.

fibers 5y ago

i had to roll back to 9.6 on windows because \COPY is fundamentally broken for large cvs

CapriciousCptl 5y ago
What's the issue? Just on Windows? Mac OS X with 13.2 has no issue for me with the 1.1gigabyte 20million record csv just imported last week, or some bigger ones I did a few months back.
- tpxl 5y ago
  Same here, 500MB, 10 million row csv file with no issues on Postgres 11.8.

dilyevsky 5y ago

Kubernetes when it comes to clustering.

eterm 5y ago

I think anyone who has worked a lot with MSSQL would disagree with Postgres being "so much better". It's only really in the last few years that postgres has pulled ahead, MSSQL was lot more feature rich and performant for a decade.

harikb 5y ago
MSSQL ? As in Microsoft SQL Server? I have heard this argument a lot and all the comparisons I have seen are specific benchmarks on specialized hardware. My own personal experience wasn’t anything like the benchmarks
- sigzero 5y ago
  By "few years" he has to mean 10 to 15 years. ;)
- HideousKojima 5y ago
  MSSQL still has a few features that set it apart from Postgres. Off the top of my head are Filestream (basically storing files in the database while still having them accessible as files on the filesystem) and temporal tables without the need for extensions. Personally if I were choosing the tech stack for my company I'd still go for Postgres though
  - johnthescott 5y ago
    pg has had file fw for years: https://www.postgresql.org/docs/13/file-fdw.html https://www.postgresql.org/docs/13/file-fdw.html

dimgl 5y ago

Postgres is good, even great, but this is hyperbole. Postgres has its downsides, autovacuum being one of them.

petergeoghegan 5y ago
Although the article doesn't mention it, index bloat will be far better controlled in Postgres 14: https://www.postgresql.org/docs/devel/btree-implementation.html#BTREE-DELETION https://www.postgresql.org/docs/devel/btree-implementation.h... One benchmark involving a mix of queue-like inserts, updates, and deletes showed that it was practically 100% effective at controlling index bloat: https://www.postgresql.org/message-id/CAGnEbogATZS1mWMVX8FzZHMXzuDEcb10AnVwwhCtXtiBpg3XLQ@mail.gmail.com https://www.postgresql.org/message-id/CAGnEbogATZS1mWMVX8FzZ... The Postgres 13 baseline for the benchmark/test case (actually HEAD before the patch was committed, but close enough to 13) showed that certain indexes grew by 20% - 60% over several hours. That went down to 0.5% growth over the same period. The index growth much more predictable in that it matches what you'd expect for this workload if you thought about it from first principles. In other words, you'd expect about the same low amount of index growth if you were using a traditional two-phase locking database that doesn't use MVCC at all. Full disclosure: I am the author of this feature.
- edoceo 5y ago
  Thank you!!
- dimgl 5y ago
  Wow, this is actually incredible. One of my biggest gripes with Postgres is going to be solved. Thank you for sending this over!
  - petergeoghegan 5y ago
    Thanks. I forgot to mention that the test case had constant long-running transactions, each lasting 5 minutes. Over a 4 hour period for each tested configuration. This level of improvement was possible by adding a relatively simple mechanism because the costs are incredibly nonlinear once you think about them holistically, and consider how things change over time. The general idea behind bottom-up index deletion is that we let the workload figure out what cleanup is required on its own, in an incremental fashion. Another interesting detail is that there is synergy with the deduplication stuff -- again, very nonlinear behavior. Kind of organic, even. Deduplication was a feature that I coauthored with Anastasia Lubennikova that appeared in Postgres 13.

vosper 5y ago

> Do any other entrenched software projects come to mind? Elasticsearch is underrated here, IMO. Yes, there are alternatives for simple fulltext search. But there’s a lot more it can do (adhoc aggregations incorporating complex fulltext searches, with custom scripted components; geospatial; index lifecycle management) and if you’re using those features, there’s nothing else comparable. It’s pretty stable, too, once you’ve got the cluster configured. We don’t have outages due to problems with Elasticsearch.

jeff-davis 5y ago
I don't know about elasticsearch specifically, but I'm skeptical of special-purpose systems for databases. They are great in some cases and terrible in others, and over time, use cases push database systems into their worst cases. Use cases rarely stay in the sweet spot of a special-purpose system. That being said, if the integration is great, and/or the special system is a secondary one (fed from a general-purpose system), then it's often fine.
- vosper 5y ago
  I’m not sure I fully understand your comment (databases that are special-purpose and evolve out of a sweet spot, or special-purpose systems using databases in worst-case ways?). I certainly wouldn’t say ES is the former. We use it for some conplex things that (AFAIK) no other (publicly available; I don’t what eg Twitter or Google has going on) system could provide at the scale we need. Everything we’re doing is well within the realm of what ES is built for, and it’s the only system built for it. It’s not perfect, but most of our performance issues could be solved by scaling out, where query or index optimization isn’t tractable.
  - jeff-davis 5y ago
    I interpreted (misinterpreted?) your comment to be suggesting ES for wider use cases.
bradleyjg 5y ago
It’s frustrating to need a run-time team for a piece of infrastructure, especially one sold as IaaS. It’s totally understandable that you’d need developers to have expertise in patterns and anti-patterns, as well as needing an expert to set things up in the first place, but you shouldn’t have to have a dedicated ES monitoring / tuning / babysitting team like Oracle DBAs of yore. That you do, means it isn’t there yet as a product.

stickfigure 5y ago

I'm an enormous fan of Postgres, it's my default go-to RDBMS. But the memory expense of connections is a huge issue and this article doesn't convince me that it's solved. The machine being used for this benchmark has 96 vCPUs, 192G of RAM, and costs $3k/mo. My business runs just fine on a 3.75G, 1 vCPU instance. But idle connections eat up a huge amount of RAM and I sometimes find myself hitting the limits when a load spike spins up extra frontend instances. Sure I could probably setup pgbouncer and some other tools but that's a lot of headache. I'm acutely aware that MySQL (which I dislike because no transactional DDL) does not suffer from this issue. I also don't see this being solved without a major rewrite, which seems unlikely. So Postgres has at least one very serious fault that makes room in the marketplace. The poor replication story is another.

btbuilder 5y ago
I agree - the disparity between the cost of idle connections in Postgres vs MSSQL is hampering our ability to migrate.
- pgaddict 5y ago
  Can you elaborate / quantify the memory requirements a bit? I don't have much experience with MSQQL in this respect, so I'm curious how big the difference is.
  - btbuilder 5y ago
    Sure, SQL Server supports a maximum of 32767 connections each of which use around 128kB. Meaning that if you use the max connections you’ll need 4GB for the connection overhead. We see no noticeable drop in performance with increased idle connection with our workload.
- gher-shyu3i 5y ago
  Why are you migrating out of curiosity? Price reasons?
  - 5y ago

jeff-davis 5y ago

I like to say that "Postgres is a great default". It's generally very good, and also very adaptable to special purposes, so it covers a wide range of use cases. But saying "so much better" is too strong.

threeseed 5y ago
It's the inevitable circlejerk we get with every PostgreSQL post on HN. Which is a shame because it means the legitimate and serious faults (i.e. lack of native HA/clustering) just get waved away.

jasonwatkinspdx 5y ago

There's a ton of room for improvement in the architecture of relational databases. This isn't a dig against Postgres, or ignoring how difficult it will be to get a new system to the same level of maturity. But databases designed natively for cloud/clustering, SSDs, (pmem soon perhaps), etc are quite a bit different. There's enormous simplifications and performance gains possible. There's been a lot of exciting work in this area over the last decade or so. Andy Pavlo's classes are great surveys of the latest work: https://15721.courses.cs.cmu.edu/spring2020/ https://15721.courses.cs.cmu.edu/spring2020/ CosmosDB is an example of a relational (multi paradigm properly) database with a quite different architecture vs the classic design, that's moved into production status quite rapidly. FaunaDB and CockroachDB are moving with solid momentum too.

gogopuppygogo 5y ago
Cockroach is the worst brand for a database ever. Even Croach would be a massive branding improvement. This is similar to how gimp is a terrible brand.
- meesterdude 5y ago
  I mean... the WORST? For me Mongo takes the cake, but oracle is up there too.
  - zdragnar 5y ago
    Really? Oracle actually makes a lot of sense to me for a database name (in the 'source of truth' sense, not in the prophet sense). Mongo, on the other hand, has definitely always had the racist/ablist slur as the first connotation for me.
    - whatshisface 5y ago
      I've learned almost all the slurs I know from comments or media sources complaining about them. It's the only place they're used in polite society.

jpeter 5y ago

What exactly makes Postgres better than MySql? There seem to be certain design decisions like WAL or process per connection that cause problems at scale https://eng.uber.com/postgres-to-mysql-migration/ https://eng.uber.com/postgres-to-mysql-migration/

Tostino 5y ago
That article really isn't a good critique of Postgres.