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

161–170 of 210 posts

Re: PostgREST – REST API from any PostgreSQL database

#161

Post by brightball »

threeseed wrote: post #136
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…

How can you compare being locked into a DB versus locked into an ORM ? One of the main features of an ORM has been abstraction from the intrinsic properties of that database. ORM was a concept that was popularised by the original Obj-C/EOF/WebObjects back in the day which supported retrieving data from any database you pointed it at. And it fully supported you enhancing it's access layer with database specific featur…

This is a much longer topic but it boils down to 2 things:

1. Switching your database is not easy with or without stored procedures because it will involve down time for the application while the data is migrated, then verifying that it works as expected in the new database with that ORM. You hope for the best, but it's always more complicated to switch a database.

2. The ORM tends to lock you into the application stack. Switching a part of your application from something like Rails to Go when you need to performance tune is significantly easier and more common than switching the entire database backing the whole system.

Beyond those two are the harsh realities of working with large datasets. As soon as a dataset it non-trivially small relying on the application to do core work on it becomes self destructive by adding network latency and in many cases object creation (check some Rails benchmarks on object creation costs). It becomes a big deal.

This is not to say that doing the bulk of work in the ORM is bad or that everything should be done in the database, it's a matter of balance. The only dangerous opinions on the matter are the "purist to the detriment of all else."

Verifying uniqueness, exclusion and maintaining data integrity should be the job of the database in most cases. That is what it's good at. Performing actual business logic on that data should not unless there is a significant performance based reason for it in most cases.

In Postgres the "stored procedure" thing is a little bit different because they're significantly more valuable thanks to the volume of functionality built into PG. Everything is basically a function in PG.

In PG, you can use functions to create indexes and when the function is used in a where clause that index will be used. You can use functions to create constraints, unique indexes and even notify outside process that are listening of changes in the database with pubsub.

PG is a heck of a lot more than just a "datastore" and that's why these discussions are important. If you want a generic dumb datastore...there are databases built for that. PG is built for a whole lot more than that.

Here's a very incomplete summary: http://www.brightball.com/postgresql/why-should-you-learn-po...

Re: PostgREST – REST API from any PostgreSQL database

#162

Post by radiowave »

cbau wrote: post #150
pilif wrote: post #6

Contrary to many other "expose a RDBMS schema as an API" solutions, this one is interesting due to its very close tie-in with postgres. It even uses postgres users for authorization and it relies on the postgres stats collector for caching headers. I also very much liked the idea of using `Range` headers for pagination (which should be out-of-band but rarely is). I'm not convinced that this is the future of web devel…

Resources only map 1-to-1 with database models for trivial applications, so certainly not the future. Still, useful for getting up and running.

Coming from the old world of business IT where "integrate everything with the relational database" is standard procedure, it's certainly not unusual for the database to be a place where abstractions are defined(1), so what you expose via PostgREST may not in fact be a close match for the underlying data model.

(1 - In fact with me it's pretty much a matter of policy: any external system should access data via views named for the external system. Then, as inconvenient as it sometimes is, Postgres' dependency mechanism will keep me right about which fields in which tables are depended upon by which external systems.)

Re: PostgREST – REST API from any PostgreSQL database

#163

Post by brightball »

_yy wrote: post #141
snarfy wrote: post #129

Earlier quoted context omitted.

> The problem is scaling your database There is only one database for everything in the business? Of course it doesn't scale. The problem you describe stems from solving every business request by adding yet another table to 'the' database. It's a monolithic solution. It doesn't matter if you use database features or not. There is no difference between a mess of stored procedures and a mess of business logic classes.…

Web servers usually scale better than (traditional) databases, so it makes sense to not offload computation to the database, even if it means that there's an overhead.

That's very situational. Read scaling a database is easy. Write scaling a database is harder and doing computational logic while write scaling a database is harder still. Computational is still a very broad word though and the intensity of those computations is a huge defining factor.

The problem boils down to the "the database" idea described earlier. There are very, very few normalized datasets that I've ever seen that have write scaling concerns on more than 1 or two tables.

Move those to a separate datastore that is built for it and you've largely solved your problem. Postgres can even connect to outside datastores to run queries against them for sake of reporting.

Re: PostgREST – REST API from any PostgreSQL database

#164

Post by brightball »

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…

