pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgres NoSQL emulation
4+ messages / 3 participants
[nested] [flat]
* Postgres NoSQL emulation
@ 2011-05-10 17:56 Pierre C <lists@peufeu.com>
0 siblings, 1 reply; 4+ messages in thread
From: Pierre C @ 2011-05-10 17:56 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Postgres NoSQL emulation
@ 2011-05-10 21:32 Merlin Moncure <mmoncure@gmail.com>
parent: Pierre C <lists@peufeu.com>
0 siblings, 2 replies; 4+ messages in thread
From: Merlin Moncure @ 2011-05-10 21:32 UTC (permalink / raw)
To: Pierre C <lists@peufeu.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Postgres NoSQL emulation
@ 2011-05-11 03:13 Scott Marlowe <scott.marlowe@gmail.com>
parent: Merlin Moncure <mmoncure@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Scott Marlowe @ 2011-05-11 03:13 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; +Cc: Pierre C <lists@peufeu.com>; pgsql-performance
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Postgres NoSQL emulation
@ 2011-05-11 11:14 Pierre C <lists@peufeu.com>
parent: Merlin Moncure <mmoncure@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Pierre C @ 2011-05-11 11:14 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; +Cc: pgsql-performance
> 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.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2011-05-11 11:14 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-05-10 17:56 Postgres NoSQL emulation Pierre C <lists@peufeu.com>
2011-05-10 21:32 ` Merlin Moncure <mmoncure@gmail.com>
2011-05-11 03:13 ` Scott Marlowe <scott.marlowe@gmail.com>
2011-05-11 11:14 ` Pierre C <lists@peufeu.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox