PostgreSQL as Schemaless Database [pdf]

Post by jshen » Sun, Mar 31, 2013, 3:58 PM UTC

jacques_chester wrote: post #20

xenator wrote: post #17

Earlier quoted context omitted.

Don't remember exactly when, probably is was in time when multicore hosting servers becomes relatively cheap. We made measurement MySQL vs PostgreSQL and surpisely found that PG is visible faster. Don't remember exactly numbers. So even if team never push server as supa-pupa-fast on the market they was neat choice for people who know. But the same time, even if PG documentation can be used as textbook for RDB theory…

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…

Post by lucian1900 » Sun, Mar 31, 2013, 4:09 PM UTC

guilloche wrote: post #46

lucian1900 wrote: post #37

Earlier quoted context omitted.

Depends what you mean by "NoSQL". MongoDB is pretty terrible overall, so don't even bother. Systems like Dynamo/Riak/Cassandra sacrifice consistency (most of the time) for performance, largely because a distributed setup (sharding in particular) is easier when you don't guarantee consistency. Note that none of them sacrifice durability. It is possible to have a distributed and consistent database, but you either have…

Thanks for your reply. But I have one more question, if I need to manage bank account, then the database must be consistent. Lets say that I just need to keep balance and social security number for the account, and only need to query the balance. Can No-SQL like redis have better performance than relational database? (assume same on logs and transactions).

Post by DrJokepu » Sun, Mar 31, 2013, 5:14 PM UTC

eksith wrote: post #18

aaronyo wrote: post #9

Earlier quoted context omitted.

Do you have any thoughts on expression indexes? Assumed I would have to pull out fields from the document to index reasonably on pgsql, but this presentation at least shows that simple expression indexes perform well. I wonder about compound, nested, etc.

I'm not too familiar with the current schema, but I do know we make extensive use of partial indexes ( WHERE (vnnk IS NOT NULL) , WHERE (fhs > 0) etc...) and compound indexes in btree. In GIN, for example, this isn't possible, but GIN itself works reasonably well for partial matches (since it's also btree) so it's not too much of a deal. We use a combination of hstore, GIN, conventional fields and multiple indexes wi…

Post by apunic » Sun, Mar 31, 2013, 5:24 PM UTC

Post by mvc » Sun, Mar 31, 2013, 6:01 PM UTC

jfb wrote: post #44

jacques_chester wrote: post #12

Earlier quoted context omitted.

PostgreSQL hasn't spent 18 years pitching itself as a pure performance play, though. It started as a research project into the object-relational model and was adapted to be a safe and featuresome database first and performant second. Mongo's performance will converge downwards as people demand more features and safety guarantees. If however you want the original premise -- memory-only, schemaless, auto-sharded docume…

"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…