How to safely create a read replica of production for analytics and data teams

Analyzing real-time production data can significantly improve decision-making and business intelligence capabilities. However, direct queries to production databases may result in data loss and performance issues. This is where a read replica is most beneficial. A read replica is a solution that engineers can leverage to guarantee that business intelligence teams can aggressively query data without compromising the integrity of primary production systems.

The general idea of using read replicas involves segregating read-only work from production database operations, which serve multiple purposes. One of these is the ability to distribute read requests across multiple read replica compute instances. This allows for easy application scaling and significantly increases throughput for both read-write and read-only workloads.

This tutorial will guide you through the steps to set up a read-only replica database on Neon, a fully managed serverless Postgres database that simplifies the creation and management of databases and their replicas. After setting up the read replica, we will use it with Metabase, a GUI capable of parsing and presenting data.

Prerequisites

• A Neon paid plan account
• A Metabase account
• Basic knowledge of PostgreSQL

Creating a Neon project

One requirement for following this tutorial is having a Neon project. We’d create a simple e-commerce project with three tables: Orders, Products, and Reviews. Let’s get started.

To create a Neon project, first sign up on Neon. On the Neon console, click on the New Project button. Fill in the Project Creation form and click on Create project.

Note: When you create a project in Neon, a ready-to-use database named neondb is automatically created within the Postgres cluster. You can then create additional databases as needed within the project.

Once the project is successfully created, navigate to the tab on the left side of the screen and click on SQL Editor.

Paste the following command to create the Orders, Products, and Reviews tables, respectively:

**Orders table:**

    -- Create the orders table
    CREATE TABLE Orders (
        OrderID VARCHAR(10) PRIMARY KEY,
        CustomerEmail VARCHAR(50),
        OrderDate DATE,
        OrderTotal DECIMAL(10, 2),
        Status VARCHAR(20)
    );

    -- Insert the orders data
    INSERT INTO Orders (OrderID, CustomerEmail, OrderDate, OrderTotal, Status) VALUES
    ('ORD00001', '[e-mail]', '2024-05-15', 125.43, 'Shipped'),
    ('ORD00002', '[e-mail]', '2024-05-12', 78.99, 'Processing'),
    ('ORD00003', '[e-mail]', '2024-05-10', 219.00, 'Completed'),
    ('ORD00004', '[e-mail]', '2024-05-08', 42.11, 'Awaiting Payment'),
    ('ORD00005', '[e-mail]', '2024-05-07', 99.95, 'On Hold'),
    ('ORD00006', '[e-mail]', '2024-05-05', 15.20, 'Cancelled'),
    ('ORD00007', '[e-mail]', '2024-05-03', 32.78, 'Returned'),
    ('ORD00008', '[e-mail]', '2024-05-01', 87.42, 'Delivered'),
    ('ORD00009', '[e-mail]', '2024-04-29', 101.33, 'Partially Shipped'),
    ('ORD00010', '[e-mail]', '2024-04-27', 54.00, 'Shipped');

Products table:

    -- Create the table
    CREATE TABLE Products (
        ProductID VARCHAR(10) PRIMARY KEY,
        SKU VARCHAR(10),
        Name VARCHAR(100),
        Category VARCHAR(50),
        Price DECIMAL(10, 2),
        Stock INT
    );

    -- Insert the data
    INSERT INTO Products (ProductID, SKU, Name, Category, Price, Stock) VALUES
    ('PRO00001', '123ABC', 'Wireless Noise-Cancelling Headphones', 'Electronics', 199.99, 50),
    ('PRO00002', 'DEF456', 'Running Shoes (Men\'s)', 'Clothing', 79.95, 25),
    ('PRO00003', 'GHI789', 'Coffee Maker (Smart)', 'Appliances', 129.00, 10),
    ('PRO00004', 'JKL012', 'Yoga Mat', 'Fitness', 39.99, 75),
    ('PRO00005', 'MNO345', 'Wireless Phone Charger', 'Electronics', 24.99, 100),
    ('PRO00006', 'PQR678', 'Backpack (Travel)', 'Accessories', 59.99, 30),
    ('PRO00007', 'STU901', 'Hardcover Notebook', 'Office Supplies', 14.99, NULL), -- Unlimited stock represented as NULL
    ('PRO00008', 'VWX234', 'Beard Trimmer (Electric)', 'Personal Care', 42.50, 40),
    ('PRO00009', 'YXZ567', 'Air Purifier', 'Home & Garden', 99.00, 15),
    ('PRO00010', 'CBA123', 'Stainless Steel Water Bottle', 'Kitchen & Dining', 19.99, 80);

