8 ms·
PostgreSQL as Schemaless Database [pdf]
- sylvinus 14y agoWhat I get from these slides is that MongoDB has better average performance (when both using indexes or doing full scans) compared to all schemaless PostgreSQL options.
- lucian1900 14y agoSadly it's not a fair fight, of course it'll beat Mongo (even though Postgres is ACID). I would be more interested in comparing the biggest machine you can get on AWS running Postgres with a cluster of smaller machines running Cassandra. I expect Cassandra to win, but of course it's not consistent.
- thomasfl 14y agoWhen 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.
- malkia 14y agoMaybe sharding?
- ProblemFactory 14y agoThe great thing about PostgreSQL is that it is hybrid. With http://www.postgresql.org/docs/9.1/static/hstore.html 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 to add indexes, constraints or joins.
- rurounijones 14y agoThat is... I am not sure, either madness or genius. But an intriguing idea either way.
- sandGorgon 14y agowould it be possible to build an extension to expose a mongo compatible interface in postgres - essentially being able to use all the ORM/code built around mongo for postgres, seamlessly.
- lucian1900 14y agoYes, but why bother? There are already so many good SQL clients out there, like SQLAlchemy.
- sandGorgon 14y agothere are many apps built around mongodb that it would be interesting to do a drop-in swap with postgres (assuming protocol compatibility). hell, ill personally pay a lot to have a mysql compliant interface just for WordPress!
- lucian1900 14y agoI see, that motivation makes more sense to me :-) I guess it should be possible to write a wrapper that executed at isolation level "single".
- grncdr 14y agoYes, I don't think it would even be that difficult if you used plv8js, but it would (probably?) be relatively slow.
- lifeisstillgood 14y agoWell that's Monday's afternoon decided !
- eksith 14y agoThis is pretty much the reason we're using Postgres for our document storage. Admittedly, our doc store isn't really extensive and it's mostly for internal use (with limited client access), but we just didn't see why it was necessary to go with a new database when the existing stack worked well. YMMV, but we've yet to come across an instance where a pure document DB was necessary or desired, even to store documents (which from my perspective seems nothing more than a flattened EAV model).
- aaronyo 14y agoDo 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.
- kiwidrew 14y agoOne approach to expression indexes on JSON would be a multicolumn index. Column A is the path, e.g. "foo.bar[2].baz" and column B is the value at that path... of course you'd need to use GIN -- which as mentioned already is slow to build -- since one row would map to multiple index entries. But this ought to be a fairly workable approach.
- eksith 14y agoI'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 with the schema abstracted out on our app to provide a document storage and search engine with a separate app for client login. I believe some metadata is stored in conventional fields (varchar integer etc...). Performance compared to the conventional schema is of course noticably slower, but not unacceptably so for a few thousand docs and growing (this is compared to hundereds of millons of rows on the conventional schema). But such is the sacrifice for reliability. When NASA hit a spat of failed missions to Mars (explosions, collisions, miscalculations etc...) people were joking about the creed at the time, "Faster, Better, Cheaper; Pick 2". This kind of engineering is a turn off for a lot of folks, so I can certainly see the appeal of a document DB. I've also seen people fall into the trap of "I don't know what kind of data I'll be getting so I'll go with a doc store". Well, there has to be some hint of what kind of data could be arriving so engineering for that with some wiggle room early is a good idea. You shouldn't take my word or the slides at face value, though. Really think through, experiment, throw stones at it etc... and see if it works for you.
- xenator 14y agoI want to admit that conclusion in presentation can be interpreted vise versa: MongoDB almost is fast as PostgreSQL. Also, notice that comparsion was done with Mongo 2.2, in 2.4 JS engine was changed to V8, so there is a lot of speed improvements. And compare PG have 18 years of development vs 5 of MongoDB. So what I want to say? Difference is not so big after all.
- jacques_chester 14y agoPostgreSQL 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 document database -- Mongo is still the easier pick. It's a cliche, but it's easy to: 1. overestimate the size of your data, (especially since relational models by design squeeze out all duplicated statements of data) 2. overestimate how much traffic they need to serve 3. underestimate what can be achieved by stock RDBMSes with modern hardware 4. underestimate the deferred development costs of schemaless data and 5. underestimate the costs of avoiding durable storage. It's a really hard field to make generalisations about right at the moment. The algo-economics of hardware (especially SSDs) and software have been changing so fast that any generalisation formed even 2 years ago is unreliable. Speaking only for myself, I need a lot of talking down from my data-safety/data-integrity tree. I don't trust myself. That's why I use tools that slow me down.
- xenator 14y agoDon'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 in high schools I always hear from other people that it is boring. And I agree with that. SQL in general and PG interfaces in particular is just "so old school". MongoDB looks more friendly.
- neya 14y agoWhere is taligent when we need him? MongoDB is pretty impressive considering its age to the market, but I'd still go with the relational version of PostgreSQL anyday. Not because of the benchmarks, but in general, most use-cases are covered well enough by our relational db's like MySQL and PostgreSQL. The most expensive mistake one could make is using a NoSQL db where an SQL db fits perfectly. I admittedly made this mistake and learnt a LOT. Now, these benchmarks are just some extra spice for sticking with my lovely PostgresSQL :)
- jacques_chester 14y agoI think it's easy to forget that mongo did hit an under-served sweetspot when it turned up in 2008ish. But SSDs and the explosion in per-core RAM density have steadily rendered those advantages less compelling. I imagine that as time goes on the PostgreSQL team will do their usual tortoise impression, steadily closing the distance on the NoSQL pioneers (and they deserve credit for reopening and re-exploring this chapter in database history).
- lucian1900 14y agoYou managed to choose the worst new database out there to call impressive :) Cassandra is impressive. Mongo? Not so much.
- taligent 14y agoWhy 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.com/2011/11/benchmarking-cassandra-scalability-on.html http://techblog.netflix.com/2011/11/benchmarking-cassandra-s...
- neya 14y agoI only tagged you because you seem to have experience running Mongodb in production (as claimed by you in one of the previous threads) and I honestly wanted your feedback about this. Also I was curious to see if you would evaluate this nifty PostgreSQL feature :)
- aaronyo 14y agoIt's only one of several interesting comparisons in the slides, but I think we need more data points on the disk space to make use of the data. Does it grow linearly? probably, but is there a +x in there? Mongo has some big upfront disk allocations. Were those considered somehow?
- jtchang 14y agoReally 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.
- inopinatus 14y agoI'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.
- shawn-butler 14y agoCan you elaborate on what you mean by key deletion problems?
- sargun 14y agoSo, the problem that I have with PostgreSQL isn't that it doesn't have every datastore under the sun, but rather the lack of automated distribution, and fault-tolerance. This is not a hard thing to build though (continue reading please). I don't mean that it's an easy task, but that the semantics by which you may be able to build a fault-tolerant, distributed database on top of Postgresql are pretty straightforward. 1. Partitioning: So, Postgresql has multiple notions of partitions. Ideally, this would be done at the database level in Postgres, but the idea is that instead of having your entire database fail at once, a transparent layer in which you SET PARTITION_KEY=X before querying would make this pretty straightforward. It would be nice if there was a semi-transparent proxy layer that this. 2. Replication / Fault-tolerance: Postgresql today has built-in synchronous master/slave replication. It would be nice if someone built a automated failover system on top this. Alternatively, it would be really interesting if someone had multiple postgresql instances, and then build a WAL log per partition, and performed 3PC between N replicas, and you could have a fully peer-to-peer postgres. I imagine these transactions would either need to be PL/SQL, or fully written-out, staged serializable SQL queries. 3. Co-processors: One of the biggest benefits a distributed, fault-tolerant Postgresql setup would give you is the ability to collocate your application with the data. Instead of having to do read, modify, write, you can write complex business logic that can run on whichever Postgres partition's master node, and the data doesn't have to go over the network. I feel by introducing some basic wrappers around postgres to support partitioned, master-slave topologies, and perhaps a slightly different query interface to support this transparently, PostgreSQL could effectively replace most modern NoSQL databases.
- karavelov 14y agoThen you may be interested in PostgreSQL-XC: http://postgres-xc.sourceforge.net/ http://postgres-xc.sourceforge.net/ It is transparent, consistent, load-balancing and fault-tolerant cluster. Though some of the features of PostgreSQL are not supported yet, eg. triggers.
- bbromhead 14y agoHot Standby in postgres is pretty damn good for replication (like you said making failover occur properly takes a little bit of effort but its not too hard). Also pgpool2 helps a lot with partitioning and parallel queries. For a relational db, postgres does a pretty good job at distribution and fault tolerance.
- adrianhoward 14y agoDamn. The "XML. It seemed a good idea at the time" slide caused me to spray coffee on my laptop.
- Evbn 14y agoSee also "XML literals as a native syntax in Scala."
- karteek 14y agoFirst thing I remembered after reading the title is http://pgsnake.blogspot.in/2010/04/postgres-91-release-theme.html http://pgsnake.blogspot.in/2010/04/postgres-91-release-theme...
- mh- 14y agohaha, thanks for posting that. i enjoyed: "It's time for us to switch to something fresher. I personally would have preferred XSLT, but QUEL is almost as good."
- guilloche 14y agoI am always wondering why no-sql has better performance than SQL if we have same simple use case (no join etc.). If the data is well structured and no join is needed, can I assume that relational database should have better performance than no-SQL?
- lucian1900 14y agoDepends 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 a bottleneck (master nodes in BigTable/HBase), require significant infrastructure (Google Spanner) or make schema changes very hard (Hyperdex). It's a hard problem.
- guilloche 14y agoThanks 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).
- lucian1900 14y agoRedis is consistent, it's just less durable than Postgres. A query cache is a very good usage for it. It's also likely to be faster than Postgres for the simple operations it is capable of. The only downside is that (for now at least) it is single node, with optional replica slave(s).
- fpp 14y agoWould be interesting to know which version of MongoDB was used (2.4 also uses V8). The original slides are at: https://wiki.postgresql.org/images/b/b4/Pg-as-nosql-pgday-fosdem-2013.pdf https://wiki.postgresql.org/images/b/b4/Pg-as-nosql-pgday-fo... There is also another presentation on the use of PL/V8 (on Heroku) at the PostgreSQL wiki - from last week's NYC talks: http://plv8-talk.herokuapp.com/#1 http://plv8-talk.herokuapp.com/#1
- disbelief 14y agoIn one slide it says he used MongoDB 2.2 on his mac.
- cinbun8 14y agoThere is still some way to go to support better indexing of json fields, but I'm pretty excited about these developments. In the future you might see schemaless and schema driven data living side by side on postgres.
- weisk 14y agosorry but the first thing I readt was "PostgreSQL the shemales database". Confusing ..
- xkcdfanboy 14y agoSame 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!
- eru 14y ago> If a word ends in a vowel, you must use a dash. Feeling prescriptive these days?
- ogdr 14y agoMongoDB's native storage type is not JSON, but BSON. One should know this if they're about to wade in a deep river, trying to argue against it.
- apunic 14y agoWhen I checked hstore's functionality the last time (~6 months ago) it was rather limited compared to Mongo's full-blown API. Any new observations?
- hannibalhorn 14y agoThe new JSON functions have landed in the development version, for anyone interested in seeing the future syntax: http://www.postgresql.org/docs/devel/static/functions-json.html http://www.postgresql.org/docs/devel/static/functions-json.h...
- forgotAgain 14y agoAlmost 6,000 synchronous commits per second to a 7,200 rpm disk seems high to me. Anyone seeing those numbers for any database that synchronously writes the log to disk?
- sendob 14y agohttp://pgeoghegan.blogspot.com/2012/06/towards-14000-write-transactions-on-my.html http://pgeoghegan.blogspot.com/2012/06/towards-14000-write-t... may interest you
- forgotAgain 14y agoWhile very impressive, I don't see how your post is applicable in this case. The OP at the very least implies that the transaction are done end to end and not batched. I find 6,000 distinct disk I/O operations per second, to append the log, very very high for a 7.2k disk.
- sendob 14y agothat post, also discusses using a 7200rpm disk. I think where you may be confused is considering them as distinct IO operations, vs seeing it as a point which must have been flushed past ( sequentially )
- forgotAgain 14y agoNo, I don't think I'm confused. The OP says postgresql is used in its default configuration: Stock PostgreSQL 9.2.2, from source. No changes to postgresql.conf. Given that statement then either the writes are serialized or postgresql is not ACID compliant in its default configuration. I'm not an expert on postgresql but I assume that it is ACID compliant in its default configuration. Therefore my skepticism on the 6K writes per second.
- sendob 14y agofrom the link: "In Postgres 9.2, this improvement automatically becomes available without any further configuration." "Essentially, it accomplishes this by reducing the lock contention surrounding an internal lock called WALWriteLock. When an individual backend/connection holds this lock, it is empowered to write WAL into wal_buffers, an area of shared memory that temporarily holds WAL until it is written, and ultimately flushed to persistent storage." "With this patch, we don’t have the backends queue up for the WALWriteLock to write their WAL as before. Rather, they either immediately obtain the WALWriteLock, or else queue up for it. However, when the lock becomes available, no waiting backend actually immediately acquires the lock. Rather, each backend once again checks if WAL has been flushed up to the LSN that the transaction being committed needs to be flushed up to. Oftentimes, they will find that this has happened, and will be able to simply fastpath out of the function that ensures that WAL is flushed (a call to that function is required to honour transactional semantics). In fact, it is expected that only a small minority of backends (one at a time, dubbed “the leader”) will actually ever go through with flushing WAL. In this manner, we batch commits, resulting in a really large increase in throughput..." I am sorry to cut and paste so much of the article, but I hope this is helpful?
- antimora 14y agoThe problem with this scheme is that Postgres does not support JSON indexing and partial updates which are important for high transaction databases.
- meritt 14y agoOh. This is legit? I skipped this story because I assumed it was an April Fools' prank.