Login and Permission Tips and Tricks in using pg_dump and pg_restore

pg_dump and pg_restore are commonly used in data movement between PostgreSQL servers. Best practices for pg_dump and pg_restore assume access to superuser accounts, which are not always available in cloud offerings. Shau Phang shares some tips and tricks that will help you in executing pg_dump and pg_restore when you are migrating data from on-premise to the cloud.
Read Shau’s post → 

An interview with Louise Grandjonc on Postgres Index Types and where to find them

Last week before DjangoCon US, Python developer and Azure Postgres engineer Louise Grandjonc sat down for an interview with Claire Giordano about her DjangoCon talk “Postgres Index Types and where to find them”. Louise explains technical topics in a way that makes sense—and she often uses unusual (and fun) examples, from crocodiles to owls, from Harry Potter to Taylor Swift. Here’s an edited transcript of Claire’s interview with Louise (@louisemeta). Enjoy.
Read the interview → 

Landlord: per tenant stats in Postgres with Citus

We created the landlord feature in Citus to give you statistics by tenant, going one step beyond the awesomeness of pg_stat_statements in Postgres. Landlord is useful for developers of multi-tenant apps who want insight into whether any tenants are creating load hotspots.
Find out more about landlord → 

Fun with SQL: Common Table Expressions for more readable queries

CTEs are a powerful tool to help build logic blocks and gradually construct more complex SQL queries. With CTEs, your SQL becomes readable to someone who doesn’t have detailed knowledge of the application. If you use CTEs, your colleagues may appreciate reading your query—even a query hundreds of lines long. But, you’ll want to be careful: if heavily used within your app, CTEs are an optimization boundary. Yet for use cases where readability is as important as a few milliseconds, CTEs are a good choice.
Craig has fun with SQL → 

12 Factor: Dev/prod parity for your database

Dev/prod parity is a challenge when it comes to databases. You can run the same version of your database, and have a sandbox copy, but testing & staging with production data is a challenge. In this post, Craig reminds us that Dev/prod parity is one of the factors defined by the Twelve-Factor app, and explains why Dev/prod parity is easy, until it's not.
More about 12 Factor and databases → 

Video: JSONB Tricks: Operators, Indexes, and When to (Not) Use It

In his talk at PostgresOpen, Colton Shepard shared his insights on when to use jsonb, and when not to. He also covered operator usage and optimization, indexing, and what’s coming in Postgres 12. In this talk, you will get details on these topics, complete with practical examples and real-world stories.
Watch the video → 

Brandur’s article on "Doubling the Sorting Speed of Postgres Network Types with Abbreviated Keys"

A patch Brandur recently committed added SortSupport for the inet and cidr types, which by his measurement, a little more than doubles sorting speed on them. In this article, Brandur explains some of the subtleties which made designing an abbreviated key that would be faithful to those subtleties but still efficient.
Read Brandur’s article → 

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 → 

Microsoft Ignite in Orlando Nov 4-8

Ignite is Microsoft’s annual gathering of technology leaders and practitioners. It is 4 days of vision, discussions, and hands-on learning about the latest tools—as well as the insights that are driving tomorrow’s innovations. The Postgres team will be there with talks on Hyperscale (CItus), why developers love Postgres, running Postgres at scale on-premise and in the cloud, and more. Hope to see you there!
More on Ignite → 

ICYMI: Not all Postgres connection pooling is equal

Here, Craig explains the benefits of PgBouncer. Even if your database already has connection pooling setup at the application level, your database might still benefit from connection pooling with PgBouncer. This can begin as early as 10s of connections to your database, and is critical when you have several hundred connections.
More on PgBouncer → 

PGConf EU, Percona Live Europe & more:

• Come see us at PGConf EU in Milan! We have sessions onTopN, how to contribute beyond code, what to do when things go wrong, and more.

• At Percona Live Europe in Amsterdam, Craig Kerstiens will present “Postgres: From Respected-by-DBAs to Loved-by-Application-Developers”

• The Postgres team at Microsoft is proud to sponsor diversity and inclusion programming at Monktoberfest.

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 watch this short demo of Hyperscale (Citus) on Azure Database for PostgreSQL.