4 ms·

PostgreSQL is awesome (I really love it), now let's do some complaining: * I'm not fan of doing backups with pg_dump. I think it would be a lot more awesome if

PostgreSQL is awesome (I really love it), now let's do some complaining: * I'm not fan of doing backups with pg_dump. I think it would be a lot more awesome if I could tell the database to back itself up in-band. Even better, I would like to be able to ask the database to schedule its own backups, instead of having to set up my own cron jobs (or equivalents). Commercial databases can do that. * The sad state of libpq (the C API). Libpq is where PostgreSQL really shows its age. It's very cumbersome to use and can't do (or can't do well) a bunch of things the raw protocol is capable of. Since almost all PostgreSQL bindings are built on libpq, none of the bindings support these features either. * It would be cool if it was possible to kill sessions in-band (other database servers can do that). With PostgreSQL, the only way to shut down misbehaving sessions is sshing in to the database server and doing a `kill` on the process of the session. * The infamous COUNT(*) fiasco.

dkadams 15y ago
Depending on how badly the session is misbehaving: http://www.postgresql.org/docs/9.1/static/libpq-cancel.html http://www.postgresql.org/docs/9.1/static/libpq-cancel.html or pg_cancel_backend(pid int) or pg_terminate_backend(pid int)
- deleted 15y ago
  [deleted]
- DrJokepu 15y ago
  Oh that's very cool, I din't know about that one. I guess this item can be removed from my list.
  - fdr 15y ago
    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.html#AEN91658 http://www.postgresql.org/docs/9.1/interactive/protocol-flow...
reactor 15y ago
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>';
- jeltz 15y ago
  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.
pvh 15y ago
* Check out wal-e (https://github.com/heroku/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
- fdr 15y ago
  > 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.
lobster_johnson 15y ago
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.

dkadams 15y ago

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

deleted 15y ago
[deleted]
DrJokepu 15y ago
Oh that's very cool, I din't know about that one. I guess this item can be removed from my list.
- fdr 15y ago
  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.html#AEN91658 http://www.postgresql.org/docs/9.1/interactive/protocol-flow...

reactor 15y ago

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>';

jeltz 15y ago
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.

pvh 15y ago

* Check out wal-e (https://github.com/heroku/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

fdr 15y ago
> 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.

lobster_johnson 15y ago

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.