7 ms·

Don’t Assume PostgreSQL Is Slow

77 points by craigkerstiens 11y ago

bigdubs 11y ago
I'm not sure where this mantra that pgsql is slow comes from. My sense is it's more from the monolithic nature of a typical install which makes it difficult to scale horizontally to handle supreme amounts of load. For most development use cases, and even most production use cases, pgsql is plenty fast for almost everything if you design your schema well and leverage things like indexes.
- ubercore 11y ago
  I think the impression lingers from the early days when MySQL with myisam was the scaling "secret sauce" for everyone, because it leaves behind so many features. I don't know that MySQL was ever significantly faster after InnoDB became the engine most people used.
  - davidw 11y ago
    Mysql was fast in the same way that a bicycle going down a hill without brakes is 'fast'.
    - meritt 11y ago
      You understand this is the sort of ignorant rhetoric that leads to the OP's post, right? InnoDB has been available for years and has been the default storage engine for nearly 6 years. Maria and Percona both default to XtraDB. The vast majority of MySQL criticisms haven't been valid for nearly a decade.
      - davidw 11y ago
        You understand "was", don't you? Mysql really took hold around 2000. Postgres was a perfectly valid option back then, too, but... "fast!". InnoDB still doesn't do DDL transactions, does it? That said, clearly Mysql has gotten more robust and Postgres faster.
- rdtsc 11y ago
  > I'm not sure where this mantra that pgsql is slow comes from. MongoDB factor is probably why from a few years back. Basically MongoDB had defaults that threw data over the wall and didn't send acknowledgement back to the client, then relied on dirty pages flushing to disk in the background. Well it turns out if you don't actually write your data to disk, you get really nice benchmark results. ( Therefore the MongoDB is a webscale phenomenon with the obligatory famous video: https://www.youtube.com/watch?v=b2F-DItXtZs https://www.youtube.com/watch?v=b2F-DItXtZs ) So a lot of people who didn't know how things work were impressed by sending data to /dev/null at high speed and saw postgres as slow and uncool. It was of course debunked since then but the vibe kind of hung around.
  - jfb 11y ago
    It's older than that. Postgres used to be compared directly with MySQL, back when ISAM was MySQL's "storage" engine. It was definitely slower -- of course it was doing more -- but it's really never been slow. But memes are difficult to kill.
- gdulli 11y ago
  Going back to the late 90s, early 00s there was this idea that MySQL is fast and Postgres is slow. It's not accurate or even relevant anymore because MyISAM is how MySQL got a reputation for being fast, but that's no longer the default table type and people know by now that it's not a good default practice. And Postgres has improved significantly. And proper index use isn't an arcane art anymore. It's been many years since this has been accurate and also many years since this was as widely believed as the author of this article believes. Also, while speed no longer differentiates these databases, it should rarely if ever have been the primary factor in choosing one. (Or in avoiding Postgres in general.)
- chubot 11y ago
  It's because SQL has no performance model. You can't tell by looking at the code (the query) what the performance will be like. For example, you can speed up or slow down code by orders of magnitude by changing a line in a completely different part of the code base (i.e. adding a index). And it's also not an orthogonal change -- increasing your read speed decreases your write speed. The common case for business apps is suboptimal schemas and missing indices. Postgres is fast enough, but it's not trivial to use well. On the other hand, anyone who has programmed in Python or Ruby pretty much has an intuitive feel for how well Redis operations perform. With SQL you get these wild swings, depending on what the query optimizer does. I'm not saying you should use Redis -- it's worth it to learn how to use Postgres. But there's just a lower barrier to entry for non-SQL solutions.
  - acdha 11y ago
    > On the other hand, anyone who has programmed in Python or Ruby pretty much has an intuitive feel for how well Redis operations perform. … have you ever seen that not inevitably lead to wild performance swings as people reinvent SQL in their favorite abstraction layer? The same kind of developers who used to make an ORM demand-load a million objects to calculate a number are now doing that with Redis, Mongo, DynamoDB, etc. and telling themselves that it's okay for it to take 5 minutes to sum a million records because it's Big Data™️. I would posit that you're more likely to see good performance from a database engine with many thousands of engineer-hours poured into optimizations than what the average developer is going to produce reinventing a reporting language in Ruby/Python. Those same developers who can't add an index on a SQL database are not suddenly going to figure out how to maintain denormalized records in Redis.
    - crdoconnor 11y ago
      >telling themselves that it's okay for it to take 5 minutes to sum a million records because it's Big Data If it's the kind of query you run every week, it probably is okay. I've not optimized tons of queries like that before - and prevented other developers from doing so - not because it's "big data", but simply because the risk introduced by changing the code and the effort involved in mitigating that risk simply didn't make the optimization worthwhile. In most cases I never would have built it myself from the ground up in such a way that it would take 5 minutes (e.g. without indexes), but somebody else's dumb system that's running in production and solving real human problems in an entirely acceptable time frame is still code that's solving people's problems. And, changes always come with a higher cost and risk than most developers imagine - especially in big systems.
