6

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.

  • 6
    wow, sounds hard. You know there is a channel specifically for DB questions? you might get better answers on there. – Ewan Nov 15 '18 at 20:41
  • @Evan I considered to post that at the Database Administrators/ channel, but its about the overall design. Though tightly bound to PostgreSQL implementation. – πάντα ῥεῖ Nov 15 '18 at 21:01
  • 1
    And of course, it is hard. Otherwise I wouldn't have come up with that question. – πάντα ῥεῖ Nov 15 '18 at 21:02
3

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.

  • 1
    "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." Why? The PostgreSQL optimizer promises to flatten these out anyways? – πάντα ῥεῖ Nov 15 '18 at 21:06
  • you might be ok in your case. I've had issues with unions in particular. Also its easier to pass in say a tenant id than to dynamicaly generate named views – Ewan Nov 15 '18 at 21:09
  • "just modifying the SQL statement rather than running your SQL on a view." That's one of the very crucial parts of the challenge. We don't want to do that in 1st place, but apply transparent transformations as much as possible in 1st place. – πάντα ῥεῖ Nov 15 '18 at 21:14
  • yes I can understand you have some extra challenges over and above simply creating a schema that works. I can only give vague advice from my experience of multi-tenant dbs. ive tried schemas, views, dbs and a big single db in the past on mysql and mssql. multiple dbs seems to work the best. – Ewan Nov 15 '18 at 21:21
  • 5
    Regarding splitting the data: Postgres supports table partitioning for improving performance while keeping most capabilities of a single table. – Marco Borchert Nov 16 '18 at 7:46
1

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.

  • Real tables have some disadvantages as (at least partly) mentioned: 1. Updates are harder to maintain per SCHEMA 2. NTFS is more stressed unless using table spaces for each SCHEMA 3. pg_catalog.pg_attributes is stressed with a load of redundant, unnecessary information. – πάντα ῥεῖ Nov 16 '18 at 3:29
  • 2
    No, sorry. If you have to update your schema, you’d much rather have the change isolated to a single table than the one that everyone uses. It cuts down on fragmentation, index rebuilds... – Telastyn Nov 16 '18 at 3:34
  • "that you can keep the single db small (a few gig) for a single tenant" Notice that the system isn't really like this, we don't have absolutely isolated and encapsulated tenants, they all share some master data (which is also continuously maintained). – πάντα ῥεῖ Nov 16 '18 at 3:34
  • "No, sorry. If you have to update your schema, you’d much rather have the change isolated to a single table than the one that everyone uses." That's bound to the versions of our client software we're rolling out. ATM that's done at runtime, if a program enters a specific "tenant" (sorry to abuse that term, it's not really one). So if we install a specific software version at a customers system, we already know that the table structures can be updated for the whole system. – πάντα ῥεῖ Nov 16 '18 at 3:42
  • 1
    Something that concerns me is how transactional activity will impact the system when all data is in a master table with billions of records. Updating indexes would be a bottleneck, even if you tune your queries to retrieve data quickly. Partitioned tables will help here. – maple_shaft Nov 16 '18 at 16:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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