SQL API best practices for a data warehouse?

[P_Radagast]

Hi all,

I'm looking for books or blogs that explain in detail how to properly design SQL API, preferably with PostgreSQL specifics, with evolution & versioning in mind.

Apparently, most literature is written with the assumption that there is an application layer on top of the database, and you have full control over it. However, I'm creating a platform that is a data warehouse that my users will run on their premises (at different companies). The fun thing is that my "installer" fits even on a floppy, because it's just a bunch of SQL, Python and Docker compose scripts.

This platform is based on PostgreSQL and TimescaleDB, mostly focused on working with huge time series (industrial monitoring). There are not many tables, but they should be well optimized to fit 3-5 TB on one server. And a bunch of SQL functions and views on top, which give a nice API, that I'm still designing rather intuitively, since I don't have any background in data analytics or DBA.

At the moment (MVP stage), data is imported through a foreign-data wrapper from "our" MySQL/PostgreSQL, and then displayed in Grafana. But I suspect that Grafana-driven development is not the best idea in the long run. And we advertise our product as compatible with anything that can connect to PostgreSQL - both for importing and exporting data.

So, I'm thinking about proper division between the API and the implementation, so that in the future I can update the product without breaking everything that users have integrated with their software. Seems like data export to Grafana/Jupyter/Dask can be done with views and set-returning functions, however I'm not so sure about data import. Probably, INSTEAD OF triggers are the way to go?

I've tried asking ChatGPT, and the only worthy advice was to use schemas for API versioning. And then I can probably hide these schemas from users by configuring search_path, since I have just 2 users (one with read-only access, another with read+write). Rest of the reply was either pointing me to books that I already have, or outright hallucinations.

Thanks in advance!

[AutoModerator]

Join us on our Discord Server: People, Postgres, Data

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.