---
title: "Testing Postgres extensions with Docker and GitHub Actions"
publication: "Postgres Weekly"
issue: 362
published_at: "2020-07-01"
canonical_url: "https://postgresweekly.com/issues/362"
---

This week's Postgres news, views, and tools.       

#362 — July 1, 2020 

[Web Version](https://postgresweekly.com/issues/362)   

Postgres Weekly 

[![\](https://res.cloudinary.com/cpress/image/upload/w_1280,e_sharpen:60/v1593609071/cchxwnmldtjs9xojgu4u.jpg)](https://aws.amazon.com/blogs/aws/amazon-rds-proxy-now-generally-available/)  

[Amazon RDS Proxy Now Generally Available](https://aws.amazon.com/blogs/aws/amazon-rds-proxy-now-generally-available/) — RDS Proxy is a fully managed database proxy for RDS (which includes the PostgreSQL variant). No code changes needed but there is a charge ($0.015 per hour per vCPU of your database servers). This is likely to be of particular interest to folks building serverless apps due to the high number of open database connections that can occur. 

Channy Yun (AWS)   

[Testing Extensions with GitHub Actions](https://justatheory.com/2020/06/test-extensions-with-github-actions/) — `pgxn-tools` provides a Docker image with scripts to install and run any version of Postgres and this can be brought together with a CI/CD platform like GitHub Actions to test Postgres extensions on multiple Postgres versions. 

David E. Wheeler   [![\](https://copm.s3.amazonaws.com/17472eff.png)](https://resources.2ndquadrant.com/postgres-bdr-whitepaper-download?utm_campaign=BDR3%20WhitePaper&utm_source=Cooper%20Press) 

[Whitepaper: BDR - Advanced Clustering & Scaling for PostgreSQL](https://resources.2ndquadrant.com/postgres-bdr-whitepaper-download?utm_campaign=BDR3%20WhitePaper&utm_source=Cooper%20Press) — Learn more about Advanced Clustering & Scaling for PostgreSQL with BDR including use cases, architectures, features & benefits. BDR provides multi-master replication with AlwaysOn availability, worldwide clusters, & Cloud Native deployments for PostgreSQL databases. 

2ndQuadrant PostgreSQL Products sponsor   

[Calculating Differences Between Rows in SQL](https://www.cybertec-postgresql.com/en/calculating-differences-between-rows-in-sql/) — If you have lots of rows that represent changing values (such as time series data, say) being able to calculate the _differences_ between the values over time could be useful, here are some approaches. 

Hans-Jürgen Schönig   

PDF: [Running Postgres in Kubernetes](https://static.sched.com/hosted_files/ossna2020/fc/Running%20Postgres-as-a-Service%20in%20Kubernetes.pdf) — A slidedeck based rundown of the top options for running Postgres in Kubernetes and some of the features contained within each. 

Lukas Fittl   

[13 Tips to Improve Insert Performance on Postgres and TimescaleDB](https://blog.timescale.com/blog/13-tips-to-improve-postgresql-insert-performance/) — The first five tips are related to Postgres in general, with the remainder being specific to [TimescaleDB](https://github.com/timescale/timescaledb), a time series data extension for Postgres. 

Michael Freedman   

[On Join Strategies and Performance](https://www.cybertec-postgresql.com/en/join-strategies-and-performance-in-postgresql/) — It’s worth understanding the three forms of _join strategy_ Postgres uses when joining relations and how you can influence them with indexes. 

Laurenz Albe   

[Can Case Comparison Be Controlled?](https://momjian.us/main/blogs/pgblog/2020.html#June_26_2020) — _“Let’s go over some Postgres case-precision behaviors like the handling of character strings, identifiers, and keywords.”_ A handy primer to case comparison issues here. 

Bruce Momjian   

[Timescale Cloud: Hosted, High-Performance PostgreSQL](https://tsdb.co/start-free) — Now available in 75+ regions across 3 major clouds. Analytics, performance, and scale. 👉 [Start free](https://tsdb.co/start-free), no credit card required. 

Timescale sponsor   

[Postgres Anonymization on Demand](https://severalnines.com/database-blog/postgresql-anonymization-on-demand) — If your database stores personally identifiable information – and it probably does! – anonymization can help protect that data while still keeping the database useful. This articles digs into an approach for applying anonymization globally, covering all applications, with minimal code rewrites. 

Achilleas Mantzios   

[Setting Up `ora2pg` for an Oracle to Postgres Migration](https://info.crunchydata.com/blog/setup-ora2pg-for-oracle-to-postgres-migration) — I _suspect_ if you have access to an Oracle database, you might not be looking around for tutorials randomly, but if you are.. and if you need to migrate the data to Postgres.. [ora2pg](http://ora2pg.darold.net/) and this tutorial can help. 

Yorvi Arias   

[Authenticating pgpool II with LDAP](https://www.highgo.ca/2020/06/25/authenticating-pgpool-ii-with-ldap/) 

Ahsan Hadi   

[A Story of Indexing JSONB Columns](https://vsevolod.net/postgresql-jsonb-index/) — A neat mix of story and tip when it comes to working with JSONB at scale. 

Vsevolod Solovyov      supported by [![YugabyteDB\](https://res.cloudinary.com/cpress/image/upload/v1593529453/obtljl96reuhv3jzrxqr.png)](https://blog.yugabyte.com/why-we-built-yugabytedb-by-reusing-the-postgresql-query-layer/)  

💡**Tip of the Week** 

Use WITH to query a subset 

There is a whole lot you can do with Common Table Expressions (CTEs) from traverse trees to using window functions in rankings. CTE functions are implemented by the “WITH” clause. A basic use is to query a subset -- similar to a subquery. 

Let’s demonstrate this by setting up some sample data:  

CREATE TABLE databases (
 name TEXT, model TEXT, type TEXT, pgcompat boolean, opensource boolean);
 
 INSERT INTO databases VALUES
 ('YugabyteDB', 'rdbms' , 'distributed sql', TRUE, TRUE), 
 ('CockroachDB', 'rdbms', 'distributed sql', FALSE, FALSE),
 ('MongoDB', 'document', 'nosql', FALSE, FALSE), 
 ('Neo4j', 'graph', 'nosql', TRUE, FALSE),
 ('Cassandra', 'columnar', 'nosql', FALSE, TRUE);   

If we wanted to run a query against databases that were open source licensed, support SQL and are compatible with PostgreSQL:  

WITH dbs AS (
 SELECT 
 name, 
 model,
 (CASE 
 WHEN type = 'distributed sql' then TRUE
 ELSE FALSE
 END) sql_support,
 pgcompat,
 opensource
 FROM
 databases
 )
 SELECT
 name,
 model
 FROM 
 dbs
 WHERE
 opensource = TRUE AND
 pgcompat = TRUE AND
 sql_support = TRUE
 ORDER BY 
 name;   

We created a CTE called dbs and reinterpreted the “type” field to be sql_support as a boolean using a case statement. In the main select statement we query against this CTE and in our where clause, we are able to specify only booleans. 

This gets us a result like so:  

name | model
 -------------------
 YugabyteDB | rdbms   

This week’s tip is sponsored by [YugabyteDB](https://blog.yugabyte.com/why-we-built-yugabytedb-by-reusing-the-postgresql-query-layer/), the high-performance distributed SQL database for internet-scale applications. Serve applications with SQL query flexibility and cloud native agility.          

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