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

61–70 of 90 posts

Re: PostgreSQL as Schemaless Database [pdf]

#61

Post by ams6110 »

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

Don't think if these guarantees as your primary data entry validation (though they are a stopgap for mistakes there). Your app will (ideally) never encounter violations; if it does, it means it's doing something wrong and should probably bail out with a "I am confused, please have my developers fix me" type of error.

Re: PostgreSQL as Schemaless Database [pdf]

#62

Post by ams6110 »

jacques_chester wrote: post #23
nnq wrote: post #22

Earlier quoted context omitted.

Care to elaborate on your last sentence? I'm really curious about some corners of software history and this seems like a hint of a corner I never knew existed...

Basically, hierarchical and network databases were around before relational databases. Both of them have the problem that they require a lot of up-front planning. When you build a hierarchy, you need to know how it's going to be used as that will determine the structure. Turning hierarchies into networks -- ie trees into graphs -- eases this a bit but you still have the problem of arranging things for easy navigation…

Though interestingly, for quite a while the one thing hierarchical database were good at was the one thing RDBMS was bad at: reconstructing an arbitrary-depth hierarchy. Take the classic "bill of materials"... easy in a hierarchical database, but in an RDBMS you have to recursively query to walk the hierarchy, or use special support like Oracle's "CONNECT BY PRIOR"

Re: PostgreSQL as Schemaless Database [pdf]

#64

Post by 3amOpsGuy »

jchrisa wrote: post #47
taligent wrote: post #43

Earlier quoted context omitted.

Why do you need me ? I am just that crazy troll who believes that no database is perfect for all use cases and that making blind generalisations like "all NoSQL is bad" is stupid. Especially since MongoDB is as different from Cassandra or Riak as it is from Spanner as that is from the other 120+ odd NoSQL databases. And if benchmarks impress you I suggest you take a look at Cassandra sometime. http://techblog.netflix…

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 like is we've never had downtime, not even scheduled. We've had nodes fail, we've had datacentres isolated during disaster recovery tests, yet our cluster has continued on regardless (2 x sub clusters per region). Whatever way you stack it up, that's impressive. Remember with cassandra there's no load balancers or other shenanigans involved.

Garbage collection / virtual machine concerns are a red herring due to the mechanics of a cassandra query (certainly for CL < RF)

Re: PostgreSQL as Schemaless Database [pdf]

#65

Post by mercurial »

ams6110 wrote: post #62
jacques_chester wrote: post #23

Earlier quoted context omitted.

Basically, hierarchical and network databases were around before relational databases. Both of them have the problem that they require a lot of up-front planning. When you build a hierarchy, you need to know how it's going to be used as that will determine the structure. Turning hierarchies into networks -- ie trees into graphs -- eases this a bit but you still have the problem of arranging things for easy navigation…

Though interestingly, for quite a while the one thing hierarchical database were good at was the one thing RDBMS was bad at: reconstructing an arbitrary-depth hierarchy. Take the classic "bill of materials"... easy in a hierarchical database, but in an RDBMS you have to recursively query to walk the hierarchy, or use special support like Oracle's "CONNECT BY PRIOR"

Alternatively, you can use nested sets [1], optionally storing the hierarchy in a different table. Unless you're really interested in good write performance.

1: http://en.wikipedia.org/wiki/Nested_set_model

Re: PostgreSQL as Schemaless Database [pdf]

#66

Post by jfb »

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

One could build out from the database schema -- Postgres certainly provides sufficient type information to build consistency double-checks in other layers of the application. As an added bonus, other consumers of the data won't be flying blind.

Re: PostgreSQL as Schemaless Database [pdf]

#67

Post by andrewflnr »

ams6110 wrote: post #62
jacques_chester wrote: post #23

Earlier quoted context omitted.

Basically, hierarchical and network databases were around before relational databases. Both of them have the problem that they require a lot of up-front planning. When you build a hierarchy, you need to know how it's going to be used as that will determine the structure. Turning hierarchies into networks -- ie trees into graphs -- eases this a bit but you still have the problem of arranging things for easy navigation…

Though interestingly, for quite a while the one thing hierarchical database were good at was the one thing RDBMS was bad at: reconstructing an arbitrary-depth hierarchy. Take the classic "bill of materials"... easy in a hierarchical database, but in an RDBMS you have to recursively query to walk the hierarchy, or use special support like Oracle's "CONNECT BY PRIOR"

I actually read a paper a while back describing how to do trees in an RDBMS by encoding the parent hierarchy of a row in a string primary key or something like that. To get everything in a subtree, just check for keys with a certain prefix. I can't seem to find it now, though.

EDIT: should have gone to HNSearch first. Here's the comment where I found it: https://news.ycombinator.com/item?id=4921541 . Specifically, http://www.tetilab.com/roberto/pgsql/postgres-trees.pdf

EDIT 2: tetilab link seems broken. Try this: http://darwin.zoology.gla.ac.uk/~rpage/MyToL/www/downloads/t...

Re: PostgreSQL as Schemaless Database [pdf]

#68

Post by xkcdfanboy »

weisk wrote: post #33

sorry but the first thing I readt was "PostgreSQL the shemales database". Confusing ..

Same here, don't feel bad.

And to the downvoters, the correct word is aschematic or schema-less with a dash.

If a word ends in a vowel, you must use a dash.

Hatless is fine, but scrupleless should be scruple-less. The more you know, you scruple-less bastards!

Re: PostgreSQL as Schemaless Database [pdf]

#69

Post by drdaeman »

joevandyk wrote: post #54
drdaeman wrote: post #50

Earlier quoted context omitted.

Postgres won't let you perform any sufficiently long (read-only) queries on slaves. For example, I was unable to run pg_dumpall on a slave to have DB snapshots. This is [well-explained]( http://www.postgresql.org/message-id/201102272005.00234.jens... ), but still inconvenient. This way, slaves are only useful for failover and possibly offloading very short read-only transactions, which is pretty limited.

This used to be the case, but it's not anymore. You can run long-living queries, run pg_dump, etc on slaves.

Ah, that's really good news to hear. Seems it's time to upgrade. :)

Could you, please, provide a reference to a documentation or a changelog? Maybe I'm looking for a wrong keywords, but I can't find any mentions of such changes by myself.

Re: PostgreSQL as Schemaless Database [pdf]

#70

Post by bbromhead »

moe wrote: post #60
joevandyk wrote: post #54

Earlier quoted context omitted.

This used to be the case, but it's not anymore. You can run long-living queries, run pg_dump, etc on slaves.

Do you have a reference in which pg version this got fixed? We still pg_xlog_replay_pause() our slaves, it would be nice to drop that.

http://www.postgresql.org/docs/9.2/static/runtime-config-rep...
Post reply on HN