#288 — January 16, 2019

Read on the Web

Postgres Weekly

Looking Back: A History of Postgres — There’s some background to this, but essentially it’s a free chapter from a ACM-commissioned book about Turing Award winners and it digs deep into Postgres’s origins. Direct PDF link.

Joseph M. Hellerstein

How to Contribute to Postgres — PostgreSQL gets a new release each year. Development on the new release happens well ahead of time with improvements being organized into ‘commitfest’ sprints. Here we look at some ways to get started contributing yourself.

Craig Kerstiens

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

Unearthing Some Hidden PostgreSQL 11 Gems — Postgres 11 is barely three months old so it’s still taking people time to dig through all of its new features. Kaarel Moppel has done just that and shares some of the bits he found interesting.

Kaarel Moppel

PostgresConf 2019 CFP Extended Until Friday — PostgresConf US 2019 is taking place in New York City this March and they’ve had over 170 proposals so far. They’re keen for more, though, so you have until Friday (January 18th) if you want to join the party.

Joshua Drake

Is Amazon's DocumentDB Really Postgres? — Amazon just announced DocumentDB, a MongoDB-compatible document database, but under the covers there are a number of hints that point to it actually being powered by Postgres…

Ken Rugg

Performance Troubleshooting using New Azure Database for PostgreSQL Features — If you’re using Postgres on Azure, this could be handy.

Harini Gupta (Microsoft)

One Security System for Application, Connection Pooling and PostgreSQL - Making the Case for LDAP

Achilleas Mantzios

pgDash Diagnostics and Monitoring for PostgreSQL — Maximize performance and gain insights into your PostgreSQL deployment. Review diagnostics, monitor core functions, share with your team.

pgDash sponsor

A Review of 'Mastering PostgreSQL in Application Development' by Dimitri Fontaine

Dmitry Shvetsov

pgqr: A QR Code Generation Extension for Postgres — We’re still trying to work out why this is useful, but if you need it, you need it.. :-)

Abdul Yadi

supported by

💡 Tip of the Week

Finding which tables need extra indexes

pg_stat_user_tables is a fantastic system view that can show us invaluable information about the tables in our databases and can be used in diagnosing performance issues. Vacuum statistics like last_autovacuum and n_dead_tup will show if there are tables that aren’t being vacuumed efficiently and n_tup_hot_upd if you aren't performing HOT updates.

One of the things to look for first, though, is if there are tables that need new indexes as this can have a significant impact on your database's overall performance:

SELECT relname,
    seq_scan,
    seq_tup_read,
    idx_scan
FROM pg_stat_user_tables
WHERE seq_scan > 0
ORDER BY 3 DESC LIMIT 10;

This simple query will help highlight tables that are heavy on sequential scans and potential candidates for adding an index.

By looking at the seq_scan results, you can see precisely how many sequential scans have happened on a table. Then looking at seq_tup_read you can see how many tuples are being read during those scans. If those numbers are significantly higher on certain tables, some further indexes may be badly needed.

This week's tip is provided by Peter Tormey @ SoFi and sponsored by strongDM. SoFi relies on strongDM to manage and secure access to databases and servers. Learn more about strongDM.

If you have any ideas for your own tips, reach out here - we'll pay $50 for your tip if we use it in an issue.