Postgres Pro
Downloads
Home   >   mailing lists

Thread: Postgres NoSQL emulation

Postgres NoSQL emulation

From
"Pierre C"
Date:
While reading about NoSQL,

> MongoDB let's you store and search JSON objects.In that case, you don't
> need to have the same "columns" in each "row"

The following ensued. Isn't it cute ?

CREATE TABLE mongo ( id SERIAL PRIMARY KEY, obj hstore NOT NULL );
INSERT INTO mongo (obj) SELECT ('a=>'||n||',key'||(n%10)||'=>'||n)::hstore
 FROM generate_series(1,100000) n;

SELECT * FROM mongo LIMIT 10;
  id |           obj
----+-------------------------
   1 | "a"=>"1", "key1"=>"1"
   2 | "a"=>"2", "key2"=>"2"
   3 | "a"=>"3", "key3"=>"3"
   4 | "a"=>"4", "key4"=>"4"
   5 | "a"=>"5", "key5"=>"5"
   6 | "a"=>"6", "key6"=>"6"
   7 | "a"=>"7", "key7"=>"7"
   8 | "a"=>"8", "key8"=>"8"
   9 | "a"=>"9", "key9"=>"9"
  10 | "a"=>"10", "key0"=>"10"

CREATE INDEX mongo_a ON mongo((obj->'a')) WHERE (obj->'a') IS NOT NULL;
CREATE INDEX mongo_k1 ON mongo((obj->'key1')) WHERE (obj->'key1') IS NOT
NULL;
CREATE INDEX mongo_k2 ON mongo((obj->'key2')) WHERE (obj->'key2') IS NOT
NULL;
VACUUM ANALYZE mongo;

SELECT * FROM mongo WHERE (obj->'key1')='271';
  id  |            obj
-----+---------------------------
  271 | "a"=>"271", "key1"=>"271"
(1 ligne)

EXPLAIN ANALYZE SELECT * FROM mongo WHERE (obj->'key1')='271';
                                                      QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
  Index Scan using mongo_k1 on mongo  (cost=0.00..567.05 rows=513 width=36)
(actual time=0.024..0.025 rows=1 loops=1)
    Index Cond: ((obj -> 'key1'::text) = '271'::text)
  Total runtime: 0.048 ms

Re: Postgres NoSQL emulation

From
Merlin Moncure
Date:
On Tue, May 10, 2011 at 12:56 PM, Pierre C <lists@peufeu.com> wrote:
>
> While reading about NoSQL,
>
>> MongoDB let's you store and search JSON objects.In that case, you don't
>> need to have the same "columns" in each "row"
>
> The following ensued. Isn't it cute ?
>
> CREATE TABLE mongo ( id SERIAL PRIMARY KEY, obj hstore NOT NULL );
> INSERT INTO mongo (obj) SELECT ('a=>'||n||',key'||(n%10)||'=>'||n)::hstore
> FROM generate_series(1,100000) n;
>
> SELECT * FROM mongo LIMIT 10;
>  id |           obj
> ----+-------------------------
>  1 | "a"=>"1", "key1"=>"1"
>  2 | "a"=>"2", "key2"=>"2"
>  3 | "a"=>"3", "key3"=>"3"
>  4 | "a"=>"4", "key4"=>"4"
>  5 | "a"=>"5", "key5"=>"5"
>  6 | "a"=>"6", "key6"=>"6"
>  7 | "a"=>"7", "key7"=>"7"
>  8 | "a"=>"8", "key8"=>"8"
>  9 | "a"=>"9", "key9"=>"9"
>  10 | "a"=>"10", "key0"=>"10"
>
> CREATE INDEX mongo_a ON mongo((obj->'a')) WHERE (obj->'a') IS NOT NULL;
> CREATE INDEX mongo_k1 ON mongo((obj->'key1')) WHERE (obj->'key1') IS NOT
> NULL;
> CREATE INDEX mongo_k2 ON mongo((obj->'key2')) WHERE (obj->'key2') IS NOT
> NULL;
> VACUUM ANALYZE mongo;
>
> SELECT * FROM mongo WHERE (obj->'key1')='271';
>  id  |            obj
> -----+---------------------------
>  271 | "a"=>"271", "key1"=>"271"
> (1 ligne)
>
> EXPLAIN ANALYZE SELECT * FROM mongo WHERE (obj->'key1')='271';
>                                                     QUERY PLAN
> ---------------------------------------------------------------------------------------------------------------------
>  Index Scan using mongo_k1 on mongo  (cost=0.00..567.05 rows=513 width=36)
> (actual time=0.024..0.025 rows=1 loops=1)
>   Index Cond: ((obj -> 'key1'::text) = '271'::text)
>  Total runtime: 0.048 ms

why even  have multiple rows? just jam it all it there! :-D

merlin

Re: Postgres NoSQL emulation

From
Scott Marlowe
Date:
On Tue, May 10, 2011 at 3:32 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
> why even  have multiple rows? just jam it all it there! :-D

Exactly, serialize the object and stuff it into a simple key->value
table.  Way more efficient than EAV.

Re: Postgres NoSQL emulation

From
"Pierre C"
Date:
> why even  have multiple rows? just jam it all it there! :-D

LOL

But seriously, when using an ORM to stuff an object hierarchy into a
database, you usually get problems with class inheritance, and all
solutions suck more or less (ie, you get a zillion tables, with assorted
pile of JOINs, or stinky key/attributes schemes where all attributes end
up as TEXT, or a table with 200 columns, most of them being NULL for a
given line).

NoSQL guys say "hey just use NoSQL !".

In a (common) case where the classes have some fields in common and othen
searched, and that the DB needs to know about and access easily, those
become columns, with indexes. Then the other fields which only occur in
some derived class and are not very interesting to the DB get shoved into
a hstore. The big bonus being that you use only one table, and the "extra"
fields can still be accessed and indexed (but a little slower than a
normal column). However I believe hstore can only store TEXT values...

Could be interesting. Or not.