Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

The key value store we ignored (Postgresql)

blog.creapptives.com

Post reply on HN
Print view View original

51–60 of 65 posts

Re: The key value store we ignored (Postgresql)

#51

Post by smosher »

Animus7 wrote: post #37
jallmann wrote: post #36

Earlier quoted context omitted.

> Riak -> Actually a key/value store with link walking, you can just write map reduce for that document oriented feel! Again I won't bother writing a map-reduce job just to fetch out document with particular values. http://howfuckedismydatabase.com/nosql/ Quite apt.

Personally I find writing MapReduce jobs (in JavaScript no less) to be unbelievably clean and easy when your stack is Riak + Node. Of course if you've been using SQL for years then this probably sounds difficult in comparison. Except what if you're a JS guy with zero SQL experience? "Ok, it's a database. How do I query it?" "You learn this completely new language and dynamically compile your questions down to it, but…

I'm not a big fan of NoSQL, but sometimes I want to write a query and sometimes I just want to write some code, and I could see the appeal of doing it in JS, and especially some of the languages that target JS.

The thing about SQL as an attack vector is frustrating because it (usually) doesn't need to be: use prepared statements and let the driver handle value substitution for you. It's quicker and easier than escaping everything.

Re: The key value store we ignored (Postgresql)

#52

Post by smosher »

zobzu wrote: post #40

I've been using a lot of MySQL and some NoSQL solutions over the years. I don't need most of what NoSQL is supposed to offer and uhm, a couple of years ago I tried PGSQL. I've never found the same level of finish, performance, etc. It's also very customizable and very dependable. You can get extremely good performance out of it. Finally... the devs are plain AWESOME. Every time I had an issue, question, etc, not even…

Finally... the devs are plain AWESOME.

About 10 years ago I had cause to look at the source and I have to say I don't think I've ever seen better written C. The changes we were considering were vetoed, but I marveled at how easy it was to identify the place I would have needed to make the changes and verify that they would not have unintended consequences.

I don't know if it still does, but the code I saw back then read like a book.

Re: The key value store we ignored (Postgresql)

#53

Post by saurik »

7952 wrote: post #12

I tried hstore on a project for many of the reasons mentioned. I just hated having to always represent data as strings.

I am confused by this comment. In the PostgreSQL protocol, everything is a string if you use the text protocol, but you can also use the binary protocol, in which case there is a reasonable binary storage format for hstore. It is even more confusing, as whichever protocol you use seems like an implementation detail of your driver: at the level of an application you should only be working with dictionaries.

Re: The key value store we ignored (Postgresql)

#54

Post by djb_hackernews »

joevandyk wrote: post #47
djb_hackernews wrote: post #15

> ...database grew to a size of 239691780 bytes (Just 228MB) > ...gives me 14689 rows just under 360ms on average Uh, 360ms seems like an awfully long time to query such a small dataset.

I think that includes the time to transfer the data.

I sure hope not. That's usually not the way you benchmark database queries.

Re: The key value store we ignored (Postgresql)

#55

Post by einhverfr »

fauigerzigerk wrote: post #34
imbriaco wrote: post #20

I agree with the author that hstore is very interesting, but the data structures are not the key selling point in the NoSQL space in my opinion. The most overlooked advantage to things like Cassandra and Riak are the fact that you have no single point of failure in the system. If an individual node fails, there is no operational impact. Postgres does have (finally!) a nice replication story, so you have data protecti…

Agreed, but of course the massive write parallelism and fault tolerance of DBMS like Cassandra comes at the cost of dropping ACID, which may cause a lot of complexity elsewhere in the system. It also comes at the cost of limiting the types of queries you can perform without resorting to procedural code (at least in the case of column family based architectures). In other words, it comes at the cost of productivity. S…

there's a project I have recently come across called Postgres-XC which would solve the problem you describe quite nicely. Basically it's Teradata-style clustering based on PostgreSQL. It isn't full-featured yet (version 0.9.6 is the current version, and it doesn't support things like windowing functions), but it looks extremely interesting in this space.

Re: The key value store we ignored (Postgresql)

#56

Post by einhverfr »

jacques_chester wrote: post #44
mulander wrote: post #28

Earlier quoted context omitted.

I can sustain this claim. I work with a system implemented almost entirely in Oracle PL/SQL. Some tables in the system are nearing 800-900 columns, their size often exceeds 600 GB per table (not many of such large tables though). Querying isn't a problem at all. Large schema changes are also mostly painless. The only point at which one has to be really careful is when a schema change requires actual calculations base…

A lot of criticisms I read about the immutable limits of RDBMSes turn out, upon closer inspection, to be criticisms of MySQL. Oracle sort of sails past these limitations like a superliner: expensively and gracefully. Not that I particularly like Oracle as a programmer. I have to check my calendar every time I hit the 32-character limit for names or once again have to write some_field_expressing_truth varchar2(1); con…