malkia 11y ago
FYI: The source code to the nextval() function: https://github.com/postgres/postgres/blob/71fc49dfe1d99bd83cd99c2e7a39f93e07d19310/src/backend/commands/sequence.c#L498 https://github.com/postgres/postgres/blob/71fc49dfe1d99bd83c...
herf 11y ago
Redis can do a lot more than 12k operations/sec. Bottleneck is not the backend here at all.
- arielweisberg 11y ago
  I am guessing this is a single threaded benchmark. Performance concerns in practice are much more complicated than "What is the average response time for a single thread on one operation?"
Herald_MJ 11y ago
Postgres isn't slow. But this is a very specific example that doesn't actually prove anything either way.
andy_ppp 11y ago
There are valid reasons to distribute load between redis and your database, per query performance ranks pretty low compared to your overall database load, for example.
mbell 11y ago
> Not really (test on an Amazon i2-2xlarge instance, client over local sockets, Python client) This is a flawed benchmark setup with little connection to the real world.
- imaginenore 11y ago
  No, it's the right way to do it. This way you don't pollute your benchmark with random things like connection speed, connection pool performance, networking issues, etc.
  - deleted 11y ago
    [deleted]
imaginenore 11y ago
The biggest problem with this benchmark is it being single threaded. Redo it with 32 or 256 concurrent (local) requests and see what happens. I bet Redis will end up being quite a bit quicker.
fishtoaster 11y ago
My fun postgres story is the time I spent a week learning and using CouchDB to do some operations across five to eight million tweets. I mean, surely a relational db is ill-suited to something like that, right? After no small amount of frustration, I just threw a cleaned-up version of each tweet into postgres and did a `LIKE` query across all of them. It returned in, iirc, less than a second (vs nearly a minute for couch). The lesson, of course, is not that couch is bad and postgres is great, but rather as the OP said: "Human intuition about what might be slow is almost certainly wrong."
- astine 11y ago
  I once built a location aware application on MongoDB. After months of Mongo being the bottleneck in the application and periodically crashing, I switched to a naive implementation of the application with PostgreSQL and the application sped up five-fold. I've learned my lesson on Mongo. Postgres, however, has always been pretty awesome.
  - DrJosiah 11y ago
    Postgres with its Geo extensions are also pretty solid.
- theandrewbailey 11y ago
  > Human intuition about what might be slow is almost certainly wrong. He got that right. Try it for yourself: https://computers-are-fast.github.io/ https://computers-are-fast.github.io/
- gdulli 11y ago
  Not only was that a good outcome because 5m-8m isn't "big", there's even a more performant way to do real text search within Postgres that would let you scale far beyond that number before outgrowing it.
  - Johnny555 11y ago
    Whether or not full text search is more scalable than a full table scan depends on the use case - if he's continually inserting and deleting rows from his table while doing relatively few queries, it may very well be more scalable to do the full table scans to avoid the continual overhead of maintaining the full-text index on inserts and deletes.
deleted 11y ago
[deleted]
deleted 11y ago
[deleted]
jamiesonbecker 11y ago
It sounds like this was more of a tip to always test your assumptions, rather than an serious attempt as a benchmark. It does its job in that respect, but there's so much more to both of these servers than the test case. It sounds like OP wrote a for loop in Python but was surprised that the Redis one was only marginally faster. Nearly all of the speed loss would have been in that loop, not in either server. Both servers are so fast that all of the latency was in the mechanics of a sequential for loop. Postgresql is awesome for so many things, and certainly so is Redis, but those are really two distinct sets with very little intersection. Knowing when to use each is something that a good architect intuitively grasps. (these two in particular complement each other very well.) Always test your assumptions within your specific use case, and don't embark on performance optimizations until you've solved all of your other requirements first.

