postgrest VS TimescaleDB

wait till you find out https://github.com/PostgREST/postgrest.

Yes that's exactly what I'm reading here and there. It seems to be the approach that Supabase is taking itself when receiving queries via their API gateway, powered by PostgREST. I had a look at the source code for the latter and it's basically converting the claims in the received JWT into as many local set_config() calls (basically a wrapper function around SET LOCAL). See this related discussion.

I suspect this is the culprit (might be wrong): https://github.com/PostgREST/postgrest/blob/main/src/PostgREST/Config/PgVersion.hs

Pretty cool - wonder how it compares to Postgrest (https://postgrest.org/).

Generally been a fan of these sorts of models - SQL data is very malleable - and treating the tables as movable from the start really helps move faster as a developer - database migrations become easier as you don't need to do a lot of work with API.

I can see how this would cause problems if you always used this in production after finding a fit for the data model - but for rapid prototyping and development - big fan.

Just as an addendum, the supabase.com team are bullish on the power and value of leaning heavily into the database. iirc, they're also the team behind postgREST which makes it easy to spin up a full REST API on the back of a PostgREST database. I used to think this kind of approach was a disaster, but I've changed my mind significantly. When being willing to use stored procedures, you can spin up this REST API, AND use the same stored procedures for your application as well, to ensure consistency of business logic, all without needing a separate business logic API middleware service between DB and the rest of the world. It's not all roses, but it is pretty amazing.

At work, we've finally replaced a large part of a custom (mostly-)web backend with PostgREST recently, and that's quite a relief: considerably less code to maintain in that project now, and that was a rather awkward code. Something akin to PostgREST's "Embedding with Top-level Filtering" [1] had to be provided for all the tables, with OpenAPI schema and a typed API (Haskell + Servant); I avoided manually writing it all down, but at the cost of poking framework internals, and maintainability suffered. It was particularly annoying that the code doesn't really do anything useful, except for standing between a database and an HTTP client, and simply mimics the database anyway. Whenever a change had to be introduced, it was introduced into the database, the backend, and the frontend simultaneously, so it wasn't even useful for some kind of compatibility.

Now PostgREST handles all that, and only a few less trivial endpoints are handled by a custom backend (including streaming, which I'm considering replacing with postgrest-websocket [2] at some point).

During the switch to PostgREST, the encountered minor issues were those with inherited tables (had to set a bunch of computed/virtual columns [3] in order to "embed" those), and with a bug on filtering using such relations (turned out it was an already-fixed regression [4], so an update helped). Also a couple of helper stored procedures (to use via /rpc/) for updates in multiple tables at once (many-to-many relationships, to edit entities along with their relationships, using fewer requests) were added (though the old custom backend didn't have that), the security policies were set from the beginning, the frontend was rewritten (which allowed to finally switch without adding more work), so it was only left to cleanup the backend.

Not using views, since as mentioned above, database changes usually correspond to frontend changes, and the API doesn't have to be that stable yet.

Happy with it so far.

[1] https://postgrest.org/en/stable/api.html#embedding-with-top-...

[2] https://github.com/diogob/postgres-websockets

[3] https://postgrest.org/en/stable/api.html#computed-virtual-co...

[4] https://github.com/PostgREST/postgrest/issues/2530

For how often Nix is mentioned here on HackerNews, has anybody looked at the Nix config files for this repo to build it locally?

https://github.com/PostgREST/postgrest/blob/main/cabal.proje...

I don't know for the stack you're looking for but is this close enough ? https://github.com/PostgREST/postgrest

It's neither MySQL nor Go, but https://github.com/PostgREST/postgrest has figured out most of the patterns and API designs you could follow.

Have you heard of timescale? https://www.timescale.com/ Seems similar to ocient but specifically for time series data.

Slowly I understood that instead of a vanilla PostgreSQL database I need to use to use Timescale which is based on PostgreSQL. I am sure others would have come to this conclusion much faster than I did.

Good question... haha I haven't really considered it. I'm no too versed in this domain and so the whole project will be a learning experience. One of the things is that it will include time-series harvest data. I was searching around for ways to implement this and found solutions like TimescaleDB and InfluxDB. Seems like also there are just some plugins that can sit on top of PostgreSQL.

We use Grafana on top of Timescale for metric analytics. It's working pretty well -- we're doing a few dozen events a second.

(NB - blog author/Timescale employee)

One thing we're improving as we move forward in documentation and other areas is explaining why doing joins (and things like window functions) is difficult in continuous aggregates and not the current focus. Honestly, it's part of the reason most databases haven't tackled this problem before.

Once you add in joins or things that might refer to data outside of the refresh window (LAG values for example), things get really complicated. For instance, if you join to a dimension table and a piece of metadata changes, does that change now need to be updated and reflected back in all of this historical aggregate data that's outside of the automatic refresh policy? Same with a window function - if data within a window hasn't changed but data that *might* be hit because of the window function reference does change, continuous aggregates would have to know about that for each query and track those changes too.

I'm not saying it's impossible or that it won't be solved someday, but the functionality with continuous aggregates that keeps the aggregate data updated automatically (without losing any history) *and* being able to perform fast joins on the finalized data is a very useful step that's not available anywhere else within the Postgres ecosystem.

RE: CAGG on top of a CAGG - you're certainly not the only person to request this[1] () and we understand that. Part of this is because of what I discussed above (tracking changes across multiple tables), although having finalized data might make this more possible in the future.

That said (!!!), the cool thing is that we already *have* begun to solve this problem with hyperfunction aggregates and 2-step aggregation, something I showed in the blog post. So, if your dataset can benefit from one of the hyperfunction aggregates that we currently provide, there are lots of cool things you can do with it, including rollups into bigger buckets without creating a second continuous aggregate! If you haven't checked them out, please do! [2][3]

[1]: https://github.com/timescale/timescaledb/issues/1400

either we go for RDS, but we stick to the AWS handpicked extensions (exit timescale, citus or their columnar storage, ... ),

It could make sense to switch to a timescales db which could store and query timescales much more efficient than mariadb. Of course, for the indented small use-cases mariadb could still be more than enough. For postgresql, there would be the https://github.com/timescale/timescaledb extension.