14 ms·

PostgreSQL displays an amazing and rare combination in _any_ software, much less a free (beer/libre) product: reliable, predictable performance and usability fo

by rcoder 12y ago

PostgreSQL displays an amazing and rare combination in _any_ software, much less a free (beer/libre) product: reliable, predictable performance and usability for standard workloads combined with an extensible, flexible platform for experimentation and development. Rock-solid "classic" OLTP database? Check. Rich geospatial data platform? You betcha. Structured document store? That too. Pluggable storage engines and column stores? Why not! Don't get me wrong: there are definitely workloads for which Postgres is a poor choice. Its replication features are far behind MySQL, much less its pricey commercial competitors and the more reliable NoSQL options (Cassandra, HBase, etc.). There's also been little work that I know of* to optimize the storage engine for SSDs. (*- I'd love to be learn this is just my ignorance and someone is really focused on large-scale SSD-backed Postgres deploys and tuning. Links/references appreciated.) If you can work within those constraints though it's an awesome product.

ddorian43 12y ago
It doesn't have pluggable-storage-engines. Postgresql is working with bidirectional replication (master-master).
- rcoder 12y ago
  Re: storage engines, my apologies; those mean something very specific in the MySQL world and the more appropriate term for Postgres-land would be foreign data wrappers. I stand by my point about flexibility regardless.
- keeperofdakeys 12y ago
  What's the purpose of having pluggable-storage-engines? From what I can see, InnoDB is the only engine to support basic features like transactions, foreign-keys, MVCC, etc. I'm sure there are reasons to use the other storage-engines, like MyISAM for archiving lots of data, but why not just use a different database for this? Is there a performance price for having this flexibility?
  - ddorian43 12y ago
    Yeah mysql-implementation sucks. And (from reading the discussions on pg-hackers) was that it's very hard to keep semantics (transactions, snapshot-isolation, indexes etc). Different storage-engines have different pros-cons (the same with indexes gin/gist). You could have: in-memory storage engine (ex: current unlogged tables don't replicate since they don't have wal) compressed (something like tokudb/mx) columnar (see monetdb vs citus) etc It's harder to use another database because maintance + flexibility (ex: postgresql has arrays).
    - tomiko_nakamura 12y ago
      People are working on other storage types for PostgreSQL. That's all I can say at this moment.
      - ddorian43 12y ago
        Strange that they are working in secrecy. I would guess something like[1] would be better. [1]: http://www.postgresql.org/message-id/CA+U5nM+AFftDf-8UaMoe7Z8W6Sx-2EjFvEHephKQ=doEQ2Y1nQ@mail.gmail.com http://www.postgresql.org/message-id/CA+U5nM+AFftDf-8UaMoe7Z...
      - ddorian43 12y ago
        Maybe you meant about this ? http://www.datanami.com/2015/03/04/fujitsu-adding-column-oriented-processing-engine-to-postgresql/ http://www.datanami.com/2015/03/04/fujitsu-adding-column-ori...
  - dragonwriter 12y ago
    > why not just use a different database for this? You effectively are using different databases (as far as, e.g., operational characteristics) but with a common interface presented to client apps, letting the visibile database engine serve as an abstraction layer, and the consuming app(s) don't need to be aware of the backend differences, and are also somewhat insulated against needing to change if the storage engine used for various pieces changes.
  - deleted 12y ago
- tomiko_nakamura 12y ago
  It doesn't have MySQL-like pluggable storage, and frankly I'm thankful for that. How many of the MySQL storage engines actually work, including transactions for queries working with tables using different storage engines and such? Or support all the features like fulltext, and so on? Whenever I want to get scared at night, I either watch the first "Alien" movie or read "MySQL restrictions and limitations". So no, thank you very much. OTOH, PostgreSQL code base is one of the cleanest and well structured code bases I've seen, and adding a new storage engine is not all that complicated, assuming you know what you're doing and have time to do that properly. If you just want something simple, managed outside but accessed by SQL, use FDW.
s_kilk 12y ago
> predictable performance Hmm, maybe. Where I work we use Postgres a lot, and the performance (for our workload) can only be described as "brittle". We often see problems whereby making slight syntactic changes to queries will cause confusion in the query planner and performance will go through the floor. Getting around this often involves trying out a few semantically equlivalent ways of phrasing a query and trying to find one that doesn't suck, then just remembering that for next time. Another problem we've had is that after a while the query planner can go nuts suddenly, making bad decisions based on accumulated stats. It's fun watching what is usually a two-second query last multiple hours because the query planner has lost its mind. Anywhere else I'd claim that this is down to incompetence, but we have two senior engineers with multiple decades of DB experience, and even they end up tearing their hair out over postgres performance. Overall, I like postgres, a lot, and I'd choose it over MongoDB and other noSql solutions for most workloads, but it's performance can be very hit-or-miss compared to other (proprietary) relational databases. TLDR: postgres performance is usually pretty good, but in some cases can be brittle, resulting in a lot of effort spent needling the query planner into doing the right thing. The effort spent is much more than would be spent with some other (unfortunately proprietary) databases.
- tomiko_nakamura 12y ago
  The only response I have to that is "talk to developers on the mailing list" (either pgsql-performance or pgsql-hackers). Maybe it's possible to improve the planning - maybe your queries are uncommon / difficult to estimate / running into a thinko in the planner. I don't know.
  - fleetfox 12y ago
    Try asking on irc, #postgresql at freenode.
    - tomiko_nakamura 12y ago
      Yeah, IRC is a good place to ask too. The obvious problem is that not all developers hang there all the time, so for longer discussions the mailing lists are better (and you can just attach test cases, for example).
takeda 12y ago
> There's also been little work that I know of* to optimize the storage engine for SSDs. (- I'd love to be learn this is just my ignorance and someone is really focused on large-scale SSD-backed Postgres deploys and tuning. Links/references appreciated.) I'm by no means expert in databases or SSDs but one of the key point of SSD is fast random access and overall speed. Generally on SSD you should set random_page_cost to 1.1, perhaps you could also adjust other _cost settings to reflect the speed differences. I can't think of any other things that would have effect (TRIM support is handled by filesystem and not too relevant, since Postgres doesn't mutate the data (copy-on-write)), block size is not specific to SSD and applied in HDD as well.

by rcoder 12y ago

ddorian43 12y ago

It doesn't have pluggable-storage-engines. Postgresql is working with bidirectional replication (master-master).

rcoder 12y ago
Re: storage engines, my apologies; those mean something very specific in the MySQL world and the more appropriate term for Postgres-land would be foreign data wrappers. I stand by my point about flexibility regardless.
keeperofdakeys 12y ago
What's the purpose of having pluggable-storage-engines? From what I can see, InnoDB is the only engine to support basic features like transactions, foreign-keys, MVCC, etc. I'm sure there are reasons to use the other storage-engines, like MyISAM for archiving lots of data, but why not just use a different database for this? Is there a performance price for having this flexibility?
- ddorian43 12y ago
  Yeah mysql-implementation sucks. And (from reading the discussions on pg-hackers) was that it's very hard to keep semantics (transactions, snapshot-isolation, indexes etc). Different storage-engines have different pros-cons (the same with indexes gin/gist). You could have: in-memory storage engine (ex: current unlogged tables don't replicate since they don't have wal) compressed (something like tokudb/mx) columnar (see monetdb vs citus) etc It's harder to use another database because maintance + flexibility (ex: postgresql has arrays).
  - tomiko_nakamura 12y ago
    People are working on other storage types for PostgreSQL. That's all I can say at this moment.
    - ddorian43 12y ago
      Strange that they are working in secrecy. I would guess something like[1] would be better. [1]: http://www.postgresql.org/message-id/CA+U5nM+AFftDf-8UaMoe7Z8W6Sx-2EjFvEHephKQ=doEQ2Y1nQ@mail.gmail.com http://www.postgresql.org/message-id/CA+U5nM+AFftDf-8UaMoe7Z...
    - ddorian43 12y ago
      Maybe you meant about this ? http://www.datanami.com/2015/03/04/fujitsu-adding-column-oriented-processing-engine-to-postgresql/ http://www.datanami.com/2015/03/04/fujitsu-adding-column-ori...
- dragonwriter 12y ago
  > why not just use a different database for this? You effectively are using different databases (as far as, e.g., operational characteristics) but with a common interface presented to client apps, letting the visibile database engine serve as an abstraction layer, and the consuming app(s) don't need to be aware of the backend differences, and are also somewhat insulated against needing to change if the storage engine used for various pieces changes.
- deleted 12y ago
tomiko_nakamura 12y ago
It doesn't have MySQL-like pluggable storage, and frankly I'm thankful for that. How many of the MySQL storage engines actually work, including transactions for queries working with tables using different storage engines and such? Or support all the features like fulltext, and so on? Whenever I want to get scared at night, I either watch the first "Alien" movie or read "MySQL restrictions and limitations". So no, thank you very much. OTOH, PostgreSQL code base is one of the cleanest and well structured code bases I've seen, and adding a new storage engine is not all that complicated, assuming you know what you're doing and have time to do that properly. If you just want something simple, managed outside but accessed by SQL, use FDW.

s_kilk 12y ago

> predictable performance Hmm, maybe. Where I work we use Postgres a lot, and the performance (for our workload) can only be described as "brittle". We often see problems whereby making slight syntactic changes to queries will cause confusion in the query planner and performance will go through the floor. Getting around this often involves trying out a few semantically equlivalent ways of phrasing a query and trying to find one that doesn't suck, then just remembering that for next time. Another problem we've had is that after a while the query planner can go nuts suddenly, making bad decisions based on accumulated stats. It's fun watching what is usually a two-second query last multiple hours because the query planner has lost its mind. Anywhere else I'd claim that this is down to incompetence, but we have two senior engineers with multiple decades of DB experience, and even they end up tearing their hair out over postgres performance. Overall, I like postgres, a lot, and I'd choose it over MongoDB and other noSql solutions for most workloads, but it's performance can be very hit-or-miss compared to other (proprietary) relational databases. TLDR: postgres performance is usually pretty good, but in some cases can be brittle, resulting in a lot of effort spent needling the query planner into doing the right thing. The effort spent is much more than would be spent with some other (unfortunately proprietary) databases.

tomiko_nakamura 12y ago
The only response I have to that is "talk to developers on the mailing list" (either pgsql-performance or pgsql-hackers). Maybe it's possible to improve the planning - maybe your queries are uncommon / difficult to estimate / running into a thinko in the planner. I don't know.
- fleetfox 12y ago
  Try asking on irc, #postgresql at freenode.
  - tomiko_nakamura 12y ago
    Yeah, IRC is a good place to ask too. The obvious problem is that not all developers hang there all the time, so for longer discussions the mailing lists are better (and you can just attach test cases, for example).

takeda 12y ago

> There's also been little work that I know of* to optimize the storage engine for SSDs. (- I'd love to be learn this is just my ignorance and someone is really focused on large-scale SSD-backed Postgres deploys and tuning. Links/references appreciated.) I'm by no means expert in databases or SSDs but one of the key point of SSD is fast random access and overall speed. Generally on SSD you should set random_page_cost to 1.1, perhaps you could also adjust other _cost settings to reflect the speed differences. I can't think of any other things that would have effect (TRIM support is handled by filesystem and not too relevant, since Postgres doesn't mutate the data (copy-on-write)), block size is not specific to SSD and applied in HDD as well.