7 ms·
Postgres 9.2 – The Database You Helped Build
- zrail 14y agoI hadn't seen anything about pg_stat_statements before. That will be extremely helpful to have around.
- deleted 14y ago[deleted]
- riffraff 14y agoI just started a pet project some days ago where I had to store some JSON stuff and was sad to find heroku postgres did not support it yet. And now it does :)
- davidw 14y agoI wonder how often it'd actually come up that you could get stuff through your application layer into the database and/or back out without wanting to tweak it first. Where i have Rails JSON api's, I usually do some tweaking/modification of the JSON that comes out, for instance.
- joevandyk 14y agoYou may want to look into using postgresql functions to modify the json that comes out. Probably much faster, especially if you have lots of nested stuff coming from different tables.
- riffraff 14y agoI don't think I'd ever want to _serve_ json from the db, and as you say, I'd tweak the data for common usage anyway. But my use case is the opposite: I am storing exactly the data coming from external APIs. My table has some fields I already extracted from the JSON data, cause I need them now, yet I don't want to throw away the full data, as I may need it in the future. So, I was storing the original JSON as text. But, having support for the API format in the db itself is much better, as I can also actually query and manipulate this data without pulling all the text fields in my client code.
- joevandyk 14y agoheroku has unofficially supported 9.2/json for a couple months.
- cmaggard 14y agoWe just upgraded our database plan on Monday. Guess we're doing it again soon to get on the new hotness!
- jedberg 14y agoWhen I was running the Postgres databases for reddit, I was constantly seeking people who could teach me better ways to tune Postgres on AWS. Most people I would meet would just regurgitate what anyone could learn with a day or two of googling and reading. At one point I got scared by the prospect that I might be an expert on Postgres on AWS, because frankly I didn't know all that much about it and thought we were doomed if that was really the case. Then I went over had lunch with the Heroku team, and it was eye opening. These guys truly knew how to run Postgres on AWS (and presumably still do). I can't think of another org that is moving Postgres on AWS forward better than Heroku.
- pvh 14y agoThanks a lot! We've come a long way since then - forks, followers, dataclips, extensions, new plans, new versions... Man, actually it kind of terrifies me to think of it all.
- ayanb 14y agoHey, do you have customer instances where the app is on non-aws bare metal ( like Softlayer) and the db is on heroku postgres? What are the pros and cons of this scenario?
- pvh 14y agoAbsolutely. Basically, as long as your app is running somewhere with Very Good Latency to your database, this can be a fine pattern. In practice, this means if the data center is also in Ashburn, Virginia, and better yet, has a DirectConnect deal set up with AWS, you should be in business. The best way to test this is to provision a database and see how latency from your app is. My opinion is that 5ms latency would be a ceiling on what I'd tolerate for my own apps, and I'd probably look for more like 1-2ms or better.
- ayanb 14y agoThanks pvh. Exactly what i was looking for. Would you mind if I send you an email with couple of more questions?
- cmalpeli 14y agocan't tell from this, but will existing Postgres databases at Heroku be automatically upgraded, or do you need to provision a new instance and migrate the data?
- joevandyk 14y agoExisting databases won't be automatically upgraded. https://devcenter.heroku.com/articles/upgrade-heroku-postgres-with-pgbackups https://devcenter.heroku.com/articles/upgrade-heroku-postgre... has information about the upgrade process.
- pvh 14y agoWe figure reliability of your database as one of our chief missions. To that end, we prefer not to do anything which could even remotely be a possible cause of operational issues. Since changing over to a new version is easy, we tend to let users do so at their leisure. That said, in the event of a major 0-day we could be forced to migrate people, but it hasn't happened yet.
- mfenniak 14y agoDoes Heroku's Postgres implementation run with a server-side connection pooling tool like pgpool or pgbouncer?
- pvh 14y agoWe don't provide server-side connection pooling today, no. It adds too many gotchas to be universally applicable and has had too few use cases to reach the top of our TODO list. We do hear from time to time from people who want it though, so it's certainly not out of the question.