How to Migrate From PostgreSQL to SingleStoreDB

The global Postgres community boasts tens of thousands of users and several thousand enterprises. That must say something good about the technology, right?

Postgres has become very attractive to developers, thanks to benefits like its strong support for transactions, ability to handle JSON natively and ecosystem extensibility. Moreover, the database is widely adopted — given that it is open source and available as an option on popular services like AWS RDS. SaaS applications like Instagram, FlightAware and Reddit even use Postgres.

Postgres developers looooooooove Postgres, so much so that it’s the #4 database on TrustRadius and on the DB-Engines ranking!

So, What’s the Catch?

The data-intensive world we live in today has introduced new, far more complex customer demands than ever before. SaaS providers have gone from simply providing a service to providing data as a service, even if it wasn’t originally part of their business plan. For example, take Strava: once an app to help athletes track their runs and bike rides, Strava now has a value-add analytics product to compare today’s workout to historical ones (and they can charge for it, too!).

As these demands for in-app analytics continue, developers managing these services have become hard-pressed to scale Postgres (and other databases, like MySQL). Analytics over trillions of records become quite slow on single-node systems that are not built for large scale aggregations, window functions and filters with tight SLAs.

Several providers saw an opportunity to scale Postgres to be a distributed SQL database — like CitusDB. Users could then take advantage of distributing their database across several machines and be able to partition their data cleanly. It turns out that this approach works fine if all you need is more compute for transactional queries, but the band-aid still falls off when it comes to the complex analytics. Not to mention, there have been several anecdotal reports of reliability issues with distributed Postgres.

The Good News? There’s Another Way

As discussed so far, the demands of data-intensive applications are centered around support for transactions and analytics. SingleStoreDB is the only database that supports both transactions and analytics, while still maintaining all of the great features of open source databases like Postgres! SingleStoreDB is a multi-model, real-time distributed SQL database with strong JSON support, ecosystem integration through our MySQL wire compatibility and robust support for analytics with our patented Universal Storage — and SingleStoreDB is the #1 database on TrustRadius.

Many organizations have migrated their applications from Postgres to SingleStoreDB. The majority of these migrations were completed in weeks — some in just days. Here’s a great example:

Foodics is a leader in the restaurant and point-of-sale industry. One huge differentiator for Foodics’ business is their ability to provide advanced analytics on inventory, menus and overall restaurant operations. As Foodics added more analytics features to their offering, they experienced database-level challenges including:

Ongoing service instability
Constant re-balancing of data
Low concurrency that only supported 200 users

Foodics came to SingleStore looking to improve their analytics performance on transactional data, and engineers from both teams collaborated on a two-week sprint to change their database destiny. Some of the tests included loading 10 billion rows, using complex queries with wide date ranges and leveraging SingleStoreDB’s dbbench to simulate large concurrency loads. Data loads via S3 were seamless thanks to SingleStoreDB Pipelines. After experimenting with a few different shard keys and sort keys, Foodics saw fantastic results:

A performant analytics engine (with Columnstore) to democratize data access
High concurrency to support a large number of reports being generated simultaneously
A fully managed database with low TCO that freed up engineering teams

Watch the webinar: Supercharging SaaS Applications: The Foodics Story

So How Do I Do It?

Bulk data load

In this example, we’ll use a table we’ve created in AWS Aurora MySQL and migrate it to SingleStoreDB.

AWS RDS Postgres Table:

1CREATE TABLE `scan` (2  `scan_id` bigint(20) NOT NULL,3  `package_id` bigint(20) NOT NULL,4  `loc_id` int(11) DEFAULT NULL,5  `Loc_name` char(5) DEFAULT NULL,6  PRIMARY KEY (`package_id`,`scan_id`)7) ;

Scan table in Postgres:

1select count(*) from scan;27340032

Simple export of data as a CSV:

1SELECT * from scan2INTO OUTFILE  s3 's3://data-bucket-pb/Jan13/scan.csv' 3FORMAT CSV4FIELDS TERMINATED BY ',' 5ENCLOSED BY '"'6LINES TERMINATED BY '\r\n';

