---
title: "Ever wanted to create a Postgres extension in C?"
publication: "Postgres Weekly"
issue: 317
published_at: "2019-08-07"
canonical_url: "https://postgresweekly.com/issues/317"
---

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

#317 — August 7, 2019 

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

Postgres Weekly 

[![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60/v1565174369/cf22e4nqrrvzco0djbht.jpg)](https://www.percona.com/blog/2019/07/31/postgresql-simple-c-extension-development-for-a-novice-user/)   

[The Absolute Beginner's Guide to Creating a Postgres Extension with C](https://www.percona.com/blog/2019/07/31/postgresql-simple-c-extension-development-for-a-novice-user/) — 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](https://blog.couchbase.com/postgres-jsonb-and-nosql/) — 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)   [![\](https://copm.s3.amazonaws.com/f3fcd316.png)](https://pganalyze.com/ebooks/optimizing-postgres-query-performance?utm_source=PostgresWeeklyPrimary) 

[Free eBook: How to Get a 3x Performance Improvement on Your Postgres Database](https://pganalyze.com/ebooks/optimizing-postgres-query-performance?utm_source=PostgresWeeklyPrimary) — 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'](https://www.ongres.com/blog/benchmarking-do-it-with-transparency/) — There’s been a bit of a dramatic ‘back and forth’ between Ongres and MongoDB recently over [a benchmark](https://www.enterprisedb.com/news/new-benchmarks-show-postgres-dominating-mongodb-varied-workloads) which showed Postgres ‘dominating’ MongoDB in certain workloads. MongoDB responded with [a call to ‘do it right’](https://www.mongodb.com/blog/post/benchmarking-do-it-right-or-dont-do-it-at-all) 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'](https://www.2ndquadrant.com/en/blog/postgresql-award/) — 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](https://www.2ndquadrant.com/en/blog/postgresql-regular-expressions-and-pattern-matching/) — 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](https://www.percona.com/blog/2019/08/02/out-of-memory-killer-or-savior/) — 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](https://www.percona.com/blog/2019/07/30/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](https://www.influxdata.com/blog/metrics-to-monitor-in-your-postgresql-database/?utm_campaign=postgres&utm_medium=newsletter&utm_source=cooperpress) — 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'](https://www.2ndquadrant.com/en/blog/postgres-is-the-coolest-database-reason-5-it-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](https://www.postgresql.org/about/news/1959/) — 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](https://abbas-technical.blogspot.com/2019/07/how-to-display-encoded-byte-stream-of.html) — I can’t think of a situation I’d use it, but an interesting bit of SQL nonetheless. 

Abbas      supported by [![GitPrime\](https://res.cloudinary.com/cpress/image/upload/v1554813939/kwagarrsergds4sxgjr9.png)](https://resources.gitprime.com/books/20-patterns/?utm_source=nl%28pgw%29&utm_medium=email-nl&utm_campaign=nl%28pgw%29)  

💡 Tip of the Week 

Using `pgstattuple` to look at dead rows and free space 

[pgstattuple](https://www.postgresql.org/docs/11/pgstattuple.html) 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'); 

![](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60/v1565092544/mtu5it0lrih9ho7r2w96.png)   

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'); 

![](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60/v1565093436/l5mitxgxty3d4f8mj5ke.png)   

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](https://resources.gitprime.com/books/20-patterns/?utm_source=nl%28pgw%29&utm_medium=email-nl&utm_campaign=nl%28pgw%29)_. Get your copy of their new field guide '[20 Patterns to Watch for in Engineering Teams](https://resources.gitprime.com/books/20-patterns/?utm_source=nl%28pgw%29&utm_medium=email-nl&utm_campaign=nl%28pgw%29)', filled with actionable insights to help debug your development process with data.      

🗓 Upcoming Postgres Events 

- [PGDay Austria 2019](https://pgday.at/en/) (September 6 in Wiener Neustadt, Austria)
- [PostgreSQL Conference Asia 2019](http://2019.pgconf.asia/) (September 8-11 in Bali, Indonesia)
- [PostgresConf Silicon Valley 2019](https://postgresconf.org/conferences/SV2019) (September 18-20 in San Jose) — the schedule (including training) has now been published.
- [PostgresConf South Africa 2019](https://postgresconf.org/conferences/SouthAfrica2019) (October 8-9 in Johannesburg) — An opportunity for the database administration and developer community who use Postgres to get to know each other.          

Curated by [Cooperpress](https://cooperpress.com/), [Peter Cooper](https://twitter.com/peterc), 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