2

I have an existing relational Postgresql database. A few of the tables contain very fat blobs, they would be much better of as NoSQL Documents. This would significantly lighten our relational database.

So, we thought of moving those blob-table out into a NoSQL solution like CosmosDB or MongoDB. However there are foreign key dependencies with purely relational tables and this complicates moving those tables out into their own database.

I have found that PSQL natively supports storing Documents and can be distributed. The solutions I looked at so far are CitusData and Postgres XL. For those who used those how do they compare?

Has anyone encountered similar situations before? Did you separate out into a NoSQL database? Or has anyone partitioned their PSQL into relational and NoSQL parts? How did that go? What would you recommend to look out for in hindsight?

gaukhar
  • 182
  • 2
  • 14
  • 1
    This type of question is very broad and opinion-soliciting, with no objective answer. How you accomplish this is really up to you, regarding working with multiple databases (*polyglot persistence*), vs working within a single database (Postgres in your case). Unfortunately, off topic for StackOverflow. – David Makogon Oct 16 '17 at 17:15
  • 1
    So basically you really want SQL but call it nosql? Why? Have you tried just making it all "SQL", or at least JSON objects? What was the issue that needed to be solved by NoSQL? – Joe Love Oct 16 '17 at 18:33

2 Answers2

5

(Citus Engineer Here)

Postgres has JSONB column type which is powerful and flexible. What you can do is to keep your structural table as is and put a jsonb column for the blob data. Test this with single node Postgres and if that works for you, great!

If you have a problem with the scale of your data, i.e. memory or storage or CPU of a single machine is not enough for your workload and you cannot go bigger, then you can try scaling out with Citus or Postgres-XL.

I have no experience with Postgres-XL but Citus is pretty easy to try. There are docker images that you can use or you can create an account on Citus Cloud to try a 1-week free dev plan (it would not be suitable for benchmarking purposes).

Ahmet Eren Başak
  • 493
  • 1
  • 4
  • 14
0

Every RDBMS->NoSQL migration would require one of the two: 1. embedding some of these dependent documents into the ones that are actually queried by the user 2. referencing dependent documents by id and inferring these relationships on read.

Very typical, everyone does it every day, don't be afraid. BTW, you don't have to make a choice between Cosmos DB and MongoDB - just use Cosmos DB with MongoDB API.

alekseys
  • 321
  • 1
  • 6
  • The OP isn't asking to migrate from RDBMS->NoSQL. And it's not as simple as just embedding vs referencing. Typically the entire schema needs to be re-evaluated. – David Makogon Oct 16 '17 at 20:47
  • My comment related to this controversial line of thought: _"we thought of moving those blob-table out into a NoSQL solution like CosmosDB or MongoDB. However there are foreign key dependencies with purely relational tables and this complicates moving those tables out into their own database."_ And yes, it is RBDMS->NoSQL. – alekseys Oct 17 '17 at 00:41