Apache Ignite with Posgresql

Question

Objective: To scale existing application where PostgreSQL is used as a data store.

How can Apache Ignite help: We have an application which has many modules and all the modules are using some shared tables. So we have only one PostgreSQL master database and It's already on AWS large SSD machines. We already have Redis for caching but as we no limitation of Redis is, It's not easy partial updates and querying on secondary indexes.

Our use case: We have two big tables, one is member and second is subscription. It's many to many relations where one member is subscribed in multiple groups and we are maintaining subscriptions in subscription table. Member table size is around 40 million and size of this table is around 40M x 1.5KB + more ~= 60GB

Challenge

A challenge is, we can't archive this data since every member is working and there are frequent updates and read on this table.

My thought:

Apache Ignite can help to provide a caching layer on top of PostgreSQL table, as per I read from the documentation.

• Now, I have a couple of questions from an Implementation point of view.

  1. Will Apache Ignite fits in our use case? If Yes then,
  2. Will apache Ignite keep all data 60GB in RAM? Or we can distribute RAM load on multiple machines?
  3. On updating PostgreSQL database table, we are using python and SQLALchamy (ORM). Will there be a separate call for Apache Ignite to update the same record in memory OR IS there any way that Apache Ignite can sync it immediately from Database?
  4. Is there enough support for Python?
  5. Are there REST API support to Interact with Apache Ignite. I can avoid ODBC connection.
  6. How about If this load becomes double in next one year?

A quick answer is much appreciated and Thanks in Advance.

Answer 1

in addition to alamar's answer:

2. You can store your data in-memory on many machines, as Ignite supports partitioned caches that are divided on parts and are distributed between machines. You can set data-collocations and number of backups.

3. There is an interesting memory model in Apache Ignite that allows you to persist data on the disk quickly. As Ignite Developers said a database behind the cluster will be slower than Ignite persistence because communication goes through external protocols

4. In our company we have huge Ignite cluster that keeps in RAM much more data

Answer 2

1. Yes it should fit your case.

2. Apache Ignite has persistence meaning it can store the data on disk optionally, but if you employ it for caching only it will happily store everything in RAM.

3. There are two approaches. You can do your updates on Apache Ignite (which will propagate them to PostgreSQL) or you can do your updates to PostgreSQL and have Apache Ignite fetch them on the first use (pull from PostgreSQL). The latter only works for new records as you can imagine. There is no support of propagating data from PostgreSQL to Apache Ignite, I guess you could do something like that by using triggers but it is untested.

4. There is 3rd party client. I didn't try it. Apache Ignite only has built-in native clients for C++/C#/Java for now, other platforms can only connect through JDBC/ODBC/REST and only use a fraction of functionality.

5. There is REST API and it have improved recently.

6. 120GB doesn't sound like anything scary as far as Apache Ignite is concerned.