#362 — July 1, 2020

Web Version

Postgres Weekly

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 — 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

Whitepaper: BDR - Advanced Clustering & Scaling for PostgreSQL — 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 — 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 — 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 — The first five tips are related to Postgres in general, with the remainder being specific to TimescaleDB, a time series data extension for Postgres.

Michael Freedman

On Join Strategies and Performance — 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? — “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 — Now available in 75+ regions across 3 major clouds. Analytics, performance, and scale. 👉 Start free, no credit card required.

Timescale sponsor

Postgres 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 — 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 and this tutorial can help.

Yorvi Arias

Authenticating pgpool II with LDAP

Ahsan Hadi

A Story of Indexing JSONB Columns — A neat mix of story and tip when it comes to working with JSONB at scale.

Vsevolod Solovyov

supported by YugabyteDB

💡 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, the high-performance distributed SQL database for internet-scale applications. Serve applications with SQL query flexibility and cloud native agility.