Ask HN: Shared Database Anti(?)-Pattern | Hacker News Reader

Ask HN: Shared Database Anti(?)-Pattern

5 pointscristaloleg5 comments

Hi, looks like another team is interested in one of our tables, they're planning to just read, so it will be 100% read-only queries (probably from replica).

I'm curious, how popular/acceptable/hopeless such pattern is?

I understand that giving someone read access to the database isn't free (we cannot change schema easily, we cannot change DB, etc) but at the same time, we don't need to create a dummy HTTP-service to just do simple queries (and spend more time on unmarshal-marshal stuff).

Let's assume I'm asking about Postgres but will be very happy to hear stories or thoughts about other DBs (relational, nosql, graph, wide-column, analytical, inmem, etc) and are there any difference in sharing them practically or architectural.

Thank you.

View on HN

We use Stitch[1] for this. You can selectively replicate some tables from any "source" to any selected destination[2]. I believe 1 destination is there on the free plan. You can point to any self hosted(or cloud) Postgres DBs. There are a bunch of supported destinations eg. Postgres, Snowflake etc. The sources also can be a varied like Google sheets.

We use this to send relevant tables to analytics teams outside of engineering. There's a bit of tweaking initially(i.e. having a timestamp column like 'last_updated` helps to replicate only those rows)

Having a separate readonly DB user is also pretty common. We use that internally inside engineering(where we trust them slightly more). Important bit is to always add a statement_timeout so people cannot not run random slow queries on your production database that are hung for days.

I've also had my eye on https://www.singer.io/ , which seems like an open source ETL tool similar to Stitch. But i've not experimented with it locally yet. It uses "taps" and "targets" nomenclature. And there's a healthy ecosystem of opensource extensions of creating your own taps/targets.

[1] https://www.stitchdata.com/

[2] https://www.stitchdata.com/integrations/destinations/

It's not that you can't change the DB. Options could be:

- collaborate more with any breaking changes

- add features to the DB and keep backwards compatibility

- use a DB View so it can be changed as needed and not affect other apps (most similar to an API)

- use Stored Procedures instead of direct table access as it gives more compatibility

- there are other methods too.

Nice trick with stored procedures, I thought only about views.

Agree, we can change the DB but if someone access the db with Postgres driver and after a month it's Mongo - user also must change the driver and this must be aligned, discussed and planned upfront.

If the table is copied somewhere else, you must also remember to update both copies when there is an update or other things can go out of sync.

(Depends on what this table is and how often it can change if at all.)

How to mirror an Oracle Table to MongoDB https://medium.com/@johnlpage/oracle-pl-sql-and-the-mongodb-...

Reply on news.ycombinator.com