By the same logic though, at the point that heavy write load becomes a reality it's just as feasible to move the heavy write table to an isolated datastore and leave 95% of your data (structurally) in the PG. Even use a PG foreign data wrapper to connect to that new datastore to allow PG to continue any necessary queries against it.

I'm not ever going to argue for heavy stored procedure usage but there are definitely times when it makes sense and more times still when using the features in your database instead of setting up multiple different standalone systems for pubsub, search, json data, etc when your database can do it all makes sense.

It's very similar to the "you can always switch the slow parts" point with Rails to move a part to Go. You can do it all in PostgreSQL and then when you actually reach a point where you've grown it into a bottleneck, move it out.

Postgres isn't SQL Server and it isn't Oracle and it isn't MySQL. It's Postgres. It's a tool that you choose because of it fits your needs, not because somebody told you it was a good database. You choose it as part of your stack. If you are using PostgreSQL because you wanted a dumb datastore then you chose the wrong database and should probably reavaluate your options. That's like getting a Lamborghini to make grocery runs.

http://www.brightball.com/postgresql/why-should-you-learn-po...

Re: PostgREST – REST API from any PostgreSQL database

#165

Post by volaski »

AlisdairO wrote: post #160
threeseed wrote: post #133

Earlier quoted context omitted.

Scaling the data layer is a huge challenge. No doubt. But calling databases that are designed for solving these problems "snake oil" undermines the huge amount of work that serious engineers have invested in this. No one has ever promised linear scalability and perfect consistency/reliability. No one. Cassandra, HBase, CouchDB etc even MongoDB have built in scalability as a first order priority from day one and have…

> 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"

Re: PostgREST – REST API from any PostgreSQL database

#166

Post by mozumder »

curiousjorge wrote: post #148

The comments are unbelievably negative considering the quality and the range of features this offers. This is extremely useful because I won't have to spend time writing out REST api in order to expose the Postgre data. Often a client just wants to access the data with REST api and to write an entire stack just to serve a few doesn't make sense. There's no expectation that this is going to serve a gazillion requests…

A lot of the optimizations are for responsiveness, not throughput. It's terrible to work with a site that has a one second response time. Cut that down to 15ms and it's an entirely different site, something that you might want to use. And, the side benefit is that it's more likely to support 10,000 users/second because you just optimized the system.

Re: PostgREST – REST API from any PostgreSQL database

#167

Post by brightball »

batou wrote: post #109
nunwuo wrote: post #94

Earlier quoted context omitted.

> Avoid state at all costs. Stored procedures are stateful. Schema and migrations is pain enough already. What do you mean by that? How is having a bunch of queries in a stored procedure more "stateful" than having the same queries in the application? > Write me a check constraint that validates an email address being put in a varchar column and reports back a sensible message which can be bound to an entry field wit…

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 then saving the number back to the database and instead pass in an increment command.

Check it in the application but let the database make sure it doesn't get violated in a race condition. There's a significant amount of either/or in this entire conversation (not just you, the whole thread) when the database absolutely can and should be leveraged for certain things.

It's extremism and purism where the problems get introduced (in both directions).

Re: PostgREST – REST API from any PostgreSQL database

#168

Post by qaqy »

scottschulthess wrote: post #147
_yy wrote: post #141

Earlier quoted context omitted.

Web servers usually scale better than (traditional) databases, so it makes sense to not offload computation to the database, even if it means that there's an overhead.

Web server codebases are typically also way easier to modify, unit test, with better tools and languages.

there is even pl\brainfuck so as far as choice of langs PG has you covered

Re: PostgREST – REST API from any PostgreSQL database

#169

Post by vkjv »

curiousjorge wrote: post #148

The comments are unbelievably negative considering the quality and the range of features this offers. This is extremely useful because I won't have to spend time writing out REST api in order to expose the Postgre data. Often a client just wants to access the data with REST api and to write an entire stack just to serve a few doesn't make sense. There's no expectation that this is going to serve a gazillion requests…

I imagine some of the negative comments are due to the passive aggressiveness of the README.

"It provides a cleaner, more standards-compliant, faster API than you are likely to write from scratch."

"If you're used to servers written in interpreted languages (or named after precious gems), prepare to be pleasantly surprised by PostgREST performance."

Re: PostgREST – REST API from any PostgreSQL database

#170

Post by elchief »

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?

Cuz you use GET for queries with REST
Post reply on HN