During the afformentioned "Sproc Hell", we were putting application logic in the database. Of course, this made perfect sense: it was secure because of bound params and strict typing, it was fast because it avoided several trips to the database for multi query operations and even for single query statements, query plans were precomputed and cached by the DB. You were also able to tweak application logic without deploying code, which was likely a clumsy process involving more than one team and various manual steps. This is all bollocks, as we've learned many scars and gray hairs later.
Now, the proposal here is entirely different. While yes, you are creating a function in your database, you are doing it to access data in a JSON structure, per the OP. Because in Postgres you can create an index on the result of any expression, including a function, you can now create indexes on functions that parse and access data your JSON docs. And it's fast.
I don't think it was all bollocks. It was just due to the fact that sproc interfaces sucked. Also development of quality sprocs is qualitatively different than upper level app code (among other things, you want a single large query front and center to the extent possible), and so if you write stored procedures the way you write application code they will suck.
Now, what we do with LedgerSMB is build our stored procedures as basically named queries, inspired by web services (both SOAP and REST have been inspirations there). The procedures are intended to be relatively discoverable at runtime, with the aggressive attempts to use what infrastructure exists for this purpose that REST gives for HTTP.
Stored procedures are not a problem. They allow you to encapsulate a database behind an API, and the desire to do that is a major point of Martin Fowler's NoSQL advocacy (arguing for doing this for NoSQL dbs).
I actually played with mongo_fdw. At this point, it's a really cute hack, and useful for some things, but it doesn't give Postgres enough information and knobs to really let the query planner work effectively, so it ends up being really slow for complex things. I do love the concept, though.
A little-known feature of Postgres' streaming replication is Synchronous Replication or 2-safe replication, available from version 9.1 onward. It allows you to specify that a given commit must be durable on a standby server prior to the server returning to the client.
You can turn this on cluster wide, or just for single transactions using session variables. In your example, you can specify that this update must be synchronously replicated, and as such the consequent request will show the user the updated data.
Wait, so you want to block the response until a cross database RPC completes? That sounds like it would not be very performant and possibly introduce some other problems. I'm guessing this is best used sparingly with specific transactional queries.
Also, I think that mode does not yet support waiting for application of the deltas, only their flushing to disk. That will probably be added some-day, so this is close but no cigar.
In reality, what one could do now is check the 'snapshot' on the follower and the leader and figure out if a change has been propagated or not. This is exposed by the function txid_current_snapshot().
Latency of syncrep is poor, as you suggest, but throughput is about the same -- not everything blocks on acknowledgements from a standby individually, but rather standbys report their progress through a totally ordered stream of changes, and the leader will un-block a "COMMIT" (explicit, or implicit when not using BEGIN) when it sees that a standby has passed the appropriate transaction number (this is a small fib, because I think it think it thinks in terms of a different unit known as WAL Records/XLogPosition, but this is quite close to the truth).
So, for example, one session may only be able to commit a few times a second on a high-latency link, but thirty parallel sessions may not commit at 1/30th the throughput each, but given a case of very small commits closer to 30-times the throughput in aggregate of one client.
Also, 9.2+ has a notion of 'group commit' where this same dynamic (submit deltas, wait on a number to pass by) is applied for local writes relative to the on-file-system crash recovery log, the WAL, even though syncrep to another machine via network was added in 9.1 (i.e. earlier). This is much better at numerous small writes than older versions of Postgres, where it is likely that many backends would issue their own sync requests to disk rather than sharing one.
Postgres, as of 9.0, has binary log streaming replication built in, truth. Slony ad other logical replication solutions out there are quite hard to set up and come with their gotchas.
A rock solid one command (cli)/click (web) is a whole different story though, making it super easy to form more complex DB topologies and adapting them as your needs change.
JSON is much more powerful than hstore. Not only can it store different JSON data types (boolean, numbers) but also you can store documents with multiple levels deep, which is not possible on the hstore type.
Having said that, hstore is perfect when storing simple key/values. It's been battle tested and used for years and has some powerful and native indexing possibilities.
recent awesome discovery: you can use Inconsolata in your google docs to paste in code samples and whatnot, available from the Add Fonts item in the font picker.
> Also, I'd hope they will give a few months warning before flipping the switch completely, similar to how the hoptoad/airbrake switch went.
Funny you should say that, as I was involved in the hotpoad/airbrake transition and am now involved in the Heroku postgres one. The transition will be as smooth as we possibly can, no data will be lost, there will be plenty of time to move your data, and easy ways of doing so.
We are really excited about offering a development and free 9.1 postgres service, a huge step forward from the current 8.3 shared database service, and are looking forward to moving people over to the new instances.
