Postgres tips for the average and power user

An internal email amongst the Postgres team here at Microsoft—about random Citus development and psql tips—prompted quite a discussion about Postgres tips. Craig pulled the most useful ones into this post which hit the front page of Hacker News.
Useful Postgres tips → 

Testing your Django app with Citus

Engineer Louise Grandjonc shares her experience testing a multi-tenant Django application on Citus. Here are step-by step instructions outlining how to set up an automated test suite with Citus.
Read Louise’s post → 

Video: Venice DB: a petabyte-scale real-time analytics service running Postgres on Azure

At PGCon in Ottawa in May, Min Wei spoke about VeniceDB, a large scale OLAP service with a custom build of Citus on Azure. The 1 PB cluster supports the Microsoft CoreOS executive decision dashboard that hosts all measures, from edge devices to cloud OS. If you are tackling real time analytics on massive amounts of data, we think you might like this video. :-)
Watch the video → 

PostgreSQL's indexes & GIN 🍸

In this article, Louise Grandjonc answers the question: why should we use GIN indexes for index arrays, jsonb, and tsvector (for fulltext search) data types and operators? Crocodiles with bad teeth and dentists who happen to be plover birds help Louise present two examples, one for arrays and one for fulltext search.
More on GIN indexes → 

PostgresOpen sessions announced—and the schedule is coming any minute

Sessions for the September PostgresOpen conference in Florida have been announced and the speaker lineup looks great—new and seasoned Postgres speakers from around the world to delight and inspire you. :-) From the art of Postgres, to Postgres’s IO subsystem, to Explain plans. You’ll even discover the many ways you can contribute to Postgres (beyond code).
Check out the PostgresOpen sessions → 

Using query caching to improve performance of read-intensive workloads

When building cloud-native applications using managed services, latency between the application and database layer can add to the query response times impacting overall application performance. In this post, Parikshit explains the pros and cons of using query caching to improve query performance on Azure Database for PostgreSQL.
More on query caching → 

Fun with SQL: Recursive CTEs in Postgres

For applications that deal with ecommerce and shopping categories, recursive CTEs are a great help. Craig explains why next time you need to do some recursive computation across your data, you should consider doing it directly in SQL rather than loading all that data into your app.
Read Craig’s post → 

What’s happening with Citus and Microsoft Azure?

Hyperscale (Citus) is now available in preview on Azure Database for PostgreSQL. Want to stay in the loop? We’ve made it easy for you. Just sign up to the Citus on Azure interest list.
Sign up on the Citus on Azure interest list → 

Steps to install and setup PgBouncer connection pooling proxy with Azure DB for PostgreSQL

Ramkumar & Parikshit, share the steps to install and setup a PgBouncer connection pooling proxy between your app and Azure Database for PostgreSQL. In addition, they also provide a QuickStart template to enable you to quickly deploy and setup PgBouncer in a VM and get started. Sweet!
Steps to install & setup PgBouncer → 

ICYMI: Database sharding explained in plain English

In building CItus to scale out Postgres horizontally, we’ve learned a thing or two about sharding PostgreSQL. Our engineers are often asked to walk people through what sharding is, and how it works. Here’s how Craig explains it. In plain English.
More about database sharding → 

And the conference circuit continues. Come find us at these upcoming events:

• Dimitri Fontaine will present “The Art of Postgres” at PGConf.Brasil

• At PyBay, Louise Grandjonc to present “Amazing things your ORM(s) can and can't do”

• Craig Kersteins to speak on “Building PostgreSQL apps at any scale with Hyperscale (Citus)” at Music City Tech

• The Postgres team at Microsoft is pleased to sponsor PGDay Austria

• Louise Grandjonc to present “Postgres Index types and where to find them” at DjangoCon in San Diego

Is Citus right for you?

Want to explore whether Citus is right for you and your use case? Learn more about the Citus product, check out the documentation, or let us know if you want to book a demo.