Reviews table:

    -- Create the reviews table
    CREATE TABLE Reviews (
      ReviewID VARCHAR(255) PRIMARY KEY,  -- Can be alphanumeric for unique identifier
      ProductID INT NOT NULL,
      CustomerName VARCHAR(255) NOT NULL,
      Rating DECIMAL(2,1) NOT NULL,  -- Decimal for rating (1.0 to 5.0)
      ReviewText TEXT NOT NULL,          -- Text allows for longer reviews

      FOREIGN KEY (ProductID) REFERENCES Products(ProductID)  -- Links reviews to products
    );

    -- Insert the orders data
    INSERT INTO Reviews (ReviewID, ProductID, CustomerName, Rating, ReviewText)
    VALUES
      ('REV00001', 1, 'Alice Johnson', 5.0, 'These headphones are amazing! The noise cancellation is incredible and the sound quality is top-notch.'),
      ('REV00002', 2, 'David Lee', 4.0, 'Great shoes for running, very comfortable and lightweight. However, the size runs a bit small, so I recommend ordering a half size up.'),
      ('REV00003', 3, 'Sarah Miller', 3.0, 'The coffee maker works well, but the app can be a bit buggy sometimes. It would be great if they could improve the connectivity.'),
      ('REV00004', 4, 'Michael Brown', 5.0, 'Love this yoga mat! Perfect thickness and grip for my workouts. It also folds up nicely for easy storage.'),
      ('REV00005', 5, 'Jennifer Williams', 4.5, 'This phone charger is super convenient. No more fumbling with wires! The only downside is that the charging speed could be a bit faster.'),
      ('REV00006', 6, 'Daniel Garcia', 4.0, 'This backpack is great for travel. Its spacious enough to fit everything I need, and the straps are comfortable for carrying even when its full.'),
      ('REV00007', 7, 'Elizabeth Taylor', 5.0, 'This hardcover notebook is exactly what I was looking for. The paper quality is excellent, and the cover feels very durable.'),
      ('REV00008', 8, 'William Chen', 3.5, 'The beard trimmer works well, but its a bit noisy. Otherwise, its a good value for the price.'),
      ('REV00009', 9, 'John Smith', 4.0, 'The air purifier is definitely making a difference in the air quality of my home. I can already breathe easier.'),
      ('REV00010', 10, 'Mary Jones', 5.0, 'This stainless steel water bottle is perfect for keeping my drinks cold all day long. Its also very stylish and leakproof.');

We have successfully created our e-commerce Neon project with three tables: Orders, Products, and Reviews. We’ve also seeded the database with sample data.

Creating a read replica for the database

Neon read replicas are dedicated instances for handling read-only operations on existing data. Unlike traditional methods, Neon doesn't copy data. Instead, Neon's architecture routes read requests to a single source. Adding a read replica to a Neon project does not require additional storage.

Creating a read replica is just adding a read-only compute endpoint to a Neon branch. You can create multiple read replicas per branch. Connecting to a read replica is the same as connecting to any branch, but you use a read-only compute endpoint instead of a read-write one.

In the Neon Console, select Branches. Select the branch where your database resides and click on Add. You’ll be prompted with a Create Compute Endpoint dialog. Select Read-only replica as the Compute type and click on Create.

