How we handle the growth of our largest tables without breaking our applications
JULO is an Indonesian fintech company that was established in 2016. Along with the growth of the company, the data stored on our database have also grown rapidly and this has given us some big challenges lately:
- Application bottlenecks caused by slow queries
- Obsolete data cannot be removed, leading to bloated table sizes due to increasing dead tuples, we needed unnecessarily larger amount of disk space.
- Overloaded main database and increasing cost of high performance disks.
On weekly performance sync-ups, I had seen the number of alerts of slow API responses and high load on the database high increasing month by month. I then started thinking about:
- Bad clients experience on long requests affected by slow queries
- And a nightmare 😱😭 if we end up hitting the limit on ECS instance spec limitation
We need to do some actions before it becomes worst!
A brief overview of our tech stack
JULO’s backend stack consists of:
- Python, Django, and Django Rest Framework on the application layer
- While the database layer relies on Django ORM and self-managed PostgreSQL separated by schemas.
Troubleshooting tools
When issues start to happen, we rely on a few monitoring tools to identify the root cause. Two of them that we rely a lot on for tackling the challenges with large tables are Datadog and Grafana backed with VictoriaMetrics.
With Datadog, we make us of their APM for identifying slow queries from the application layer. Specifically for managing the database, we created some custom dashboards. For example, for monitoring the size of the top 25 biggest tables queried from Datadog.
SELECT
nspname || '.' || relname AS table_name,
pg_total_relation_size(C.oid) AS table_size
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
AND C.relkind <> 'i'
AND nspname !~ '^pg_toast'
ORDER BY pg_total_relation_size(C.oid) DESC
LIMIT 25;Whereas with Grafana, it is responsible for monitoring system resources like CPU, memory, cache, disk, and I/O on the databases.
Solution workflow
At one point, after a hard look at the monitoring tools, we decided to do something about the increasing instability problems from the data storage point of view, without affecting development and business flows. Along with other engineers at JULO, we came up with a solution to migrate some data to multiple databases based on data use cases, as well as implementing table partitioning.
We broke it down into phases.
- Preparation and Synchronization Data did on separately
- Check data consistency requires around 2–5 mins of downtime
- Migration is implemented as backend release like usual
We have confidence with these little effort and no major code changes, we could solve our problem. And we did!
In this article, I will walk us through the details of migrating one of these tables, a table of 300GB, named shortened_url.
Preparation
- Create new database
- New PostgreSQL 12 as the destination DB for backend compatible and other improvements in indexing, partitioning, etc.
- Install pg_partman — PostgreSQL extension for table partitioning.
2. Table on destination database
On this step, we created a parent table partitioned by a timestamp field, cdate with indexes, sequence same with source table
CREATE TABLE shortened_url (
…
) PARTITION BY RANGE (cdate);
ALTER TABLE ONLY shortened_url REPLICA IDENTITY FULL;
ALTER TABLE shortened_url OWNER TO ops_server;
CREATE INDEX shortened_url_short_url_idx ON ops.shortened_url
USING btree (short_url text_pattern_ops);
CREATE SEQUENCE shortened_url_shortened_url_id_seq
AS integer
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER SEQUENCE shortened_url_shortened_url_id_seq
OWNED BY shortened_url.shortened_url_id;
ALTER TABLE ONLY shortened_url ALTER COLUMN shortened_url_id
SET DEFAULT nextval('shortened_url_shortened_url_id_seq'::regclass);Because unique indexes (including primary keys) cannot be created on a natively partitioned parent unless they include the partition key, pg_partman helps to manage this by using a template table to manage properties that currently are not supported by native partitioning.
For this scenario, we are going to manually create the template table first shortened_url_template so that when we run create_parent() the initial child tables that are created will have a primary key. If you do not supply a template table to pg_partman, it will create one for you in the schema that you installed the extension to.
However, properties you add to that template are only then applied to newly created child tables after that point. You will have to retroactively apply those properties manually to any child tables that already existed.
CREATE TABLE shortened_url_template (LIKE shortened_url);
ALTER TABLE ONLY shortened_url_template
ADD CONSTRAINT shortened_url_pkey PRIMARY KEY (shortened_url_id);
ALTER TABLE ONLY shortened_url_template
ADD CONSTRAINT shortened_url_short_url_key UNIQUE (short_url);
-- oldest data from origin DB - table shortened_url was on 2019–04–09 21:57:38
SELECT partman.create_parent(
'shortened_url', 'cdate', 'native', 'monthly', p_premake:='24',
p_start_partition:='2019–04–09 00:00:00',
p_template_table:= 'shortened_url_template');Synchronization data
After the preparation of partitioned table shortened_url without data yet. The next step we will sync data of shortened_url from source — non partition table partitioned table by using aData synchronization service on Alibaba Cloud.
- On Alibaba cloud console , choose Data Transmission Service then go to Data Synchronization
- At Synchronization Tasks page , choose region and the source and destination instances
- On Source object — choose the table name as the object to sync
- After job initialized, it will run completely both FULL and INCREMENTAL stages to successfully sync the data from source to the destination.
Check data consistency
For checking data consistency , we used a comparison methods:
- Count of total row on the primary key, shortened_url_id
- Content of last 1 million records on shortened_url as a hash string by running the following query
select
md5(string_agg(hash, '|')) as hash
(select count(shortened_url_id) from shortened_url) as total_row
from (
select md5(shortened_url::text) as hash
from shortened_url
order by shortened_url_id desc
limit 1000000
) subquery;Migration
- Update sequence key
One of the limitations of Data Synchronization is sequence keys not being synchronized. Therefore, we need to update it manually by:
- Checking the last value on Source database
SELECT last_value FROM ops.shortened_url_shortened_url_seq;- Then updating the sequence value on destination database
SELECT setval('ops.shortened_url_shortened_url_id_seq'::regclass, <value>);2. Routing new data to the new DB on backend release
Django’s support for interacting with multiple databases. If you want to interact with multiple databases, you’ll need to take some additional steps
- In settings.py snippet defining two databases:
DATABASES = {
'default': {
'ENGINE': 'juloservice.core.customized_dbconn',
'NAME': os.getenv('PG_MAIN_NAME'),
'USER': os.getenv('PG_MAIN_USER'),
'PASSWORD': os.getenv('PG_MAIN_PASSWORD'),
'HOST': os.getenv('PG_MAIN_HOST'),
'PORT': os.getenv('PG_MAIN_PORT'),
}
'logging_db': {
'ENGINE': 'juloservice.core.customized_dbconn',
'NAME': os.getenv('PG_LOGGING_NAME'),
'USER': os.getenv('PG_LOGGING_USER'),
'PASSWORD': os.getenv('PG_LOGGING_PASSWORD'),
'HOST': os.getenv('PG_LOGGING_HOST'),
'PORT': os.getenv('PG_LOGGING_PORT'),
},
}- Now we need to handle routing by sending queries of shortened_url to logging_db
class LoggingDbRouter:
route_db_tables = {
'shortened_url',
}
db = 'logging_db'
def db_for_read(self, model, **hints):
if model._meta.db_table in self.route_db_tables:
return self.db
return None
def db_for_write(self, model, **hints):
if model._meta.db_table in self.route_db_tables:
return self.db
return None
def allow_migrate(self, db, db_table, model_name=None, **hints):
if db_table in self.route_db_tables:
return self.db
return None- Finally in settings.py we added the following ( path.to. with the actual Python path to the module LoggingDbRouter where the routers are defined).
DATABASE_ROUTERS = ['path.to.LoggingDbRouter']The Result?
When we released in production on December 2022, we achieved some improvements:
- Query Duration report by datadog APM:
- The P95, P90, P75 Latency reduced from >3ms to ~1.6ms
- The P50 latency latency reduced from ~1.45ms to 1.1ms
2. Obsolete data can be easily applied TRUNCATE/DROP monthly child table without bloated table worries.
3. Django support of multiple databases opened the path for us to start separating data types to multiple database starting from default and logging_db.
Key takeaways
- Understand the content and the use case of the tables to see whether they are suitable to be partitioned tables and/or to be separated into separate databases.
- Using Partman for creating and managing both time-based and serial-based table partition sets saved us time and effort to create and maintain long scripts to partition tables.
- Make use of your cloud platform’s tools, like we use Alibaba Data synchronization service to synchronize data between databases.
- Take advantage of features of application framework to deal with databases, like using Django multiple database to connect to multiple DBs.
