Show HN: BedquiltDB – A Mongo-like JSON doc store built on Postgres
bedquiltdb.github.io
bedquiltdb.github.io
I've been working on this project on-and-off for a few years now, and thought it was time to show it to the public.
The Gist:
BedquiltDB is a vaguely mongodb-inspired json store built on top of PostgreSQL's jsonb column type. It does the things you'de expect, creating _id fields automatically, creating tables on the first write, etc.
It is implemented as a postgres extension (a horrible lump of PL/PgSQL), and client libraries for python, node and clojure.
While I'm not using it in production for anything, it is pretty well tested, and I had fun making it, which is what really matters :)
Questions welcome!
I wouldn't be opposed to re-writing in a more-performant extension language if needed.
EDIT: also, PL/PgSQL is installed by default, and I wanted to see if I could get this project to work without any other dependencies aside from a base PostgerSQL 9.4 installation.
I loathe and detest dependencies! Just saying.
It even is much faster compared to PL/pgSQL (at least in a project I've been working on lately).
That's good to hear. At the time I had read somewhere that PL/V8 can be slow because it needs to cast/convert data back and forth between javascript and native Postgres representations. It's possible that that assessment is wrong, however.
In my case PL/pgSQL FOR loop was particularly slow; lack of builtin data structures like map or stack (though stack is emulated in V8 via array) caused performance problems – array operations are really slow in PL/pgSQL (compared to V8), instead of using map I've use indexed temporary table (creation of such is quite costly).
Still you might have a point here – PL/pgSQL stores execution plans with the function, so there is a chance of being more efficient here.
but, there is also the ability to simply query from inside a function using plv8.execute(), which gives you the ability to use JSONB operators, which are much faster.
https://github.com/adewes/blitzdb
It provides a uniform, mongo-like interface over various storage backends, most notably SQL (Postgres, SQLite) via SQLAlchemy, MongoDB itself (well, naturally) and a flat-file based backend that doesn't have any external dependencies.
Feel free to check it out, feedback is welcome.
Is there a list of people using it somewhere? Is someone using it for serious things?
[edit] Just to clarify, you said "its UI" but postgrest and ng-admin are independent pieces of software, that happened to work very nicely together.
I'm trying to understand why anyone would pick MongoDB over PostgreSQL these days?
http://www.slideshare.net/8kdata/torodb-scaling-postgresql-l...
Edit: does this help? ToroDB do certainly need to get better documentation :(
https://github.com/torodb/torodb/wiki/Setting-up-ToroDB-as-h...
Also, speaking as someone who was an operator of a large Postgres DB and who is now an operator of a large Mongo DB, although Mongo has a nominal lead in this one area for the time being, by going to Mongo you sacrifice a lot (I really can't emphasize this point enough; the distribution/sharding story is really the only thing that Mongo is doing a better job of right now, Postgres is a clear winner in every other way). I'm really looking forward to (and hoping for) extensions like Citus becoming more mature so that optimal choice of database will be obvious under all circumstances.
[1] https://www.citusdata.com/blog/17-ozgun-erdogan/403-citus-un...
For new projects, I agree with you!
Then there are all kinds of other issues related indexes and the differences between mongo and PostgreSQL and so on.
I would argue that JSON(b) support is now production ready in Postgres 9.5+. You can read, write, update, delete, and it is fast.
I mean that this persons code is not production-ready. As in there is no chance it emulates all of the features of mongo or has been performance tested etc.
http://www.postgresql.org/docs/9.4/static/datatype-json.html
1. Can't sort using a GIN index.
2. Stats are shit with GIN indices - Postgres assumes that every filter has 100 matches, every array has 99 elements or something. Usually this works well, sometimes (specifically in my case) it's terrible. Specifically, it means that if you combine an aggregation over a GIN filter with an inner join, the performance will be awful because postgres picked a nested loop join for expected 100 results instead of a hash join for the actual 50,000.
3. This also means that additional expression indices you add basically aren't getting used, because it'll always appear better to the query planner to use the GIN index first. This includes things like sorting.
4. Because of how jsonb is implemented (basically arrays containing fields ordered by key alphabetical), we discovered that we were taking out more and more fields out of our jsonb field and placing them in other columns, which kinda missed the point of it (but was very good that we could do so, yay relational data model).
5. Sorting can become an epic chore if you don't have stuff like enum types etc, especially when you try to combine it with tsvectors.
So, very happy with Postgres, but don't slam people who don't use Postgres because of insufficient json support - because Postgres' json support although excellent has quirks that will bite you with certain use cases. Very good, but not a panacea.
2. That's (probably) occurring because the default statistics target is 100. Just increase them with:
ALTER TABLE gin_index ALTER COLUMN indexed_column SET STATISTICS 1000;
That should hopefully help with all the other issues you are seeing.
P.S. while looking at MongoDB a little further, I discovered that it doesn't support sorting by collation. This was logged as a JIRA bug in 2014, and as of yet I don't believe there is a solution.
Conversely, the more recent a piece of tech is, the more likely it is go into production right away, with people only starting to question their decision after the 3 year new-and-shiny barrier is broken (but usually not because of its faults, but because something newer and shinier can now replace it).
Edit: incidentally, don't you think it's ironic that the "new and shiny" thing that was supposed to knock relational databases (now over 36 years old) off it's perch has been found to be lacking in a variety of areas, not least of which is that a "schemaless" document store almost always gains a defacto schema?
Seems to me that the ability to join and divide two relations is a fantastic application of relational algebra and not something you can do particularly well in MongoDB at all.
[1] http://cryto.net/~joepie91/blog/2015/07/19/why-you-should-ne...
[2] http://www.sarahmei.com/blog/2013/11/11/why-you-should-never...
[3] https://aphyr.com/posts/322-call-me-maybe-mongodb-stale-read...
Which is a problem why, precisely?
projects.insert({
'_id': "BedquiltDB",
'description': "A ghastly hack.",
'quality': "pre-alpha",
'tags': ["json", "postgres", "api"]
})
Much python code uses single quotes, with double quotes for doc strings. Except where it is more convenient.
More convenient places include structures when escaping strings. "Joe's easier here's why." Is better than 'Joe\'s easier here\'s why.' Also structures which are JSON structures too (makes copy/pasta easier).
Though with py3, using alt-shift-] on my machine gives me ’, so I can do 'Joe’s easier, here’s why.'
It's useful to be able to contain quotes in other quotes without escaping them.
i.e. '"hello there"' or "it's nice to see you"
Also, on many projects I've worked on, single quotes were for strings that were not meant to be shown to users (keys etc), and double quotes were for strings that would eventually be shown on a screen. It looks like that's what they're doing here.
Interesting, I've never seen that convention before.
In Ruby, I use single-quoted strings for things that can't possibly, by their nature, contain any string interpolation, to indicate that this is so; and then double-quoted strings for more arbitrary text that either contains interpolation, or might, in the future, get interpolation added. The coder here might be used to Ruby programming.
Though, also, it occurs to me that—since this is a dictionary that will become JSON—its type is {String => Object}. The single-quoted strings sort of read to me as indicating strings that couldn't be any other type; while the double-quoted strings are strings just because strings happen to be the response there, but could be any other object.
i can dig it. works decently as visual articulation.
Meaning I can dropin use node-mongo, mongo-ruby, pymongo with your extension.
I'm developing an app on Meteor now where the CRUD operations for the largest class of users work well with the MongoDB conventions, but I also need to prepare for the other portion of users who will need more complex queries to analyze the data produced by the first portion.
It's awesome, PostgreSQL is awesome.