Today I’m going to share one of my real-time experiences when I was working on a project.
Now, what I’m about to describe is one of the most challenging cases when it comes to databases.
We were using NodeJS with Sequelize ORM. The project had a heavy database (approx. 10 -12 GB) in the initial phase, and approx. 70% of the project database data was common for all regions/instances. Even the tables were tightly coupled as lots of relations were present b/w tables.
So, the requirement was to set up multiple instances of the project in different G-cloud Regions/Geolocation. How did we proceed with it?? See below-
Now that you understand the situation, let me tell you how did we deal with it-
We cloned the master database into 2 different Sequelize instances, i.e. master DB and cloned DB, and then refactored all APIs/code conditionally to fetch data from different servers
But what about the table’s associations as I already mentioned tables were tightly coupled, and we are using lots of associations (one-to-one, one-to-many, many-to-one, many-to-many)?
When we have a single DB, we can easily use includes (in Sequelize) or use join(in raw query) to fetch data from the child table with the help of a forging key, but when we have 2 different database servers then we can’t use joins b/w 2 different DB server.
What we needed to do was-
After considering all these points, what do you think is the best approach to handle this case? Well, we will discuss here a solution that takes far less effort. Let’s go then-
For this scenario, we will use a foreign-data wrapper, PostgreSQL support multiple foreign-data wrappers to connect with remote/external database servers, as we are using PostgreSQL database, so we will use POSTGRES_FDW extension provided by PostgreSQL.
POSTGRES_FDW extension allows us to connect with remote PostgreSQL server and helps to fetch data from remote tables same as we do with a local server without any extra subquery, you can easily query from remote tables and also apply joins b/w local tables and foreign tables.
To configure POSTGRES_FDW,
CREATE EXTENSION postgres_fdw; CREATE SERVER fdw_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'localhost', dbname 'demo2', port '5432'); CREATE USER MAPPING FOR postgres SERVER fdw_server OPTIONS (user 'postgres', password 'postgres'); IMPORT FOREIGN SCHEMA public FROM SERVER fdw_server INTO public;
Let’s take an example, we have 1 database containing 2 tables (existing situation)
Now as per our requirement, we need to store common data in 1 DB and the client specifies data in another DB, so we need to create 2 different database servers (master DB server and client DB server)
CREATE DATABASE client_db; CREATE TABLE IF NOT EXISTS public.users ( id uuid NOT NULL, name character varying(255) NOT NULL, email_id character varying(255) NOT NULL, status boolean DEFAULT false, CONSTRAINT users_pkey PRIMARY KEY (id)); INSERT INTO public.users(id, name, email_id) VALUES ('3a79173b-7357-4fde-af46-1851ab7c908a', 'Om Sharma', 'firstname.lastname@example.org'), ('3a79173b-7357-4fde-af46-1851ab7c908b', 'Mahendra Sharma', 'email@example.com');
CREATE DATABASE master_db; CREATE TABLE IF NOT EXISTS public.user_licenses( id uuid NOT NULL, count integer DEFAULT 0, user_iduuid, expiration_date timestamp with time zone, status boolean DEFAULT false, CONSTRAINT user_licenses_pkey PRIMARY KEY (id)); INSERT INTO public.user_licenses(id, count, user_id) VALUES ('4062fb3d-794a-4590-ac9a-9d15f8b0ee12', 10,'3a79173b-7357-4fde-af46-1851ab7c908a'), ('4062fb3d-794a-4590-ac9a-9d15f8b0ee13',4,'3a79173b-7357-4fde-af46-1851ab7c908b');
Now, we need to configure and connect the master database with the child database using postgres_fdw extension.
CREATE EXTENSION postgres_fdw; CREATE SERVER fdw_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'localhost', dbname 'master_db', port '5432'); CREATE USER MAPPING FOR postgres SERVER fdw_server OPTIONS (user 'postgres', password 'postgres'); IMPORT FOREIGN SCHEMA public FROM SERVER fdw_server INTO public;
Now we are done with configurations, you can verify the changes by running this query-
You can also try some other queries to verify connections-
And this was how we were able to deal with this challenge.
For more details, you can check this demo git repository.
Thanks for reading.😊 Hope you found the blog insightful. And if you did, don’t forget to hit some claps.😃