Scalability: Millions of tables vs one big master table and millions of views

Question

We have to migrate an old (million LOC) system from SAP ADS (formerly Sybase) ISAM based (so called free tables) based system to a PostgreSQL database.

The system implements kind of multitenancy regarding putting some basic information (chancellery ID, client ID, fiscal year, etc.) just into the windows folder paths and the SQL statements use these to build table addresses with the appropriate paths for the specific table(-files).

Since SAP decided to shutdown support for the Advantage Database Server, we're forced to migrate the system to another (real) RDBMS.
We pretty much already decided to go with PostgreSQL since that would support kind of namespacing (schemata) to at least map the windows folder paths to something we could use to replicate this.

Big things to note

While I am using the term multitenancy here, it's not really like this:

1. The tenant sub information spaces share some master data which is maintained continuously.
2. The whole meta data model is bound to a specific software version that is rolled out to our customer's systems
3. We have a lot of varying customer systems in the field (i.e. we kinda lost track of all the use cases served over time)
4. Changes in the factual code are error prone regarding unexpected side effects
5. All in all it's we have a Big Ball of Mud, which doesn't leave us much space for refactoring due to overzealous efforts, but still needs features applied continuously, be it for legal requirements (tax laws changing etc.), or customer (product management) demands.

Thus the above stated things should make it clear that we

• don't want to refactor the database in a way it hits the existing factual applications
• should take a path for the database design that works well for all of our customers system installations (from the single Windows Laptop installation, up to the Corporate Windows file server system)

The major ways to Rome

ATM we're discussing two major approaches how to do this:

1. Migrating the existing ADS tables to PostgreSQL table objects as they appear in each of the folders to table instances residing in specific PostgresSQL SCHEMA instances.
2. Migrating the existing ADS tables to PostgreSQL master-table objects, and create views for them a including a mapping table and a reference key included with the master table and another table where these SCHEMA names are mapped using a unique ID.

Both of the approaches are already technically almost solved, and we're able to migrate the existing systems either way.

Though there are still points to consider which path we should take.

• Some of our architects say from their guts that having billions of rows in a single master-table would likely have a significant performance impact with the SQL statements.
• Others (like me) argue that a view/master-table model would have several benefits regarding the overall DB-design efficiency.

We're doing measurements already, but I'd like to hear some advice, maybe some experiences, or even other choices of DB-design ideas, for which of the approaches might scale better.

Some edge data:

• The overall system currently maintains ~1000 basic table structures (some of them with +400 column fields).
• We have customers with multitenancy paths up to 10.000.
• Some of those (master-)tables might need to keep up rows in the range of billions.
• Since indexes are (seemingly) cheap in ADS, there are sometimes many of these used with the existing ISAM tables.

What we already noticed:

• The apprehended performance impact for SQL statements applied to master tables through views vs single table objects is way from linear scaling going with the amount of data rows (I've been measuring that using exemplary EXPLAIN ANALYZE server side).
• Using tables per SCHEMA the pg_catalog tends to grow big, especially the pg_catalog.pg_attributes table. Any operations done with these (including the PostgreSQL query optimizer) might be hit by the sheer big data content.
• Currently we use a single tablespace for all of the tables. While we could organize tablespaces for each virtual directory path, this seems to overcomplicate the DB-design though and might even turn up other problems.
• As mentioned before, single tablespaces for such amount of relations doesn't seem to scale well with PostgreSQL and an underlying NTFS windows filesystem.
• Regarding the underlying NTFS system, we should consider that there will be a minimum block size reserved for each file created, and PostgreSQL creates several files for any pg_class relation object, which includes tables, indexes, blob fields, etc.

May be what I've offered with my observations here is a bit biased, but I'd like to ask about either alternatives, or absolute no goes for either of those models we're discussing.

---

_{Since my boss is a wise guy, and he knows that I am one of the architects supporting the view-model, he gave as a homework to me, to give them information what are the cons actually.}

Answer 1

Ok heres my vague architectual advice.

I think you are correct in equating the folders to indexes on a single table rather than schemas.

But accessing a single table through views can occasionally be problematic. I would always advise just modifying the SQL statement rather than running your SQL on a view.

Secondly, the shear volume of data could also be a problem. You may find you have to split it up somehow to remain performant.

I would suggest splitting it by creating more than one database with the same schema and splitting the data by tenant and/or date.

This has the advantage of being able to move the data to physically different hardware if required. The disadvantage of course is that you wont be able to query across the splits as effectively.

Answer 2

Make real tables. Then you can actually enforce foreign key constraints, you can actually have indexes that apply only to the subset of entities that actually use them, you can cut waaaay down on contention under concurrent use. You can actually enjoy the benefits of a RDMS...

*Unless* there is some clear and independent tenancy where you can shard the data in such a way that you can keep the single db small (a few gig) for a single tenant and your usage patterns are such that the few gigs of data has a few useful indexes along with relatively few writes.