Building a MongoDB Clone in Postgres • Hacker News Board

Re: Building a MongoDB Clone in Postgres

#21

Post by rarrrrrr » Mon, Jun 04, 2012, 6:30 PM UTC

jerrysievert wrote: post #13

DEinspanjer wrote: post #8

It is interesting, but what I really need in the JSON functionality of PG is some internal representation that will allow fast and efficient exploration of the JSON blob within the query. i.e. being able to refer to a single attribute within the select/where/groupby clauses without having to pay the toll of serde every time.

exactly. calling the find_in_obj() function can get fairly expensive, especially given the need to execute JSON.parse() for every call. there is definitely a lot of room for improvement in the postgres native JSON toolkit. i am hoping that building this exposes more of those issues and helps move it forward.

I haven't tried it, but I think you would be able to use a PostgreSQL functional index (i.e. indexing the result of a function call to extract a particular bit of data from the JSON.)

http://www.postgresql.org/docs/9.1/interactive/indexes-expre...

Re: Building a MongoDB Clone in Postgres

#22

Post by rosser » Mon, Jun 04, 2012, 6:34 PM UTC

rarrrrrr wrote: post #21

jerrysievert wrote: post #13

Earlier quoted context omitted.

exactly. calling the find_in_obj() function can get fairly expensive, especially given the need to execute JSON.parse() for every call. there is definitely a lot of room for improvement in the postgres native JSON toolkit. i am hoping that building this exposes more of those issues and helps move it forward.

I haven't tried it, but I think you would be able to use a PostgreSQL functional index (i.e. indexing the result of a function call to extract a particular bit of data from the JSON.) http://www.postgresql.org/docs/9.1/interactive/indexes-expre...

I was just coming here to say exactly this. You could also use a partial index (that is, an index with a WHERE clause; so, "WHERE json_field LIKE '%foo.bar = baz%'" or whatever).

Re: Building a MongoDB Clone in Postgres

#23

Post by jerrysievert » Mon, Jun 04, 2012, 6:35 PM UTC

rarrrrrr wrote: post #21

jerrysievert wrote: post #13

Earlier quoted context omitted.

exactly. calling the find_in_obj() function can get fairly expensive, especially given the need to execute JSON.parse() for every call. there is definitely a lot of room for improvement in the postgres native JSON toolkit. i am hoping that building this exposes more of those issues and helps move it forward.

I haven't tried it, but I think you would be able to use a PostgreSQL functional index (i.e. indexing the result of a function call to extract a particular bit of data from the JSON.) http://www.postgresql.org/docs/9.1/interactive/indexes-expre...

you can, but in order to build a partial index, the function needs to be immutable. not exactly ideal in this case.

Re: Building a MongoDB Clone in Postgres

#24

Post by jeffdavis » Mon, Jun 04, 2012, 6:36 PM UTC

nsanch wrote: post #16

jshen wrote: post #10

Earlier quoted context omitted.

"but I wouldn't build a serious site on top of anything that didn't have some sort of built-in sharding story." There are many serious sites that don't need sharding.

Fair, my statement was overly broad. Sites that are read-only or store blob data in something like S3 can often avoid sharding for quite a while and rely on machines to just get bigger over time. That said, if your site grows in some way you didn't originally anticipate and you get to a point where you need to shard, but can only do so by changing data stores, then it's sad.

"That said, if your site grows in some way you didn't originally anticipate and you get to a point where you need to shard, but can only do so by changing data stores, then it's sad."

I think you're being too absolute. For instance, Instagram used sharding in postgres, and they didn't have to throw anything away or dedicate any huge engineering team to solve it.

Re: Building a MongoDB Clone in Postgres

#25

Post by mtkd » Mon, Jun 04, 2012, 6:37 PM UTC

I do encourage any SQL user, who hasn't already tried MongoDB, to fire it up and try it themselves. Mongoid in Ruby is fairly fast to get started.

I've been using SQL since early 90s. For web apps and large collections I've started using MongoDB more recently. It's one of the most exciting technologies I've used in a long time.

It takes a while to stop thinking SQL, but once you pass that it's really very primitive (in a good way) - and frictionless for development. It really fits what I want to do with web apps in particular.

So far I've not needed to scale it, but I've less apprehension about that than I used to have about scaling SQL back in the day - the large denormalised tables I used to build from SQL for performance are now the default.

