# Source: https://neon.com/llms/guides-read-replica-data-analysis.txt

# Run analytics queries with Read Replicas

> The document explains how to utilize Neon's read replicas to execute analytics queries, enabling users to offload query workloads from primary databases to improve performance and scalability.

## Source

- [Run analytics queries with Read Replicas HTML](https://neon.com/docs/guides/read-replica-data-analysis): The original HTML version of this documentation

With Neon's read replica feature, you can instantly create a dedicated read replica for running data-intensive analytics or reporting queries. This allows you to avoid disruption or performance degradation on your production database.

A read replica reads data from the same source as your primary read-write compute. There's no data replication, so creating a read replica is a near-instant process. For more information about Neon's read replica architecture, see [Read replicas](https://neon.com/docs/introduction/read-replicas).

## Scenario

Suppose you have a `sales` table in your production database. The table and data might look something like this:

```sql
CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    product_id INT NOT NULL,
    sale_amount DECIMAL(10,2) NOT NULL,
    sale_date DATE NOT NULL
);

INSERT INTO sales (product_id, sale_amount, sale_date) VALUES
(1, 20.50, '2022-07-24'),
(2, 35.99, '2022-08-24'),
(1, 20.50, '2022-09-24'),
(3, 15.00, '2023-01-24'),
(1, 20.50, '2023-04-24');
...
```

You want to find the total sale amount for each product in the past year, but due to the large number of products and sales in your database, you know this is a costly query that could impact performance on your production system.

This guide walks you through creating a read replica, connecting to it, running your query, and optionally deleting the read replica when finished.

   **Tip** Metabase Analytics Use Case: [Metabase](https://www.metabase.com/) is an open-source business intelligence (BI) company that provides a platform for visualizing and analyzing data. With Metabase and Neon, you can: - Create a read replica in Neon - Configure [Autoscaling](https://neon.com/docs/introduction/autoscaling) to define minimum and maximum limits for compute resources - Configure [scale to zero](https://neon.com/docs/introduction/scale-to-zero) to define whether the read replica scales to zero when not being used - Configure a connection to the read replica from Metabase. With this setup, your read replica only wakes up when Metabase connects, scales to sync job requirements without affecting your production database, and scales back to zero after the job sync is finished.

## Create a read replica

Creating a read replica involves adding a read replica compute to a branch.

   **Note**: The Free plan is limited to a maximum of 3 read replica computes per project.

You can add a read replica compute- to any branch in your Neon project by following these steps:

1. In the Neon Console, select **Branches**.
2. Select the branch where your database resides.
3. Click **Add Read Replica**.
4. On the **Add new copmpute** dialog, select **Read replica** as the **Compute type**.
5. Specify the **Compute size settings**. You can configure a fixed size compute with a specific amount of vCPU and RAM (the default) or enable autoscaling by configuring a minimum and maximum compute size using the slider. You can also configure a **Scale to zero** setting, which determines whether a compute suspends due to inactivity after 5 minutes.
      **Note**: The compute size configuration determines the processing power of your database.
6. When you finish making your selections, click **Create**.

Your read replica is provisioned and appears on the **Computes** tab of the **Branches** page. The following section describes how to connect to your read replica.

Alternatively, you can create read replicas using the [Neon CLI](https://neon.com/docs/reference/cli-branches#create) or [Neon API](https://api-docs.neon.tech/reference/createprojectendpoint), providing the flexibility required to integrate read replicas into your workflows or CI/CD processes.

Tab: CLI

```bash
neon branches add-compute mybranch --type read_only
```

Tab: API

```bash
curl --request POST \
     --url https://console.neon.tech/api/v2/projects/late-bar-27572981/endpoints \
     --header 'Accept: application/json' \
     --header "Authorization: Bearer $NEON_API_KEY" \
     --header 'Content-Type: application/json' \
     --data '
{
  "endpoint": {
    "type": "read_only",
    "branch_id": "br-young-fire-15282225"
  }
}
' | jq
```

## Connect to the read replica

Connecting to a read replica is the same as connecting to any branch, except you connect via a read replica compute instead of your primary read-write compute. The following steps describe how to connect to your read replica with connection details obtained from the Neon Console.

1. Click the **Connect** button on your **Project Dashboard**. On the **Connect to your database modal**, select the branch, the database, and the role you want to connect with.
1. Under **Compute**, select the **Replica** compute.
1. Select a **Database** and the **Role** you want to connect with.
1. Copy the connection string. This is the information you need to connect to the read replica from your client or application.

   The connection string appears similar to the following:

   ```bash
   postgresql://alex:AbC123dEf@ep-cool-darkness-123456.us-east-2.aws.neon.tech/dbname?sslmode=require&channel_binding=require
   ```

   If you expect a high number of connections, enable the **Connection pooling** toggle to add the `-pooler` flag to the connection string.

   The information in your connection string corresponds to the following connection details:
   - role: `alex`
   - password:`AbC123dEf`
   - hostname: `ep-cool-darkness-123456.us-east-2.aws.neon.tech`
   - database name: `dbname`. Your database name may differ.

   When you connect to a read replica, no write operations are permitted on the connection.

1. Connect to your application from a client such as `psql` or add the connection details to your application. For example, to connect using `psql`, issue the following command:

   ```bash
   psql postgresql://alex:AbC123dEf@ep-cool-darkness-123456.us-east-2.aws.neon.tech/dbname?sslmode=require&channel_binding=require
   ```

## Run the analytics query on the read replica

An analytics query on your `sales` table might look something like this:

```sql
SELECT product_id, SUM(sale_amount) as total_sales
FROM sales
WHERE sale_date >= (CURRENT_DATE - INTERVAL '1 year')
GROUP BY product_id;
```

If you have a lot of products and sales, this query might impact performance on your production system, but running the query on your read replica, which has its own dedicated compute resources, causes no disruption.

## Delete the read replica

When you are finished running analytics queries, you can delete the read replica if it's no longer required. Deleting a read replica is a permanent action, but you can quickly create a new read replica when you need one.

   **Tip**: Alternatively, you can let the read replica scale to zero so that it's readily available the next time you need it. Neon's [Scale to Zero](https://neon.com/docs/introduction/scale-to-zero) feature will suspend the compute until the next time you access it. Scale to zero occurs automatically after 5 minutes of inactivity.

To delete a read replica:

1. In the Neon Console, select **Branches**.
1. Select a branch.
1. On the **Computes** tab, find the read replica you want to delete.
1. Click **Edit** &#8594; **Delete compute**.