<story><title>Designing a SaaS Database for Scale with Postgres</title><url>https://www.citusdata.com/blog/2016/10/03/designing-your-saas-database-for-high-scalability/</url></story><parent_chain><item><author>e1g</author><text>We serve enterprise customers, and use the &amp;#x27;separate database&amp;#x27; strategy for these reasons -&lt;p&gt;1. Stronger data isolation. Technically schemas provide the same level of isolation, but they are much harder to explain or defend during compliance audits.&lt;p&gt;2. More secure backups. Client data can be encrypted with their own key and stored as per their needs.&lt;p&gt;3. More useful backups. The frequency and retention of backups can vary to meet the SLA requirements (and costs). And we if something goes wrong, we can recover that particular customer&amp;#x27;s data in isolation without impacting the rest of the application&amp;#x2F;customers or worry about merging it in.&lt;p&gt;4. Secure data deletion. Many European customers demand that &lt;i&gt;all&lt;/i&gt; their data is securely removed upon termination. This creates a massive problem with purging backup information if everything is in the same dump.&lt;p&gt;5. Independent load &amp;amp; performance. If one customer is particularly high-load or chatty, we can move them onto a separate server where they don&amp;#x27;t impact well behaved folks.&lt;p&gt;6. Easier horizontal scalability. Just move heavy loads to their own servers + read replicas.&lt;p&gt;7. Direct access to data. Specifically, we can use BI tools that do not understand schemas and even give direct access to clients&amp;#x27; analysts.&lt;p&gt;8.Independent migration paths. Sometimes the customer&amp;#x27;s timetable does not allow them to upgrade to the newest version right now (e.g. they are in the middle of using the app for a key process this month). We can leave their account routed to the previous version in both the codebase and the data store.&lt;p&gt;Out of those, the key 3 reasons are: stronger data isolation, better backup strategy, and more predictable compliance story. But that&amp;#x27;s enterprise: even if we&amp;#x27;re widely successful, we&amp;#x27;ll have &amp;quot;thousands&amp;quot; of customers - never millions. And we can manage thousands of databases, so this architecture path is preferable to us within those boundaries.</text></item></parent_chain><comment><author>noxee</author><text>How do you handle look ups that would require cross tenant queries? e.g. each customer has multiple devices connected devices to our system and we want to know the health (is it online, is it responding, etc).&lt;p&gt;The one way I&amp;#x27;ve thought of current is either having an aggregate table in a &amp;quot;central&amp;quot; database that would be used to collect these kind of statistics. It would be &amp;quot;real-time&amp;quot; but it would be near real-time depending on the frequency of updates. The downside is you have the over head of maintaining a separate data source.&lt;p&gt;The other option was to just have the software set up to query each tenant at a time and take the performance&amp;#x2F;time hit. That&amp;#x27;s not really the best experience and probably violates the idea of data isolation.</text></comment>
<story><title>Designing a SaaS Database for Scale with Postgres</title><url>https://www.citusdata.com/blog/2016/10/03/designing-your-saas-database-for-high-scalability/</url></story><parent_chain><item><author>e1g</author><text>We serve enterprise customers, and use the &amp;#x27;separate database&amp;#x27; strategy for these reasons -&lt;p&gt;1. Stronger data isolation. Technically schemas provide the same level of isolation, but they are much harder to explain or defend during compliance audits.&lt;p&gt;2. More secure backups. Client data can be encrypted with their own key and stored as per their needs.&lt;p&gt;3. More useful backups. The frequency and retention of backups can vary to meet the SLA requirements (and costs). And we if something goes wrong, we can recover that particular customer&amp;#x27;s data in isolation without impacting the rest of the application&amp;#x2F;customers or worry about merging it in.&lt;p&gt;4. Secure data deletion. Many European customers demand that &lt;i&gt;all&lt;/i&gt; their data is securely removed upon termination. This creates a massive problem with purging backup information if everything is in the same dump.&lt;p&gt;5. Independent load &amp;amp; performance. If one customer is particularly high-load or chatty, we can move them onto a separate server where they don&amp;#x27;t impact well behaved folks.&lt;p&gt;6. Easier horizontal scalability. Just move heavy loads to their own servers + read replicas.&lt;p&gt;7. Direct access to data. Specifically, we can use BI tools that do not understand schemas and even give direct access to clients&amp;#x27; analysts.&lt;p&gt;8.Independent migration paths. Sometimes the customer&amp;#x27;s timetable does not allow them to upgrade to the newest version right now (e.g. they are in the middle of using the app for a key process this month). We can leave their account routed to the previous version in both the codebase and the data store.&lt;p&gt;Out of those, the key 3 reasons are: stronger data isolation, better backup strategy, and more predictable compliance story. But that&amp;#x27;s enterprise: even if we&amp;#x27;re widely successful, we&amp;#x27;ll have &amp;quot;thousands&amp;quot; of customers - never millions. And we can manage thousands of databases, so this architecture path is preferable to us within those boundaries.</text></item></parent_chain><comment><author>andreygrehov</author><text>&amp;gt; 8.Independent migration paths.&lt;p&gt;In this case, how is the business logic (codewise) handled across different states of databases?</text></comment>