Performance Comparison Between Couchbase and Postgres

Apr 22, 2024

In this article, we have done comparison between Postgres and Couchbase on three types of Scenarios. As i have performed on this local there are different steps followed to setup the same thing.

PostgreSQL Setup:

Pull the PostgreSQL Docker image:

docker pull postgres:14.5

Run a PostgreSQL container:

 docker run --name postgres -e POSTGRES_PASSWORD=mysecretpassword -d postgres

Note: Replace mysecretpassword with your desired password.
To Execute queries within the container:

 docker exec -it bcfcaceb90be bash

If you want to execute queries outside then the container use

psql --host=localhost --username=postgres --dbname=postgres

Replace bcfcaceb90be with your container ID.
Import data into PostgreSQL:
- Create a table:

CREATE TABLE orders1 ( order_id SERIAL PRIMARY KEY, order_item_id SERIAL, customer_name VARCHAR(255) NOT NULL, customer_email VARCHAR(255) NOT NULL, order_date DATE NOT NULL, total_amount DECIMAL(10, 2) NOT NULL, city VARCHAR(255) NOT NULL, country VARCHAR(255) NOT NULL, postal_code VARCHAR(20) NOT NULL );

- Import data:

 COPY orders1 FROM '/path' DELIMITER ',' CSV HEADER;

Replace /path with the path inside the PostgreSQL container where the CSV file is located.
Create an index on the joining key:

 CREATE INDEX index_order_id ON orders(order_id);

Couchbase Setup:

Install Couchbase:

docker run -d --name db -p 8091-8096:8091-8096 -p 11210-11211:11210-11211 couchbase

Run the Couchbase container:

 docker logs db

View logs to get setup details.
Copy files from local system to the container and execute the container as mentioned above in postgres steps
Import data into Couchbase collection:

 cbimport csv -c container_id -u username -p password -b bucket_name --scope-collection-exp "scope_name.collection_name” -d file:///path -g key::%key% -t 5

Replace placeholders with actual values.
Create a secondary index:

CREATE INDEX idx_order_id ON orders(order_id);

Both databases are now set up with similar configurations. Perform a load test on join queries with 45k requests in 60 seconds. On average, observe 7.5 requests per second. Note that the p95 and p99 Response Time tend to be better in PostgreSQL compared to Couchbase. Load Test has been done using artillery tool

config:
  # This is a test server run by team Artillery
  # It's designed to be highly scalable
  target: http://localhost:8080
  phases:
    - duration: 60
      arrivalRate: 5
      rampTo: 10
      name: Warm up phase
  # Load & configure a couple of useful plugins
  # https://docs.art/reference/extensions
  plugins:
    ensure: {}
    apdex: {}
    metrics-by-endpoint: {}
  apdex:
    threshold: 100
  ensure:
    thresholds:
      - http.response_time.p99: 100
      - http.response_time.p95: 75
scenarios:
  - flow:
      - loop:
          - get:
              url: "/orders/postgres/multiDocument"
        count: 100

Comparison Table