Note: You can specify the Compute size options by configuring a Fixed-Size compute with a precise amount of vCPU and RAM (the default) or choosing a minimum and maximum compute size to enable autoscaling. Additionally, you can adjust the Suspend compute after a period-of-inactivity parameter, determining how long your read-only compute will remain inactive before being automatically suspended. The default setting is five minutes.

---

We have successfully created a read-only replica for our database. To view the read replicas for a branch, select Branches in the Neon Console and select the branch where you created the replica. Your read replicas are identified in the Type field under the Computes header. Read replicas have a R/O value instead of an R/W value.

Retrieving the read replica connection string

Connecting to a read replica in a Neon project is similar to connecting to any branch. The only difference is that we use a read-only compute endpoint instead of a read-write one. The read replica connection string will connect our read replica to Metabase, enabling us to perform analytics on our replica data without affecting the main data.

To get the read replica connection string, select Branches on the Neon Console, and on the branch where you created the read replica, click on the ellipsis menu and click on Connect like so:

You’ll be prompted with a modal. Copy the connection string and save it somewhere. We will need it to connect our read replica to Metabase.

A PostgreSQL connection string is similar to the following: postgresql://neondb_owner:************@ep-silent-sound-n9ittsn1.us-west-2.aws.neon.tech/neondb?sslmode=require.

Connecting your read replica to Metabase

Metabase is an open-source business intelligence (BI) tool that allows users to explore, analyze, and visualize data from various sources. We are going to leverage Metabase to visualize our Neon read replica database.

To add a database connection, click on the settings icon in the top right of the Metabase dashboard, navigate to Admin settings, click on Databases, and then click Add a database. We will be prompted with a Metabase's setup field. Fill out the field with the connection string from the Neon console. The string can be broken down like so:

    # connection url breakdown: postgres://[user]:[password]@[neon_hostname]/[dbname]
    DATABASE TYPE='PostgreSQL'
    DISPLAY NAME='PostgSQL database' # The display name for the database in the Metabase interface
    HOST='neon_hostname' # eg 'ep-silent-sound-n9ittsn1.us-west-2.aws.neon.tech'
    PORT='5432' # default postgress port
    DATABASE NAME='dbname' # eg 'neondb'
    USERNAME='user' # eg 'neondb_owner'
    PASSWORD='password' # eg 'JOrmPQz34tAf'

Set the SSL to require. This ensures that your connection to the Neon database is encrypted for secure data transmission. Neon requires that all connections use SSL/TLS encryption.

After filling in the details, click Save. If you encounter errors while adding the Neon read replica database to Metabase, check out the Metabase documentation on adding a PostgreSQL database.

Performing analysis on Metabase

We can analyze the data now that we have successfully added our read replica database to Metabase.

On the Metabase dashboard, navigate to Browse data. This will show us all the databases we have created or added. Click on the PostgreSQL database, which we just added. We can now see the Orders, Products, and Review tables we created on the Neon console.

One thing to note is that this isn’t the actual database but a replica of the database with read-only access. Now, we can query, analyze, and do anything else with this data without affecting the original database. This is the key aspect of the read replica feature.

Let's analyze the total sum of the order by order date. We can achieve this by clicking on the Orders section and selecting the Order Total field. Finally, applying the Sum over time function will give us the desired aggregated daily order totals.

The read replica database stays in sync with the primary database. It is also asynchronous, meaning it is updated with changes made by a read-write compute as soon as those changes are processed by Safekeepers and Pageservers.

Conclusion

This tutorial explores creating read replicas in Neon, a serverless PostgreSQL solution. Read replicas offer a dedicated environment for data analysis without compromising production database performance. Unlike traditional methods, Neon doesn't copy data but efficiently routes read requests.

We built a sample e-commerce project with Neon and created a read replica. We then connected it to Metabase, a BI tool, for data exploration.

Sign up for a Neon account today and leverage the read replica feature to scale your applications and perform in-depth analytics seamlessly.