Ask HN: Am I abusing Postgres? If yes, then what are the alternatives?

6 points by _448 4 days ago

I have this requirement for an app(mobile and web) where the users can create different collections of data attributes. The attributes can be name, email, text etc. A collection itself can be an attribute. The users can then create relations between these collections. The relations also can hold attributes such as name, description, settings etc. Setting itself can be a collection of attributes. Users can create any number of collections and link then using various relations.

    +--------------+     +------------+     +--------------+
    | collection_a |---->| relation_x |---->| collection_b |
    +--------------+     +------------+     +--------------+
           |            +------------+     +--------------+
           +------------| relation_y |<----| collection_c |
                        +------------+     +--------------+
There are other requirements such as, a user should receive notifications if specified data changes. So the DB should support notifications. And the other requirements are that the DB should support auditing and be open source with active community.

So I chose PostgreSQL and came up with this DB design:

collection { id, name, json_data } relation {id, collection_from_id, collection_to_id}

Just two tables.

Is Postgres the right DB for this or are there better alternatives? I considered CouchDB, but gave up the idea as audit support is challenging.