Citus Data Blog

Citus Unforks from PostgreSQL, Goes Open Source

Citus Data Donates 1% of Equity to PostgreSQL Organizations

The donation of 1% of Citus Data stock is being made to advance the growth of the open source PostgreSQL database in both the U.S. & in Europe. The Citus database is an extension to PostgreSQL. Citus Data is also joining the Pledge 1% movement alongside organizations such as Atlassian, Twilio, Box, & more.

Create Statistics in PostgreSQL 10

Postgres stores a lot of statistics about your data in order to effectively retrieve results when you query your database. Here we deep dive into some of the statistics PostgreSQL stores and how you can leverage a new feature in Postgres 10 to improve performance when different columns are related.

Speeding up sort performance in Postgres 15

Deep dive into the sort performance improvements in PostgreSQL 15, along with charts to visualize what types of queries will be faster. Includes speedups in single column sorts, reduced memory consumption with generation memory context, specialized sort routines for common datatypes, & use of k-way merge.

Principles of Sharding for Relational Databases

Some say sharding is the best way to scale a database. Sharding enables you to linearly scale cpu, memory, and disk by separating your database into smaller parts. Yet, sharding is controversial. This post explores the principles of sharding relational databases for B2B, B2C, and B2B2C applications.

What's new in the Postgres 16 query planner

Postgres 16 introduces quite a few improvements to the query planner and makes many SQL queries run faster than they did on previous versions of PostgreSQL. This post is a deep dive into 10 of the improvements, including code blocks and comparisons of query plan output between Postgres 15 & Postgres 16.

SQL, Scaling, and What's Unique About Postgres

Citus gives you all the greatness of Postgres plus the superpowers of distributed tables. By distributing your data and queries, your application gets high performance—at any scale. The Citus database is available as open source and as a managed service on Azure.

Designing a SaaS Database for Scale with Postgres

Exploration of different architectures for designing a SaaS database that scales for multi-tenant data models, including one database per tenant, one schema per tenant, and having all tenants share the same tables. The right choice for you depends on whether you’re optimizing for scale or isolation.

Citus 10 brings columnar compression to Postgres

Deep dive into Citus columnar storage for Postgres. Includes a quickstart, as well as performance measurements for data loading, compression ratios, and query response times. Also covers how columnar relates to Citus, integration with Postgres range partitioning, limitations, & ways to get started.

Introducing WAL-G: Faster Disaster Recovery for Postgres

A key part of running a reliable database as a service is ensuring you have a good plan for disaster recovery. This post introduces WAL-G, the successor to WAL-E for Postgres, a complete rewrite of WAL-E that provides the same functionality plus 4x faster restores on recovery.

PostgreSQL 11 and Just in Time Compilation of Queries

PostgreSQL 11 includes a new component in the execution engine: a JIT expression compiler. A benchmark comparison shows up to 29.31% speed improvements in PostgreSQL 11 using the JIT expression compiler, executing TPC-H Q1 at scale factor 10 in 20.5s instead of 29s when using PostgreSQL 10.

Recursive Common Table Expressions in Postgres

Common Table Expressions in PostgreSQL are a powerful SQL construct. You can think of CTEs as a view that is materialized only while the query is running. Recursive CTEs allow themselves to be called until some condition is met. This post from the Citus Data team explores how recursive CTEs work in Postgres.

Citus 6.1 Released – Horizontally scale your Postgres database

Making PostgreSQL tick: New features in pg_cron

Since pg_cron 1.5, you can easily schedule jobs that run every 1-59 seconds. And pg_cron 1.6 added support for Postgres 16. This blog post walks you through the benefits of second granularity jobs with the pg_cron open source extension to Postgres.

Postgres tips for Rails developers

At RailsConf, the Citus engineering team found themselves sharing lots of useful Postgres tips on how to debug and improve the performance of your Postgres database from your Rails app. Includes topics like long-running queries, misbehaving queries, pg_stat_statements, pgBouncer, and more.

PostgreSQL Columnar Store for Analytic Workloads

Ways to paginate in Postgres (2016)

Pagination, pervasive as it is in web applications, is easy to implement inefficiently. This post examines different methods of server-side pagination and their tradeoffs in PostgreSQL. Methods explored include limit-offset, cursors, keyset pagination, as well as more exotic techniques.

How to Scale PostgreSQL on AWS: Learnings from Citus Cloud

Lessons learned in scaling out the PostgreSQL database in the cloud using Citus, an open source extension to Postgres that is also available as a fully-managed database as a service. From operational pain points like HA to disaster recovery to backups to monitoring and logging and alerts.

How Postgres is more than a relational database: Extensions

Extensions allow Postgres to be customized and extended in powerful new ways. Here we find a tour of extensions and how they can make Postgres into much more than a relational database

Citus 12: Schema-based sharding for PostgreSQL

Release blog post for the 12.0 release of the Citus extension to Postgres. Dive into new Citus 12 features such as schema-based sharding for multi-tenant SaaS applications (and microservices) as well improvements in the Citus support for the Postgres 15 MERGE feature.

Unstructured Datatypes in Postgres – Hstore vs. JSON vs. JSONB

All about when to use unstructured data types in Postgres, such as Hstore, JSON, and JSONB. The tl;dr is that the right data type depends on your use case. Here we dig deep into the benefits, and why JSONB became so popular since it first got real and proper support in Postgres 9.4. The B in JSONB stands for better.

Postgres Count Performance

Everybody counts, but not always quickly. This post looks into how the PostgreSQL database optimizes counting. In Postgres, there are ways to count orders of magnitude faster. We’ll look at exact counts (distinct counts) as well as estimated counts, using approximation algorithms such as HyperLogLog (HLL) in Postgres.

What it means to be a Postgres extension

Because Citus is an extension to PostgreSQL and not a fork, the effort required to remain compatible with the latest versions of the open source PostgreSQL database has been dramatically reduced. After a new PostgreSQL version is released, Citus packages are often updated and in sync within days.

Citus and pg_partman: Creating a scalable time series database on Postgres

How Citus Executes Distributed Transactions on Postgres

Distributed transactions are one of the meanest, baddest problems in relational databases. This post explains why distributed transactions are hard and how the Citus database supports distributed transactions by employing features like distributed deadlock detection, 2PC, and streaming replication in Postgres.

Useful Postgres Extension: Pg_stat_statements

Inspired by a talk in the PostgreSQL devroom at FOSDEM, a post on the usefulness of the pg_stat_statements extension to Postgres and why every Postgres developer should have it in their toolbox. With sections on getting started, and how to extract insights with pg_stat_statements.

Customizing My Postgres Shell

As a developer your CLI is your home. This post walks through some favorite customizations of the Postgres .psqlrc file, configurations that will make you more efficient in your use of Postgres. If you setup your psqlrc today, you can enjoy psql as much as your shell.

Introducing Citus Cloud

Postgres tips for the average and power user

A collection of power user tips for those working with Postgres

Sharding data models

At Citus we make it simple to shard PostgreSQL. So we’ve thought a lot about different data models for sharding. This post covers 5 different data models for sharding, from sharding by tenant (multi-tenant data models), sharding by geography, sharding by entity id, sharding a graph, and time-based partitioning.