I'm not sure where this mantra that pgsql is slow comes from. My sense is it's more from the monolithic nature of a typical install which makes it difficult to scale horizontally to handle supreme amounts of load. For most development use cases, and even most production use cases, pgsql is plenty fast for almost everything if you design your schema well and leverage things like indexes.

ubercore 11y ago
I think the impression lingers from the early days when MySQL with myisam was the scaling "secret sauce" for everyone, because it leaves behind so many features. I don't know that MySQL was ever significantly faster after InnoDB became the engine most people used.
- davidw 11y ago
  Mysql was fast in the same way that a bicycle going down a hill without brakes is 'fast'.
  - meritt 11y ago
    You understand this is the sort of ignorant rhetoric that leads to the OP's post, right? InnoDB has been available for years and has been the default storage engine for nearly 6 years. Maria and Percona both default to XtraDB. The vast majority of MySQL criticisms haven't been valid for nearly a decade.
    - davidw 11y ago
      You understand "was", don't you? Mysql really took hold around 2000. Postgres was a perfectly valid option back then, too, but... "fast!". InnoDB still doesn't do DDL transactions, does it? That said, clearly Mysql has gotten more robust and Postgres faster.
rdtsc 11y ago
> I'm not sure where this mantra that pgsql is slow comes from. MongoDB factor is probably why from a few years back. Basically MongoDB had defaults that threw data over the wall and didn't send acknowledgement back to the client, then relied on dirty pages flushing to disk in the background. Well it turns out if you don't actually write your data to disk, you get really nice benchmark results. ( Therefore the MongoDB is a webscale phenomenon with the obligatory famous video: https://www.youtube.com/watch?v=b2F-DItXtZs https://www.youtube.com/watch?v=b2F-DItXtZs ) So a lot of people who didn't know how things work were impressed by sending data to /dev/null at high speed and saw postgres as slow and uncool. It was of course debunked since then but the vibe kind of hung around.
- jfb 11y ago
  It's older than that. Postgres used to be compared directly with MySQL, back when ISAM was MySQL's "storage" engine. It was definitely slower -- of course it was doing more -- but it's really never been slow. But memes are difficult to kill.
gdulli 11y ago
Going back to the late 90s, early 00s there was this idea that MySQL is fast and Postgres is slow. It's not accurate or even relevant anymore because MyISAM is how MySQL got a reputation for being fast, but that's no longer the default table type and people know by now that it's not a good default practice. And Postgres has improved significantly. And proper index use isn't an arcane art anymore. It's been many years since this has been accurate and also many years since this was as widely believed as the author of this article believes. Also, while speed no longer differentiates these databases, it should rarely if ever have been the primary factor in choosing one. (Or in avoiding Postgres in general.)
chubot 11y ago
It's because SQL has no performance model. You can't tell by looking at the code (the query) what the performance will be like. For example, you can speed up or slow down code by orders of magnitude by changing a line in a completely different part of the code base (i.e. adding a index). And it's also not an orthogonal change -- increasing your read speed decreases your write speed. The common case for business apps is suboptimal schemas and missing indices. Postgres is fast enough, but it's not trivial to use well. On the other hand, anyone who has programmed in Python or Ruby pretty much has an intuitive feel for how well Redis operations perform. With SQL you get these wild swings, depending on what the query optimizer does. I'm not saying you should use Redis -- it's worth it to learn how to use Postgres. But there's just a lower barrier to entry for non-SQL solutions.
- acdha 11y ago
  > On the other hand, anyone who has programmed in Python or Ruby pretty much has an intuitive feel for how well Redis operations perform. … have you ever seen that not inevitably lead to wild performance swings as people reinvent SQL in their favorite abstraction layer? The same kind of developers who used to make an ORM demand-load a million objects to calculate a number are now doing that with Redis, Mongo, DynamoDB, etc. and telling themselves that it's okay for it to take 5 minutes to sum a million records because it's Big Data™️. I would posit that you're more likely to see good performance from a database engine with many thousands of engineer-hours poured into optimizations than what the average developer is going to produce reinventing a reporting language in Ruby/Python. Those same developers who can't add an index on a SQL database are not suddenly going to figure out how to maintain denormalized records in Redis.
  - crdoconnor 11y ago
    >telling themselves that it's okay for it to take 5 minutes to sum a million records because it's Big Data If it's the kind of query you run every week, it probably is okay. I've not optimized tons of queries like that before - and prevented other developers from doing so - not because it's "big data", but simply because the risk introduced by changing the code and the effort involved in mitigating that risk simply didn't make the optimization worthwhile. In most cases I never would have built it myself from the ground up in such a way that it would take 5 minutes (e.g. without indexes), but somebody else's dumb system that's running in production and solving real human problems in an entirely acceptable time frame is still code that's solving people's problems. And, changes always come with a higher cost and risk than most developers imagine - especially in big systems.

