If you want to learn more about Citus on Microsoft Azure, read this post about Hyperscale (Citus) on Azure Database for PostgreSQL.

Skip navigation

Citus Blog

Articles tagged: Postgres

Postgres is an amazing RDBMS implementation. Postgres is open source and it’s one of the most standard-compliant SQL implementations that you will find (if not the most compliant.) Postgres is packed with extensions to the standard, and it makes writing and deploying your applications simple and easy. After all, Postgres has your back and manages all the complexities of concurrent transactions for you.

In this post I am excited to announce that a new version of pg_auto_failover has been released, pg_auto_failover 1.4.

pg_auto_failover is an extension to Postgres built for high availability (HA), that monitors and manages failover for Postgres clusters. Our guiding principles from day one have been simplicity and correctness. Since pg_auto_failover is open source, you can find it on GitHub and it’s easy to try out. Let’s walk through what’s new in pg_auto_failover, and let’s explore the new capabilities you can take advantage of.

Keep reading

One common challenge with Postgres for those of you who manage busy Postgres databases, and those of you who foresee being in that situation, is that Postgres does not handle large numbers of connections particularly well.

While it is possible to have a few thousand established connections without running into problems, there are some real and hard-to-avoid problems.

Since joining Microsoft last year in the Azure Database for PostgreSQL team—where I work on open source Postgres—I have spent a lot of time analyzing and addressing some of the issues with connection scalability in Postgres.

Keep reading

Many of you rely on databases to return correct results for your SQL queries, however complex your queries might be. And you probably place your trust with no questions asked—since you know relational databases are built on top of proven mathematical foundations, and since there is no practical way to manually verify your SQL query output anyway.

Since it is possible that a database’s implementation of the SQL logic could have a few errors, database developers apply extensive testing methods to avoid such flaws. For instance, the Citus open source repo on GitHub has more than twice as many lines related to automated testing than lines of database code. However, checking correctness for all possible SQL queries is challenging because of the lack of a “ground truth” to compare their outputs against, and the infinite number of possible SQL queries.

Keep reading
Jeff Davis

How to securely authenticate with SCRAM in Postgres 13

Written byBy Jeff Davis | July 28, 2020Jul 28, 2020

Making security easy to use is crucial because hard-to-use security is likely to be neglected entirely. SCRAM with channel binding is a variation of password authentication that is almost as easy to use, but much more secure.

In basic password authentication, the connecting client simply sends the server the password. Then the server checks that it’s the right one, and allows the client to connect. Basic password authentication has several weaknesses which are addressed with SCRAM and channel binding.

In this article, you’ll learn how to set up authentication using SCRAM with channel binding in Postgres. I implemented the client connection parameter channel_binding in PostgreSQL 13, due to be released in late 2020 (PostgreSQL 13 is in beta now). SCRAM and Channel Binding have already been supported in several releases, but this new connection parameter is necessary to realize the security benefits of SCRAM and Channel Binding

Keep reading

Custom types—called user-defined types in the PostgreSQL docs—are a powerful Postgres capability that, just like Postgres extensions, were envisioned from Day One in the original design of Postgres. Published in 1985, the Design of Postgres paper stated the 2nd design goal as: “provide user extendibility for data types, operators and access methods.”

It’s kind of cool that the creators of Postgres laid the foundation for the powerful Postgres extensions of today (like PostGIS for geospatial use cases, Citus for scaling out Postgres horizontally, pg_partman for time-based partitioning, and so many more Postgres extensions) way back in 1985 when the design of Postgres paper was first published.

Keep reading

This year, I was so excited about doing a workshop about optimizing Python & Django apps with Postgres superpowers for the PyCon 2020 conference.

Working with other developers on performance is something I always find amazing. So props to the Python people at Microsoft who encouraged my team to create a workshop on Postgres for PyCon 2020. Thank you to Nina Zakharenko, Dan Taylor, & Crystal Kelch.

Alas, we had to change our plans and find other ways to share the PostgreSQL workshop content that we had prepared. So I created a video on the topic of database performance for Django developers, to help teach you the PostgreSQL tips and tricks that have served me well in optimizing my Django apps. These tips are what I call “Postgres superpowers.”

Keep reading

In one of our recent releases of the open source Citus extension, we overhauled the way Citus executes distributed SQL queries—with the net effect being some huge improvements in terms of performance, user experience, Postgres compatibility, and resource management. The Citus executor is now able to dynamically adapt to the type of distributed SQL query, ensuring fast response times both for quick index lookups and big analytical queries.

We call this new Citus feature the “adaptive executor” and we thought it would be useful to walk through what the Citus adaptive executor means for Postgres and how it works.

Keep reading
Ozgun Erdogan

Microsoft Azure Welcomes PostgreSQL Committers

Written byBy Ozgun Erdogan | March 3, 2020Mar 3, 2020

Interview with the Postgres committers who have joined the Postgres team at Microsoft by Sudhakar Sannakkayala (Partner Director, Azure Data) and Ozgun Erdogan (Principal, Azure Data)—cross-posted from the Azure Postgres blog.

In recent years, the data landscape has seen strong innovation as a result of the onset of open source technologies. At the forefront, PostgreSQL has shown that it’s the open source database built for every type of developer. By staying true to its principles of being standards-compliant, highly programmable, and extensible, PostgreSQL has solidified its position as the “most loved database” of developers across the board—ranging from scenarios for OLTP, analytics, and business intelligence to processing various formats of geometric data using the PostGIS extension.

Keep reading
Craig Kerstiens

Postgres tips for the average and power user

Written byBy Craig Kerstiens | July 17, 2019Jul 17, 2019

Personally I’m a big fan of email, just like blogging. To me a good email thread can be like a good novel where you’re following along always curious for what comes next. And no, I don’t mean the ones where there is an email to [email protected] and someone replies all, to only receive reply-all’s to not reply-all. I mean ones like started last week internally among the Azure Postgres team.

The first email was titled: Random Citus development and psql tips, and from there it piled on to be more and more tips and power user suggestions for Postgres. Some of these tips are relevant if you’re working directly on the Citus open source code, others relevant as anyone that works with Postgres, and some useful for debugging Postgres internals. While the thread is still ongoing here is just a few of the great tips:

Keep reading
Craig Kerstiens

Postgres and superuser access

Written byBy Craig Kerstiens | April 4, 2019Apr 4, 2019

A few days ago a CVE was announced for Postgres. To say this CVE is a bit overblown is an understatement. The first thing to know is you’re likely completely safe. If you run on a managed service provider you are not going to be affected by this, and if you’re managing your own Postgres database all chances are you are equally as safe. This CVE received a note from Tom Lane on the pgsql-announce mailing list in response to it getting a broad amount of awareness and attention.

But, we thought this might be a good time to talk about a few principles and concepts that underly how Postgres works.

Keep reading

Page 1 of 9