0

I'm building a SaaS which in one database I will have a large targe (something like 50milions rows), and this table will serve information for all the customers of the system.

But, that SaaS will be a multi-tenant / multi-database software, were when a client register in, we will provision his own database to access AND create a foreign_table for that large table outside of the database...

Question 1: Is is the best approach? Creating a foreign table for each customer may result in a large usage of storage of the server?

Question 2: The original table has kind 10 others indexes (most of them btree), and with the foreign_table for the customers, those indexes will work properly?

2
  • Reference to a foreign table doesn't duplicate the data; it's just a descriptor of where the data actually are. I'd be more concerned about performance of queries against the foreign table; perhaps you should try some typical queries and see if they perform well enough.
    – mustaccio
    Commented Jun 20 at 19:20
  • Also, FWIW, 50 million rows is a rather smaller sized database.
    – J.D.
    Commented Jun 20 at 23:46

1 Answer 1

0

Using a foreign table won't increase the storage space requirements. A foreign table just looks like a table, but any operation that you perform on it will be redirected to the remote table. This is quite similar to the way that a database view has no storage.

With the little information you provide, it is hard to say if your setup is good. Consider these implications of a foreign table:

  • any request on a foreign table will lead to a request on the remote database where the data reside, so if many databases have a foreign table for the samme data, that could lead to some strain on the database where the data are stored

  • any request on the foreign table leads to a query on the remote database, so you have to consider the network latency between the databases

  • if the database that holds the data is unavailable, so will be the foreign tables on all other databases

If that is a problem, you could consider using logical replication to create local copies of the table.

1
  • Thanks for all of your inputs! It will be very useful! Commented Jun 21 at 11:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.