---
title: "Faster counting in Postgres, PGCon 2019, and some handy tools"
publication: "Postgres Weekly"
issue: 300
published_at: "2019-04-10"
canonical_url: "https://postgresweekly.com/issues/300"
---

The latest Postgres news, articles, tools, and projects.       

#300 — April 10, 2019 

[Read on the Web](https://postgresweekly.com/issues/300)   

Postgres Weekly 

[![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60/x9ttl8oxzxvbn0mhiw6v.jpg)](https://www.cybertec-postgresql.com/en/count-made-fast/)   

[How To Improve The Performance of `COUNT(*)`](https://www.cybertec-postgresql.com/en/count-made-fast/) — Using `count(*)` is often quite slow in Postgres. This article explores a variety of options to make counting rows faster using approximations and other tricks. 

Laurenz Albe   

[When A Vulnerability is Not a Vulnerability](https://blog.hagander.net/when-a-vulnerability-is-not-a-vulnerability-244/) — Sometimes _intentional_ features can get recorded as ‘vulnerabilities’ for the record, such as with [CVE-2019-9193](https://nvd.nist.gov/vuln/detail/CVE-2019-9193) and Postgres’s `COPY TO/FROM PROGRAM` feature. If you administer a Postgres system at all, it’s worth knowing about in case it has abuse potential on your deployment. 

Magnus Hagander   [![\](https://copm.s3.amazonaws.com/d400ddf8.png)](https://www.datadoghq.com/postgres-monitoring/?utm_source=Advertisement&utm_medium=CooperPress&utm_campaign=CooperPress-PostgresWeeklyP) 

[Real-Time Postgres Performance Monitoring](https://www.datadoghq.com/postgres-monitoring/?utm_source=Advertisement&utm_medium=CooperPress&utm_campaign=CooperPress-PostgresWeeklyP) — Collect out-of-the-box and custom PostgreSQL metrics and correlate them with data from your distributed infrastructure, applications, and logs. Gain real-time performance insights and set intelligent alerts with Datadog. Start a free trial. 

Datadog sponsor   

[PGCon 2019 Registration Now Open](https://www.pgcon.org/2019/registration.php) — This year the popular Postgres conference is taking place in Ottawa, Canada across May 28-31 including two days of tutorials, two days of talks, and an unconference. 

PGCon   

[pgmetrics 1.6.2: Collect and Display Stats from a Running Postgres Server](https://github.com/rapidloop/pgmetrics) — The [homepage](https://pgmetrics.io/) has a lot more info including [example output.](https://pgmetrics.io/docs/index.html#example) 

RapidLoop   

[Controlling Postgres' Planner with Explicit `JOIN` Clauses](https://www.postgresql.org/docs/11/explicit-joins.html) — It’s straight from the docs, but this is a handy set of advice, particularly if, like me, you usually prefer the ANSI SQL 89 join style. 

Postgres Documentation   

[Why `hot_standby_feedback` can be misleading](https://postgresrocks.enterprisedb.com/t5/Postgres-Gems/Why-hot-standby-feedback-can-be-Misleading/ba-p/3172) — When setting up replication in Postgres, `hot_standby_feedback` is a feature that can present a bit more complication than you might expect. 

Richard Yen   

[Continuous Replication From a Legacy Postgres Version to a Newer Version Using Slony](https://www.percona.com/blog/2019/04/09/continuous-replication-from-legacy-postgresql-version-using-slony/) — Native streaming replication in PostgreSQL works only between servers running the same major version, but [Slony](http://www.slony.info/) provides an application-level alternative. 

Nickolay Ihalainen   

[![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60/fi5nogk38qlbv1yo9sky.jpg)](https://github.com/akarki15/dbdot)  

[dbdot: Generate DOT Descriptions for Postgres Schemas](https://github.com/akarki15/dbdot) — Essentially another way to create diagrams of your database schema. 

Aashish Karki   

[How CockroachDB Brought JSONB to Their Distributed Database](https://www.cockroachlabs.com/blog/json-coming-to-cockroach/) — ...plus why they went with Postgres syntax. 

Cockroach Labs sponsor   

[vipsql: A Vim Plugin for Interacting with 'psql'](https://github.com/martingms/vipsql) — Version 2.0 came out just a couple of weeks ago. 

Martin Gammelsæter       supported by [![GitPrime\](https://res.cloudinary.com/cpress/image/upload/v1554813939/kwagarrsergds4sxgjr9.png)](https://resources.gitprime.com/books/20-patterns/?utm_source=nl%28pgw%29&utm_medium=email-nl&utm_campaign=nl%28pgw%29)  

💡 Tip of the Week 

Selecting a 'sample' of a table's rows 

You have a table full of data and you want to select a random sample of that data. Using `SELECT` with an ordering of `RANDOM()` is one way to do it, but can have performance issues over large datasets. Thankfully, there's a better way. 

First, let's create a table that contains 1000 rows — the numbers 1 to 1000:  

CREATE TABLE numbers (number int);
 INSERT INTO numbers (number)
 SELECT id FROM generate_series(1,1000) AS id;   

Postgres 9.5 introduced a new `TABLESAMPLE` clause that lets you sample tables in different ways (2 ways by default, but more can be added via extensions). Let's give it a go at retrieving a random 0.5% of the rows from our table:  

SELECT * FROM numbers TABLESAMPLE BERNOULLI (0.5);
 number 
 --------
 101
 213
 278
 433   

The `BERNOULLI` here selects rows from the table with a 0.5% chance (so statistically we'd get 5 rows on average), but there's also a more efficient `SYSTEM` method that uses a cruder block-based technique (with the possible downside that it tends to return ranges/groups of rows within the table). 

You can also use `TABLESAMPLE` with other SQL statements like `UPDATE` or `DELETE` if, say, you wanted to delete 30% of a table's rows for whatever reason. 

And if you didn't know about `[generate_series()](https://www.citusdata.com/blog/2018/03/14/fun-with-sql-generate-sql/)`, consider that an extra tip for free :-) 

This week's tip is sponsored by _[GitPrime](https://resources.gitprime.com/books/20-patterns/?utm_source=nl%28pgw%29&utm_medium=email-nl&utm_campaign=nl%28pgw%29)_. Get your copy of their new field guide **['20 Patterns to Watch for in Engineering Teams'](https://resources.gitprime.com/books/20-patterns/?utm_source=nl%28pgw%29&utm_medium=email-nl&utm_campaign=nl%28pgw%29)**, filled with actionable insights to help debug your development process with data.      

🗓 **Upcoming Postgres Events** 

- [pgconf.de 2019 (May 10 in Leipzig, Germany)](https://2019.pgconf.de/) — The latest edition of the highly successful German-speaking PostgreSQL conference.
- [PGDay.IT 2019 (May 16-17 in Bologna, Italy)](https://2019.pgday.it/en/)
- [PGCon 2019 (May 28-31 in Ottawa, Canada)](https://www.pgcon.org/2019/) — An annual conference for users and developers to meet and discuss all things Postgres.
- [Postgres Vision 2019 (June 24-26 in Boston, MA)](https://postgresvision.com/)          

Curated by [Cooperpress](https://cooperpress.com/) 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