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

6 points by _448 3 years 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.

petercooper 3 years ago

You absolutely can use Postgres for this. There are extensions for the auditing. You could rig something up with triggers for the notifications or use Supabase depending on how realtime you want to be. Much of the decision comes down to the ops side and how/where you're going to manage it, because Postgres will eat up this use case for breakfast no problem.