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

121–130 of 210 posts

Re: PostgREST – REST API from any PostgreSQL database

#121

Post by benkant »

ecopoesis wrote: post #115
benkant wrote: post #12

This is good work and if I ever did web development, it would be like this. Why people in the web world don't use stored procedures and constraints is a mystery to me. That this approach is seen as novel is in itself fascinating. It's like all those web framework inventors didn't read past chapter 2 of their database manuals. So they wrote a whole pile of code that forces you to add semantics in another language else…

Because databases are a very poor fit for APIs? This is one of my biggest problems with high holy REST: it generally just means reimplementing your SQL API in HTTP semantics. APIs should be about encapsulating business logic. Databases should be about storing data in a reliable, predictable way.

That's what views are for.

Re: PostgREST – REST API from any PostgreSQL database

#122

Post by untog »

abrookewood wrote: post #83
batou wrote: post #51

Earlier quoted context omitted.

That is quite frankly horse poop. We use stored procedures. Not by choice; this is a legacy we're stuck with. It is nothing but an unadulterated disaster of a technology regardless of what you use it for. I'm talking 45,000 stored procedures here. 2000 tables. TiB of data. 50,000 requests/second across SOAP/web/desktop etc. It's hell. Problems with stored procedures: 1) Performance. More code running in the hard to s…

Some counter-points I've heard made: 1) Performance. Stored procedures are fast, meaning it will be longer before you need to scale out. 2) Security. If you only use stored procs, you're a lot less exposed to SQL injections etc. I don't really have a firm opinion either way, but it's not as clear cut as you are making out.

Performance. Stored procedures are fast, meaning it will be longer before you need to scale out.

That was once the case, but every major DB now caches execution plans for commonly run queries.

Re: PostgREST – REST API from any PostgreSQL database

#123

Post by CHY872 »

markbernard wrote: post #50
bdcravens wrote: post #40

Earlier quoted context omitted.

Is it a common circumstance to completely switch your data layer without having a massive rewrite of your application? Do people frequently flip from pg to Oracle, or from SQL Server to MongoDB, and it was super-easy because that layer was abstracted?

Not frequently, but yes. Was forced to move from Oracle to Postgres, not that I mind Postgres. Very little software changes were required to get up and running. If everything was in views or stored procedures the change would have taken months instead of weeks.

On the other hand, I had to write for some software that supported both Oracle and Postgres (many deployments, new using Postgres and old migrating over time to Postgres) and it was a chore. All tests had to be run in both, SQL was 'same but different' with different types, and for perf reasons there was tonnes of hinting, which obviously pg ignores so overall performance was very different.

Re: PostgREST – REST API from any PostgreSQL database

#124

Post by benkant »

pjc50 wrote: post #38
weitzj wrote: post #31

Could maybe somebody of the older experienced people comment whether this is a good idea? I find it intriguing, but maybe I am just one generation behind and you were to say: "Been there done that. This strong dependency on the database was really not a good idea in the long run because... "

I like the way you think. In the old days, DB vendor independence was considered important, because the Free databases were not considered good enough for Enterprise use. It had to be Oracle or IBM. Even if you were starting out on MySQL, you wanted to retain portability for when you moved to Oracle. Hence building ORMs as an abstraction layer allowing vendor independence and more Enterprise-y Java. There's also the…

> There's also the "demarcation" issue.

This is probably just as significant as vendor independence or technical issues. But I remember it a little differently.

In the old days you'd have programmers who did the code and the DBAs who were tasked with making sure the data wasn't broken. They would write SPs, use constraints and triggers etc. Depending on the team programmers might do these as well or instead, but it was the DBAs domain ultimately.

Then programmers wrote ORMs and started doing all that stuff in code and somehow managed to seize control of data validation and semantics from the DBAs.

Re: PostgREST – REST API from any PostgreSQL database

#125

Post by brightball »

3pt14159 wrote: post #65
benkant wrote: post #12

This is good work and if I ever did web development, it would be like this. Why people in the web world don't use stored procedures and constraints is a mystery to me. That this approach is seen as novel is in itself fascinating. It's like all those web framework inventors didn't read past chapter 2 of their database manuals. So they wrote a whole pile of code that forces you to add semantics in another language else…

You are speaking from ignorance with the voice of authority. I worked on a rails app that handled a billion requests per day. The problem isn't performance of the web framework, those are easy to load balance and split into C or cache when you need it. The problem is scaling your database, keeping your data secure, and iterating to meet business goals with a growing codebase and infrastructure. A mess of stored proce…

The bigger issue is this idea that everything needs to live in one place. For the bulk of an application handling a billion requests / day I'd wager that most of that traffic is isolated to certain types of data.

I'd wager that because in almost every case I've ever seen it's true. You just don't tend to see every table in a normalized dataset bearing the traffic load.

If that is the case, rolling that particular piece of data out to a more easily scalable store will largely fix the problem, if caching, async writes and buffered writes didn't already.

Everything else can very easily sit in PostgreSQL, avoid race conditions, maintain data integrity, have permissions controlled and be accessed from multiple languages directly without requiring an API layer. Then you can use a foreign data wrapper to let PG query that other data source (mongo, couchbase, redis, whatever) and join the results with the other data in the database just like it's all one bit happy dataset.

