4 ms·

I've been playing with some more advanced feature in postgres (lately, mostly json and jsonb related), and one of the main aspect I'm concern about is the poten

by NhanH 12y ago

I've been playing with some more advanced feature in postgres (lately, mostly json and jsonb related), and one of the main aspect I'm concern about is the potential pitfalls -- specifically performance pitfalls. Mostly because I feel like there are too many magics around, and I have no idea how much overhead for any action is. The advanced features allow you to have more than one way to do things, sometimes in a slightly more concise way (I've been tempting to use jsonb type in place of simple many-to-many tables if one of the values are just always be string). It's not that I'm expecting my app to be web scale or anything, but I'd rather not having to redesign everything one a query choke on a million records. And most of the docs tend to be light on performance characteristics. As performance is one of the most important aspect in database system in general (which I'd say on par with consistency, and way a head of features), it'd be nice to have docs focus more on not just how to use features, but when it shouldn't be used.

BonoboBoner 12y ago
If you ever need web scale, there is always nosql to fall back on Kappa
fleetfox 12y ago
What do you mean by "magic" and "no idea how much overhead for any action is". EXPLAIN is your friend.
EmielMols 12y ago
I think this is a valid point. For non-performance-critical code where readability is more important (eg analytics) the additional language features could be nice. In my experience, 9/10 production use cases are covered using a path as primary key with prefix matching (select path,data from tree where path='a/b' or path like 'a/b/%') - as a side effect your subtree nodes will often be on the same table page (mySQL by default, pgSQL when using CLUSTER).
sergiosgc 12y ago
Your concern is valid. Whenever complexity is abstracted away you may suddenly introduce an exponential order algorithm in an otherwise simple looking query. Having said so, Postgresql has actually surprised me very little throughout time. The planner's "reasoning" is quite comprehensible. Inevitably I trigger a slow plan here and there but, once identified, correction is usually straightforward. My overall opinion is that postgresql has quite solid theoretical foundations and it shows. What I mean is that, in advanced stuff, there'll always be some "magic", and that postgresql has the least amount of black box magic I've ever seen on a RDBMS (I'm looking at you, Oracle). (Postgresql has been my non-clustered database of choice since about 2004. I'm definitely biased.)

by NhanH 12y ago

BonoboBoner 12y ago

If you ever need web scale, there is always nosql to fall back on Kappa

fleetfox 12y ago

What do you mean by "magic" and "no idea how much overhead for any action is". EXPLAIN is your friend.

EmielMols 12y ago

I think this is a valid point. For non-performance-critical code where readability is more important (eg analytics) the additional language features could be nice. In my experience, 9/10 production use cases are covered using a path as primary key with prefix matching (select path,data from tree where path='a/b' or path like 'a/b/%') - as a side effect your subtree nodes will often be on the same table page (mySQL by default, pgSQL when using CLUSTER).

sergiosgc 12y ago

Your concern is valid. Whenever complexity is abstracted away you may suddenly introduce an exponential order algorithm in an otherwise simple looking query. Having said so, Postgresql has actually surprised me very little throughout time. The planner's "reasoning" is quite comprehensible. Inevitably I trigger a slow plan here and there but, once identified, correction is usually straightforward. My overall opinion is that postgresql has quite solid theoretical foundations and it shows. What I mean is that, in advanced stuff, there'll always be some "magic", and that postgresql has the least amount of black box magic I've ever seen on a RDBMS (I'm looking at you, Oracle). (Postgresql has been my non-clustered database of choice since about 2004. I'm definitely biased.)