Citus Data Blog

Thoughts on scaling out PostgreSQL, sharding, multi-tenant apps, real-time analytics, and distributed databases.

Craig Kerstiens
By Craig Kerstiens
July 31, 2018

Introducing Landlord: per tenant stats in Postgres with Citus

Postgres keeps getting better and better. In recent years, the Postgres community has added JSONB support, improved performance, and added so many usability enhancements. The result: you can work even more powerfully with your database. Over the past 8 years, my favorite two enhancements have been JSONB and pg_stat_statements. Pg_stat_statements is a built-in extension that allows you to get high level insights into queries that are being run as well as their performance—without having to be an expert and without needing a PhD in databases.

Introducing the new landlord feature in Citus 7.5

With Citus 7.5, we’ve gone one step beyond the awesomeness of pg_stat_statements and Postgres, with the new landlord feature in Citus—to give you per-tenant stats.

Continue reading
Claire Giordano
By Claire Giordano
July 29, 2018

All the things coming soon to PostgresOpen SV 2018

In this world of all things digital where so many of us are online so much of the time—what with architecting, coding, QA'ing, blogging, and slacking—it’s kind of refreshing to step away from our devices and talk to other humans face-to-face at an event.

Especially when it’s a conference chock full of PostgreSQL open source people, from users to developers to community leaders.

Especially when it’s right in our own backyard here in San Francisco.

Especially when it’s PostgresOpen SV 2018

Continue reading
Marco Slot
By Marco Slot
July 25, 2018

High performance distributed DML in Citus

One of the many unique abilities of SQL databases is to transform data using advanced SQL queries and joins in a transactional manner. Commands like UPDATE and DELETE are commonly used for manipulating individual rows, but they become truly powerful when you can use subqueries to determine which rows to modify and how to modify them. It allows you to implement batch processing operations in a thread-safe, transactional, scalable manner.

Citus recently added support for UPDATE/DELETE commands with subqueries that span across all the data. Together with the CTE infrastructure that we’ve introduced over the past few releases, this gives you a new set of powerful distributed data transformation commands. As always, we’ve made sure that queries are executed as quickly and efficiently as possible by spreading out the work to where the data is stored.

Let’s look at an example of how you can use UPDATE/DELETE with subqueries.

Continue reading
Craig Kerstiens
By Craig Kerstiens
July 19, 2018

ZFS Private Beta on Citus Cloud

ZFS is a open source file system with the option to store data on disk in a compressed form. Itself ZFS supports a number of compression algorithms, giving you flexibility to optimize both performance and how much you store on disk. Compressing your data on disk offers two pretty straightforward advantages:

  1. Reduce the amount of storage you need—thus reducing costs
  2. When reading from disk, requires less data to be scanned, improving performance

To date, we have run Citus Cloud—our fully-managed database as a service that scales out Postgres horizontally—in production on EXT4. Today, we’re excited to announce a limited beta program of ZFS support for our Citus Cloud database. ZFS makes Citus Cloud even more powerful for certain use cases. If you are interested in access to the beta contact us to get more info, or continue reading to learn more about the use cases where ZFS and Citus and Postgres can help.

Continue reading
Sai Krishna Srirampur
By Sai Krishna Srirampur
July 3, 2018

Using search_path and views to hide columns for reporting with Postgres

Data security and data privacy are important, no one disputes that. We all want to keep private things private and to keep our data secure. And yet, data needs to be shared, to enable insights, to help organizations observe patterns and have those “ah-ha” moments. None of us want the extreme where, in an effort to keep data secure, there is no access to data of any form within your organization, and the result is no business insights or analytics. With GDPR going into effect, you’ve likely been rethinking what security controls you have in place.

Here at Citus Data we collaborate with SaaS businesses and larger enterprises alike, generally to consult on Postgres data models and how to best scale out their database. (Our Citus extension to Postgres enables you to scale out Postgres horizontally. The benefit: performance.) In working with teams, one common thing we’ve seen companies do is to restrict who can see which bits of Personally Identifiable Information (PII) within your database. There are a number of approaches, including heavyweight ETL processes that mask PII bits. An ETL process tends to introduce a certain amount of latency from the time data is in your system until the time it can be analyzed.