If you want something to play with that could scale to that size (although maybe not ready for production), see Postgres-XC (http://postgres-xc.sourceforge.net/). This looks promising.

Re: The key value store we ignored (Postgresql)

#57

Post by einhverfr »

fauigerzigerk wrote: post #34
imbriaco wrote: post #20

I agree with the author that hstore is very interesting, but the data structures are not the key selling point in the NoSQL space in my opinion. The most overlooked advantage to things like Cassandra and Riak are the fact that you have no single point of failure in the system. If an individual node fails, there is no operational impact. Postgres does have (finally!) a nice replication story, so you have data protecti…

Agreed, but of course the massive write parallelism and fault tolerance of DBMS like Cassandra comes at the cost of dropping ACID, which may cause a lot of complexity elsewhere in the system. It also comes at the cost of limiting the types of queries you can perform without resorting to procedural code (at least in the case of column family based architectures). In other words, it comes at the cost of productivity. S…

That said, there are very good reasons not to use RDBMS in cases where the data model or specific access patterns just don't fit. But in most of those cases, I find that using in memory data structures combined with file system storage or something like BerkeleyDB is a much better fit than any server based DBMS.

I want to go into this just a little. The issue here is just that there are tradeoffs. Understanding these tradeoffs is what good design is about.

RDBMS's excel at one thing: presenting stored data for multiple purposes. This is what relational algebra gets you and while SQL is not relational algebra it is an attempt to bridge relational algebra with a programming language.

An RDBMS will never be as fast performance-wise as a basic object storage system. However, what it buys you is flexibility for that part of the data that needs to be presented for multiple uses.

So the sort of access pattern that doesn't fit is something like an LDAP server. Here you have a well-defined method for integrating the software, and the use cases for ad hoc reporting usually aren't there.

On the other hand, you have something like an ERP that allows file attachments to ERP objects. Even though the data model doesn't fit exactly, you can't do this gracefully with a NoSQL solution.

So I suggest people think about the need for flexibility in reporting. the more flexibility required, the more important an RDBMS becomes.

Additionally if you have multiple applications hitting the same database, an RDBMS is pretty hard to replace with any other possible solution.

Re: The key value store we ignored (Postgresql)

#58

Post by einhverfr »

zobzu wrote: post #40

I've been using a lot of MySQL and some NoSQL solutions over the years. I don't need most of what NoSQL is supposed to offer and uhm, a couple of years ago I tried PGSQL. I've never found the same level of finish, performance, etc. It's also very customizable and very dependable. You can get extremely good performance out of it. Finally... the devs are plain AWESOME. Every time I had an issue, question, etc, not even…

Finally... the devs are plain AWESOME.

A few years ago I got a dreaded call from a customer. A point of sale system was taking 45 seconds suddenly to post invoices. This is a big deal for a cash register role, as you can imagine.

So I went on site, ran some diagnostics, isolated the problem query, etc. At this point they were into the slow part of the afternoon so I asked about it on the -perform list along with query plans and everything else I could think of.

Within an hour I had received an email from Tom Lane explaining:

1) What the planner was getting wrong

2) Why the planner was making an error there, and why this was a special planner case.

3) Giving me suggestions as to how to fix it.

First rate support, let me tell you. (Also, planner behavior was changed in a release soon after to accommodate my use case.)

Re: The key value store we ignored (Postgresql)

#59

Post by buu700 »

fauigerzigerk wrote: post #34
imbriaco wrote: post #20

I agree with the author that hstore is very interesting, but the data structures are not the key selling point in the NoSQL space in my opinion. The most overlooked advantage to things like Cassandra and Riak are the fact that you have no single point of failure in the system. If an individual node fails, there is no operational impact. Postgres does have (finally!) a nice replication story, so you have data protecti…

Agreed, but of course the massive write parallelism and fault tolerance of DBMS like Cassandra comes at the cost of dropping ACID, which may cause a lot of complexity elsewhere in the system. It also comes at the cost of limiting the types of queries you can perform without resorting to procedural code (at least in the case of column family based architectures). In other words, it comes at the cost of productivity. S…

>Cassandra comes at the cost of dropping ACID

Suddenly I want to spend my next Saturday night setting up Cassandra at a party...

Edit: Come on, "dropping acid"! I thought it was funny...

Re: The key value store we ignored (Postgresql)

#60

Post by mikeytown2 »

maxpert wrote: post #17
mikeytown2 wrote: post #13

how does this compare to MySQL's HandlerSocket?

I haven't done benchmarks yet but if you are looking for HandlerSocket benchmarks http://blog.creapptives.com/post/3329352663/the-nosql-dogma is the link

Those benchmarks are very flawed as HandlerSocket only works in InnoDB. This is a better post on the subject: http://yoshinorimatsunobu.blogspot.com/2010/10/using-mysql-a...
Post reply on HN