Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

PostgreSQL as Schemaless Database [pdf]

wiki.postgresql.org

Post reply on HN
Print view View original

21–30 of 90 posts

Re: PostgreSQL as Schemaless Database [pdf]

#21

Post by aaronyo »

eksith wrote: post #18
aaronyo wrote: post #9

Earlier quoted context omitted.

Do you have any thoughts on expression indexes? Assumed I would have to pull out fields from the document to index reasonably on pgsql, but this presentation at least shows that simple expression indexes perform well. I wonder about compound, nested, etc.

I'm not too familiar with the current schema, but I do know we make extensive use of partial indexes ( WHERE (vnnk IS NOT NULL) , WHERE (fhs > 0) etc...) and compound indexes in btree. In GIN, for example, this isn't possible, but GIN itself works reasonably well for partial matches (since it's also btree) so it's not too much of a deal. We use a combination of hstore, GIN, conventional fields and multiple indexes wi…

Reliability is gold.

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.

Re: PostgreSQL as Schemaless Database [pdf]

#22

Post by nnq »

jacques_chester wrote: post #20
xenator wrote: post #17

Earlier quoted context omitted.

Don't remember exactly when, probably is was in time when multicore hosting servers becomes relatively cheap. We made measurement MySQL vs PostgreSQL and surpisely found that PG is visible faster. Don't remember exactly numbers. So even if team never push server as supa-pupa-fast on the market they was neat choice for people who know. But the same time, even if PG documentation can be used as textbook for RDB theory…

Right, so MongoDB goes back to my pet theory about how reward schedules shape technology selection. 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 upf…

Care to elaborate on your last sentence? I'm really curious about some corners of software history and this seems like a hint of a corner I never knew existed...

Re: PostgreSQL as Schemaless Database [pdf]

#23

Post by jacques_chester »

nnq wrote: post #22
jacques_chester wrote: post #20

Earlier quoted context omitted.

Right, so MongoDB goes back to my pet theory about how reward schedules shape technology selection. 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 upf…

Care to elaborate on your last sentence? I'm really curious about some corners of software history and this seems like a hint of a corner I never knew existed...

Basically, hierarchical and network databases were around before relational databases.

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).

Re: PostgreSQL as Schemaless Database [pdf]

#24

Post by sargun »

So, the problem that I have with PostgreSQL isn't that it doesn't have every datastore under the sun, but rather the lack of automated distribution, and fault-tolerance.

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.

Re: PostgreSQL as Schemaless Database [pdf]

#28

Post by fpp »

Would be interesting to know which version of MongoDB was used (2.4 also uses V8).

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

Re: PostgreSQL as Schemaless Database [pdf]

#29

Post by mh- »

karteek wrote: post #26

First thing I remembered after reading the title is http://pgsnake.blogspot.in/2010/04/postgres-91-release-theme...

haha, thanks for posting that. i enjoyed:

"It's time for us to switch to something fresher. I personally would have preferred XSLT, but QUEL is almost as good."

Re: PostgreSQL as Schemaless Database [pdf]

#30

Post by karavelov »

sargun wrote: post #24

So, the problem that I have with PostgreSQL isn't that it doesn't have every datastore under the sun, but rather the lack of automated distribution, and fault-tolerance. 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.…

Then you may be interested in PostgreSQL-XC: http://postgres-xc.sourceforge.net/

It is transparent, consistent, load-balancing and fault-tolerant cluster. Though some of the features of PostgreSQL are not supported yet, eg. triggers.

Post reply on HN