There is no fiasco in count(*), it is THE general behavior of any MVCC database. Try the same with MySQL InnoDB, you get the same result (or worse). MySQL MyISAM DB gets the result fast because it is non-trasactional. To get approximate count very quickly on Pg, you can use -- select reltuples from pg_class where relname='<table_name>';

Indeed, in the benchmarks I have seen PostgreSQL is faster then InnoDB at running count(*) over entire tables but slower when a indexable WHERE clause was added due to the lack of index only scans in PostgreSQL. This may change in 9.2.

* Check out wal-e (https://github.com/heroku/wal-e), we wrote it and we manage 250k postgres databases with it. :)

* libpq does basically suck, but go has a pretty great native implementation. suggestions are welcome for improvement, patches are even better.

* pg_cancel_backend() plus select-fu on the `pg_locks` table joined with `pg_stat_activity` = joy

* fast count(*) is possible via index-only scans in 9.2

> fast count() is possible via index-only scans in 9.2

I'd like to expand on this. It will be faster, but still involves an O(n) over the qualifying data, but it'll be a lot less data, particularly for tables with wider rows. To some extent this problem is fundamentally unable to reach a small O(1) without introducing more lock contention and expense that many people don't want to pay continuously and across every possible query or dimension that can exist (in the latter case, it is clearly impossible given the existence of arbitrary WHERE clauses), but I think it'd be nice if people could add a new physical construct whereby count(*) queries on certain dimensions can be made faster at the cost of lock contention or resource consumption, not entirely unlike indexes.

Depending on how badly the session is misbehaving: http://www.postgresql.org/docs/9.1/static/libpq-cancel.html or pg_cancel_backend(pid int) or pg_terminate_backend(pid int)

Oh that's very cool, I din't know about that one. I guess this item can be removed from my list.

Internally there is also an interesting mechanism: when one connects, the backend hands you a "backend key", which is a 64 bit number. The client is intended to remember that number. Cancellations are processed by sending a cancel request to the server -- which involves a special new connection and a different first packet -- including that secret number. As an interesting security precaution, absolutely no acknowledgement or response is made to the cancellation request.

libpq knows how to do all this.

The reason for this is that to avoid inspecting the connection for new instructions during query processing, acting under the assumption that cancellations will be rare.

http://www.postgresql.org/docs/9.1/interactive/protocol-flow...

As others have pointed out, there is no such "fiasco". However, if you want to simply count the entire table and don't care if the number is exact within the current transaction, you can cheat and do:

    select n_live_tup from pg_stat_user_tables
    where relname = 'your_table_name'

This will access the internal statistics about how many tuples are in the table. It ignores the transaction's isolation level since it's a global value, and it may be a few milliseconds out of date, but it's otherwise correct.