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

PostgreSQL as Schemaless Database [pdf]

wiki.postgresql.org

Post reply on HN
Print view View original

71–80 of 90 posts

Re: PostgreSQL as Schemaless Database [pdf]

#71

Post by inopinatus »

jtchang wrote: post #16

Really great presentation/slidedeck. For me I still feel MongoDB is a semi niche solution. I rather go with a standard SQL database unless there is a specific reason why you need NoSQL.

I've tried both MongoDB+Mongoid and Postgresql+hstore as a way to do better inheritance than STI in ActiveRecord.

Both have deficiencies: Mongo is not mature enough for an old lag like me to trust (and is incompatible with some Rails extensions), and hstore had problems with key deletion and cannot do object references or indeed types other than strings.

Re: PostgreSQL as Schemaless Database [pdf]

#72

Post by jshen »

jshen wrote: post #51
jacques_chester wrote: post #20

Earlier quoted context omitted.

Right, so MongoDB goes back to my pet theory about how reward schedules shape technology selection. It takes very little upfront to get data into or out of a MongoDB instance. Import the relevant module, name the server, persist. Blam, done, no need to worry about how to map an object model to a relational model or vice versa. Here kid, have a dopamine hit. Meanwhile, a relational database imposes a schema on you upf…

I think you missed a word. "Strict systems punish you now to prevent POTENTIAL future punishment". and that makes a big difference.

I'm not sure why I'm getting downvoted. Many of us are doing startups. Punishing me now can have significant business impact, because it basically means slower development. In many startups, that future punishment may never come!

Also, I've spent over two years working with a schema-less database and we don't have the corner cases the OP is mentioning. Most of them are rather easy to avoid.

Re: PostgreSQL as Schemaless Database [pdf]

#75

Post by jchrisa »

3amOpsGuy wrote: post #64
jchrisa wrote: post #47

Earlier quoted context omitted.

I keep hearing Cassandra touted as fast, but I just don't see it. Instacluster brags about 17ms at the 99th percentile being fast, but where I come from that gets you thrown out of the running.[1] The Netflix benchmark linked above sees 10,000 ops/sec/node. Again, not impressive. Skip to slide 22 of this comparative benchmark with Couchbase Server[2] and you'll see that I don't want to make this a speed contest, as t…

What else would you say is good about Cassandra? I find the datamodel inflexible and often hard to work with for real world use cases. I think the tooling is immature and the hector API leaves me feeling depressed (pycassa and astyanx are quite a lot better). All that said, I find cassandra to be fast, incredibly fast. 99th percentile on our 9Tb time series store is around 20ms on below par hardware. Another thing i…

I'm glad you are happy with 20ms, to me it is slow...

Probably the best thing about Cassandra is that it is written in Java, so it is easy to fork and add custom behavior. Of course this is orthogonal to performance.

Re: PostgreSQL as Schemaless Database [pdf]

#76

Post by rurounijones »

ProblemFactory wrote: post #32
thomasfl wrote: post #2

When the benchmark shows postgres can do the same as mongo, only faster, then why aren't we seeing more hybrid solutions? Online retailers would be better off using relational storage for monetary transactions and time series, and json storage for document like data.

The great thing about PostgreSQL is that it is hybrid. With http://www.postgresql.org/docs/9.1/static/hstore.html you can put schemaless documents into the same database, even into the same table row as your usual relational data. You could start with a schema that's only a primary key and hstore for all other data (like a fully schemaless database), but over time extract important fields from the hstore into columns…

That is... I am not sure, either madness or genius.

But an intriguing idea either way.

Re: PostgreSQL as Schemaless Database [pdf]

#77

Post by jacques_chester »

mvc wrote: post #59
jfb wrote: post #44

Earlier quoted context omitted.

"1. overestimate the size of your data, (especially since relational models by design squeeze out all duplicated statements of data)" Of course, this is opt-in in a SQL world, more's the pity. I've seen some nightmarish ActiveRecord spawned Postgres monstrosities. I wonder how much of the attraction of schema-less DBs comes from the lack of friction between the largely guarantee-free idea of data storage precipitated…

Here's a question for people who prefer to put their guarantees (i.e. unique/check constraints, foreign keys etc) in their database. How do you present these constraints to the user? How do you show when they've been violated? Just present them with the PG error message? Maintain a mapping of PG errors to more domain specific messages? Bite the bullet and just code them twice (once for the DB, and once for the user)?

Other code-generating tools build from the schema outwards.

So for example, my professional life is currently about an obscure system called Oracle Application Express. You point ApEx at a schema and it will auto generate forms, reports, validations and so forth by introspecting on the database.

Rails used to do something like this: you would point it at a database and it would generate code based on a superficial reading of the schema. Certainly nothing to do with validations etc.

Re: PostgreSQL as Schemaless Database [pdf]

#79

Post by shawn-butler »

inopinatus wrote: post #71
jtchang wrote: post #16

Really great presentation/slidedeck. For me I still feel MongoDB is a semi niche solution. I rather go with a standard SQL database unless there is a specific reason why you need NoSQL.

I've tried both MongoDB+Mongoid and Postgresql+hstore as a way to do better inheritance than STI in ActiveRecord. Both have deficiencies: Mongo is not mature enough for an old lag like me to trust (and is incompatible with some Rails extensions), and hstore had problems with key deletion and cannot do object references or indeed types other than strings.

Can you elaborate on what you mean by key deletion problems?

Re: PostgreSQL as Schemaless Database [pdf]

#80

Post by jeffdavis »

mvc wrote: post #59
jfb wrote: post #44

Earlier quoted context omitted.

"1. overestimate the size of your data, (especially since relational models by design squeeze out all duplicated statements of data)" Of course, this is opt-in in a SQL world, more's the pity. I've seen some nightmarish ActiveRecord spawned Postgres monstrosities. I wonder how much of the attraction of schema-less DBs comes from the lack of friction between the largely guarantee-free idea of data storage precipitated…

Here's a question for people who prefer to put their guarantees (i.e. unique/check constraints, foreign keys etc) in their database. How do you present these constraints to the user? How do you show when they've been violated? Just present them with the PG error message? Maintain a mapping of PG errors to more domain specific messages? Bite the bullet and just code them twice (once for the DB, and once for the user)?

Good question, and one that could use some discussion (e.g. in blog posts). Perhaps I'll write one.

I think some redundancy is a practical necessity, regardless of your approach (using database constraints or only application code). Consider registering a unique username: even if you check for availability beforehand, you still need to handle the race condition. That's guaranteed to be a somewhat awkward user interaction because they thought it was available but you found out that it was taken by a concurrent user.

So, your application always needs to have a nice way to avoid errors when they can be caught early (e.g. javascript check while filling out the form), and a less-nice way of handling errors when they can't (e.g. "sorry, that name has already been registered" after submitting the form).

There are SQL-standard codes for many constraint errors, so those can be turned into exceptions.

For things like CHECK constraints, think of those more like an assert: intentionally redundant.

Post reply on HN