Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

PostgREST – REST API from any PostgreSQL database

github.com

Post reply on HN
Print view View original

171–180 of 210 posts

Re: PostgREST – REST API from any PostgreSQL database

#171

Post by vkjv »

scttnlsn wrote: post #155
caf wrote: post #45

Earlier quoted context omitted.

Presumably because browsers talk HTTP, but don't talk Postgres' native protocol.

Yeah, this seems great if you want to completely eliminate the middle tier and have a client application talk directly w/ the database. I'm curious why there's an entire query API via query string parameters. Why not just expose a single POST /query endpoint where you can send some SQL?

You can take advantage of browser and CDN caching with a GET but not with a POST.

Throw in ETAGs and you can do a lot of cool stuff to make things scale really well.

Re: PostgREST – REST API from any PostgreSQL database

#172

Post by vkjv »

3pt14159 wrote: post #139
rapind wrote: post #130

Earlier quoted context omitted.

Once you get rid of your N+1s the bottleneck in my experience (working with Rails now since v1.2) has always be Rails / Ruby itself. It is so incredibly slow, even using just Metal (even Sinatra for that matter). The slowdown at the view level is significant. I always have a caching strategy (usually varnish in front of nginx) with Rails unless it's literally only supporting a handful of users, and anytime I need to…

The point is that when Rails gets too slow it is very easy to switch to something like cacheing or C (Or Go, or whatever). Even if you just split it off at nginx or use a worker pool in a faster language. Or if you need lots of concurrency use Go. Or even replace the Ruby code with one fairly nasty SQL statement or a single stored procedure. The other 95% of your code can be slow Rails. You know those pages where a u…

I am a postgresql novice, but I've used the JSON serialization and it is indeed fast. But, here's my question:

When you do a 1-to-many join and return the same fields very many times, do the binary drivers optimize that or is it return many times? With JSON serialization (or serializing to arrays), you only get the one row.

Re: PostgREST – REST API from any PostgreSQL database

#174

Post by mixmastamyk »

batou wrote: post #135
benkant wrote: post #131

Earlier quoted context omitted.

I'm sorry to hear your anecdote. I would have to come see your particular situation to see exactly what you mean by 1, 2, 5, because those don't seem unsolvable, but in general: > 3) Orthoganality You've introduced that by treating a relational store as a "hole to put your shit in". It's not fair to blame the database for that. > 4) Duplication Not with the project that is the topic of this thread you don't. > 6) Loc…

Certainly not an anedote. I was an Oracle and SQL Server DBA for a number of years amongst other hats on stupid big datasets and loads. Add to that 25 years' experience getting companies out of deep trouble that everyone else has given up on. I know my shit. Orthogonality: I haven't introduced anything here. Very rarely does any conceptual model of reality fit into the relational model. It's more imperative than that…

Interesting, though as this is a way to interface with Postgres I'm not sure your warnings about licensing costs are useful. Still, there are the other reasons you mentioned.

Re: PostgREST – REST API from any PostgreSQL database

#175

Post by glogla »

kelseydh wrote: post #128
glogla wrote: post #69

Earlier quoted context omitted.

You can do that using SQL views. That means you can provide consistent api of your choosing no matter how the original tables look like. Postgrest docs also describe how you should use this feature to version you API.

Implementing SQL views is not exactly trivial if you are operating under an existing Ruby on Rails app. I suspect most RoR developers don't use SQL views unless they absolutely need to for a certain query that's heavy on performance. As a result, the coupling to the schema does seem to be concerning as moving everything to SQL views appears to be a high amount of overhead if you're already successfully relying on an…

You're right. It kind of depends on whether you look on the DB as a datastore, that is the DB and the Rails app are the same "system" with regards to other parts of the infrastructure, or of the database is system by itself, that provides services to other systems.

It depends on what the DB does, really - eshop app + db might be the first one, while operational data warehouse would be the second. Usually you end up with something in the middle - like the eshop app, that provides views with summarized data to be ETLed to reporting.

Re: PostgREST – REST API from any PostgreSQL database

#176

Post by AlisdairO »

volaski wrote: post #165
AlisdairO wrote: post #160

Earlier quoted context omitted.

> It's always a shame to see HN act like you scale vertically and magically every problem is solved. When this is seen (and IME it's a pretty minority opinion) I think it's there as a reaction to the massive overuse and hype regarding a lot of newer-gen DBs. There's absolutely no doubt that there are good uses for them, but those cases are pretty niche compared to the level of their uptake.

