<story><title>Document Storage Gymnastics with Postgres</title><url>http://rob.conery.io/2015/03/01/document-storage-gymnastics-in-postgres/?utm_source=feedburner&amp;utm_medium=feed&amp;utm_campaign=Feed%3A+wekeroad%2FEeKc+%28Rob+Conery%29</url></story><parent_chain><item><author>s_kilk</author><text>&amp;gt; predictable performance&lt;p&gt;Hmm, maybe.&lt;p&gt;Where I work we use Postgres a lot, and the performance (for our workload) can only be described as &amp;quot;brittle&amp;quot;. 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.&lt;p&gt;Getting around this often involves trying out a few semantically equlivalent ways of phrasing a query and trying to find one that doesn&amp;#x27;t suck, then just remembering that for next time.&lt;p&gt;Another problem we&amp;#x27;ve had is that after a while the query planner can go nuts suddenly, making bad decisions based on accumulated stats. It&amp;#x27;s fun watching what is usually a two-second query last multiple hours because the query planner has lost its mind.&lt;p&gt;Anywhere else I&amp;#x27;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.&lt;p&gt;Overall, I like postgres, a lot, and I&amp;#x27;d choose it over MongoDB and other noSql solutions for most workloads, but it&amp;#x27;s performance can be very hit-or-miss compared to other (proprietary) relational databases.&lt;p&gt;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.</text></item><item><author>rcoder</author><text>PostgreSQL displays an amazing and rare combination in _any_ software, much less a free (beer&amp;#x2F;libre) product: reliable, predictable performance and usability for standard workloads combined with an extensible, flexible platform for experimentation and development.&lt;p&gt;Rock-solid &amp;quot;classic&amp;quot; OLTP database? Check. Rich geospatial data platform? You betcha. Structured document store? That too. Pluggable storage engines and column stores? Why not!&lt;p&gt;Don&amp;#x27;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&amp;#x27;s also been little work that I know of* to optimize the storage engine for SSDs. (*- I&amp;#x27;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&amp;#x2F;references appreciated.)&lt;p&gt;If you can work within those constraints though it&amp;#x27;s an awesome product.</text></item></parent_chain><comment><author>tomiko_nakamura</author><text>BTW I wonder what you mean by &amp;quot;accumulated stats&amp;quot; ...&lt;p&gt;Regarding your senior engineers - maybe they&amp;#x27;re competent, maybe not.&lt;p&gt;But most importantly, the experience from one database is often difficult to transfer to a different database. For example I often work with people coming from the Oracle world, and they often apply things that work on Oracle but fail on PostgreSQL. And don&amp;#x27;t use the advanced cool stuff in PostgreSQL.</text></comment>
<story><title>Document Storage Gymnastics with Postgres</title><url>http://rob.conery.io/2015/03/01/document-storage-gymnastics-in-postgres/?utm_source=feedburner&amp;utm_medium=feed&amp;utm_campaign=Feed%3A+wekeroad%2FEeKc+%28Rob+Conery%29</url></story><parent_chain><item><author>s_kilk</author><text>&amp;gt; predictable performance&lt;p&gt;Hmm, maybe.&lt;p&gt;Where I work we use Postgres a lot, and the performance (for our workload) can only be described as &amp;quot;brittle&amp;quot;. 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.&lt;p&gt;Getting around this often involves trying out a few semantically equlivalent ways of phrasing a query and trying to find one that doesn&amp;#x27;t suck, then just remembering that for next time.&lt;p&gt;Another problem we&amp;#x27;ve had is that after a while the query planner can go nuts suddenly, making bad decisions based on accumulated stats. It&amp;#x27;s fun watching what is usually a two-second query last multiple hours because the query planner has lost its mind.&lt;p&gt;Anywhere else I&amp;#x27;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.&lt;p&gt;Overall, I like postgres, a lot, and I&amp;#x27;d choose it over MongoDB and other noSql solutions for most workloads, but it&amp;#x27;s performance can be very hit-or-miss compared to other (proprietary) relational databases.&lt;p&gt;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.</text></item><item><author>rcoder</author><text>PostgreSQL displays an amazing and rare combination in _any_ software, much less a free (beer&amp;#x2F;libre) product: reliable, predictable performance and usability for standard workloads combined with an extensible, flexible platform for experimentation and development.&lt;p&gt;Rock-solid &amp;quot;classic&amp;quot; OLTP database? Check. Rich geospatial data platform? You betcha. Structured document store? That too. Pluggable storage engines and column stores? Why not!&lt;p&gt;Don&amp;#x27;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&amp;#x27;s also been little work that I know of* to optimize the storage engine for SSDs. (*- I&amp;#x27;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&amp;#x2F;references appreciated.)&lt;p&gt;If you can work within those constraints though it&amp;#x27;s an awesome product.</text></item></parent_chain><comment><author>tomiko_nakamura</author><text>The only response I have to that is &amp;quot;talk to developers on the mailing list&amp;quot; (either pgsql-performance or pgsql-hackers).&lt;p&gt;Maybe it&amp;#x27;s possible to improve the planning - maybe your queries are uncommon &amp;#x2F; difficult to estimate &amp;#x2F; running into a thinko in the planner. I don&amp;#x27;t know.</text></comment>