Postgresql – do with the new json field

I think you see a false dichotomy that does not exist.

It can be useful to have connection pooling in place even if you expect a 1:1 mapping of clients to back-ends. If your connections are long-lived, you won't benefit from reducing backend setup/teardown overhead, as it's small and amortized across a long period. A pool like PgBouncer may remain useful for other reasons:

• Block until a connection is available rather than return an immediate max_connections exceeded error;
• You can switch the pool target server if you fail-over to a standby without having to reconfigure applications;
• You can limit application database workers to lower than max_connections, so you can still make reporting and maintenance connections as a non-superuser.

Additionally, if suitable for your application you can use transaction-level pooling to greatly increase the number of clients that can be served by your server.

I would not try to keep a strict 1:1 mapping from Apache workers to PostgreSQL workers, personally. If you've got (say) 16 cores and good I/O on your PostgreSQL box you might want something like 16-20 active PostgreSQL workers for optimal performance. You're almost certain to want more Apache workers than that, since they'll be kept busy by things like:

• persistent HTTP connections from idle clients;
• Unresponsive or very slow clients;
• Intentional DoS connections;
• Network interruptions between client and server; etc

If possible, consider a transaction-pooling design with short-lived transactions instead.

What about:

with tweets(type, id) as ( 
    select 'add' as type, tweets.id from tweets 
) select row_to_json(row(array_agg(tweets))) from tweets;

This is actually one of the really cool things about PostgreSQL, arrays, and complex types. One can aggregate them and then convert. You should get a structure which is effectively:

tweets[] (i.e. an array of tweet tuples) which then get should converted to JSON properly).

Now, if you need a more complex structure than this, you probably want to define types separately. Something like:

CREATE TYPE tweet_set AS (
    set_id int,
    tweets tweet[],
    generated_at timestamp, 
    ....
);

And then come up with a query to generate that, and then convert it to JSON.

One of the basic issues is that since row_to_json expects a record type, you have to pass in a record, not an anyarray. You could also do:

with tweets(type, id) as ( 
    select 'add' as type, tweets.id from tweets 
) select array_to_json(array_agg(tweets)) from tweets;