Citus Blog

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 all-employees@company.com 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:

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.

I talk with a lot of folks that set their database up, start working with it, and then are surprised by issues that suddenly crop up out of nowhere. The reality is, so many don't want to have to be a DBA, instead you would rather build features and just have the database work. But your is that a database is a living breathing thing. As the data itself changes what is the right way to query and behave changes. Making sure your database is healthy and performing at it's maximum level doesn't require a giant overhaul constantly. In fact you can probably view it similar to how you approach personal health. Regular check-ups allow you to make small but important adjustments without having to make dramatic life altering changes to keep you on the right path.

After years of running and managing literally millions of Postgres databases, here's my breakdown of what your regular Postgres health check should look like. Consider running this on a monthly basis to be able to make small tweaks and adjustments and avoid the drastic changes.

Choosing a database isn't something you do every day. You generally choose it once for a project, then don't look back. If you experience years of success with your application you one day have to migrate to a new database, but that occurs years down the line. In choosing a database there are a few key things to consider. Here is your checklist, and spoiler alert, Postgres checks out strongly in each of these categories.

SQL by itself is great and powerful, and Postgres supports a broad array of more modern SQL including things like window functions and common table expressions. But rarely do I write a query where I don't want to tweak or format the data I'm getting back out of the database. Thankfully Postgres has a rich array of functions to help with converting or formatting data. These built-in functions save me from having to do the logic elsewhere or write my own functions, in other words I have to do less work because Postgres has already done it for me which I'm always happy about.

We've covered a set of functions earlier, today we're going to look at some different categories of functions to dive deeper.

In an earlier blog post I wrote about how breaking problems down into a MapReduce style approach can give you much better performance. We've seen Citus is orders of magnitude faster than single node databases when we're able to parallelize the workload across all the cores in a cluster. And while count (*) and avg is easy to break into smaller parts I immediately got the question what about count distinct, or the top from a list, or median?

Exact distinct count is admittedly harder to tackle, in a large distributed setup, because it requires a lot of data shuffling between nodes. Count distinct is indeed supported within Citus, but at times can be slow when dealing with especially larger datasets. Median across any moderate to large size dataset can become completely prohibitive for end users. Fortunately for nearly all of these there are approximation algorithms which provide close enough answers and do so with impressive performance characteristics.

Extensions are capable of extending, changing, and advancing the behavior of Postgres. How? By hooking into low level Postgres API hooks. The open source Citus database that scales out Postgres horizontally is itself implemented as a PostgreSQL extension, which allows Citus to stay current with Postgres releases without lagging behind like other Postgres forks. I've previously written about the various types of extensions, today though I want to take a deeper look at the most useful Postgres extension: pg_stat_statements.

About once a month I get this question: "How do I contribute to Postgres?". PostgreSQL is a great database with a solid code base and for many of us, contributing back to open source is a worthwhile cause. The thing about contributing back to Postgres is you generally don't just jump right in and commit code on day one. So figuring out where to start can be a bit overwhelming. If you're considering getting more involved with Postgres, here's a few tips that you may find helpful.

Various families have various traditions in the US around Christmas time. Some will play games like white elephant where you get a mix of decent gifts as well as gag gifts... you then draw numbers and get to pick from existing presents that have been opened ("stealing" from someone else) or opening an up-opened one. The game is both entertaining to try to get something you want, but also stick Aunt Jennifer with the stuffed poop emoji with a Santa hat on it.

Other traditions are a bit simpler, one that my partner's family follows is drawing names for one person you buy a gift for. This is nice because you can put a bit of effort into that one person without having to be too overwhelmed in tracking down things for multiple people. Each year we draw names for the next year. And by now you're probably thinking what does any of this have to do with SQL? Well normally when we draw names we write them on a piece of paper, someone takes a picture, then that gets texted around to other family members. At least for me every October I'm scrolling back through text messages to try to recall who it was I'm supposed to buy for. This year I took a little time to put everyone's name in a SQL database and write a simple query for easier recall.

I recently had the honor of speaking at the last Keep Ruby Weird. A good part of the talk dealt with Postgres and since Citus Data is not only a database company but also a Postgres company, I figured sharing those parts on the Citus Data blog would be a good idea. If you'd like to see it in talk form, or you'd also like to know how to watch movies rendered as emojis in your terminal, I encourge you to watch the talk.