Fortunately, Postgres provides a few primitives that can be used directly within your database to hide PII, while still enabling sophisticated analytics and exploration of data in real time.

Here we’ll look at using Postgres schemas and views to provide access to data while keeping PII safe and hidden.

Continue reading
Craig Kerstiens
By Craig Kerstiens
June 28, 2018

Options for scaling from 1 to 100,000 tenants

When you first start out in building a SaaS application you talk about that day in the future when you will have scaling problems, how that’ll be the day, how that would be a good problem to have. You focus on getting the first few customers, making sure they have a great experience, and suddenly you’re at 10s of customers, then 100s. You’ve upgraded your app server to a larger one, then you’ve gone from one ec2 app server to multiple ones with ELB in front of things. You’ve upgraded your Postgres database from an r3.large on AWS, to r3.xlarge, now you’re eyeing that r3.2xlarge next month. In the back of your mind though, you’re starting to look at your plans for future growth of your SaaS app, and you’re wondering how much larger you can keep going. Your database is performing well at 100 tenants (tenants = customers), your back of the napkin math says you’ll be able to scale your app up to 1,000 tenants, but after that you know you’re going to have to explore some options.

What are those options and what are the trade-offs and benefits?

Continue reading
Craig Kerstiens
By Craig Kerstiens
June 21, 2018

Fun with SQL: Functions in Postgres

In our previous Fun with SQL post on the Citus Data blog, we covered window functions. Window functions are a special class of function that allow you to grab values across rows and then perform some logic. By jumping ahead to window functions, we missed so many of the other handy functions that exist within Postgres natively. There are in fact several hundred built-in functions. And when needed, you can also create your own user defined functions (UDFs), if you need something custom. Today we’re going to walk through just a small sampling of SQL functions that can be extremely handy in PostgreSQL.

Continue reading
Craig Kerstiens
By Craig Kerstiens
June 19, 2018

How do you pronounce Citus?

It’s a common question we get at conferences, on calls, in meetings. “Citrus”, “Citius”, “Citus”, is that how you pronounce it? The quick and short of it is, we’re not named after a fruit. You pronounce it like site-us.

Most tend to leave it there, without wondering further. But a few do enquire as to the meaning. Citus’ name comes from the Olympic Motto “Citius, Altius, Fortius” which is Latin for “Faster, Higher, Stronger.” Our goal at Citus Data is to build a database that’s fast both for transactional and analytical workloads.

Continue reading
Marco Slot
By Marco Slot
June 14, 2018

Scalable incremental data aggregation on Postgres and Citus

Many companies generate large volumes of time series data from events happening in their application. It’s often useful to have a real-time analytics dashboard to spot trends and changes as they happen. You can build a real-time analytics dashboard on Postgres by constructing a simple pipeline:

  1. Load events into a raw data table in batches
  2. Periodically aggregate new events into a rollup table
  3. Select from the rollup table in the dashboard

For large data streams, Citus (an open source extension to Postgres that scales out Postgres horizontally) can scale out each of these steps across all the cores in a cluster of Postgres nodes.

One of the challenges of maintaining a rollup table is tracking which events have already been aggregated—so you can make sure that each event is aggregated exactly once. A common technique to ensure exactly-once aggregation is to run the aggregation for a particular time period after that time period is over. We often recommend aggregating at the end of the time period for its simplicity, but you cannot provide any results before the time period is over and backfilling is complicated.

Continue reading
Craig Kerstiens
By Craig Kerstiens
June 12, 2018

Configuring memory for Postgres

work_mem is perhaps the most confusing setting within Postgres. work_mem is a configuration within Postgres that determines how much memory can be used during certain operations. At its surface, the work_mem setting seems simple: after all, work_mem just specifies the amount of memory available to be used by internal sort operations and hash tables before writing data to disk. And yet, leaving work_mem unconfigured can bring on a host of issues. What perhaps is more troubling, though, is when you receive an out of memory error on your database and you jump in to tune work_mem, only for it to behave in an un-intuitive manner.

Continue reading

Page 2 of 20