SQL still has a place and MongoDB is no replacement for complex models, but give it a try before buying that you can do it all with Postgres tweaks.

Re: Building a MongoDB Clone in Postgres

#26

Post by rosser » Mon, Jun 04, 2012, 6:40 PM UTC

jerrysievert wrote: post #23

rarrrrrr wrote: post #21

Earlier quoted context omitted.

I haven't tried it, but I think you would be able to use a PostgreSQL functional index (i.e. indexing the result of a function call to extract a particular bit of data from the JSON.) http://www.postgresql.org/docs/9.1/interactive/indexes-expre...

you can, but in order to build a partial index, the function needs to be immutable. not exactly ideal in this case.

Immutability is perfectly appropriate for that use-case, though. Creating an index via a function that may return different outputs when provided the same input is always going to lose.

Re: Building a MongoDB Clone in Postgres

#27

Post by jshen » Mon, Jun 04, 2012, 6:42 PM UTC

nsanch wrote: post #16

jshen wrote: post #10

Earlier quoted context omitted.

"but I wouldn't build a serious site on top of anything that didn't have some sort of built-in sharding story." There are many serious sites that don't need sharding.

Fair, my statement was overly broad. Sites that are read-only or store blob data in something like S3 can often avoid sharding for quite a while and rely on machines to just get bigger over time. That said, if your site grows in some way you didn't originally anticipate and you get to a point where you need to shard, but can only do so by changing data stores, then it's sad.

" Sites that are read-only or store blob data in something like S3 can often avoid sharding"

Still too broad. Sorry, this is a pet peeve of mine, where tech people assume that everyone else has the same issues as them. For example, I worked on an ecommerce site that made over a mil a year. They had less than 10k products, and will never need sharding. They are not read-only, they have people updating their products on a daily basis through the site.

Re: Building a MongoDB Clone in Postgres

#28

Post by jonknee » Mon, Jun 04, 2012, 6:59 PM UTC

nsanch wrote: post #16

jshen wrote: post #10

Earlier quoted context omitted.

"but I wouldn't build a serious site on top of anything that didn't have some sort of built-in sharding story." There are many serious sites that don't need sharding.

Fair, my statement was overly broad. Sites that are read-only or store blob data in something like S3 can often avoid sharding for quite a while and rely on machines to just get bigger over time. That said, if your site grows in some way you didn't originally anticipate and you get to a point where you need to shard, but can only do so by changing data stores, then it's sad.

> That said, if your site grows in some way you didn't originally anticipate and you get to a point where you need to shard, but can only do so by changing data stores, then it's sad.

I don't agree. Almost every site that grows in ways that weren't anticipated (or at a scale that wasn't anticipated) will have to make technical changes. If you don't need to make any changes it's almost certainly the case that you originally over-engineered. If you're optimizing for cases that you don't anticipate, I don't know what to call it other than over-engineering. Facebook started simple and only made Cassandra when they needed to, Google didn't have BigTable when they started, etc etc.

Re: Building a MongoDB Clone in Postgres

#29

Post by rhizome » Mon, Jun 04, 2012, 7:05 PM UTC

randomdata wrote: post #19

rhizome wrote: post #15

Earlier quoted context omitted.

Isn't this a category error? If you need to query within serialized data, you don't want to serialize. Normalize your database for this.

Wouldn't that kind of defeat the purpose of using a document database in the first place? Being able to throw unstructured data into the system, and then being able to query on that data once the space is better understood is where the document databases really shine. If you are able to start with rigid structure, you, in many cases, could have just used a relational database to begin with.

Wouldn't that kind of defeat the purpose of using a document database in the first place?

Yes. Use the right tool for the job.

Re: Building a MongoDB Clone in Postgres

#30

Post by rhizome » Mon, Jun 04, 2012, 7:06 PM UTC

rdtsc wrote: post #18

rhizome wrote: post #15

Earlier quoted context omitted.

Isn't this a category error? If you need to query within serialized data, you don't want to serialize. Normalize your database for this.

Or de-normalize and keep your data serialized but also keep an additional index of data you want to query.

Serialized is already denormalized, you're talking about a kind of double-denormalization where you introduce metadata as a guide for the serialized-data queries. Spaghetti.