Read replicas — Data analysis and reporting
Leverage read replicas for running data-intensive queries
With Neon's read replica feature, you can instantly create a dedicated read-only compute instance 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 read-write compute instance. 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.
Suppose you have a
sales table in your production database. The table and data might look something like this:
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 it's 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.
Create a read replica
Creating a read replica involves adding a read-only compute endpoint to a branch. You can add a read-only compute endpoint to any branch in your Neon project by following these steps:
- In the Neon Console, select Branches.
- Select the branch where your database resides.
- Click Add compute.
- On the Create Compute Endpoint dialog, select Read-only as the Compute type.
- Specify the Compute size options. You can configure a Fixed Size compute with a specific amount of vCPU and RAM (the default) or enable Autoscaling and configure a minimum and maximum compute size. You can also configure the Auto-suspend delay period, which is the amount of idle time after which your read-only compute is transitioned to an idle state. The default setting is 300 seconds (5 minutes). You can set this value up to 604800 seconds (7 days).
The compute size configuration determines the processing power of your database. More vCPU and memory means more processing power but also higher compute costs. For information about compute costs, see Billing metrics.
- When you finish making your selections, click Create.
Your read-only compute is provisioned and appears in the Computes section of the Branches page. This is your read replica. The following section describes how to connect to your read replica.
Connect to the read replica
Connecting to a read replica is the same as connecting to any branch, except you connect via a read-only compute endpoint instead of a read-write compute endpoint. The following steps describe how to connect to your read replica with connection details obtained from the Neon Console.
On the Neon Dashboard, under Connection Details, select the branch, the database, and the role you want to connect with.
Under Compute, select the RO-replica compute endpoint.
Select a Database and the Role you want to connect with.
Copy the connection string. This is the information you need to connect to the read replica from you client or application.
The connection string appears similar to the following:
If you expect a high number of connections, select Pooled connection to add the
-poolerflag to the connection string.
The information in your connection string corresponds to the following connection details:
- database name:
neondb. This is the ready-to-use database created with your Neon project. Your database name may differ.
When you connect to a read-only compute, no write operations are permitted on the connection.
Connect to your application from a client such as
psqlor add the connection details to your application. For example, to connect using
psql, issue the following command:
Run the analytics query on the read replica
An analytics query on your
sales table might look something like this:
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.
To delete a read replica:
- In the Neon Console, select Branches.
- Select a branch.
- Under Computes, find the read-only compute endpoint you want to delete. Read replicas have a
- Click the compute endpoint menu on the right side of the table, and select Delete.
- On the confirmation dialog, click Delete.