Postgres Weekly Issue 303: May 1, 2019

#303 — May 1, 2019

A Deep Dive into GIN Indexes — The latest in an ongoing series digging deep into Postgres’ index types. This time it’s the turn of Generalized Inverted Indexes which are suited for indexing composite values where elements within those composite values need to be looked up frequently (e.g. full text search). This post goes into a lot of depth.

Egor Rogov

Masquerade: A Postgres Proxy to Mask Data in Realtime — This could be handy if you want developers, analysts, or others to use your database but in a privacy-friendly way, as Masquerade will mask/obfuscate data in real time while still using the original underlying database. Clever idea!

Tonic

If Engineers Need Access, You Need strongDM — strongDM works with everything in your stack. Seriously. Any database. Any server. Every environment. Learn more.

strongDM sponsor

Exporting CSV from Postgres using psql — If you want to occasionally bring data into another tool for analysis or for sharing with others, dumping it out as CSV can be helpful. It’s easy enough with psql but Philipe has also created a helpful wrapper tool called psql2csv.

Philipe Fatio

Postgres Features You May Not Have Tried But Should — A golden oldie has been doing the rounds again on social media, but I always like being reminded of things to investigate in Postgres. Pub/sub, table inheritance, triggers, and arrays all make the cut.

RapidLoop

Zedstore: Compressed In-Core Columnar Storage for Postgres — Postgres had pluggable storage committed a few weeks ago. We now already have a new storage engine emerging which had been worked on in parallel, zedstore, which has some promising benefits.

Ashwin Agrawal

Monitoring Your PostgreSQL Database with Telegraf and InfluxDB — This tutorial will specifically cover the process of setting up Telegraf and InfluxDB to monitor PostgreSQL.

InfluxData sponsor

How to Get Started with Mapping GIS Data — This tutorial brings together a lot of neat things, including Leaflet.js, the Chicago Open Rideshare Dataset, Digital Ocean and Postgres.

Sam Cohen

Around the World in Two Billion Transactions — The world of transaction IDs can sometimes be a bit of a minefield, particularly in heavily used databases. Shaun considers the problems and potential future solutions.

Shaun Thomas

supported by

💡 Tip of the Week

How to track long-waiting locks in your database

Reading and understanding your database's logs can give you useful visibility into what’s going on in your database. pganalyze has written a book covering the top 6 Postgres log events you need to be monitoring, and we're looking at one here.

Certain database operations (such as adding columns to a table or some types of update) require that either tables or rows are locked, preventing other queries from being able to perform operations on those tables or rows.

Lock related issues can be difficult to analyze, but if you're trying to dig into a lock related issue, you can add this to your Postgres config file to get more insight:

    log_lock_waits = on
    

Follow this with SELECT pg_reload_conf(); to reload the config, and Postgres will now log any situations where a lock request isn't granted within 1 second, like so:

    LOG: process 2078 still waiting for ShareLock on transaction 1045207414 after 1000.100 ms
    

This will then be followed by further information about the context of the situation, what is currently holding the lock, and the query involved, so that you can begin to investigate further.

Want to dig into 5 more similar situations where log files can help you out? You can download the complete book “The Most Important Events to Monitor in Your Postgres Logs” for free here. You might also enjoy their previous eBook “Best Practices for Optimizing Postgres Query Performance”.

This week's tip is sponsored by pganalyze. DBAs and developers use pganalyze to identify the root cause of performance issues in their Postgres database, to optimize queries and to get alerts about critical issues.

🛠 Tools and Code

A 'Fairly' Full Featured Ansible Role for Postgres — An Ansible 2.0 role which installs and configures Postgres 11 with extensions, databases and users.

ANXS

A Proof-of-Concept for SQL-Driven Logging from Go

Zaydek MG

psql2csv: Run A Query in psql and Output the Result as CSV

Philipe Fatio

🗓 Upcoming Postgres Events

pgconf.de 2019 (May 10 in Leipzig, Germany) — The latest edition of the highly successful German-speaking PostgreSQL conference.
PGDay.IT 2019 (May 16-17 in Bologna, Italy)
PGDay Belgium 2019 (May 17 in Leuven, Belgium) — Registration has just opened.
PGCon 2019 (May 28-31 in Ottawa, Canada) — An annual conference for users and developers to meet and discuss all things Postgres.
Postgres Vision 2019 (June 24-26 in Boston, MA)