You should read "innovator's dilemma"

Is that comment intended to imply that companies will go under if they fail to deploy new technology that doesn't target their business needs?

Re: PostgREST – REST API from any PostgreSQL database

#177

Post by merb »

brightball wrote: post #167
batou wrote: post #109

Earlier quoted context omitted.

Stateful: If I have to load the stored procedure into the persistence engine then that step is required. This is no more stateful than queries in the application but it means that the relevant state in both the application and the database engine needs to be reloaded and constantly sychronised. Ergo, two times the work. CHECK constraint violated is no good for humans. Prevention is better than cure here. Why shouldn'…

"Why shouldn't I enforce unique constraints in the application?" You should to both. For all the reasons you mention, it's often cleaner to just do it in the application especially when you can use a framework with a simple "validate_uniqueness" flag. But, what you're describing is also the very definition of a race condition. It's the same reason you don't increment counters by retrieving them, adding 1 to it and th…

I do most what you say and still think both of you are right. I mean stored procedures have some use cases but i've seen people using it EVERYWHERE and I've seen people (including myself) NEVER use it.

I mean currently my dataset is so small I don't need stored procedures, I barely do anything more than CRUD. Okay I have a bigger GROUP BY query but that is all, and at one point I load a HUGE dataset into my application memory (1000 rows) but that works REALLY REALLY fast in scala and I tried to create a stored procedure around it, but I failed, and the application code uses the dataset to generate a big calculation. Currently I just have a Map> which is easy accessible and usable for my calculation. I mean I could've done similar with stored procedures but the performance gains are really low.

Re: PostgREST – REST API from any PostgreSQL database

#178

Post by [deleted] »

vkjv wrote: post #171
scttnlsn wrote: post #155

Earlier quoted context omitted.

Yeah, this seems great if you want to completely eliminate the middle tier and have a client application talk directly w/ the database. I'm curious why there's an entire query API via query string parameters. Why not just expose a single POST /query endpoint where you can send some SQL?

You can take advantage of browser and CDN caching with a GET but not with a POST. Throw in ETAGs and you can do a lot of cool stuff to make things scale really well.

[deleted]

Re: PostgREST – REST API from any PostgreSQL database

#179

Post by brightball »

merb wrote: post #177
brightball wrote: post #167

Earlier quoted context omitted.

"Why shouldn't I enforce unique constraints in the application?" You should to both. For all the reasons you mention, it's often cleaner to just do it in the application especially when you can use a framework with a simple "validate_uniqueness" flag. But, what you're describing is also the very definition of a race condition. It's the same reason you don't increment counters by retrieving them, adding 1 to it and th…

I do most what you say and still think both of you are right. I mean stored procedures have some use cases but i've seen people using it EVERYWHERE and I've seen people (including myself) NEVER use it. I mean currently my dataset is so small I don't need stored procedures, I barely do anything more than CRUD. Okay I have a bigger GROUP BY query but that is all, and at one point I load a HUGE dataset into my applicati…

For what you're describing it doesn't sound like stored procs are worth it. Avoid introducing them unless you find that they are necessary or beneficial, but don't avoid them entirely on principle.

Preserving data integrity tends to be a much more worthy use case for database logic than retrieval display.

Re: PostgREST – REST API from any PostgreSQL database

#180

Post by batou »

mixmastamyk wrote: post #174
batou wrote: post #135

Earlier quoted context omitted.

Certainly not an anedote. I was an Oracle and SQL Server DBA for a number of years amongst other hats on stupid big datasets and loads. Add to that 25 years' experience getting companies out of deep trouble that everyone else has given up on. I know my shit. Orthogonality: I haven't introduced anything here. Very rarely does any conceptual model of reality fit into the relational model. It's more imperative than that…

Interesting, though as this is a way to interface with Postgres I'm not sure your warnings about licensing costs are useful. Still, there are the other reasons you mentioned.

Some organisations won't allow use of a product unless there's a support option. I can understand this after a complete system failure a few years back on an open source unsupported product. I was hired to fix it! :) Mostly though in comes EnterpriseDB and support costs then. Problem is always staff availability here in the UK though so we always end up with SQL Server and Orscle bodies.
Post reply on HN