As another poster said, a mess is a mess and honestly I don't know why he takes a shot at Rails since Rails has some of the best first class support for leveraging PostgreSQL features these days.

Wrote an entire post about it: http://www.brightball.com/ruby-postgresql/rails-gems-to-unlo...

Re: PostgREST – REST API from any PostgreSQL database

#126

Post by CHY872 »

wisty wrote: post #64
msane wrote: post #8

Earlier quoted context omitted.

Correct answer. The demo is doing everything right, parent seems confused. Browsers also don't have any special regard for '.json' in the path. The path can be anything; the path doesn't suggest anything about content-type or caching.

Yes, but if / returns a html file, and /.json returns the json, it's impossible for a browser to display the json by accident (no matter what the header is). And yes, the header seems correct, Checking, that's the API demo, the GUI is separate. Thus the confusion. Yeah, headers are the right way to do it, but a different path is also the right way to do it, and extensions like .json can make that simpler in some case…

No. The browser should send with its HTTP request:

    Accept: text/html, application/json;q=0.8
and then if the server supports returning HTML, it should return it ahead of any json. For example, Firefox sends:

    Accept: text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8
and then it will send HTML ahead of xml, ahead of anything else, where it can.

If a RESTful client can only accept JSON, it should send

    Accept: application/json
Headers are the right way to do it with a RESTful API. The reason why is that the path should indicate the resource you're trying to access and the resource is independent of the data format.

Adding '.json', path specifiers etc is a kludge; it requires web server support to actually work properly (it requires the webserver to send the right MIMEtype in the header). The browsers ignore it (I think).

For example, occasionally you'll click on an image (I see it about once a year) and you get back a stream of weird unicode. This is because the webserver is returning the .jpg as text/html or whatever, and the browser is rendering it as such.

Likewise, when you go on https://raw.githubusercontent.com/resume/resume.github.com/m... you are presented with plaintext because it has returned as Content-type: text/plain, even though the data is actually HTML.

Re: PostgREST – REST API from any PostgreSQL database

#127

Post by jakub_h »

batou wrote: post #51
benkant wrote: post #12

This is good work and if I ever did web development, it would be like this. Why people in the web world don't use stored procedures and constraints is a mystery to me. That this approach is seen as novel is in itself fascinating. It's like all those web framework inventors didn't read past chapter 2 of their database manuals. So they wrote a whole pile of code that forces you to add semantics in another language else…

That is quite frankly horse poop. We use stored procedures. Not by choice; this is a legacy we're stuck with. It is nothing but an unadulterated disaster of a technology regardless of what you use it for. I'm talking 45,000 stored procedures here. 2000 tables. TiB of data. 50,000 requests/second across SOAP/web/desktop etc. It's hell. Problems with stored procedures: 1) Performance. More code running in the hard to s…

It seems to me that least in a part of you argument, you seem to be confusing the deficiencies of particular implementations or server-run code with the idea itself. Stored procedures are bad because your RDBMS has a crappy compiler? Hmm...

(One might argue, of course, that SQL in itself turned out to be a lousy interface protocol for relational data processing, and that it caused a lot of pain to begin with. But that's a different topic.)

Re: PostgREST – REST API from any PostgreSQL database

#128

Post by kelseydh »

glogla wrote: post #69
bni wrote: post #52

What about when changes are made to the schema, wont the API just be changed in that case? Wont this lock you in with very hard coupling between your db schema and public REST API?

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 ORM.

Re: PostgREST – REST API from any PostgreSQL database

#129

Post by snarfy »

3pt14159 wrote: post #65
benkant wrote: post #12

This is good work and if I ever did web development, it would be like this. Why people in the web world don't use stored procedures and constraints is a mystery to me. That this approach is seen as novel is in itself fascinating. It's like all those web framework inventors didn't read past chapter 2 of their database manuals. So they wrote a whole pile of code that forces you to add semantics in another language else…

You are speaking from ignorance with the voice of authority. I worked on a rails app that handled a billion requests per day. The problem isn't performance of the web framework, those are easy to load balance and split into C or cache when you need it. The problem is scaling your database, keeping your data secure, and iterating to meet business goals with a growing codebase and infrastructure. A mess of stored proce…

> 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. It's still a mess.

Re: PostgREST – REST API from any PostgreSQL database

#130

Post by rapind »

3pt14159 wrote: post #65
benkant wrote: post #12

This is good work and if I ever did web development, it would be like this. Why people in the web world don't use stored procedures and constraints is a mystery to me. That this approach is seen as novel is in itself fascinating. It's like all those web framework inventors didn't read past chapter 2 of their database manuals. So they wrote a whole pile of code that forces you to add semantics in another language else…

You are speaking from ignorance with the voice of authority. I worked on a rails app that handled a billion requests per day. The problem isn't performance of the web framework, those are easy to load balance and split into C or cache when you need it. The problem is scaling your database, keeping your data secure, and iterating to meet business goals with a growing codebase and infrastructure. A mess of stored proce…

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 support non-cacheable hits like writes to more than 50 or so concurrents I consider swapping in Node or Go or something reasonably performant to handle just those writes.

Lately I've been looking into Elixir as a Rails alternative for APIs for performance and scalability. I am very intrigued by a PostgreSQL based REST API.

Post reply on HN