201 reputation
14
bio website linkedin.com/in/mattcwood
location San Mateo, CA
age 42
visits member for 4 years, 2 months
seen May 27 at 22:20

PostgreSQL DBA


Sep
4
awarded  Yearling
Jan
9
comment Should a connection pooler be run on my database server or my app server?
Massive. The cost of building up a new PostgreSQL connection is at least an order of magnitude higher than the designed-to-be-lightweight PgBouncer. For example: PostgreSQL validates and limits connections by IP/database/role using pg_hba.conf, whereas PgBouncer does not--it only validates via a local user/pass file (see system table pg_shadow for what is stored). It starts to add up. I've been doing PG for about 8 1/2 years now and when there's a performance issue, the first place I look is new connection churn. It's usually the culprit.
Jan
8
comment Should a connection pooler be run on my database server or my app server?
...I'm implementing this shift right now in a very large project and expect to see impressive performance improvements over the client-side pools, even with the overhead for each new connection to PgBouncer.
Jan
8
comment Should a connection pooler be run on my database server or my app server?
There's a caveat to this: at the number of client pools increases, the benefits of having the server-side pool increases since each pool must keep some minimum number of persistent connections open or risk "thrashing" new connections on the server when exceeding average normal client pool size. This, in turn, results in a very large number of open connections at all times to the database server which are not free to maintain (even if idle). A server-side pool doesn't care home many clients there are an shares a smaller pool of connections across all of them...
Jan
8
answered PostgreSQL 9.0 implicit transactions not rolling back
Dec
10
awarded  Critic
Jun
5
answered How can I check if my DB needs more RAM?
Nov
14
comment PostgreSQL update tool?
"Magically determining the path from Schema A to Schema B is beyond the capabilities of a computer program, even one as complex as a database system. A human needs to get involved." Truer words were never spoken!
Jul
7
awarded  Supporter
Dec
25
awarded  Teacher
Oct
1
answered How to create a PostgreSQL web application user with limited privileges as easy as possible?
Oct
1
awarded  Autobiographer