#317 — August 7, 2019

Read on the Web

Postgres Weekly

The Absolute Beginner's Guide to Creating a Postgres Extension with C — Postgres makes itself relatively easy to extend by way of extensions, most commonly built in C. This quick walkthrough shows how to set up a really basic extension development project and get it working.

Jobin Augustine

Comparing Postgres's JSONB with Couchbase — Can Postgres really take the place of a ‘NoSQL’ document-oriented database? It’s interesting to see something like this from a document-oriented database vendor, especially as it’s reasonably fair to Postgres (while still focusing on Couchbase’s strengths, naturally).

Denis Rosa (Couchbase)

Free eBook: How to Get a 3x Performance Improvement on Your Postgres Database — Learn our best practices for optimizing Postgres query performance for customers like Atlassian and how to reduce data loaded from disk by 500x.

pganalyze sponsor

Postgres vs MongoDB Benchmarking: 'Do It with Transparency or Don't Do It At All' — There’s been a bit of a dramatic ‘back and forth’ between Ongres and MongoDB recently over a benchmark which showed Postgres ‘dominating’ MongoDB in certain workloads. MongoDB responded with a call to ‘do it right’ to which Ongres have doubled down with a strong defense and a promise of more benchmarks to come.

Álvaro Hernández

Postgres Gets an O'Reilly Open Source Award for 'Lifetime Achievement' — It feels weird for a project to get a ‘lifetime’ award, but good news nonetheless! Mark Wong, Bruce Momjian and Christophe Pettus accepted the award at O’Reilly’s OSCON event on behalf of the PostgreSQL project.

Mark Wong

Examining Postgres's Support for Pattern Matching with Regular Expressions — A regular expression (known as regexes in plural) is a sequence of characters that describes a textual search pattern. Postgres supports a few ways to use regexes and this first in a promised series of posts touches on POSIX regex support.

Muhammad Haroon

How to Adjust Linux's Out-Of-Memory Killer Settings for Postgres — The Out-Of-Memory ‘Killer’ is responsible for terminating applications in order to reclaim memory and prevent the kernel from crashing. Here are some suggestions to keep it out of Postgres’s way.

Ibrar Ahmed

Using plpgsql_check to Find Compilation Errors and Profile Functions — An interesting project called plpgsql_check can be used similarly to plprofiler while also looking at PL/pgSQL code and pointing out compilation errors.

Avinash Vallarapu

Metrics to Monitor in Your PostgreSQL Database — There are several key metrics you’ll want to keep track of when it comes to database performance, and they’re not all database-specific.

InfluxData sponsor

How Postgres Can Not Be 'Bought Out' — While MySQL was an open source database, Oracle still essentially managed to take the project over by acquiring its parent company. This can’t happen with Postgres.

Umair Shahid

Barman 2.9: The Backup and Recovery Management Tool — This release introduces native support for the forthcoming Postgres 12, which includes major changes in the way Point-In-Time-Recovery and Replicas are managed.

2ndQuadrant

How to Display the Encoded Byte Stream of a VARCHAR Column — I can’t think of a situation I’d use it, but an interesting bit of SQL nonetheless.

Abbas

supported by GitPrime

💡 Tip of the Week

Using pgstattuple to look at dead rows and free space

pgstattuple is a built-in Postgres extension that provides a variety of functions for looking at statistics of various objects within your database. One use is for checking how many dead rows (often caused when data is deleted from a table) there are or how much free space there is within a table.

Let's create a table with 1000 rows of data and see what info pgstattuple can give us:

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

Now let's get pgstattuple running and query our new table with it:

CREATE EXTENSION pgstattuple;
SELECT * FROM public.pgstattuple('numbers');

These stats show us how many tuples (i.e. rows) are in our table (1000), how many dead tuples there are (0) and any free space remaining within the table.

Let's delete half the rows to get some dead rows:

DELETE FROM numbers WHERE id < 500;
SELECT * FROM public.pgstattuple('numbers');

Now we get a dead_tuple_count of 499 — the number of rows we deleted! But the overall table_len is the same.. because of those dead rows. This can be resolved by running:

VACUUM numbers;

Re-running pgstattuple at this stage will show no dead rows, but the free_space will jump up.. with table_len remaining the same! The reason for this is that VACUUM will clean up dead rows and convert them into free space (which can be re-used for new rows), but you need to go one step further and use VACUUM FULL if you really want Postgres to rewrite the table and free up that space (though you need to take care of doing that on a production table as it can be very slow and locks the table for the entire time).

This week's tip is sponsored by GitPrime. Get your copy of their new field guide '20 Patterns to Watch for in Engineering Teams', filled with actionable insights to help debug your development process with data.

🗓 Upcoming Postgres Events