PostgreSQL as Schemaless Database [pdf]
wiki.postgresql.org
wiki.postgresql.org
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).
And if benchmarks impress you I suggest you take a look at Cassandra sometime.
http://techblog.netflix.com/2011/11/benchmarking-cassandra-s...
Skip to slide 22 of this comparative benchmark with Couchbase Server[2] and you'll see that <5ms latencies at 99% is totally achievable. We can't take too much credit for these stats, most of the blame goes to the fact we use memcached at the front end, and it's been optimized for consistent low-latency for a long time.
I don't want to make this a speed contest, as there are a lot of good things about Cassandra. It just strikes me as odd that people sell Cassandra based on low latency. If you want consistent high-performance, don't use a database that runs on a language virtual machine. There are lots of great reasons to use Cassandra, standout benchmarks aren't one of them.
[1] https://www.instaclustr.com/solution#tco [2] http://www.slideshare.net/renatko/couchbase-performance-benc...
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)
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.
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.
It is transparent, consistent, load-balancing and fault-tolerant cluster. Though some of the features of PostgreSQL are not supported yet, eg. triggers.
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.
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.
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.
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).
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.
Thanks for the pointers. I will read up on GIN and hstore.
If nothing else, duplicating an element from a doc column to a first class field seems reasonable. Also, even in mongo one must define indexes which is a schema of sorts and can be non trivial to change.
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.
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.
"It's time for us to switch to something fresher. I personally would have preferred XSLT, but QUEL is almost as good."
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.
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.
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 upfront.
It seems harder because there's a minimal standard you must meet first.
It continues to seem harder because the database will insist on conformance to that schema rather than flexing to accomodate new cases.
And lastly, it seems harder because when you try to change the model, a relational database will insist that the new schema is consistent with data stored under the old schema (or that you provide a consistent way to update it).
Mongo and other document DBs don't do this. They just let you do what you want to do.
The problem is that eventually, stuff is done wrong. And you wind up with a document database full of corner cases. And you need to write complex query and load code to deal with all the quirks and historical mistakes and errors that crept in previously.
To summarise:
Loosey-goosey systems reward you now and punish you later.
Strict systems punish you now to prevent future punishment.
Humans are bad are trading off such different reward schedules. We hyperbolically discount future rewards and we are dreadful at accounting for avoided costs.
The only reason relational systems got a foothold in the first place was because the people who chose them knew from personal experience how much hierarchical-model and network-model databases could suck.
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 to nodes.
Say you build a project management system. Under a hierarchical system you might build it around the central concept of a Work Breakdown Structure. There are standard ways to produce these and they have a strictly hierarchical structure.
But here comes trouble, because now you've privileged the WBS over all other views into the model. So if for instance you're interested in an activity-based breakdown (percentage $ spent on documentation vs manufacturing etc), you're going to have to walk the entire WBS to work it out. Lucky you.
If you've ever queried an XML document in a way it wasn't originally set up for, this will seem familiar.
When relational databases introduced joins, lots of heads exploded. Pretty much anything that can be logically derived from relations can be expressed with joins, projections etc, without having to modify the database structure to accomodate those queries.
Now you can create a schema that relates WBS items to activities to cost accounts to whatever else. Now you can query them in any direction you like. Relational algebra means that the database can provably answer questions without having to waste time walking through a graph or visiting, potentially, ever node.
The historical systems to look for are IMS (the original hierarchical system) and CODASYL (a standard for network database programming).
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...
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.
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 by Rails misfeatures -- there's much less distance between a MySQL or Postgres database as "designed" by ActiveRecord and a NoSQL instance. Having already opted out of your database's particular guarantees, there's very little keeping you there.
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.
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.
Side note: what's with Mongo using so much storage? Perhaps an append-only log without garbage collection?
Which given how little MongoDB does, and how it can lose data unless you cripple the performance even more (ie. with waiting for successful write status), is impressive in itself.
>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.
Not really. The JS engine was never the bottleneck in MongoDB. The demanding stuff is done in the C/C++ core.
>And compare PG have 18 years of development vs 5 of MongoDB.
Not really relevant. PG had 18 years of development implementing hundreds of things MongoDB doesn't do, not merely doing the same schema-less stuff and tuning it for speed.
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 to add indexes, constraints or joins.
But an intriguing idea either way.
If the data is well structured and no join is needed, can I assume that relational database should have better performance than no-SQL?
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.
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).
The only downside is that (for now at least) it is single node, with optional replica slave(s).
The original slides are at: 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
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.
Anyone seeing those numbers for any database that synchronously writes the log to 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 )
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.
"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?
1) It appears to me that the technique discussed in your linked post is about ganging unrelated transactions together into a single flush to disk. I do not see that to be the case in the OP. Since all of the writes are going to the same table they are related.
2) Looking at the second graph in the linked post pgbench transactions/sec insert.sql. The number of clients is high. I got the impression from the OP that there was only a single client. Indeed if there were more than a few clients the benchmark would have been subject to the deficiencies of the client libraries used.
From the slides: "Scripts read a CSV file, parse it into the appropriate format, INSERT it into the database. • We measure total load time, including parsing time. • (COPY will be much much much faster.) • mongoimport too, most likely."
Postgres does its best to use intelligent defaults, but it is only a part of the system, it is generally up to practioners to be wary. Tools like: http://www.postgresql.org/docs/current/static/pgtestfsync.ht... Assist in this goal, but generally have to watch out for things like raid controllers that are not battery backed etc ( depending upon your environment).
There is no mention of the number of clients used directly.
I was simply trying to highlight that with the (little) information available, it is very possible.
It is also possible, that the session(s) should themselves choose to pursue an asynchronous commit strategy ( on a session level see:http://www.postgresql.org/docs/9.2/static/wal-async-commit.h... ) which would also not require modifying the configuration, I do not know as I have not seen the scripts, but it is similar to how a library could interact with:
http://docs.mongodb.org/manual/reference/command/getLastErro...
Thanks.
hell, ill personally pay a lot to have a mysql compliant interface just for WordPress!
I guess it should be possible to write a wrapper that executed at isolation level "single".
I skipped this story because I assumed it was an April Fools' prank.
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!
Feeling prescriptive these days?