We are happy to announce our official integration with Cube. Now you can enhance your modern data stack with Cube and CrateDB as a starting point for building data-intensive applications.

## Cube: API-First Business Intelligence

[Cube’s](https://cube.dev/) headless business intelligence platform makes lakehouse data consistent, performant, and accessible to every downstream application. Cube connects to dozens of warehouses, databases, or query engines, and allows you to quickly build data applications or analyze your data in BI tools. 

Cube’s advanced caching and [pre-aggregation](https://cube.dev/docs/schema/reference/pre-aggregations) layer can condense frequently-used queries to optimize response times. While the data modeling provides a central location for your metrics definitions.

Cube provides this data to applications with GraphQL, REST, and SQL APIs so that every tool—and every data consumer—can work with the same data.

![alt_text](https://ucarecdn.com/00ad48b5-c916-417d-8d5a-53f0a76767df/)

## CrateDB: Distributed database for real-time analytics

[CrateDB](https://crate.io/docs/crate/reference/en/5.0/) is an open-source distributed database management system that makes it simple to store and analyze massive amounts of data in real time. It is designed to handle various data types, including time-series, full-text search, geospatial, and structured and semi-structured data. In addition, the CrateDB engine is optimized for fast queries and aggregations thanks to in-memory columnar indexes and data compression. 

Furthermore, CrateDB offers NoSQL flexibility with an SQL interface. The SQL compatibility makes the integration of CrateDB with other data engineering tools easy and efficient.

In this article, we will demonstrate how to set up a CrateDB cluster and how to use it as a data source in Cube Cloud for further data analysis.

## Deploy a cluster on CrateDB Cloud

To deploy a new cluster on CrateDB Cloud, please sign up for a [CrateDB Cloud account](https://console.cratedb.cloud/). When creating a new organization, you are entitled to a $200 free credit to spend on cluster deployment, scaling, and other operations as you see fit.

After the signup process is completed, you will arrive at the CrateDB Cloud Console. 

![alt_text](https://ucarecdn.com/b5feea0b-947a-475a-b3c3-3b3794c82ae6/)

At this point click on the _Deploy new cluster_ button. This will open the cluster deployment screen that consists of three steps: region selection, cluster configuration, and account settings. 

As a first step, we will select a region where the cluster will be deployed. 

![alt_text](https://ucarecdn.com/0fcf81b6-bb5a-4773-b96f-7867777c862d/)

As a second step, we will choose [a configuration plan](https://crate.io/docs/cloud/tutorials/en/latest/cluster-deployment/stripe.html). For each plan, you can see the CPU, RAM, and (minimum) storage values per node. 

![alt_text](https://ucarecdn.com/d6c50105-a5ce-45b2-8d65-0ce1f8fea9a7/)

In our case, we will choose the CR0 plan which refers to a shared resource cluster. This type of cluster shares compute and storage resources with other clusters in this category and that allows better utilization of resources. 

Finally, at the bottom of the screen, you can configure your account settings. In our example we choose the following credentials:

* Project name: `crate-cube`
* Cluster name: `sample-cluster`
* Username: `admin`
* Password: _auto-generated_

![alt_text](https://ucarecdn.com/813d734e-45fe-46e3-95a6-a9e1783f69e6/)

The next step requires the setup of your[ billing information](https://crate.io/docs/cloud/tutorials/en/latest/cluster-deployment/stripe.html). You can use existing AWS or Azure subscriptions or credit card information. Once this is completed, you can see the overview of your cluster with instructions on how to connect to the cluster, how to import sample data, and usage metrics.

![alt_text](https://ucarecdn.com/630b8e8e-57de-4ae7-a7f7-bfbaeb13f568/)

To start creating tables and running queries, click on _Open Admin UI_. This opens a web administration user interface (or Admin UI) for your running cluster.

![alt_text](https://ucarecdn.com/f67cf867-631b-4831-9a4e-6d9d0dc5b295/)

Now, let’s import some data. To do so, go back to the cluster overview page and click the _Learn how to import data_ link. This will open a list of statements you need to execute to load 

NYC taxi data:

![alt_text](https://ucarecdn.com/0ce12208-9cec-475f-adce-9230426bc67f/)

Great! Now that you have the first table in your CrateDB cluster, let’s see how to add this dataset to your Cube Cloud instance.

## Connect CrateDB to Cube

To start with Cube, create a new Cube instance in [Cube Cloud](https://cube.dev/cloud). You can sign up for a Cube Cloud account with a free trier.

Once you create a new Cube instance, you will need to create the new deployment. For our _cube-crate_ deployment we choose the cloud provider and the region:

![alt_text](https://ucarecdn.com/17870922-8a59-4846-b55e-1cecc6362ffe/)

On the next page, we click on the _Create_ tab to set up our CrateDB connection:

![alt_text](https://ucarecdn.com/d0a00c79-08cc-4406-b163-cc255b7c2b07/)

To connect CrateDB with Cube we need to specify the database connection of our CrateDB cluster:

* Hostname: `sample-cluster.aks1.westeurope.azure.cratedb.net`
* Port: `5432` (default)
* Database: `crate` 
* Username: `admin`
* Password: _the password you created during cluster creation_
* SSL checkbox: _CrateDB Cloud does not allow non-encrypted connections_

![alt_text](https://ucarecdn.com/72ddb77d-17b3-45b0-abcf-408816333135/)

Click on _Apply_ and in the next view select all tables for which you want Cube to generate the initial schema. For instance, we can generate the schema for our *nyc_taxi* table.

![alt_text](https://ucarecdn.com/938e0131-83fc-4c3a-8330-421a28e44e1c/)

Click on _Generate_ button, wait for a couple of seconds and you will see the newly deployed instance that is connected to our CrateDB database.

From this point, you can start exploring generated schema and defining new data models for your applications! 

## Cube as a headless BI layer

Let's have a look at the autogenerated schema. Switch to the Schema tab and click the `NycTaxi.js` file.

![alt_text](https://ucarecdn.com/240f7630-e7c5-4da2-8901-fcbc507e969e/)

From here it's easy to add more complex measures and dimensions to create your own custom metrics layer.

Go ahead and add a custom measure.

```javascript
  totalAmountSum: {
    sql: `total_amount`,
    type: `sum`
  }
```

![alt_text](https://ucarecdn.com/1ba7ba29-bbb5-40f1-8db4-1756ed395fb7/)

After you save and commit this change, open up the playground, and let’s run a sample query.

![alt_text](https://ucarecdn.com/88e8676a-a416-4754-b535-55d0de9d9da2/)

You can connect Cube to your front-end app of choice. Choose from using React, Vue, Angular, Vanilla JavaScript for the library integration, with the option to use BI tools with Cube’s SQL API.

![alt_text](https://ucarecdn.com/08f20be2-a069-4e9a-903f-7bbe2a105097/)

This means you can continue using all the BI tools you’re already using and are connected to your CrateDB instances. Cube only acts as the middleware layer between your BI tools and CrateDB. You still use SQL to access the data for your dashboards! Pretty neat.

![alt_text](https://ucarecdn.com/4ebe6af9-0312-4924-82f8-27faa55d5448/)

You now know how to configure Cube as a metrics layer!

## Next steps

By using Cube with CrateDB in your modern data stack you get an excellent starting point for further building out your data-intensive applications.

To learn more about Cube please check our [documentation](https://cube.dev/docs/backend/sql) and [supported tools](https://cube.dev/docs/config/downstream), and give it a try by registering a free [Cube Cloud account](https://cube.dev/) today. 

If you want to try out CrateDB in a few clicks and enjoy all of the CrateDB features, sign up for the [CrateDB Cloud](https://console.cratedb.cloud/?utm_campaign=2022-Q2-WS-Free-Trial&utm_source=website&utm_medium=free-trial-overhaul&hsCtaTracking=a7e2a487-cfb9-4a50-8e75-3029b9e176fb%7C7863166c-05e4-4334-9dd5-58dfdd6e78c1) trial. 

For updates, features, and other questions you might have join our [Cube](https://slack.cube.dev/) and [CrateDB](https://community.crate.io/) communities. 


Marija Selakovic

Transactional Databases (OLTP)

In this article, you’ll learn how to configure your own modern data stack to build data-intensive apps.

Enabling data analysis at scale with Cube and CrateDB

Shadid Haque

Supabase

You'll get a performant dynamic dashboard in less than 15 minutes

Supabase tutorial: Building fast data visualization apps

[KazanExpress](https://kazanexpress.ru/) is a marketplace in Russia with free one-day delivery, regardless of the order value and region. We provide over three million products in over 20 product categories including clothing, electronics, and groceries.

Our marketplace consists of 4 offerings:

1. **B2C** — everything related to the happiness of shoppers and the process of buying goods in the marketplace. We have more than a million visitors daily, and we are loved by more than four million shoppers.
2. **B2B** — focuses on the experience of the seller and the process of selling goods in the marketplace. In recent years, more than 10,000 sellers became our partners and we were able to greatly improve their business.
3. **WMS** — a warehouse management system that is responsible for a key part of our business — efficient packaging and fast delivery that allows us to deliver orders to more than 115 cities in Russia.
4. **ERP** — a single system for automating and managing internal business processes of the marketplace. It helps build simple and effective links between different departments.

We were looking for an analytics solution—both for us and our sellers using the B2B product of KazanExpress—that can provide insight into our performance against our business goals. For KazanExpress, this includes how quickly new sellers can onboard to our marketplace, the effectiveness of existing sellers, our revenue stream, and the number of purchases on KazanExpress.  

Our sellers needed insight on things like types of customer search queries, demands for product categories, product revenue forecast to help plan product availability in the warehouse. In addition, they also needed to understand what impact activities such as price changes, promotions, adding new SKUs, and even changing product photos had on their revenue.

## Why we chose Cube at KazanExpress

Our analytics tool needed to be capable of doing aggregations from several types of databases (e.g. ClickHouse, Postgres, etc.) and easy to integrate with the KazanExpress infrastructure. We looked at three options and you'll see the pros and cons for each below:

<table>
  <thead>
    <tr>
      <th width="15%"></th>
      <th width="42.5%">Pros</th>
      <th width="42.5%">Cons</th>
    </tr>
  </thead>
  <tbody>
    <tr>
      <td><b>Yandex DataLens</b></td>
      <td valign="top">
        <ul>
          <li>Connectors to Postgres and ClickHouse</li>
          <li>Predefined set of chart types, customizable even by non-technical people</li>
          <li>Support for materialization and caching</li>
          <li>Supported by Yandex</li>
        </ul>
      </td>
      <td valign="top">
        <ul>
          <li>No pre-aggregations mechanism</li>
          <li>Large materialized views are not possible <b>(a major roadblock)</b></li>
          <li>Row-level security (RLS) doesn't support integration with 3rd party applications incl. our security layer <b>(a major roadblock)</b></li>
        </ul>
      </td>
    </tr>
    <tr>
      <td><b>In-house solution with ETL and ClickHouse data marts</b></td>
      <td valign="top">
        <ul>
          <li>We have full control of the architecture and security layer</li>
          <li>Easy to integrate with our existing infrastructure</li>
        </ul>
      </td>
      <td valign="top">
        <ul>
          <li>
            Huge amount of developer resources required to implement
            ClickHouse data marts, interaction with security middleware, etc.
          </li>
          <li>frontend developers required to build charts</li>
        </ul>
      </td>
    </tr>
    <tr>
      <td><b>Cube</b></td>
      <td valign="top">
        <ul>
          <li>Connectors to Postgres and ClickHouse</li>
          <li>Ability to build OLAP cubes on top of the raw data layer without data volume limitations</li>
          <li>Simple API and integrations with popular frontend charting libraries</li>
          <li>Support for <a href="https://cube.dev/docs/schema/reference/pre-aggregations">pre-aggregations</a></li>
          <li>Support for <a href="https://cube.dev/docs/multitenancy-setup">multitenancy</a></li>
          <li>Support for <a href="https://cube.dev/docs/security#custom-authentication">custom authentication</a></li>
          <li>Row-level Security (RLS) and <a href="https://cube.dev/docs/security#generating-json-web-tokens-jwt">JSON Web Tokens (JWT)</a></li>
        </ul>
      </td>
      <td valign="top">
        <ul>
          <li>frontend developers required to build charts</li>
          <li>
            Lack of infrastructure tools in Cube (e.g.
            instrumentation/monitoring data, official Helm chart/Kubernetes
            deployment guide)
          </li>
        </ul>
      </td>
    </tr>
  </tbody>
</table>

Obviously, Cube had a lot of pros and we decided to pursue the Cube route in July 2021. Below you will see one of our seller’s analytics dashboards that are powered by Cube.

![Seller analytics dashboard](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/9ed4d541-db24-4fde-9950-1f35959ffbfd.png)

## Implementing Cube at KazanExpress and our workflow

Our setup follows the recommended Cube deployment architecture that consists of Cube API, Cube Refresh Worker, external Redis for caching, and Cube Store cluster for working with pre-aggregations. Currently, Cube is connected to three Postgres databases and ClickHouse and you can see our architecture diagram below.

![Architecture Diagram](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/9c78020d-6f04-4857-ab22-49bbc0b88914.png)

In the Cube API, we implemented integration with our internal authorization provider to grant access to data in Cube using the same token used throughout our application.

Currently, we have 3 staging deployments in our K8s cluster. We templated the Cube platform using the Helm chart to simplify the deployment process and make it reproducible/extendable/observable. Here are our staging deployments:

- **Feature.** A feature deployment like in Git flow. This is the playground where we can make any experiments with Cube or develop new features. And at the same time, we can test visualization on the frontend.
- **Development.** A place where all approved features are located and we might build releases with required load-testing, regression testing, etc.
- **Production.** A working copy of the platform that we are confident with and available to our sellers.

Each deployment has a full copy of the Cube platform that consists of Cube API, Cube Refresh Worker, Cube Store with its own bucket on S3, and Redis for caching. This workflow gives us the capability to test our hypotheses easily and instantly deliver features to our customers.

We had three people (a frontend developer, a backend developer, and a designer) that were involved in the implementation of Cube at KazanExpress that took about 3 1/2 weeks. We also implemented Cube in three phases:

- **Phase 1.** Deployment of the Cube API into our Kubernetes cluster.
- **Phase 2.** Setting up the Cube API with a refresh worker and pre-aggregations.
- **Phase 3.** Setting up Cube Store.

## Looking ahead

We look forward to continuing to expand our analytics offering with Cube to our sellers. This includes adding more Postgres data sources and providing anonymous statistics on shoppers' search queries, buyers' demographics/interests, product category information on impressions/views/sellers, etc.

## Interested in joining KazanExpress' success with Cube?

Explore Cube [examples & tutorials](https://cube.dev/docs/examples) and [get started](https://cube.dev/docs/getting-started) today. To jumpstart your efforts, please join us on [Discourse](https://forum.cube.dev/) & [Slack](https://slack.cube.dev/), follow us on [Twitter](https://twitter.com/thecubejs), and get engaged with the growing Cube community.

Oleg Taizov

User Stories

How KazanExpress is using Cube for analytics in their marketplace offerings

You're bound to have heard the term GraphQL. Unless you live under a rock. I doubt that though. GraphQL is a query language for APIs and a runtime for fulfilling those queries with your existing data.

This tutorial will show you a step-by-step guide on how to use a GraphQL API to build Postgres metrics dashboards.

Here's what the end product will look like. You can also check out the [live preview](https://graphql-postgres-metrics-dashboard.netlify.app/).

![frame_chrome_mac_light - 2021-12-16T143822.307.png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/8a8507fb-4c19-4069-a178-9bd1036f7e06.png)

GraphQL provides a complete and understandable description of the data in your API, gives clients the power to ask for exactly what they need and nothing more. GraphQL makes it easier to evolve APIs over time and enables powerful developer tools.

The incredible growth in GraphQL's popularity has been noticed by the amazing Cube community. Our community longed for a GraphQL integration, and we listened.

### How the GraphQL API was added to Cube

I'd like to give a huge shout-out to [Luc Vauvillier](https://twitter.com/lvauvillier) who contributed initial support for the GraphQL API and laid out the principal design. Luc is the co-founder at [Gadsme](https://www.gadsme.com/). Check [this out to read more](https://cube.dev/blog/gadsme-runs-on-cube/) about how Gadsme builds awesome stuff with Cube[.](https://cube.dev/blog/gadsme-runs-on-cube/)

We at Cube were thrilled to jump on the problem at hand and build out a GraphQL API. As of the `0.29` release of Cube, you can use the GraphQL API!

### Set up Cube with GraphQL and Postgres

To configure Cube, I first needed to connect a database. I used a demo Postgres database with e-commerce data. The process of connecting a database is identical whether you're using a self-hosted instance of Cube, or Cube Cloud.

I selected PostgreSQL from the list of database options. Cube [supports dozens of databases](https://cube.dev/docs/config/databases), you can select from any mainstream database or data warehouse on the market.

![frame_chrome_mac_light_(37).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/9ca37d9b-cdc4-4ebd-a41b-31b1ded693e5.png)

Next, I had to add values to connect to my sample PostgreSQL e-commerce database. If you're using a self-hosted Cube instance, you need to [configure a few environment variables](https://cube.dev/docs/config/databases/postgres).

In Cube Cloud, it looks like this.

![frame_chrome_mac_light_(50).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/f44924c6-b117-4d50-9d78-11625238b4b9.png)

Once the connection is established, I have to generate a data schema. Selecting the `public` table and clicking generate will build a Cube data schema for me to work with.

![frame_chrome_mac_light_(51).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/ec5c7697-c698-4d8c-938e-8fdeb2193b7f.png)

Now I have access to my Cube cluster. In Cube Cloud you should always enable cluster mode for improved performance if you want to run reliably in production.

This will make sure your resources will autoscale based on the load. There's no additional cost for autoscaling, and the [pricing model for Cube Cloud](https://cube.dev/pricing) is based on throughput.

![frame_chrome_mac_light_(22).png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/00d52f17-d1c7-4768-b692-6fbb78954d33.png)

Next up, I ran a set of queries in the Cube Playground to determine what data I need to make the metrics dashboard.

First of all, I want to run a sample query to list orders by status grouped by day.

![frame_chrome_mac_light - 2021-12-16T143829.494.png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/e23359d8-70a7-4a60-b842-4632c8a0680c.png)

Let's open up the Cube GraphiQL interface and run this same query with GraphQL. You can [read more about the GraphQL API in our docs](https://cube.dev/docs/backend/graphql/).

Here's what the GraphQL query looks like for the example above.

![frame_chrome_mac_light - 2021-12-16T143831.756.png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/efd2ca84-0c97-4118-8743-96b6066a3830.png)

```graphql
query CubeQuery {
  cube(limit: 10) {
    orders(orderBy: { count: desc }) {
      count
      status
      createdAt {
        day
      }
    }
  }
}
```

Running this query in GraphiQL will return the values just like in the playground. Now we're done with the setup, let's move on to some more complex queries.

### Running Analytical GraphQL Queries

Lets add a `where` clause with a time dimension to the GraphQL query.

```graphql
query CubeQuery {
  cube(
    limit: 10
    where: { orders: { createdAt: { inDateRange: "This year" } } }
  ) {
    orders(orderBy: { count: desc }) {
      count
      status
      createdAt {
        day
      }
    }
  }
}
```

Running this query in GraphiQL will return this result set.

![frame_chrome_mac_light - 2021-12-16T143833.925.png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/66776d34-964a-4956-8148-77ba785fec95.png)

I also want to run another query with only completed order statuses.

```graphql
query CubeQuery {
  cube(
    limit: 10
    where: {
      orders: {
        status: { equals: "completed" }
        createdAt: { inDateRange: "This year" }
      }
    }
  ) {
    orders(orderBy: { count: desc }) {
      count
      status
      createdAt {
        day
      }
    }
  }
}
```

![frame_chrome_mac_light - 2021-12-16T143836.210.png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/2965b57a-2d0a-4d28-95a9-c8d03e5e9afc.png)

These queries will be perfect for building my metrics dashboard. Next, we need to build an app to display the metrics.

### Visualize Postgres Data with GraphQL

I'll start by building a React app and using Chart.js to display the metrics.

```bash
npx create-react-app dashboard-app
cd dashboard-app
yarn add @apollo/client graphql chart.js react-chartjs-2 apollo-link-http
npm start
```

This will give me a blank React app. I then add the Apollo client and all the required dependencies for building the charts.

Next up, create the file **`src/ApolloClient/client.js`** and insert the following code.

```jsx
import { ApolloClient, ApolloLink, InMemoryCache } from "@apollo/client";
import { HttpLink } from "apollo-link-http";

const uri = 'https://tan-rooster.aws-eu-central-1.cubecloudapp.dev/cubejs-api/graphql'
const httpLink = new HttpLink({ uri });

let appJWTToken = 'eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJyb2xlIjoiYWRtaW4ifQ.UHnffynBjuE3dcwEUyqldVbN-5QzMT-oiyXqkRbWJOI'
const authMiddleware = new ApolloLink((operation, forward)=> {
  if (appJWTToken) {
    operation.setContext({
      headers: {
        Authorization: `${appJWTToken}`
      }
    });
  } 
  return forward(operation);
});

export const clientWithCubeCloud = new ApolloClient({
  cache: new InMemoryCache(),
  link: authMiddleware.concat(httpLink),
});
```

Make sure to edit your `uri` and `appJWTToken` to use your values from Cube Cloud. You can get those values from the Overview tab by clicking the `How to connect` button.

![frame_chrome_mac_light - 2021-12-16T143824.985.png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/b499ee43-e261-4086-a8d4-0e7dfb4ed812.png)

Now you have to make the Apollo client available to the rest of your app. Update your **`src/index.js`** to look as follows.

```jsx
import React from 'react';
import ReactDOM from 'react-dom';
import './index.css';
import App from './App';
import reportWebVitals from './reportWebVitals';
import { client } from './ApolloClient/client';
import { ApolloProvider } from '@apollo/client';

ReactDOM.render(
  <ApolloProvider client={clientWithCubeCloud}>
    <App />
  </ApolloProvider>,
  document.getElementById('root')
);

// If you want to start measuring performance in your app, pass a function
// to log results (for example: reportWebVitals(console.log))
// or send to an analytics endpoint. Learn more: https://bit.ly/CRA-vitals
reportWebVitals();
```

Nice! Now you can move on to create the chart component, and then call the GraphQL query from the chart itself.

Create a folder called `src/Charts` and create a file called `src/Charts/BarChart.js`. Paste this code into it.

```jsx
import React from "react";
import { gql, useQuery } from '@apollo/client';
import { getRandomColor, formatDate } from './Helpers'
import { Bar } from 'react-chartjs-2';
import { Chart as ChartJS, BarElement, Title, CategoryScale, LinearScale, Tooltip, Legend } from 'chart.js';
ChartJS.register(BarElement, Title, CategoryScale, LinearScale, Tooltip, Legend);

const COMPLETEDORDERS = gql`
  query CubeQuery {
    cube(
      limit: 10
      where: {
        orders: {
          status: { equals: "completed" }
          createdAt: { inDateRange: "This year" }
        }
      }
    ) {
      orders(orderBy: { count: desc }) {
        count
        status
        createdAt {
          day
        }
      }
    }
  }
`;

const GenerateChart = () => {
  const { data, loading, error } = useQuery(COMPLETEDORDERS);

  if (loading) {
    return <div>loading</div>;
  }

  if (error) {
    return <div>{error}</div>;
  }

  if (!data) {
    return null;
  }

  const chartData = {
    labels: ['Daily Completed Orders in 2021'],
    datasets: data.cube
      .map(o => o.orders)
      .map(o => {
        return {
          data: [o.count],
          label: formatDate(new Date(o.createdAt.day)),
          backgroundColor: [getRandomColor()],
        };
      })
  }

  return (
    <Bar
      data={chartData}
    />
  );
}

const BarChart = () => {
  return (
    <div style={{ margin: "10px", paddingTop: "65px" }}>
      <h2 style={{ margin: "10px", textAlign: "center" }}>
        Bar Chart
      </h2>
      <div style={{ margin: "10px 100px", padding: "10px 100px" }}>
        <GenerateChart />
      </div>
    </div>
  );
};

export { BarChart };
```

You'll also need to create a file called `src/Charts/Helpers.js`. Paste this code.

```jsx
function getRandomColor() {
  function hexToRgbA(hex){
    let c;
    if(/^#([A-Fa-f0-9]{3}){1,2}$/.test(hex)){
        c= hex.substring(1).split('');
        if(c.length === 3){
            c= [c[0], c[0], c[1], c[1], c[2], c[2]];
        }
        c= '0x'+c.join('');
        return 'rgba('+[(c>>16)&255, (c>>8)&255, c&255].join(',')+',0.5)';
    }
    throw new Error('Bad Hex');
  }

  const letters = '0123456789ABCDEF';
  let color = '#';
  for (let i = 0; i < 6; i++) {
    color += letters[Math.floor(Math.random() * 16)];
  }

  return hexToRgbA(color);
}

function formatDate(date) {
  let d = new Date(date),
      month = '' + (d.getMonth() + 1),
      day = '' + d.getDate(),
      year = d.getFullYear();

  if (month.length < 2) 
      month = '0' + month;
  if (day.length < 2) 
      day = '0' + day;

  return [day, month, year].join('-');
}

export {
  getRandomColor,
  formatDate
};
```

Once you're done with that, you need to create a file called `src/Charts/index.js` to export the chart.

```jsx
export { BarChart } from './BarChart';
```

Lastly, edit the `App.js` and import the chart.

```jsx
import './App.css';
import {
  BarChart,
} from './Charts';

function App() {
  return (
    <div className="App">
      <BarChart />
    </div>
  );
}

export default App;
```

Go back to the browser where the React app is running. You should see this fancy chart pop up!

![frame_chrome_mac_light - 2021-12-16T143822.307.png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/8a8507fb-4c19-4069-a178-9bd1036f7e06.png)

As of today, this means you can use Cube's GraphQL API for building any type of metrics dashboard. Alongside the REST API and SQL API, you have an entire toolset at your disposal to choose from.

But I'm not done here yet. Let's add some role-based access control, also called RBAC.

### Add Role-Based Access Control with JWT Tokens

You get [multi-tenancy and RBAC in Cube](https://cube.dev/docs/security/context#top) out of the box. Let's add a security context in the Apollo client to enable RBAC.

Just like with the REST API you can add security and RBAC with JWT tokens. First open up the Env vars tab in your Cube Cloud deployment settings.

![frame_chrome_mac_light - 2021-12-16T143827.204.png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/c96be748-74ef-453d-ba38-f0019cd23345.png)

Find the `CUBEJS_API_SECRET` environment variable and copy it. This is the API secret that secures your API. Take the value and create a JWT token with [jwt.io](http://jwt.io) , make sure to add `{ "role": "admin" }` to the payload.

![frame_chrome_mac_light - 2021-12-16T143840.773.png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/2b4bf733-fb93-4576-af03-8a28e400337e.png)

You can now copy the JWT token and open up the `src/ApolloClient/client.js` file. Add the token value to the `appJWTToken` variable. Here's what it should look like.

```jsx
import { ApolloClient, ApolloLink, InMemoryCache } from "@apollo/client";
import { HttpLink } from "apollo-link-http";
const uri = 'http://localhost:4000/cubejs-api/graphql'

const httpLink = new HttpLink({ uri });

let appJWTToken = 'eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJyb2xlIjoiYWRtaW4ifQ.FUewE3jySlmMD3DnOeDaMPBqTqirLQeuRG_--O5oPNw';
const authMiddleware = new ApolloLink((operation, forward)=> {
  if (appJWTToken) {
    operation.setContext({
      headers: {
        Authorization: `${appJWTToken}`
      }
    });
  } 
  return forward(operation);
});

export const clientWithJwt = new ApolloClient({
  cache: new InMemoryCache(),
  link: authMiddleware.concat(httpLink),
});
```

Next, add the security context and RBAC to the `cube.js` file.

```js
// Cube.js configuration options: https://cube.dev/docs/config
module.exports = {
  queryRewrite: (query, { securityContext }) => {
    if (!securityContext.role) {
      throw new Error('No role found in Security Context!');
    }

    if (!securityContext.role === 'admin') {
      throw new Error('You\'re not the Admin!');
    }

    return query;
  },
};
```

Here's what it looks like in Cube Cloud.

![frame_chrome_mac_light - 2021-12-16T143838.484.png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/2d108149-e4bf-436f-bf61-ca0396daaa44.png)

You can add any particular logic you might want like RBAC, multi-tenancy, row-level access, and more.

Now, jump back to the dashboard-app. Restart it, and voila, you've added RBAC. The end outcome is this lovely chart showing orders per day for the whole of 2021 that you can only access with the `admin` role. If you remove the `appJWTToken`, your dashboard-app won't be able to fetch data anymore.

If you want to check out the source code in one place, here's the [GitHub repo](https://github.com/cube-js/cube.js/tree/master/examples/graphql-api-metrics-dashboard). And, you can also have a look at the [live preview here](https://graphql-postgres-metrics-dashboard.netlify.app/).

![frame_chrome_mac_light - 2021-12-16T143822.307.png](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/8a8507fb-4c19-4069-a178-9bd1036f7e06.png)

### Conclusion

In this tutorial, I wanted to show a step-by-step guide on how to use Cube's GraphQL API to build a metrics dashboard with data from a Postgres database.

With Cube Cloud you get a metrics layer that integrates with every major data visualization library including GraphQL compatible tools like [Chart.js](https://www.chartjs.org/) and [react-chartjs-2](https://react-chartjs-2.netlify.app/). On top of all that, it also comes with multi-tenancy support out-of-the-box. Among the different multi-tenancy options, you can enable tenant-based row-level security, role-based access, using multiple database instances, multiple schemas, and more.

If you want to learn more about GraphQL with [Cube](https://cube.dev/) check out [our announcement](https://cube.dev/blog/introducing-cube-graphql) that explains how we added the GraphQL API to Cube.

You can [register for Cube Cloud](https://cube.dev/cloud) right away if you want to have a look!

I'd love to hear your feedback about using GraphQL with Cube Cloud in the Cube Community Slack. [Click here to join](https://slack.cube.dev/)!

Until next time, stay curious, and have fun coding. Also, feel free to [leave Cube a ⭐ on GitHub](https://github.com/cube-js/cube.js) if you liked this article. ✌️

Adnan Rahic

PostgreSQL

GraphQL Postgres Metrics Dashboard with Cube

[ShopBack](https://corporate.shopback.com/) is the largest shopping rewards and discovery platform in Asia-Pacific, with a presence in Australia, Indonesia, South Korea, the Philippines, Singapore, Malaysia, Taiwan, Thailand, and Vietnam.

From a small team of six in 2014, we now house over six hundred employees across the region, having scaled to nine countries to provide a more rewarding shopping experience for over 28 million users. Our platform enables users to make better purchase decisions while delivering performance-based marketing with high and measurable return on investment to merchants.

ShopBack continues to innovate, evolve, and create value for our users and merchant partners. In 2020 alone, we powered close to US$3 billion in sales for over 5,000 merchant partners across the Asia-Pacific. To date, we have also awarded over US$200 million of cashback to our users.

![ShopBack info graphics](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/b8895d67-8413-4ee1-89c9-765f76e81279.png)

## Why ShopBack started using Cube

For all transactions on our ShopBack sites, we need to analyze all transactions, including purchase value, sales volume, etc. In January 2020, we started working on a new project that required a lot of dashboard reporting of aggregated data for both internal and external (e.g. merchant partners) application users. One of the options considered was building an in-house application and storing the data in a graph database. However, in consultation with [Yann AïtBachir](https://www.linkedin.com/in/yaitbachir/) (Head of Data at ShopBack), our engineers decided to go with Cube as it was a better fit for our use case as our data was very much relational and will be pre-aggregated into OLAP cubes for analytics.

![Shopback quote](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/46935f81-2009-43d4-aede-fe8fca409a75.png)

Since we first started using Cube, there has been an ongoing effort to add new Cube schemas, measures, etc., as our data continued to grow while improving the performance of our queries at the same time. To address performance concerns, we implemented [pre-aggregations](https://cube.dev/docs/caching/pre-aggregations/getting-started) in early 2021. Before implementing pre-aggregations, our [p95](https://findanyanswer.com/what-does-p95-mean-in-statistics) query loading time could take as long as ~50 seconds, which is an insanely bad user experience. Once we started implementing pre-aggregations, we were able to go as low as ~20 seconds p95. After we started further optimizing our pre-aggregations with indexes, modifying our approach to pre-aggregations for unique queries, and a couple of optimizations outside of Cube, we were able to get to below 5 seconds p95.

We still have a few more optimizations planned to reach our target of &lt;1 second p95, but this goes to show that proper application of pre-aggregations can drastically improve the performance of your Cube queries. The performance improvement with pre-aggregations is further demonstrated in the two figures below:

![Figure 1: Chart loading improvements](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/92903a28-4597-46bf-a4f3-60100287519c.png)

![Figure 2: Queries loading time](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/2a63e83f-d2b4-4e3f-8b9e-4020c2863e59.png)

## Implementing Cube at ShopBack

Since we started working with Cube in 2020, we have had nine people working on this project full-time with six engineers, 2 product managers, and 1 QA engineer. In addition, we had several other engineers who helped over the past 1½ year as we implemented Cube at ShopBack.

![Figure 3: Architecture Diagram Screenshot](https://cubedev-blog-images.s3.us-east-2.amazonaws.com/d27316b5-d9e5-4ab6-b796-2999d1b4ef20.png)

What you see on the figure above is an architectural diagram of our Cube implementation.

* **Main Application.** The Node.js application in AWS uses an integrated Cube API instance to serve requests to our React frontend application. It fetches 95% of our data from our pre-aggregations, and the remaining 5% have consistently small datasets, so we have not seen the need to pre-aggregate it. Cube responses are also cached within Redis whenever there’s a successful first fetch. Given that our application allows users to use multiple different filters, this significantly reduces the load times for filters that have already been used for the day.
* **Data Pipeline.** Our application acts as a downstream service that receives/pulls in data from our other services. All of this data goes through our ETL process (built in-house), which then stores the processed data into our Postgres database.
* **Caching.** Our architecture uses pre-aggregations that are being stored in the same Postgres database and a non-clustered instance of Redis for storing fetched data and tracking the latest refreshed pre-aggregations for a multi-tenant application setup. The refreshing of our pre-aggregations is done by a separate instance of our application that acts as the [refresh worker](https://cube.dev/docs/deployment/production-checklist#set-up-refresh-worker); this refresh-worker tracks the refresh keys stored in our database (as seen below) to keep track of which pre-aggregations need to be refreshed. Making the [refresh keys](https://cube.dev/docs/caching/using-pre-aggregations#refresh-strategy) manageable like this allows us to fully control whenever we want to trigger a refresh in the middle of the day should we want to.

While working with Cube, here are some of the challenges/learnings that we came across:

* **Making sure we are hitting pre-aggregations.** With the amount of data that we have, we had to make sure that we pre-aggregate our different use-cases correctly. For example, you cannot make a pre-aggregation with a granularity set as a month if there are queries with date ranges that can start/end in the middle of the month. We learned that if we didn’t pay attention to these details, we may be surprised by not “hitting” our pre-aggregations database.
* **Performance.** While it was important to make sure that pre-aggregations are being hit, it was also important to remember that these pre-aggregations are still database tables that are queried on (and in our case, they’re stored in the same source database). In other words, if you end up with a pre-aggregation with a lot of data and a lot of cardinalities, then we also need to index those pre-aggregations. We learned that [indexing](https://cube.dev/docs/schema/reference/pre-aggregations#indexes) our pre-aggregation tables has dramatically improved the performance of our queries. Below you can see a sample of our pre-aggregations that can easily have millions of rows in the pre-aggregation table. As mentioned above, adding indexes (in this case to columns `status` and `outletId`) helped contribute significantly to speeding up our queries.

```js
preAggregations: {
  overview: {
    type: 'rollup',
    measures: [ count, amount ],
    dimensions: [ status, outletId ],
    timeDimension: occuredAt,
    granularity: `day`,
    partitionGranularity: `month`, 
    segments: [ validRecords ],
    refreshKey: {
      sql: `SELECT refresh_key
            FROM preaggregation_meta
            WHERE preaggregation = 'Records'`,
    },
    indexes: {
      overviewIndex: {
        columns: [ status, outletId ],
      }, 
    }, 
  }, 
}, 
```

* **Going beyond raw SQL queries.** In general, it’s fairly easy to create raw SQL for your queries with their own joins/nested statements. However, when we started using pre-aggregations, we realized that the original SQL statements weren’t flexible enough, and you couldn’t simply “port SQL to Cube.” Now, rather than starting with SQL, we consider how we will implement Cube from the outset.
* **Extracting refresh-related information.** We are currently trying to get more logging data from refreshes for the ShopBack team and our clients (e.g., "chart data last refreshed at *hh:mm:ss*"). We extract refresh-related information by looking for select keywords in refresh logs, but if there are easier ways to do this, we’d love to learn more!

The next major Cube project for us is implementing [Cube Store](https://cube.dev/docs/caching/using-pre-aggregations#pre-aggregations-storage) to achieve lower latency and higher concurrency. We are looking forward to implementing Cube Store after our region’s shopping campaigns/festivals and once our team has enough bandwidth (you can check out what else our tech team is working on [here](https://medium.com/shopback-tech-blog)). From our investigation into Cube Store, we know that we will need to explore some workarounds:
  
* For security reasons, we do not set our AWS keys in our instance environment variables. As such, we do not store values in `CUBESTORE_AWS_ACCESS_KEY_ID` and `CUBESTORE_AWS_SECRET_ACCESS_KEY`, and these are causing unexpected behaviors with our network calls.
* With the Cube Dev team, we’re investigating a low-level error message regarding the Parquet library when we have a lot of data (e.g., 4M rows).

## Working with the Cube community

Another key benefit of working with Cube is the community, as we believe it is a great example of an active community. I worked with other open source communities before joining ShopBack, and I typically saw the number of questions/issues pile up to a pretty high number. On the other hand, with the Cube community, whenever we have questions, they typically get answered within 24 hours of posting them.
  
Also, the community members are quite collaborative in terms of helping each other out. I have had a number of direct messages on [Slack](https://slack.cube.dev) with other community members to discuss how they did their implementations/deployments. In return, I also try to reply to threads that I feel I can help with.

## Interested in joining ShopBack's success with Cube?

Explore Cube [examples & tutorials](https://cube.dev/docs/examples) and [get started](https://cube.dev/docs/getting-started) today. To jumpstart your efforts, please join us on [Discourse](https://forum.cube.dev/) & [Slack](https://slack.cube.dev/), follow us on [Twitter](https://twitter.com/thecubejs), and get engaged with the growing Cube community.

John Kevin Go

How ShopBack is using Cube to keep pace with their business & data growth

[Jobber](https://getjobber.com/), the leading provider of business management software, helps small home service businesses stay organized, connect with customers, grow revenue, and better compete against large corporations. The company's technology supports more than 50 industries, including HVAC, plumbing, lawn care, cleaning, and more. Since launching in 2011, businesses using Jobber have serviced over 15 million households in more than 47 countries. 

![zeisler-quote.png](https://media.graphcms.com/Jiqz2GcDRliDe6QP8E7T)

One of the important tools for Jobber's customers is dashboards that can provide a snapshot of what's happening in their businesses to schedule their day, optimize routing, keep track of invoices, accept payments, and more, even when people are not in traditional brick and mortar offices. Today, more than 100,000 service professionals use Jobber's platform and rely on it to stay on top of—and grow—their businesses.

![Untitled.png](https://media.graphcms.com/NWbbXWdWRym4mvp56NA3)

In the past, the Jobber team was retrieving information by doing direct queries against a production database from their Ruby on Rails application through [Active Record](https://guides.rubyonrails.org/active_record_basics.html) ORM. However, as their business scaled and with close to a decade of data, the dashboard performance started to get slower. The team worked on things like caching, query optimization, and database tuning to address performance issues but realized that more needed to be done.

![weinrich-quote.png](https://media.graphcms.com/wHVLFnwNRBC2SnfSQz37)

## How Jobber is using Cube.js

When Jobber first discovered Cube.js, they started studying the documentation and examples and liked the flexibility. The Jobber team also liked the [two-level caching](https://cube.dev/docs/caching#top) system in Cube.js as they previously had to implement a lot of that with their own code. Now they simply use Cube.js almost as a black box that they communicate with and can remove the optimization code they had to maintain in the past. Jobber's first use case with Cube.js was using daily [rollup pre-aggregations](https://cube.dev/docs/pre-aggregations#rollup) with the two-level caching. 

Another important and timely feature was the [external pre-aggregations](https://cube.dev/docs/pre-aggregations#external-pre-aggregations) capability of Cube.js. With their source database being a read-only database replica, building the pre-aggregations inline within the source database wasn't an option.

![weinrich-quote-2.png](https://media.graphcms.com/5rId8uhyS6SOIoVGwJ6T)

As you can see in the architecture diagram below, Jobber has a single PostgreSQL database backend, and they recently started using React for the frontend. Jobber has a read-only replica of the database to populate the rollup pre-aggregations database and wraps the Cube.js REST API with their own API for handling things like authentication (Cube.js is a trusted sub-system in this configuration). Jobber also leverages [multitenancy](https://cube.dev/docs/multitenancy-setup) with a query transformer to enforce at runtime that all data queries filter to the authenticated tenant. Other security features include full [certificate verification](https://cube.dev/docs/connecting-to-the-database#enabling-ssl) for database connections.

![Jobber-Conceptual Architecture.png](https://media.graphcms.com/UXwvIj0HS1288WBP6UA2)

Splitting Cube.js like this—an API server which handles the queries and a scheduled refresh server which updates the pre-aggregation rollups periodically—required leveraging some special configuration options for the orchestrator:

- **API Server**: Uses `CubejsServer` but configured so that it will only respond with data from the rollup pre-aggregations database even if queries attempt to access data outside of the available rollups (`rollupOnlyMode: true`), and additionally, should skip the consistency checks and background refreshing of the rollups (`externalRefresh: true`).

```jsx
orchestratorOptions: {
  preAggregationsOptions: {
    externalRefresh: true,
  },
  rollupOnlyMode: true,
},
```

- **Scheduled Refresh Server**: Uses `ServerCore` (no need to run a web server here!) to execute `runScheduledRefresh` repeatedly (with a five-second cooldown) until the rollups have completed refreshing. Note that leveraging a read replica requires setting up `readOnly: true` on the `driverFactory` configuration.

Jobber's architecture could be a viable option in their environment for anyone who is querying production databases (vs. querying some big data data sources).

## Deploying, testing, and including Cube.js into the CI pipeline

There were several team members at Jobber that were involved in Cube.js development and included two developers, one designer, and a product manager. The team initially used Cube.js out-of-the-box and utilized the Cube.js Developer Playground extensively, with a few additional refinements, such as converting the auto-generated project to TypeScript. Jobber uses Docker for their development environment, and they surface the Cube.js microservice in a container. 

The Jobber team also developed tests for things like additional schema validations on their CI build. One of the things the team ran into early on was the table name truncation that tends to happen with PostgreSQL. So one of their tests actually asks Cube.js what table names it's going to generate, and if the table name is longer than what PostgreSQL allows, the test will fail early in the CI process. 

The Jobber team also did two rounds of performance testing. First, the team wanted to understand where the bottlenecks for Cube.js were (e.g., CPU vs. memory) when saturated with incoming requests and observed how it responded while ramping up simulated concurrent requests.

In the load test graph below, you can see how their test showed signs of achieving maximum throughput (for the given server configuration), with response times (blue) starting to get shaky as the number of concurrent requests (yellow) ramps up. This particular test was getting 100% Redis cache hits, and the bottleneck was the CPU.

![Untitled-2.png](https://media.graphcms.com/pxNLuyThRTi0rC29dC5U)

Next, they used simulated traffic in the frontend to mimic actual customers' traffic (and data requests) more accurately and began experimenting with different scaling options (vertical and horizontal). This allowed them to observe more complex performance characteristics because both caching tiers were getting exercised.

Once on beefier servers, the CPU was no longer the bottleneck, and the next bottleneck encountered was related to memory! This manifested in a runaway memory ramp (at a certain amount of traffic) where Node.js would consume all the available memory and eventually pause the entire runtime for several seconds to run a "mark sweep" style garbage collection.

![Untitled-3.png](https://media.graphcms.com/gjZl4ZejQhKsr1EdrTyA)

After some research into how Node.js' memory configuration works—[it's complicated](https://github.com/v8/v8/blob/2b17c752d117bf0ededd23206bb38b69e641a976/src/heap/heap.h#L1106-L1136), often described incorrectly online, and changed between versions 10 and 12—the Jobber team optimized the usage of the server resources by [running concurrent copies](https://devcenter.heroku.com/articles/node-concurrency) of the Node.js process using a library called [throng](https://www.npmjs.com/package/throng).

The graph below shows the service's response time shape over three days. You actually see that each day, as the traffic starts increasing up to the peak volume (represented as the vertical white lines), the Cube.js response time actually goes down (almost counterintuitively). This is because the first hit of the day is going against the rollup pre-aggregations database (level 2), but subsequent looks at the dashboard hit the faster query result cache in Redis (level 1).

![Untitled-4.png](https://media.graphcms.com/P8MVx2q6QGu2YYlANt5n)

## Contributing to Cube.js

Jobber's team was also very active contributors to Cube.js and in many ways co-developers as they were evaluating and deploying Cube.js. This [GitHub query](https://github.com/search?l=&q=repo%3Acube-js%2Fcube.js+author%3Ajcw-+author%3Acolefichter+state%3Aclosed&type=issues) shows contributions from two team members at Jobber. They actually only show contributions via pull requests and don't include Slack or other channels.

This type of active engagement from users clearly demonstrates the value of open source. The contributions help accelerate development and, more importantly, provide valuable insight to the Cube Dev team on actual use cases in production environments.

![klotz-quote (1).png](https://media.graphcms.com/XnB22Z8Qxi1zSMstJvIA)

If you want to see Cube.js in action today at Jobber, you can visit the [Jobber's Help Center page](https://help.getjobber.com/hc/en-us/articles/360051851213-Home#h_01EDPE0KKPBMDHE96TF3DXCAD9). In the screenshot below, Cube.js is used for the four line-series charts.

![sidenavClosed.png](https://media.graphcms.com/flj48F6LShyE6kDpNVGU)

After the successful deployment, the Jobber team is continuing to explore other areas where Cube.js can reduce engineering costs while delivering great features for their customers.

## Interested in joining Jobber's success with Cube.js?

Explore Cube.js [examples & tutorials](https://cube.dev/docs/examples) and [get started](https://cube.dev/docs/getting-started) today. To jumpstart your efforts, please join us on [Discourse](https://forum.cube.dev/) & [Slack](https://slack.cube.dev/), follow us on [Twitter](https://twitter.com/thecubejs), and get engaged with the growing Cube.js community.

Ray Paik

How Jobber uses Cube.js to help customers run their businesses with high performance dashboards

Artyom Keydunov

React

D3 Dashboard Tutorial

React Dashboard: an Ultimate Guide

It’s well known that doing analytics on production RDBMS instances is prohibitive due to lock and performance issues. The introduction of replication by many of the modern RDBMSs made this process a little bit easier, but not ideal. The next evolution step here is Serverless RDBMSs. Splitting processing power from the storage brings our understanding of how to work with a RDBMS to a new level. This gives us an opportunity for infinite DB infrastructure scaling and allows us to have both OLTP and OLAP workload at the same time.

In this article, we perform an analytics performance benchmark of AWS Aurora MySQL using the [Cube.js Serverless Analytics Framework](https://github.com/statsbotco/cube.js).

**TL;DR**: Unoptimized 100M rows test dataset ran by Serverless MySQL Aurora can be queried in 176 seconds. A query time of less than 200ms is achievable for the same dataset using multi-stage querying approach.

## Aurora MySQL Setup
Setup of Aurora MySQL is fairly simple. Just go to your [RDS Service in AWS Console](https://console.aws.amazon.com/rds/home) and select create database. 

![](https://media.graphcms.com/uAneZZ7S3GTvrD6pRwRd)

Here you should select the Aurora and MySQL compatible version that supports Serverless deployment. 
Then choose Serverless and enter your credentials to access your DB. That’s it! Your Aurora instance is configured and ready to launch.

## Preparing your dataset
For the purpose of testing, we’ll generate a 100M row dataset out of [Sakila Sample Database](https://dev.mysql.com/doc/sakila/en/). In order to do that, we’ll use slightly modified versions of Sakila tables.

First we’ll introduce the `customer` table:

```sql
CREATE TABLE customer (
  customer_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  store_id TINYINT UNSIGNED NOT NULL,
  first_name VARCHAR(45) NOT NULL,
  last_name VARCHAR(45) NOT NULL,
  email VARCHAR(50) DEFAULT NULL,
  address_id SMALLINT UNSIGNED NOT NULL,
  active BOOLEAN NOT NULL DEFAULT TRUE,
  create_date DATETIME NOT NULL,
  last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY  (customer_id),
  KEY idx_fk_store_id (store_id),
  KEY idx_fk_address_id (address_id),
  KEY idx_last_name (last_name)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
```

Then the `payment` table:

```sql
CREATE TABLE payment (
  payment_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
  customer_id SMALLINT UNSIGNED NOT NULL,
  staff_id TINYINT UNSIGNED NOT NULL,
  rental_id INT DEFAULT NULL,
  amount DECIMAL(5,2) NOT NULL,
  payment_date DATETIME NOT NULL,
  last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY  (payment_id),
  KEY idx_fk_customer_id (customer_id),
  CONSTRAINT fk_payment_customer FOREIGN KEY (customer_id) REFERENCES customer (customer_id) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
```

*Quick hint: Aurora can’t be accessed using a public IP and is accessible only within VPC. To connect to it from your machine, you should use either VPN/SSH Tunnel or you can use AWS RDS query editor, which works just inside browser.*

To fill the `customer` and `payment` tables we’ll use a sample of the first 600 `customer` rows and the first 500 `payment` rows from [Sakila Sample Database](https://dev.mysql.com/doc/sakila/en/).

We’ll then use the `payment` table to generate actual records for the 100M `payment_big` table. Its definition is very similar to `payment`:
```sql
CREATE TABLE payment_big (
  payment_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  customer_id SMALLINT UNSIGNED NOT NULL,
  staff_id TINYINT UNSIGNED NOT NULL,
  rental_id INT DEFAULT NULL,
  amount DECIMAL(5,2) NOT NULL,
  payment_date DATETIME NOT NULL,
  last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY  (payment_id),
  KEY idx_fk_customer_id (customer_id),
  CONSTRAINT fk_payment_big_customer FOREIGN KEY (customer_id) REFERENCES customer (customer_id) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
```

As MySQL doesn’t have a generator feature, we’ll introduce some helper views for that.

Integer number generator up to 16:
```sql
CREATE OR REPLACE VIEW generator_16
AS SELECT 0 n UNION ALL SELECT 1  UNION ALL SELECT 2  UNION ALL 
   SELECT 3   UNION ALL SELECT 4  UNION ALL SELECT 5  UNION ALL
   SELECT 6   UNION ALL SELECT 7  UNION ALL SELECT 8  UNION ALL
   SELECT 9   UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
   SELECT 12  UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL 
   SELECT 15;
```

Integer number generator up to 256:
```sql
CREATE OR REPLACE VIEW generator_256 AS SELECT ( hi.n * 16 + lo.n ) AS n FROM generator_16 lo , generator_16 hi;
```

To generate actual payments, we’ll use the following SQL, which generates 2,048,000 rows at once:
```sql
INSERT INTO payment_big (customer_id, staff_id, rental_id, amount, payment_date, last_update) 
SELECT customer_id, staff_id, rental_id, amount * 10 * RAND() as amount, TIMESTAMPADD(MINUTE, generator_256.n * 1000 + 1000 * RAND(), payment_date) as payment_date, last_update
FROM payment, generator_256, generator_16
```

We’ll call it 50 times to get a table with approximately 100M rows. 
## Cube.js setup
[Cube.js](https://github.com/statsbotco/cube.js) is an open-source analytics framework that works as an intermediate between your analytics users and your database. It provides analytic SQL generation, query results caching and execution orchestration, data pre-aggregation, security, API for query results fetch, and visualization. We’ll use it mostly for caching and pre-aggregation to get a sense of how fast Aurora MySQL analytics can be and what the delay is between data ingestion and getting insight from it.

To create a Cube.js application, you’ll need to install Cube.js CLI and Serverless CLI for deployment:
```bash
$ npm install -g cubejs-cli
$ npm install -g serverless
```

Then, let’s create an app:
```bash
$ cubejs create aurora-benchmark -d mysql -t serverless
```

In `serverless.yml` you should define credentials to access your Aurora MySQL and Redis instance as well as provide vpc settings to access your resources. Please [learn more about deployment here](https://statsbot.co/cubejs/docs/deployment).

We’ll also introduce Cube.js schema definitions for our benchmark.
`schema/Customers.js`:
```javascript
cube(`Customers`, {
 sql: `select * from test.customer`,

 measures: {
   count: {
     type: `count`
   }
 },

 dimensions: {
   id: {
     sql: `customer_id`,
     type: `number`,
     primaryKey: true
   },

   email: {
     sql: `email`,
     type: `string`
   },

   name: {
     sql: `${CUBE}.first_name || ${CUBE}.last_name`,
     type: `string`
   }
 }
});
```

`schema/Payments.js`:
```javascript
cube(`Payments`, {
 sql: `select * from test.payment_big`,

 joins: {
   Customers: {
     sql: `${CUBE}.customer_id = ${Customers}.customer_id`,
     relationship: `belongsTo`
   }
 },

 measures: {
   count: {
     type: `count`
   },

   totalAmount: {
     sql: `amount`,
     type: `sum`
   }
 },

 dimensions: {
   id: {
     sql: `payment_id`,
     type: `number`,
     primaryKey: true
   },

   date: {
     sql: `payment_date`,
     type: `time`
   }
 }
});
```

That’s everything we need to deploy it so let’s do it:
```bash
$ serverless deploy -v
```

If everything is set up correctly, you should see something like this in your shell:
```bash
Service Information
service: aurora-benchmark
stage: dev
region: us-east-1
stack: aurora-benchmark-dev
resources: 16
api keys:
  None
endpoints:
  GET - https://123456789a.execute-api.us-east-1.amazonaws.com/dev/
  ANY - https://123456789a.execute-api.us-east-1.amazonaws.com/dev/{proxy+}
functions:
  cubejs: aurora-benchmark-dev-cubejs
  cubejsProcess: aurora-benchmark-dev-cubejsProcess
layers:
  None
```
Go to your main GET endpoint link which should look like this: ` https://123456789a.execute-api.us-east-1.amazonaws.com/dev/`

If everything is done right, you should see a code sandbox with a sample pie chart.

Let’s create our time series chart for the `Payments` cube. To do this let’s add `moment` import to `index.js`:

```javascript
import moment from 'moment';
```

You should add it as a dependency as well using the blue `Add Dependency` button or `package.json`.

Then replace `renderChart` and `query`:
```jsx
const renderChart = resultSet => (
  <Chart scale={{ category: { tickCount: 8 } }} height={400} data={resultSet.chartPivot()} forceFit>
    <Axis name="category" label={{ formatter: val => moment(val).format("MMM DD") }} />
    <Axis name="Payments.count" />
    <Tooltip crosshairs={{ type: 'y' }} />
    <Geom type="line" position="category*Payments.count" size={2} />
  </Chart>
);

const query = {
  measures: ["Payments.count"],
  timeDimensions: [{
    dimension: 'Payments.date',
    granularity: 'day',
    dateRange: ['2005-09-01', '2005-10-08']
  }]
};
```

After edits you should get an `index.js` similar to this:
```jsx
import React from "react";
import ReactDOM from "react-dom";

import cubejs from "@cubejs-client/core";
import { QueryRenderer } from "@cubejs-client/react";
import { Chart, Axis, Tooltip, Geom, Coord, Legend } from "bizcharts";
import moment from 'moment';

const API_URL = "https://123456789a.execute-api.us-east-1.amazonaws.com/dev"; // change to your actual endpoint

const renderChart = resultSet => (
  <Chart scale={{ category: { tickCount: 8 } }} height={400} data={resultSet.chartPivot()} forceFit>
    <Axis name="category" label={{ formatter: val => moment(val).format("MMM DD") }} />
    <Axis name="Payments.count" />
    <Tooltip crosshairs={{ type: 'y' }} />
    <Geom type="line" position="category*Payments.count" size={2} />
  </Chart>
);

const query = {
  measures: ["Payments.count"],
  timeDimensions: [{
    dimension: 'Payments.date',
    granularity: 'day',
    dateRange: ['2005-09-01', '2005-10-08']
  }]
};

const cubejsApi = cubejs(
  "eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpYXQiOjE1NTExODQ0NDksImV4cCI6MTU1MTI3MDg0OX0.KLkKp2pRnw9ZlrwMGkoBlpdgGy4eol7258aKVwJLPuM",
  { apiUrl: API_URL + "/cubejs-api/v1" }
);

const App = () => (
  <div style={{ textAlign: 'center', fontFamily: 'sans-serif' }}>
   <h1>Payments Count</h1>
    <QueryRenderer
      query={query}
      cubejsApi={cubejsApi}
      render={({ resultSet, error }) =>
        (resultSet && renderChart(resultSet)) ||
          (error && error.toString()) || <span>Loading...</span>
      }
    />
</div>
);

const rootElement = document.getElementById("root");
ReactDOM.render(<App />, rootElement);
```

If everything works well, you should see a line chart similar to this one:

![](https://media.graphcms.com/h4SlFFYORT6Kb0u7ofCo)

## Benchmark
At this point we have an unoptimized data set of 100M records in our `payment_big` table. We’ll use serverless logs to see how long it takes to process queries on the Aurora MySQL side as Cube.js writes these processing logs by default. To enable log tail we’ll use:

```bash
$ serverless logs -t -f cubejsProcess
```

If we request one month of data we’ll get a processing delay of 176 seconds:
```bash
2019-02-27T12:47:45.384Z	794618d5-5eb3-40ce-88f3-cce6d75786f1	Performing query completed:
{
    "queueSize": 2,
    "duration": 175900,
    "queryKey": [
        "SELECT\n      DATE_FORMAT(CONVERT_TZ(payments.payment_date, @@session.time_zone, '+00:00'), '%Y-%m-%dT00:00:00.000Z') `payments.date_date`, count(payments.payment_id) `payments.count`\n    FROM\n      test.payment_big AS payments\n  WHERE (payments.payment_date >= TIMESTAMP(convert_tz(?, '+00:00', @@session.time_zone)) AND payments.payment_date <= TIMESTAMP(convert_tz(?, '+00:00', @@session.time_zone))) GROUP BY 1 ORDER BY 1 ASC LIMIT 10000",
        [
            "2005-09-01T00:00:00Z",
            "2005-09-30T23:59:59Z"
        ],
        []
    ]
}
```

Not too bad for an unoptimized table of 100M data points. Let’s add an index for `payment_date` to see if it can affect this time:
```sql
CREATE INDEX payment_big_date ON payment_big (payment_date)
```

The same query with index will be processed in 31 seconds which is great but still not fast enough to build a great user experience. What we can do is introduce pre-aggregations for this table. To do this let’s just add:

```javascript
preAggregations: {
 main: {
   type: `rollup`,
   measureReferences: [count],
   timeDimensionReference: date,
   granularity: `day`,
   partitionGranularity: `day`
 }
}
```
to `schema/Payments.js` so we can get:
```javascript
cube(`Payments`, {
 sql: `select * from test.payment_big`,

 joins: {
   Customers: {
     sql: `${CUBE}.customer_id = ${Customers}.customer_id`,
     relationship: `belongsTo`
   }
 },

 measures: {
   count: {
     type: `count`
   },

   totalAmount: {
     sql: `amount`,
     type: `sum`
   }
 },

 dimensions: {
   id: {
     sql: `payment_id`,
     type: `number`,
     primaryKey: true
   },

   date: {
     sql: `payment_date`,
     type: `time`
   }
 },

 preAggregations: {
   main: {
     type: `rollup`,
     measureReferences: [count],
     timeDimensionReference: date,
     granularity: `day`,
     partitionGranularity: `day`
   }
 }
});
```

It’ll create a rollup table for each day of data and will refresh it incrementally. Here `partitionGranularity` instructs Cube.js to create rollup table per day of data. Rollup table itself will contain `count` measure and `date` dimensions with `day` granularity. In fact every rollup table here will contain just 1 row which contains `count` for specific day calculated. Let’s deploy it and see how it changes query processing times:
```bash
$ serverless deploy -v
```

If we request the same interval of data, Cube.js will build pre-aggregations first for each day, which takes 1.5 seconds per one day:
```bash
2019-02-27T13:33:19.267Z	11477db2-f66e-4278-9103-eefbbc513be3	Performing query completed:
{
    "queueSize": 1,
    "duration": 1578,
    "queryKey": [
        [
            "CREATE TABLE stb_pre_aggregations.payments_main20050928 AS SELECT\n      DATE_FORMAT(CONVERT_TZ(payments.payment_date, @@session.time_zone, '+00:00'), '%Y-%m-%dT00:00:00.000Z') `payments.date_date`, count(payments.payment_id) `payments.count`\n    FROM\n      test.payment_big AS payments\n  WHERE (payments.payment_date >= TIMESTAMP(convert_tz(?, '+00:00', @@session.time_zone)) AND payments.payment_date <= TIMESTAMP(convert_tz(?, '+00:00', @@session.time_zone))) GROUP BY 1",
            [
                "2005-09-28T00:00:00Z",
                "2005-09-28T23:59:59Z"
            ]
        ],
        [
            [
                {
                    "current_hour": "2019-02-27T13:00:00.000Z"
                }
            ]
        ]
    ]
}
```

And then it will query the union of all pre-aggregations tables, which takes less than 200ms:
```bash
2019-02-27T13:33:23.647Z	a4162f29-570d-495f-8ca4-34600869d8e7	Performing query completed:
{
    "queueSize": 1,
    "duration": 134,
    "queryKey": [
        "SELECT `payments.date_date` `payments.date_date`, sum(`payments.count`) `payments.count` FROM (SELECT * FROM stb_pre_aggregations.payments_main20050901 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050902 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050903 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050904 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050905 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050906 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050907 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050908 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050909 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050910 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050911 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050912 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050913 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050914 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050915 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050916 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050917 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050918 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050919 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050920 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050921 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050922 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050923 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050924 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050925 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050926 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050927 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050928 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050929 UNION ALL SELECT * FROM stb_pre_aggregations.payments_main20050930) as partition_union  WHERE (`payments.date_date` >= CONVERT_TZ(TIMESTAMP(convert_tz(?, '+00:00', @@session.time_zone)), @@session.time_zone, '+00:00') AND `payments.date_date` <= CONVERT_TZ(TIMESTAMP(convert_tz(?, '+00:00', @@session.time_zone)), @@session.time_zone, '+00:00')) GROUP BY 1 ORDER BY 1 ASC LIMIT 10000",
        [
            "2005-09-01T00:00:00Z",
            "2005-09-30T23:59:59Z"
        ],
       ...
}
```

This approach allows us to split the analytics workload into small reusable chunks to avoid DB burst with an incremental update strategy.

## Conclusion
We were playing with just a test dataset and the results are very well correlated with what we see in production workloads. MySQL is capable of handling several billions of data points per table with an analytics workload pretty well. In this case, sub second query times are achievable with several seconds time to insight delay using properly organized query orchestration. 

Although Serverless Aurora MySQL is capable of handling an enormous workload while stress testing, we discovered that workload routing algorithms aren’t smart enough yet to route queries to the least loaded nodes, which under certain circumstances can lead to partial database outages while analytics querying. For production environments we recommend you use provisioned Aurora MySQL with read replicas or the [Parallel Query feature](https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/aurora-mysql-parallel-query.html) in order to split your OLTP and OLAP workload by design.

Pavel Tiunov

Amazon Aurora

Serverless Analytics Benchmark of AWS Aurora Performance

*The techniques below are useful primarily for analytics queries, although some of them could be helpful in OLTP cases as well.*

A single query optimization tip can boost your database performance by 100x. At one point, we advised one of our customers that had a 10TB database to use a date-based multi-column index. As a result, their date range query sped up by 112x. In this post, we share five simple but still powerful tips for PostgreSQL query optimization.

<span>
We use these techniques a lot to optimize our customers PostgreSQL databases with billions of data points during <a href="https://github.com/statsbotco/cube.js">Cube.js</a> deployments.
</span>
 
To keep it easy, we ran examples for this article on a test dataset. Although it doesn’t show the actual performance improvement, you will see that our tips solve the significant set of optimization problems and work well in real-world case scenarios.

<h1>Explain analyze</h1>

Postgres has a cool extension to the well-known `EXPLAIN` command, which is called <a href="https://www.postgresql.org/docs/9.3/static/using-explain.html#USING-EXPLAIN-ANALYZE">`EXPLAIN ANALYZE`</a>. The difference is that `EXPLAIN` shows you query cost based on collected statistics about your database, and `EXPLAIN ANALYZE` actually runs it to show the processed time for every stage.
 
We highly recommend you use `EXPLAIN ANALYZE` because there are a lot of cases when `EXPLAIN` shows a higher query cost, while the time to execute is actually less and vice versa. The most important thing is that the EXPLAIN command will help you to understand if a specific index is used and how.
 
The ability to see indexes is the first step to learning PostgreSQL query optimization.

<h1>One index per query</h1>

Indexes are materialized copies of your table. They contain only specific columns of the table, so you can quickly find data based on the values in these columns. Indexes in Postgres also store row identifiers or row addresses used to speed up the original table scans.

<aside><h2 class="ui header"><a href="https://statsbot.co/blog/select-random-rows-sql/">Scalable Select of Random Rows in SQL</a></h2></aside>

It’s always a trade-off between storage space and query time, and a lot of indexes can introduce overhead for DML operations. However, when read query performance is a priority, as is the case with business analytics, it’s usually a well-working approach.
 
We advise to create one index per unique query for better performance. Look further in this post to learn how to create indexes for specific queries.

<h1>Using multiple columns in index</h1>

Let’s review the explain analyze plan of the following simple query without indexes:

```
EXPLAIN ANALYZE SELECT line_items.product_id, SUM(line_items.price)
FROM line_items
WHERE product_id > 80
GROUP BY 1
```

An explain analyze returns:

```
HashAggregate (cost=13.81..14.52 rows=71 width=12) (actual time=0.137..0.141 rows=20 loops=1)
Group Key: product_id
-> Seq Scan on line_items (cost=0.00..13.25 rows=112 width=8) (actual time=0.017..0.082 rows=112 loops=1)
Filter: (product_id > 80)
Rows Removed by Filter: 388
Planning time: 0.082 ms
Execution time: 0.187 ms
```

This query scans all of the line items to find a product with an id that is greater than 80, and then sums up all the values grouped by that product id.
 
<aside><h2>The syntax for creating indexes:</h2>CREATE INDEX index_name ON table_name (column1, column2, ...)</aside>

Now we’ll add the index to this table:

```
CREATE INDEX items_product_id ON line_items(product_id)
```

We created a <a href="https://en.wikipedia.org/wiki/B-tree">B-tree index</a>, which contains only one column: `product_id`. After reading lots of articles about the benefits of using index, one can expect a query boost from such an operation. Sorry, bad news.
 
As we need to sum up the price column in the query above, we still need to scan the original table. Depending on the table statistics, Postgres will choose to scan the original table instead of the index. The thing is, index lacks a `price` column.
 
We can tweak this index by adding a price column as follows:

```
CREATE INDEX items_product_id_price ON line_items(product_id, price)
```

If we rerun the explain plan, we’ll see our index is the forth line:

```
GroupAggregate (cost=0.27..7.50 rows=71 width=12) (actual time=0.034..0.090 rows=20 loops=1)
Group Key: product_id
-> Index Only Scan using items_product_id_price on line_items (cost=0.27..6.23 rows=112 width=8) (actual time=0.024..0.049 rows=112 loops=1)
Index Cond: (product_id > 80)
Heap Fetches: 0
Planning time: 0.271 ms
Execution time: 0.136 ms
```

But how would putting the price column first affect the PostgreSQL query optimization?

<h1>Column order in a multicolumn index</h1>

Well, we figured out that a multicolumn index is used in the previous query because we included both columns. The interesting thing is that we can use another order for these columns while defining the index:

```
CREATE INDEX items_product_id_price_reversed ON line_items(price, product_id)
```

<aside><h2 class="ui header"><a href="https://statsbot.co/blog/postgres-to-bigquery-etl/">Loading Terabytes of Data from Postgres into BigQuery</a></h2></aside>

If we rerun explain analyze, we’ll see that `items_product_id_price_reversed` is not used. That’s because this index is sorted firstly on `price` and then on `product_id`. Using this index will lead to a full index scan, <strong>which is nearly equivalent to scanning the whole table</strong>. That’s why Postgres opts to use scan for an original table.
 
It's a good practice to put in the first place columns, which you use in filters with the biggest number of unique values.

<h1>Filters + joins</h1>

It’s time to figure out what the best set of indexes is for a specific join query, which also has some filter conditions. Usually, you can achieve optimal results by trial and error.
 
As in the case of simple filtering, choose the most restrictive filtering condition and add an index for it.
 
Let’s consider an example:

```
SELECT orders.product_id, SUM(line_items.price)
FROM line_items
LEFT JOIN orders ON line_items.order_id = orders.id
WHERE line_items.created_at BETWEEN '2018-01-01' and '2018-01-02'
GROUP BY 1
```

Here we have join on `order_id` and filter on `created_at`. This way, we can create a multicolumn index that will contain `created_at` in the first place, `order_id` in the second and <code>price</code> in the third:

```
CREATE INDEX line_items_created_at_order_id_price ON line_items(created_at, order_id, price)
```

We’ll get the following explain plan:

```
GroupAggregate (cost=12.62..12.64 rows=1 width=12) (actual time=0.029..0.029 rows=1 loops=1)
Group Key: orders.product_id
-> Sort (cost=12.62..12.62 rows=1 width=8) (actual time=0.025..0.026 rows=1 loops=1)
Sort Key: orders.product_id
Sort Method: quicksort Memory: 25kB
-> Nested Loop Left Join (cost=0.56..12.61 rows=1 width=8) (actual time=0.015..0.017 rows=1 loops=1)
-> Index Only Scan using line_items_created_at_order_id_price on line_items (cost=0.27..4.29 rows=1 width=8) (actual time=0.009..0.010 rows=1 loops=1)
Index Cond: ((created_at >= '2018-01-01 00:00:00'::timestamp without time zone) AND (created_at <= '2018-01-02 00:00:00'::timestamp without time zone))
Heap Fetches: 0
-> Index Scan using orders_pkey on orders (cost=0.29..8.30 rows=1 width=8) (actual time=0.004..0.005 rows=1 loops=1)
Index Cond: (line_items.order_id = id)
Planning time: 0.303 ms
Execution time: 0.072 ms
```

As you can see, `line_items_created_at_order_id_price` is used to reduce scan by date condition. After that, it’s joined with orders using the `orders_pkey` index scan.
 
Date columns are usually one of the best candidates for the first column in a multicolumn index as it reduces scanning throughput in a predictable manner.

<h1>Conclusion</h1>

Our tips for PostgreSQL query optimization will help you to speed up queries 10-100x for multi-GB databases. They can solve most of your performance bottlenecks in an 80/20 manner. It doesn’t mean you shouldn’t double check your queries with `EXPLAIN` for real-world case scenarios.

How to speed up your database performance 100x

Simple Tips for PostgreSQL Query Optimization

Despite the fact that an ETL task is pretty challenging when it comes to loading Big Data, there’s still the scenario in which you can load terabytes of data from Postgres into BigQuery relatively easy and very efficiently. This is the case when you have a lot of immutable data distributed in tables by some timestamp. For example, a `transactions` table with a `created_at` timestamp column. BigQuery and Postgres have great tools in order to do this pretty fast and conveniently.

<h1>Preparing Postgres Tables</h1>

<p>BigQuery <a href="https://cloud.google.com/blog/big-data/2016/04/inside-capacitor-bigquerys-next-generation-columnar-storage-format" target="_blank" rel="noopener">Capacitor</a> storage format, as many other Big Data formats, is optimized for a one-time write of an entire table. To distribute data between tables, BigQuery heavily relies on the <a href="https://cloud.google.com/bigquery/docs/querying-wildcard-tables" target="_blank" rel="noopener">wild card tables</a> pattern. Typical usage is to create tables with names suffixed by some field value. Usually, date suffixes are used for this purpose. For example, the table `transactions20180301` can contain transactions created on March 1, 2018. This approach allows for both the creation of very efficient storage on the BigQuery side, and for ease of loading the data source on the storage side.</p>

In order to implement this loading approach we need to ensure we have the right indexes in the Postgres database before we start loading processes from Postgres into BigQuery. In order to create this, you can use psql to connect to your database. Let’s create an index for a `created_at` timestamp in the `transactions` table. Assuming we’re dealing with an ubuntu machine that hosts the db instance, it can be as simple as:

```
$ sudo su - postgres
$ psql yourdbname

yourdbname=>create index transactions_created_at on transactions (created_at);
```

Index creation can take a while if you have a lot of data. You can track process execution using `top` utility. You should repeat index creation for each table you’re going to load into BigQuery.

<h1>Loading Data into BigQuery</h1>

To load data into BigQuery we’re going to use BigQuery CLI, which is a very versatile tool. You can install it using <a href="https://cloud.google.com/sdk/docs/#deb" target="_blank" rel="noopener">these</a> instructions. As we’re on Linux, we’ll be using bash script in order to perform all the work. I assume BigQuery CLI is installed and authorized.

Let’s create `bigquery-upload.sh` and add the following function in order to upload a single day from a specific table:

```
#!/bin/bash

function upload_day {
  table=$1
  sel=$2
  day=$3
  next_day=$(date -d "$day+1 days" +%Y-%m-%d)
  bq_suffix=$(date -d "$day" +%Y%m%d)
  echo "Uploading $table: $day..."
  psql  -c "\\copy (select $sel from $table where created_at >= '$day' and created_at < '$next_day') TO '$table-$day.csv' WITH CSV HEADER"
  gzip $table-$day.csv
  bq load --allow_quoted_newlines --project_id  --replace --source_format=CSV --autodetect --max_bad_records 100 .$table$bq_suffix $table-$day.csv.gz
  rm $table-$day.csv.gz
};
```

This function has three arguments: table, columns for selection, and date to upload. As you can see, it uses the `\copy` operation to download a CSV from Postgres and then compresses it. BigQuery docs says loading of a compressed CSV is slower than uncompressed, but uploading uncompressed data almost always seems slower.

You can call this function as simply as adding a line at the end of the script:

```
upload_day 'transactions' '*' '2018-03-01'
```

This will create the `transactions20180301` table with one day of data. To automate the uploading process we can introduce another function:

```
function upload_table {
  t=$1
  s=$2
  start_date=$3
  end_date=$4
  while [ "$start_date" != "$end_date" ]; do
       upload_day "$t" "$s" "$start_date"
       start_date=$(date -d "$start_date+1 days" +%Y-%m-%d)
  done
}
```

This `upload_table` function has 4 arguments: table name, columns for select, start dates, and end dates.

You can make this script parametrized by adding `upload_table "$1" '*' "$2" "$3"` as the last line so that entire script will be:

```
#!/bin/bash

function upload_day {
  table=$1
  sel=$2
  day=$3
  next_day=$(date -d "$day+1 days" +%Y-%m-%d)
  bq_suffix=$(date -d "$day" +%Y%m%d)
  echo "Uploading $table: $day..."
  psql  -c "\\copy (select $sel from $table where created_at >= '$day' and created_at < '$next_day') TO '$table-$day.csv' WITH CSV HEADER"
  gzip $table-$day.csv
  bq load --allow_quoted_newlines --project_id  --replace --source_format=CSV --autodetect --max_bad_records 100 .$table$bq_suffix $table-$day.csv.gz
  rm $table-$day.csv.gz
};

function upload_table {
  t=$1
  s=$2
  start_date=$3
  end_date=$4
  while [ "$start_date" != "$end_date" ]; do
       upload_day "$t" "$s" "$start_date"
       start_date=$(date -d "$start_date+1 days" +%Y-%m-%d)
  done
}

upload_table "$1" '*' "$2" "$3"
```

Don’t forget to `$ chmod +x bigquery-upload.sh` your script. After it you can run it as

```
$ ./bigquery-upload.sh "transactions" "2017-01-01" "2018-03-01"
```

The great thing about this script of loading data from Postgres into BigQuery is you can use it to upload just a single day and it can be put in the `/etc/cron.daily` or `/etc/cron.hourly` directory so that you can get your data in nearly real time. The `--replace` option script can be run more than once for the same day to replace data as it is refreshed.

If all is done right, it should load your terabyte of data in a day or so. Please do not hesitate to <a href="https://statsbot.co/?utm_source=statsbotblog&amp;utm_medium=article&amp;utm_campaign=postgres_to_bigquery">contact us</a> and we would love to advice you.