--- title: "How one of the world's largest classifieds sites scales Postgres" publication: "Postgres Weekly" issue: 315 published_at: "2019-07-24" canonical_url: "https://postgresweekly.com/issues/315" --- The latest Postgres news, articles, tools, and projects. #315 — July 24, 2019 [Read on the Web](https://postgresweekly.com/issues/315) Postgres Weekly [![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60/v1563921627/twah9uwlfbt4ve2bn8s1.png)](https://medium.com/avitotech/standby-in-production-scaling-application-in-second-largest-classified-site-in-the-world-97a79a1929de) [Standby in Production: Scaling at the Second Largest Classified Site in the World](https://medium.com/avitotech/standby-in-production-scaling-application-in-second-largest-classified-site-in-the-world-97a79a1929de) — _Avito_ is Russia’s largest classified ads site and stores 20TB of data across a hundred Postgres nodes. Here’s a very detailed, behind the scenes look at their Postgres high availability setup. Konstantin Evteev [Automatic Index Recommendations using `pg_qualstats` and HypoPG](https://www.percona.com/blog/2019/07/22/automatic-index-recommendations-in-postgresql-using-pg_qualstats-and-hypopg/) — [HypoPG](https://github.com/HypoPG/hypopg) is a Postgres extension for creating ‘hypothetical indexes’, essentially virtual indexes you can use to test if a certain index _would_ improve your queries ahead of time. This article takes things to the next step by _automatically_ trying to find indexes worth creating. Avinash Vallarapu [![\](https://copm.s3.amazonaws.com/975a5f60.png)](https://www.strongdm.com/postgres-weekly/?utm_source=PGWeekly&utm_medium=email&utm_campaign=2019-07-22%20-%20%5BSELF%5D%20-%20%5BSDM%5D%20-%20%5BMID%5D) [Chef’s CTO Chooses StrongDM](https://www.strongdm.com/postgres-weekly/?utm_source=PGWeekly&utm_medium=email&utm_campaign=2019-07-22%20-%20%5BSELF%5D%20-%20%5BSDM%5D%20-%20%5BMID%5D) — Adam Jacob says _“strongDM takes the friction out of getting end users access to the systems they need.”_ Zero trust access to any database, server or k8 cluster. [Click here](https://www.strongdm.com/postgres-weekly/?utm_source=PGWeekly&utm_medium=email&utm_campaign=2019-07-22%20-%20%5BSELF%5D%20-%20%5BSDM%5D%20-%20%5BMID%5D%20) to try for free. strongDM sponsor [Don’t Forget the Benefits of BRIN Indexes](https://www.percona.com/blog/2019/07/16/brin-index-for-postgresql-dont-forget-the-benefits/) — BRIN indexes (which provide an efficient way to index values that can be grouped into ‘ranges’) were introduced in Postgres 9.5 and have some specific use cases. This post benchmarks a situation where they can really help. Jobin Augustine [The Random Ordering of Results with SQL](https://vladmihalcea.com/sql-order-by-random/) — An article that covers the use of `ORDER BY` to randomly sort rows in several database-specific ways, including Postgres. Vlad Mihalcea [Checking the Version of Postgres in `psql` Scripts](https://fluca1978.github.io/2019/07/18/CheckPostgreSQLVersionInScripts.html) — Let’s say you want to run different code depending on the version of Postgres that’s running.. you can! Luca Ferrari [(Even More) Postgres Tips for the Average and Power User](https://www.citusdata.com/blog/2019/07/17/postgres-tips-for-average-and-power-user/) — Hopefully you’ve been enjoying our various ‘tips of the week’ we’ve been including in Postgres Weekly this year, but here, Craig Kerstiens unearths a whole lot more :-) Craig Kerstiens [Proactively Monitor PostgreSQL Performance with Datadog](https://www.datadoghq.com/postgres-monitoring/?utm_source=Advertisement&utm_medium=CooperPress&utm_campaign=CooperPress-PostgresWeeklyS) — Improve PostgreSQL performance with actionable alerts on latency spikes, errors, anomalies, and more. Smart monitoring with Datadog - try it free. Datadog sponsor [How Are Functions and Stored Procedures Replicated?](https://www.cybertec-postgresql.com/en/postgresql-how-are-functions-and-stored-procedures-replicated/) — A brief look behind the scenes at how Postgres actually stores functions and procedures and how that feeds into their replication. Hans-Jürgen Schönig [A Certificate Authentication Recipe for Postgres Docker Containers](https://info.crunchydata.com/blog/ssl-certificate-authentication-postgresql-docker-containers) — Learn how to set up PostgreSQL certificate-based authentication with a simple Docker container recipe. Jonathan S. Katz [The 'Mysterious' `backend_flush_after` Configuration Setting](https://www.cybertec-postgresql.com/en/the-mysterious-backend_flush_after-configuration-setting/) — This is a setting very specifically for server operators (and only those using Linux too) looking to squeeze out some extra performance. Kaarel Moppel [Configuring `pgBackRest` to use Amazon S3](https://pgstef.github.io/2019/07/19/pgbackrest_s3_configuration.html) — [pgBackRest](https://pgbackrest.org/) is a powerful backup and restore tool and can be configured to use AWS S3. Stefan Fercot supported by [![Gremlin\](https://res.cloudinary.com/cpress/image/upload/v1563792558/nflelycsvpf6vndmiynl.png)](https://www.gremlin.com/?utm_medium=cpm&utm_source=PostgresWeekly&utm_content=20190724&utm_campaign=2019_Cooperpress) 💡 Tip of the Week Using `ROLLUP` to get an overall results row for aggregations If you want to group and aggregate results together, using `GROUP BY` with a single column is a common approach, but `ROLLUP` is one of a handful of clauses that can help you take things to another dimension by letting you group by multiple sets in a single query. This is a feature best demonstrated live, so let's create a table and load in some sample data.. the number of goals scored by different soccer teams in different years: CREATE TABLE goals ( team TEXT, year INT, goals INT); INSERT INTO goals VALUES ('USA', 2010, 5), ('USA', 2011, 3), ('USA', 2012, 12), ('ENG', 2010, 17), ('ENG', 2011, 9), ('ENG', 2012, 11); If we wanted to get the average number of goals scored per year, per team, we could do this: SELECT team, ROUND(AVG(goals)) FROM goals GROUP BY team; But let's say we wanted to not only group by teams but _also_ by the overall table (i.e. no column) to get the number of average goals _overall_. We could do this: SELECT team, ROUND(AVG(goals)) FROM goals GROUP BY ROLLUP(team); This gets us a result like so: team | round --------------- ENG | 12 USA | 7 (null)| 10 There's a _lot_ more to this than we can cover here, so enjoy [this tutorial](http://www.postgresqltutorial.com/postgresql-rollup/) to learn more. This week's tip is brought to you by [Gremlin,](https://www.gremlin.com/?utm_medium=cpm&utm_source=PostgresWeekly&utm_content=20190724&utm_campaign=2019_Cooperpress) a platform that helps you build more resilient software through running thoughtful Chaos Engineering experiments. _Learn more at [gremlin.com](https://www.gremlin.com/?utm_medium=cpm&utm_source=PostgresWeekly&utm_content=20190724&utm_campaign=2019_Cooperpress)_. 🗓 Upcoming Postgres Events - [PGConf.Brasil 2019](https://www.pgconf.com.br/2019/en/) (August 1-3 in São Paulo) — A three day conference with talks, tutorials, courses, and lightning talks. - [PGDay Austria 2019](https://pgday.at/en/) (September 6 in Wiener Neustadt, Austria) - [PostgreSQL Conference Asia 2019](http://2019.pgconf.asia/) (September 8-11 in Bali, Indonesia) - [PostgresConf South Africa 2019](https://postgresconf.org/conferences/SouthAfrica2019) (October 8-9 in Johannesburg) — An opportunity for the database administration and developer community who use Postgres to get to know each other. Curated by [Cooperpress](https://cooperpress.com/), [Peter Cooper](https://twitter.com/peterc), and [Craig Kerstiens](https://twitter.com/craigkerstiens). [Change your email address](https://postgresweekly.com/edit_subscription/*|UID|*) or[Stop getting this newsletter](https://postgresweekly.com/leave/*|UID|*) © Cooper Press Ltd · Fairfield Enterprise Centre, Louth, LN11 0LS, United Kingdom