---
title: "Don't let locking block Postgres's autovacuum"
publication: "Postgres Weekly"
issue: 296
published_at: "2019-03-13"
canonical_url: "https://postgresweekly.com/issues/296"
---

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

#296 — March 13, 2019 

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

Postgres Weekly 

[![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60/knlxlvjcpegpbjnkhsi9.jpg)](https://www.cybertec-postgresql.com/en/lock-table-can-harm-your-database/)   

[How `LOCK TABLE` Can Harm Your Database's Health](https://www.cybertec-postgresql.com/en/lock-table-can-harm-your-database/) — Frequent use of `LOCK TABLE` could harm your database when it conflicts with autovacuuming: _“If you use `LOCK` on a table frequently, there is a good chance that autovacuum will never be able to successfully process that table.”_ 

Laurenz Albe   [![\](https://copm.s3.amazonaws.com/27676a9c.png)](https://pgexperts.com) 

[Because Your Data Is Your Business](https://pgexperts.com) — PGX's open source experts provide the database and application skills necessary to solve database problems, accelerate existing applications, refactor infrastructure, and develop new applications. 

PostgreSQL Experts, Inc. sponsor   

[A Few Postgres Best Practices](https://blog.digitalocean.com/some-postgres-best-practices/) — Use `BIGINT` or `UUID` for primary keys, keep your credentials rotated, and use connection pooling. 

Kenneth Reitz (DigitalOcean)   

[Geolocation with Postgres](https://tapoueh.org/blog/2018/08/geolocation-with-postgresql/) — A look at the process of loading geolocation data into a Postgres database and then making queries to geolocate IPs against it. 

Dimitri Fontaine   

[Benchmarking Amazon Aurora PostgreSQL](https://severalnines.com/blog/benchmarking-managed-postgresql-cloud-solutions-part-one-amazon-aurora) — The first in a series of articles benchmarking managed PostgreSQL services. 

Severalnines   

[7 Things To Watch Out For In Your PostgreSQL Deployment](https://pgdash.io/blog/postgres-things-to-monitor.html) — Maximizing performance and establishing confident predictability of your PostgreSQL deployment. 

pgDash sponsor   

[Getting the Difference of Averages of Columns Across 2 Tables](https://stackoverflow.com/q/55111576/3951) — It’s very straightforward. 

Stack Overflow   

▶[Tuning PostgreSQL for High Write Workloads](https://www.youtube.com/watch?v=xrMbzHdPLKM) — A golden oldie from PostgresOpen Silicon Valley. 

Grant McAlister   

[A Quick Take on Hasura: A Daemon for GraphQL on Postgres](https://redmonk.com/jgovernor/2019/03/11/quick-take-on-hasura-a-daemon-for-graphql-on-postgres/) 

James Governor   

[PostGIS 2.5.2, 2.4.7, 2.3.9 Released](http://postgis.net/2019/03/11/postgis-patches/) — Relatively minor bug fix releases _but_ the first to be able to compile against [Proj 6](http://blog.cleverelephant.ca/2019/02/proj4-postgis.html) if you wish to use that. 

PostGIS Developers       supported by [![\](https://res.cloudinary.com/cpress/image/upload/v1545062952/na5isnukkrtglivbcqvs.png)](http://www.strongdm.com/product?utm_source=email&utm_medium=email&utm_campaign=2019-03-08%20-%20%5BNL%5D%20-%20%5BSchD%5D%20-%20%5BSDM%5D%20-%20COMPANY-LIST)  

💡 Tip of the Week 

An SQL tip for rounding numbers by 10s, 100s, etc. 

You might be familiar with SQL's `ROUND` function that lets you round numbers to the nearest integer or a certain number of decimal places:  

SELECT ROUND(123.456);
 # => 123
 
 SELECT ROUND(123.456, 2);
 # => 123.46   

What you can also do, however, is round numbers by tens, hundreds, and other orders of magnitude by using a _negative_ amount of rounding:  

SELECT ROUND(123.456, -2);
 # => 100
 
 SELECT ROUND(123.456, -1);
 # => 120   

This also works in MySQL, Oracle and SQL Server, though _not_ SQLite. 

This week's tip is sponsored by _[strongDM](http://www.strongdm.com/product?utm_source=email&utm_medium=email&utm_campaign=2019-03-08%20-%20%5BNL%5D%20-%20%5BSchD%5D%20-%20%5BSDM%5D%20-%20COMPANY-LIST)_. Teams at Hearst, SoFi, and Peloton trust strongDM to manage engineers’ access to everything.       

🗓**Upcoming Postgres Events** 

- [pgDay Israel 2019 (March 14 in Israel)](https://pgday.org.il/)
- [Nordic PGDay 2019 (March 19 in Copenhagen)](https://2019.nordicpgday.org/)
- [PGConf APAC 2019 (March 19 in Singapore)](http://2019.pgconfapac.org/)
- [pgconf.de 2019 (May 10 in Leipzig)](https://2019.pgconf.de/)          

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