Postgres Weekly Issue 296: March 13, 2019

#296 — March 13, 2019

How LOCK TABLE Can Harm Your Database's Health — 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

Because Your Data Is Your Business — 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 — Use BIGINT or UUID for primary keys, keep your credentials rotated, and use connection pooling.

Kenneth Reitz (DigitalOcean)

Geolocation with Postgres — 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 — The first in a series of articles benchmarking managed PostgreSQL services.

Severalnines

7 Things To Watch Out For In Your PostgreSQL Deployment — Maximizing performance and establishing confident predictability of your PostgreSQL deployment.

pgDash sponsor

Getting the Difference of Averages of Columns Across 2 Tables — It’s very straightforward.

Stack Overflow

▶ Tuning PostgreSQL for High Write Workloads — A golden oldie from PostgresOpen Silicon Valley.

Grant McAlister

A Quick Take on Hasura: A Daemon for GraphQL on Postgres

James Governor

PostGIS 2.5.2, 2.4.7, 2.3.9 Released — Relatively minor bug fix releases but the first to be able to compile against Proj 6 if you wish to use that.

PostGIS Developers

supported by

💡 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. Teams at Hearst, SoFi, and Peloton trust strongDM to manage engineers’ access to everything.

🗓 Upcoming Postgres Events