malkia 11y ago

FYI: The source code to the nextval() function: https://github.com/postgres/postgres/blob/71fc49dfe1d99bd83cd99c2e7a39f93e07d19310/src/backend/commands/sequence.c#L498 https://github.com/postgres/postgres/blob/71fc49dfe1d99bd83c...

herf 11y ago

Redis can do a lot more than 12k operations/sec. Bottleneck is not the backend here at all.

arielweisberg 11y ago
I am guessing this is a single threaded benchmark. Performance concerns in practice are much more complicated than "What is the average response time for a single thread on one operation?"

Herald_MJ 11y ago

Postgres isn't slow. But this is a very specific example that doesn't actually prove anything either way.

andy_ppp 11y ago

There are valid reasons to distribute load between redis and your database, per query performance ranks pretty low compared to your overall database load, for example.

mbell 11y ago

> Not really (test on an Amazon i2-2xlarge instance, client over local sockets, Python client) This is a flawed benchmark setup with little connection to the real world.

imaginenore 11y ago
No, it's the right way to do it. This way you don't pollute your benchmark with random things like connection speed, connection pool performance, networking issues, etc.
- deleted 11y ago
  [deleted]

imaginenore 11y ago

The biggest problem with this benchmark is it being single threaded. Redo it with 32 or 256 concurrent (local) requests and see what happens. I bet Redis will end up being quite a bit quicker.

fishtoaster 11y ago

My fun postgres story is the time I spent a week learning and using CouchDB to do some operations across five to eight million tweets. I mean, surely a relational db is ill-suited to something like that, right? After no small amount of frustration, I just threw a cleaned-up version of each tweet into postgres and did a `LIKE` query across all of them. It returned in, iirc, less than a second (vs nearly a minute for couch). The lesson, of course, is not that couch is bad and postgres is great, but rather as the OP said: "Human intuition about what might be slow is almost certainly wrong."

astine 11y ago
I once built a location aware application on MongoDB. After months of Mongo being the bottleneck in the application and periodically crashing, I switched to a naive implementation of the application with PostgreSQL and the application sped up five-fold. I've learned my lesson on Mongo. Postgres, however, has always been pretty awesome.
- DrJosiah 11y ago
  Postgres with its Geo extensions are also pretty solid.
theandrewbailey 11y ago
> Human intuition about what might be slow is almost certainly wrong. He got that right. Try it for yourself: https://computers-are-fast.github.io/ https://computers-are-fast.github.io/
gdulli 11y ago
Not only was that a good outcome because 5m-8m isn't "big", there's even a more performant way to do real text search within Postgres that would let you scale far beyond that number before outgrowing it.
- Johnny555 11y ago
  Whether or not full text search is more scalable than a full table scan depends on the use case - if he's continually inserting and deleting rows from his table while doing relatively few queries, it may very well be more scalable to do the full table scans to avoid the continual overhead of maintaining the full-text index on inserts and deletes.

deleted 11y ago

[deleted]

deleted 11y ago

[deleted]

jamiesonbecker 11y ago

It sounds like this was more of a tip to always test your assumptions, rather than an serious attempt as a benchmark. It does its job in that respect, but there's so much more to both of these servers than the test case. It sounds like OP wrote a for loop in Python but was surprised that the Redis one was only marginally faster. Nearly all of the speed loss would have been in that loop, not in either server. Both servers are so fast that all of the latency was in the mechanics of a sequential for loop. Postgresql is awesome for so many things, and certainly so is Redis, but those are really two distinct sets with very little intersection. Knowing when to use each is something that a good architect intuitively grasps. (these two in particular complement each other very well.) Always test your assumptions within your specific use case, and don't embark on performance optimizations until you've solved all of your other requirements first.