If your Postgres database gets queried by multiple app servers, you’re probably going to want a connection pooler. Michael Aboagye explains what that is and why you want one.
When picking a connection pooling tool, this benchmarking from Tembo says servers with <50 clients are best off with PgBouncer, and higher than that should consider PgCat. The post doesn’t have comments, but the author took part in the Reddit discussion.
If you use PgBouncer for connection pooling, you’ll want to check out the improvements in 1.22.0 that add better support for prepared statements. To configure it, check out the documentation on max_prepared_statements.
Technique of the Week:
Last Modified Date Column
If you wanna know when a table was last updated, you can’t, at least not accurately or easily. This is one of the many things that makes it a little challenging to fulfill a common business request: “Give me a feed of the most recent changes in this table.”
To work around that, the next business request is often, “Fine, then tell me which rows have changed since a particular date?” (That’s the same problem, but I digress.) To do that, add a timestamp column to the table – we’ll call it modified. Then, build a function to set that column:
|
1 2 3 4 5 6 7 |
CREATE OR REPLACE FUNCTION update_modified_column() RETURNS TRIGGER AS $$ BEGIN NEW.modified = now(); RETURN NEW; END; $$ language 'plpgsql'; |
Finally, set up a trigger on each table that calls the function. To learn how to do that, check out this post by Frank Wiles.
Then, departments who want to see new/changed rows can say:
|
1 2 3 |
SELECT * FROM mytable WHERE modified > the_last_time_I_checked; |
Each department can maintain their own dates of when they checked for updates – the data warehouse team might have one set of dates, the API team another – and they can all refresh the data whenever they want.
That doesn’t solve the problem of tracking deleted rows. For that, you’ll want to implement soft deletes.