10 Common Postgres Errors — Some quick fire common errors and warnings to watch out for, with symptoms and solutions, around things like memory, disk space, and permissions.
Ibrar Ahmed (Percona)
|
Whitepaper: Business Case for Professional Support — Learn the importance of Professional Support for your mission-critical PostgreSQL systems & how it can benefit your company. It increases database performance, helps scale, distributes data, reduces costs, saves you from known pitfalls, and more.
2ndQuadrant Services
|
PostgreSQL Load Balancing with HAProxy — I’m a huge fan of the haproxy TCP and HTTP proxy/load balancer, but admittedly haven’t used it for Postgres before.. This post demonstrates using HAProxy with Severalnines’ ClusterControl for load balancing Postgres much in a similar way as you might an HTTP server.
Severalnines
|
Multi-Master Replication Solutions for Postgres — Horizontally scaling Postgres has been enough of a challenge over the years that entire companies (e.g. Citus Data) have been founded to make it easier. But there are various ways to do multi-master replication nonetheless, and this post links to several approaches, whether open or closed source, free or paid.
Ibrar Ahmed (Percona)
|
supported by
💡 Tip of the Week
GROUPING SETS
Grouping sets let you perform grouping within a query that's more complex than simple GROUP BY column can do.
Given a table like this:
|
name | dept | location | salary
--------+------+----------+--------
Abbey | IT | London | 85000
Paul | IT | Madrid | 74000
Clancy | HR | London | 71000
Imani | HR | Madrid | 74000
|
We could easily get the total salaries for each 'department' with a query like:
|
SELECT dept, SUM(salary) FROM staff GROUP BY dept;
|
But what about if we want the total salaries in various ways within the same query? For example, let's say we want the total salaries for everyone, the total salaries for each department, and the total salaries for each location, all within the same query. Can it be done?
Enter 'grouping sets' which lets you define multiple sets of grouping criteria each of which will be performed separately and appended to the result set.
For example, let's group the results by department, then by location, and finally by the empty set (i.e. the total):
|
SELECT dept, location, SUM(salary) FROM staff
GROUP BY GROUPING SETS ((dept), (location), ());
|
This gives us a set of results like so:
|
dept | location | sum
------+----------+--------
| | 304000
IT | | 159000
HR | | 145000
| London | 156000
| Madrid | 148000
|
Now we can easily see from a single set of results that the staff in our example are paid more in London, paid more in the IT department, and the overall wage bill is 304000.
This week’s tip is sponsored by Retool. Build internal tools in minutes, not days. Retool provides powerful building blocks that connect to any DB so you can quickly build the tools your company needs.
|
|
🗓 Upcoming Online Events
-
Postgres Pulse - weekly at 11am ET each Monday. Weekly Zoom-based sessions with folks like Bruce Momjian, Vibhor Kumar, and other people at EnterpriseDB.
-
Postgres Vision 2020 on June 23-24. A full attempt at an online Postgres conference across multiple days with multiple tracks.
|
|
|