| |||||||||||||
PostgreSQL 9.0 arrives with many new featuresPostgreSQL 9.0 arrives with many new featuresPosted Sep 21, 2010 19:42 UTC (Tue) by flewellyn (subscriber, #5047)Parent article: PostgreSQL 9.0 arrives with many new features
This is an impressive catalog of new features. Of course, the one that interests me most is the binary replication, but the ability to set schemawide and default permissions is going to be a huge boon as well.
I'm extremely dubious, on the other hand, of the utility of HStore. Not because I think the PostgreSQL implementation of key-value stores is faulty (I certainly would think not!), but because I'm dubious of the utility of simple key-value stores in a world where we have relational capability. But, if people are going to ask for it, I suppose there's nothing wrong with PostgreSQL providing it. Particularly since this way you don't have to choose between having two incompatible database systems, or sacrificing full relational capability, to have your simple key-value store.
(Log in to post comments)
PostgreSQL 9.0 arrives with many new features Posted Sep 21, 2010 19:54 UTC (Tue) by ScOut3R (guest, #69996) [Link]
Yes, replication looks promising. Is there any documentation how it handles stuff under the hood? My biggest concern is how it handles stored procedures.
PostgreSQL 9.0 arrives with many new features Posted Sep 21, 2010 20:04 UTC (Tue) by andresfreund (subscriber, #69562) [Link]
Its binary log shipping - its shipping the write ahead log (which is also used for crash-safety) to the standby which does a "nearly normal" recovery.
The only difference is that it allows you to connect to it after it reaches a stable point.
So, the usage of stored procedures shouldn't matter...
PostgreSQL 9.0 arrives with many new features Posted Sep 21, 2010 20:23 UTC (Tue) by ScOut3R (guest, #69996) [Link]
Thank You for the clarification!
PostgreSQL 9.0 arrives with many new features Posted Sep 22, 2010 11:59 UTC (Wed) by smurf (subscriber, #17840) [Link]
> So, the usage of stored procedures shouldn't matter...
..., except that said stored procedure affects something outside of PostgreSQL. I might actually want that to happen on the backup server too.
PostgreSQL 9.0 arrives with many new features Posted Sep 22, 2010 17:28 UTC (Wed) by captrb (subscriber, #2291) [Link]
That seems like a fragile way to do things. Rather than have stored procedures cause side effects outside the database, what if you published events to a listen/notify queue, then have external processes reading the queues and cause the external state change. Have multiple queues for multiple recipients. The master and backup server would read the events from the currently-active database, failing over like the rest of your applications.
Slave servers vs. Stored Procedures Posted Sep 22, 2010 18:55 UTC (Wed) by smurf (subscriber, #17840) [Link]
The operative word here is "queue". If I want to keep the database and the external world in sync, I can't queue my changes.
Of course, it's a trade-off, and has its own pitfalls (what if the stored procedure works on the master but fails on the slave?). But so has every other solution.
PostgreSQL 9.0 arrives with many new features Posted Oct 3, 2010 22:50 UTC (Sun) by Wol (guest, #4433) [Link]
If you're worried about that, shouldn't you be using transactions as well as stored procedures?
And in the databases I use, using a stored procedure like that would cause the application to fail - "side effects are not permitted in a transaction!".
Cheers,
HStore syntax Posted Sep 21, 2010 20:31 UTC (Tue) by epa (subscriber, #39769) [Link] The article gives an example of deleting a key:SELECT profile - 'Occupation' FROM user_profile WHERE user_id = 5;Is this right? You can mutate the data with a SELECT statement?
HStore syntax Posted Sep 21, 2010 20:59 UTC (Tue) by andresfreund (subscriber, #69562) [Link]
It doesn't delete any stored value - the value returned by the select is modified.
If you wanted to modify the stored value you would need an UPDATE...
HStore syntax Posted Sep 21, 2010 21:29 UTC (Tue) by dskoll (subscriber, #1630) [Link] Josh's example reads Or even delete specific keys: I assume he means: Or even delete specific keys from the return value of SELECT. At least I hope that's what it means...
HStore syntax Posted Sep 21, 2010 21:38 UTC (Tue) by jberkus (guest, #55561) [Link]
Actually, that was a paste-o which neither I nor my proofreader caught. The example, of course, should have been:
UPDATE user_profile SET profile = profile - 'Occupation'
HStore syntax Posted Sep 30, 2010 23:57 UTC (Thu) by MattPerry (guest, #46341) [Link]
Can the SELECT statement be removed from the article? Having it there, lined through or not, is still very confusing.
HStore syntax Posted Oct 1, 2010 3:16 UTC (Fri) by jake (editor, #205) [Link]
> Having it there, lined through or not, is still very confusing.
The problem is that folks reading the comments will get confused as well, which is why I left it in but with strike-through. Is it really that unclear that it is a fix?
jake
HStore syntax Posted Oct 1, 2010 18:42 UTC (Fri) by MattPerry (guest, #46341) [Link]
> Is it really that unclear that it is a fix?
Yes, it was. I had no idea what the strike-through was supposed to signify so I ignored it and then spent time wondering why a select and then update would be needed.
> The problem is that folks reading the comments will get confused as well
A better way to handle that is to just reply to the comments in question and mention that you have fixed the problem. Most people will read the article before the comments and struck-through text provides information with no value.
Mutating with SELECT Posted Sep 28, 2010 1:06 UTC (Tue) by ringerc (subscriber, #3071) [Link]
While that specific example was an error in the article, yes, it is possible to mutate data with SELECT. SELECT may invoke stored functions - written in C, SQL, PL/PgSQL, PL/Perl, etc - that mutate data. SQL and PL/PgSQL functions invoked from SELECT may use any SQL, including INSERT/UPDATE/DELETE but also DDL. Other languages may use the SPI to affect the database's state and contents.
PostgreSQL 9.0 arrives with many new features Posted Sep 21, 2010 21:46 UTC (Tue) by jberkus (guest, #55561) [Link]
The primary utility for an added key-value store is for attributes which need to be extended at runtime, and don't need referential integrity.
A good example of this would be a multi-tenant "personals" site where most of the search criteria were built-in (age, height, weight, marital status, etc.) but where each reseller of the service was allowed to add additional "profile items". Or a web CRM (think SugarCRM) where each salesperson was allowed to add their own "tags".
The alternatives to do this in SQL are all unpalatable: Entity-Attribute-Value tables (crappy performance, huge on-disk size), DDL at runtime (dangerous), or "option_1_id" columns (awkward & confusing to query).
Of course, if you're using Hstore for your core application data, then you're probably using the wrong database. But there's a lot of cases where applications need something like a key-value store for *just one thing*.
PostgreSQL 9.0 arrives with many new features Posted Sep 21, 2010 22:09 UTC (Tue) by flewellyn (subscriber, #5047) [Link]
I've written applications that do controlled DDL at runtime without problems. I don't see why that's so dangerous.
PostgreSQL 9.0 arrives with many new features Posted Sep 21, 2010 22:19 UTC (Tue) by jberkus (guest, #55561) [Link]
In order for the application to make DDL changes at runtime, the application role needs to have unrestricted permissions on at least some tables. This creates a security hole if the web application is compromised.
The alternative is SECURITY DEFINER functions. However, most web developers don't care much for stored procedures. And, like setuid bits on files, security definer functions are potentially dangerous if not completely locked down.
In either case, you've given someone a complex way of solving their problem which requires more than a bit of database knowledge. If that person is a web GUI developer who is Just Trying To Get The Job Done, they instead end up running the web app with superuser or database owner permissions. As, indeed, SugarCRM did (and maybe still does).
PostgreSQL 9.0 arrives with many new features Posted Sep 22, 2010 5:06 UTC (Wed) by flewellyn (subscriber, #5047) [Link]
Fair enough. I suppose if all you need is extensible attributes without integrity checks, runtime DDL is overkill as well.
PostgreSQL 9.0 arrives with many new features Posted Sep 22, 2010 11:36 UTC (Wed) by tialaramex (subscriber, #21167) [Link]
Flexibility is also (at least for now) the key reason to look at an RDF storage engine.
I guess from a relational database perspective these are just "Entity-Attribute-Value tables" except that of course the attributes and values are also (potentially) entities...
If you actually have a schema that reflects your system's unchanging reality, an RDBMS remains an excellent choice, with lots of mature offerings, excellent performance, etc. RDF will not ever be a competitor for the certainty of "Every X has exactly one Y, which is a unique integer" if you can bludgeon reality to fit such rules.
The interest in key-value stores suggests that more and more people are realising that their problem isn't entirely amenable to this approach. But I'd argue that key-value is not quite flexible enough either.
But it could be that I've been using a hammer so long that now all the more sophisticated problems just look like they haven't been hit hard enough or from the right angle.
PostgreSQL 9.0 arrives with many new features Posted Sep 22, 2010 18:31 UTC (Wed) by jberkus (guest, #55561) [Link]
tialaramex,
There are definitely problems for which RDF is the right answer, and trying to use a relational database for those problems is painful at best. For example, while you *could* do a semantic web thing using a relational database, you wouldn't want to.
On the other hand, there are definitely tasks for which RDF is wildly unsuitable. Just at the moment, I'm working on an inventory management application. Again, you could do such a thing in RDF, but you'd very quickly regret it.
Any database is a model of your real-world data. All models require reductionism, and thus the model format you use should be based on what it is you want to do with the data you have. Claims that one or another data model form is "more realistic" or "more natural" than another are specious. All models are equally artificial.
PostgreSQL 9.0 arrives with many new features Posted Oct 3, 2010 23:01 UTC (Sun) by Wol (guest, #4433) [Link]
Hmm...
Speaking as a mathematician, all models are equal, true.
But speaking as a scientist, that's not true!
Some models (Newtonian Mechanics) are good enough. Some models are a very good fit (Relativity). And some models are just plain irrelevant (toroidal geometry on a ball).
Pick the right model. The "more natural" one. Because the wrong one doesn't approximate to nature (reality).
Cheers,
PostgreSQL 9.0 arrives with many new features Posted Sep 24, 2010 12:59 UTC (Fri) by dmag (subscriber, #17775) [Link]
> I'm extremely dubious, on the other hand, of the utility of HStore.
Here's two reasons to use HStore:
1) It can turn O(N) operations into O(1)
Imagine trying to implement "Facebook-like" friend list: Every time a user hits their home page, the DB needs get their "friends". In a typical join table, this will take O(N) disk seeks.
So you think about storing the friend's names right on the user object, maybe as a string. But it will be slow -- you have to parse the string (server-side inside a transaction) to modify it.
So you think about a cache. But that just introduces new problems. ("There are two hard problems in Computer Science: Naming and Cache Invalidation.")
With a HStore, you don't need a cache -- It's O(1) to get friend list, and O(1) to modify it. Of course, the trade-off is duplicate data. (That's not a NoSQL thing. SQL people are always denormalizing for performance. (e.g. "total_order_price" or "num_friends")
2) It's a lot simpler. You have a user object (row) with an HStore friends column. You add friends to it, you subtract friends from it, you get all the friends. You don't need a fancy ORM to hide the complexity of messing about with multiple tables and extra IDs. (The trade-off is no FK checking. But MySQL proved we don't need it ;)
If you squint, HStore is really more like a Docstore than a Key-Value store. (A pure KV suffers from "last in wins" problems because you can't do sub-operations on a value. Only Get and Set.)
PostgreSQL 9.0 arrives with many new features Posted Sep 24, 2010 15:47 UTC (Fri) by bronson (subscriber, #4806) [Link]
In your example you're using hstore as a cache, right? You still need to invalidate it.
Or, if you always store a new value whenever the friends list changes, then you could just do that with memcached, redis, etc. I'm not seeing the difference.
Other than being built into Postgres, of course. That's nice, but given how easy memcached and redis are to set up, not a big deal.
PostgreSQL 9.0 arrives with many new features Posted Sep 24, 2010 18:06 UTC (Fri) by dmag (subscriber, #17775) [Link]
> you're using hstore as a cache, right?
Yes, but with "HStore as a cache", you can do your cache updates in a transaction. The biggest problem with external caching is keeping them in sync. (To keep it in perfect sync, you'd need to build your own 2-phase commit.)
I can also imagine non-cache uses. For example, "has this user seen this announcement?". Seems silly to create a new table for that.
HStore is just "one more tool in the toolbox". The future of data is all about "picking the right tool", which requires knowing the engineering tradeoffs. (Actually, today it's more like "figuring out the engineering tradeoffs", but it will get easier.)
PostgreSQL 9.0 arrives with many new features Posted Oct 3, 2010 22:47 UTC (Sun) by Wol (guest, #4433) [Link]
imho (and a lot of people won't like me saying this) relational TECHNOLOGY is broken.
I use Pick - a system that's built on "key, value". PROVIDED that value is a normalised array, the resulting system is a SUPERSET of an RDBMS, with the added advantage that I can prove that successful optimisation is not possible :-)
(By that I mean that unoptimised queries are so efficient that there is no "headroom" available for an optimiser to make gains, not that optimisation can't be done. Just that even for a perfect optimiser, the cost is likely to outweigh the benefits.)
The downside, of course, is where I used the word "provided". Pick is like C - it doesn't enforce good practice. otoh, I liken relational to Pascal - when implemented strictly it gets horribly in the way of actually doing any work :-)
Cheers,
|
|||||||||||||