6 ms·

> 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

by 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).
tomiko_nakamura 12y ago
BTW I wonder what you mean by "accumulated stats" ... Regarding your senior engineers - maybe they're competent, maybe not. 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't use the advanced cool stuff in PostgreSQL.
x0x0 12y ago
The one big feature (imo) missing from postgres is query plan locking. I too -- although in our case, somehow nightly vacuuming got turned off -- have seen single digit millisecond queries suddenly get mangled by the planner and turn into multi-second queries.
- tomiko_nakamura 12y ago
  I'm not saying "plan locking" is not a useful feature in some cases, and maybe it would really solve this particular issue. But my experience is that when people say "I need it" in most cases it's "I've been working with X for a long time, and that's how you solve problems looking like this there." (where X is usually Oracle or DB2). If you really need nightly vacuuming (and not just analyze), and you accidentally turn it off, you have bigger problems than planning. This should be handled by autovacuum in modern versions, so either you're running a very old version (before 8.3 when autovacuum was enabled by default). So either you're running a very old version (i.e. older than ~5 years - sorry, not much can be done here except for upgrading). Or you don't have autovacuum tuned properly, or you really have strange workload. In those cases you should really talk to people on pgsql-performance.
  - x0x0 12y ago
    This experience was 8 years ago; autovacuum is nice. Having been bitten two or three times by a fast query suddenly getting very slow because a heuristic in the query planner flipped over (including once in the last couple of years), I'd simply like to fix a performant enough query plan and not worry about it. Just to make my opinion clear, pg along with linux and vim are the best pieces of software I use.
bsg75 12y ago
Do you find that PostgreSQL is different in this regard compared to MySQL?
sitharus 12y ago
Where I work we have the same problem with MSSQL. Unfortunately the dataset I work with is not very consistent, leading to the main key not being very useful to the query planner. I'm sure Postgres would have similar problems on the dataset. Unfortunately I'll never get to try. On the other hand, for my personal projects it's amazing. They'll never see the scale I see at work, but they do get millions of rows sometimes :)

by s_kilk 12y ago

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).

tomiko_nakamura 12y ago

BTW I wonder what you mean by "accumulated stats" ... Regarding your senior engineers - maybe they're competent, maybe not. 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't use the advanced cool stuff in PostgreSQL.

x0x0 12y ago

The one big feature (imo) missing from postgres is query plan locking. I too -- although in our case, somehow nightly vacuuming got turned off -- have seen single digit millisecond queries suddenly get mangled by the planner and turn into multi-second queries.

tomiko_nakamura 12y ago
I'm not saying "plan locking" is not a useful feature in some cases, and maybe it would really solve this particular issue. But my experience is that when people say "I need it" in most cases it's "I've been working with X for a long time, and that's how you solve problems looking like this there." (where X is usually Oracle or DB2). If you really need nightly vacuuming (and not just analyze), and you accidentally turn it off, you have bigger problems than planning. This should be handled by autovacuum in modern versions, so either you're running a very old version (before 8.3 when autovacuum was enabled by default). So either you're running a very old version (i.e. older than ~5 years - sorry, not much can be done here except for upgrading). Or you don't have autovacuum tuned properly, or you really have strange workload. In those cases you should really talk to people on pgsql-performance.
- x0x0 12y ago
  This experience was 8 years ago; autovacuum is nice. Having been bitten two or three times by a fast query suddenly getting very slow because a heuristic in the query planner flipped over (including once in the last couple of years), I'd simply like to fix a performant enough query plan and not worry about it. Just to make my opinion clear, pg along with linux and vim are the best pieces of software I use.

bsg75 12y ago

Do you find that PostgreSQL is different in this regard compared to MySQL?

sitharus 12y ago

Where I work we have the same problem with MSSQL. Unfortunately the dataset I work with is not very consistent, leading to the main key not being very useful to the query planner. I'm sure Postgres would have similar problems on the dataset. Unfortunately I'll never get to try. On the other hand, for my personal projects it's amazing. They'll never see the scale I see at work, but they do get millions of rows sometimes :)