Create a database and a table. Note the addition of a SHARD KEY and a COLUMNSTORE KEY in the SingleStore DDL. These will enable optimal distribution and organization of data to ensure lightning fast queries. SingleStoreDB Documentation offers advice on how to select these keys for your tables:

1create database mem_0113;2use mem_0113;3
4create table scan (5 scan_id BIGINT NOT NULL,6 package_id BIGINT NOT NULL,7 loc_id INT,8 loc_name CHAR(5),9 KEY (scan_id) USING CLUSTERED COLUMNSTORE,10 SHARD(package_id) );

Create SingleStore Pipeline to get data from S3. This is a super simple way to get data from several external sources:

1CREATE PIPELINE pipe1_scan2AS LOAD DATA S3 'data-bucket-pb/Jan13/scan.csv.part_00000'3CONFIG '{REDACTED} '4CREDENTIALS '{REDACTED} '5INTO TABLE mem_0113.scan6FORMAT CSV FIELDS TERMINATED BY ',' ENCLOSED BY '"'7LINES TERMINATED BY '\r\n'8(scan_id,package_id,loc_id,loc_name);

Start SingleStore Pipeline:

1start PIPELINE pipe1_scan

Check table for records, and we have the same # of rows as we did in Aurora:

1select count(*) from scan; --7340032

SingleStoreDB Replicate Tool

SingleStoreDB offers lightweight migration tooling for your bulk data load needs in initial migrations. This can also be used for incremental CDC after the initial load of data. These two features allow users to test out their workload on SingleStoreDB, and then have a zero-downtime cutover when moving to production. Let’s look at another example of a table in RDS Postgres, which covers the bulk data load:

AWS RDS Postgres Table:

1CREATE TABLE `scan` (2  `scan_id` bigint(20) NOT NULL,3  `package_id` bigint(20) NOT NULL,4  `loc_id` int(11) DEFAULT NULL,5  `Loc_name` char(5) DEFAULT NULL,6  PRIMARY KEY (`package_id`,`scan_id`)7) ;8 9select count(*) from scan;107340032

The scan table includes 7.3 million records.

Configuration File:

To configure the connectivity between RDS Postgres and SingleStoreDB, we simply populate two configuration files pointing to the respective databases. Below is the example of the SingleStoreDB config file (yaml):

1type: POSTGRESQL2
3host: demo.cynodgz9a7ys.us-east-1.rds.amazonaws.com4port: 54325
6database: my_pg_db7username: <redacted>8password: <redacted>9
10max-connections: 3011max-retries: 1012retry-wait-duration-ms: 100013
14replication-slots:15  io_replicate:16    - wal2json17  io_replicate1:18    - wal2json19
20-----------------------------------------------------------21
22type: SINGLESTORE23
24host: svc-1732741a-f499-467c-a722-9887d73150c1-ddl.aws-virginia-2.svc.singlestore.com25port: 330626
27username: <redacted>28password: <redacted>29
30#credential-store:31#  type: PKCS1232#  path: #Location of key-store33#  key-prefix: "memsql_"34#  password: #If password to key-store is not provided then default password will be used35
36max-connections: 3037
38max-retries: 1039retry-wait-duration-ms: 1000

Execute Replicate Command:

Now, we execute the REPLICATE command based on the configuration file previously populated.

./bin/replicant snapshot conf/conn/postgres.yaml conf/conn/singlestore.yaml

Verify that databases and tables exist in SingleStoreDB:

1select count(*) from scan; --7340032

Summary

As you can see, there are a few different ways to easily migrate your existing Postgres database to SingleStoreDB. This simple migration will elevate your database from single-node and slow, to distributed and lightning fast. Ingest data more easily, make your queries faster and improve support for concurrency with one of these migration options today.

Singlestore Helios offers $600 in free credits to get started with just a few clicks. The Singlestore Helios Engineering Team that contributed to this blog is always standing by to assist you with your MySQL migration, or any subsequent questions you may have about the platform.

Schedule your time to chat with SingleStore engineers today.