13 ms·

This is such a killer feature in PG, my new job uses it and it makes audits of our tenancy model dead simple. Coming from a SaaS company that used MySQL, we wo

This is such a killer feature in PG, my new job uses it and it makes audits of our tenancy model dead simple. Coming from a SaaS company that used MySQL, we would get asked by some customers how we guarantee we segmented their data, and it always ended at the app layer. One customer (A fortune 10 company) asked if we could switch to SQL Server to get this feature... Our largest customers ask how we do database multi-tenant and we point to our SDLC + PG docs and they go 'K'.

eastbound 4y ago
I honestly don’t understand how Oracle is still alive. Postgres has so many of these killer features. Also, I wonder how others do tenant separation, what other solutions there are.
- mritchie712 4y ago
  Legacy. If you have thousands of lines of code relying on Oracle the cost to migrate would be enormous.
  - toomuchtodo 4y ago
    Amazon has a great post on this topic. https://aws.amazon.com/blogs/aws/migration-complete-amazons-consumer-business-just-turned-off-its-final-oracle-database/ https://aws.amazon.com/blogs/aws/migration-complete-amazons-... I thought it was cool they retrained their Oracle DBAs into other roles as part of the project.
    - spacemanmatt 4y ago
      I work with a few former-Oracle DBAs in a PostgreSQL-flavored consultancy now and they are aces. All the root-cause analysis and organization skills transfer handily.
      - paulmd 4y ago
        Postgres is functionally and conceptually extremely similar to Oracle. There are a few oddities (in particular, oracle's "nulls are never in indexes" is kinda weird) but the redo log is similar to the WAL, etc. In most cases, similar approaches will perform similarly and experience pretty much transfers over with a few months of experience.
  - gav 4y ago
    Ignoring the cost, there's the risk/reward alignment you see in large enterprises. Imagine you're a new CIO. You know you're probably looking at a 3-5 year tenure at this new company and you want to lead with some big wins to set the tone and show your value. You're reviewing proposals from your senior leadership. One of the options is an Oracle migration. It could cost a million dollars to migrate, but you'd save a million dollars a year going forward. Oracle runs your mission-critical internal systems, any issues with the migration and the system you migrate to is going to cause significant financial and reputation damage. You'll have to defend this decision if anything goes wrong, i.e. you've absorbed a lot of risk but a lot less upside to you personally. What do you do? You put the proposal to the side and look for something that has a lot better upside.
    - beckingz 4y ago
      Exactly. The risk/cost profile for migrations is bad: If it goes well, decent return. If it goes poorly, catastrophic.
      - slt2021 4y ago
        lift and shift migrations dont really make sense. It would make sense to do architectural rewrite, from on-prem Oracle monolith to cloud native serverless stack for example. Digital transformation, yeah
    - kennethh 4y ago
      I worked in a bank previously and we migrated all our databases from Oracle to MS SQL Server. I think we used like 7-8 years to do it so I can understand people who are hesistant to convert. I think the advantages at the time (this was 10 years ago) was lower price for the db servers but also more people who are familiar with Sql Server compared to Oracle.
- revskill 4y ago
  Sharding is the only scalable way per my experience. The point about scalability here is, i can control the load as the data gets bigger.
- Scarbutt 4y ago
  oracle has flashback
- throwaway787544 4y ago
  They're like a tick. Very good at burrowing in and hard to remove. They have a lot of clients for whom a dozen million dollars is a drop in the bucket, and moving away is a decade-long millions-of-dollars project.
- beachy 4y ago
  Oracle has had the ability to do this for decades ("virtual private database"), so whatever is keeping them alive, it's nothing to do with this particular nifty Postgres feature.
- ibejoeb 4y ago
  PostgreSQL is a great product, but Oracle has so many more features. Even just in the query language. There's so much power embedded in just the model clause that would be all custom software in Postgres. If you buy Oracle, you should use Oracle. Like really lean into it. If you really need it, it will be worth the money. I don't like dealing with Oracle sales, but the product is killer.
e1g 4y ago
Every B2B client who asked us how we handle multi-tenancy also asked how we ensure their data is erased at the end of the contract. Using a shared database with RLS means you have to go through all DB backups, delete individual rows for that tenant, then re-generate the backup. That’s a non-starter, so we opted for having one DB per tenant which also makes sharding, scaling, balancing, and handling data-residency challenges easier.
- axlee 4y ago
  It makes BI work an absolute hellscape as well. Tradeoffs.
  - mrtesthah 4y ago
    Just make the application collect the statistics you need. Put them into a separate shared DB or use one of the many existing off the shelf SaaS collection frameworks.
- hobs 4y ago
  Agree - performance on row level security (at least on SQL Server) is terrible, sharing by database is fairly easy.
- gervwyk 4y ago
  Also second this, we even split our AWS org into an AWS account per tentant. Although, this will maybe be a problem if we have +100s of clients. But it makes onboarding and off-loading simple.
  - HatchedLake721 4y ago
    It depends on an annual contract value (ACV), doesn't it? You can't give an AWS account to every $99 p/m plan, but you can for enterprise $50-100k+ deals.
    - gervwyk 4y ago
      Yeah, that sums it up. I guess it means it can't be labelled as "multi-tenant" then..
gz5 4y ago
And PG supports layer 3 shut-down of link listeners and inbound fw ports. So you can combine the L7 tenancy with a secure networking architecture which eliminates the problems of managing firewalls and ACLs. One of the open source examples: https://youtu.be/s-skpw7bUfI https://youtu.be/s-skpw7bUfI
ksec 4y ago
Fortune Top 10 1 Walmart 2 Amazon 3 Apple 4 CVS Health 5 UnitedHealth Group 6 Exxon Mobil 7 Berkshire Hathaway 8 Alphabet 9 McKesson 10 AmerisourceBergen We can rule out 2,3,7,8 …

eastbound 4y ago

I honestly don’t understand how Oracle is still alive. Postgres has so many of these killer features. Also, I wonder how others do tenant separation, what other solutions there are.

mritchie712 4y ago
Legacy. If you have thousands of lines of code relying on Oracle the cost to migrate would be enormous.
- toomuchtodo 4y ago
  Amazon has a great post on this topic. https://aws.amazon.com/blogs/aws/migration-complete-amazons-consumer-business-just-turned-off-its-final-oracle-database/ https://aws.amazon.com/blogs/aws/migration-complete-amazons-... I thought it was cool they retrained their Oracle DBAs into other roles as part of the project.
  - spacemanmatt 4y ago
    I work with a few former-Oracle DBAs in a PostgreSQL-flavored consultancy now and they are aces. All the root-cause analysis and organization skills transfer handily.
    - paulmd 4y ago
      Postgres is functionally and conceptually extremely similar to Oracle. There are a few oddities (in particular, oracle's "nulls are never in indexes" is kinda weird) but the redo log is similar to the WAL, etc. In most cases, similar approaches will perform similarly and experience pretty much transfers over with a few months of experience.
- gav 4y ago
  Ignoring the cost, there's the risk/reward alignment you see in large enterprises. Imagine you're a new CIO. You know you're probably looking at a 3-5 year tenure at this new company and you want to lead with some big wins to set the tone and show your value. You're reviewing proposals from your senior leadership. One of the options is an Oracle migration. It could cost a million dollars to migrate, but you'd save a million dollars a year going forward. Oracle runs your mission-critical internal systems, any issues with the migration and the system you migrate to is going to cause significant financial and reputation damage. You'll have to defend this decision if anything goes wrong, i.e. you've absorbed a lot of risk but a lot less upside to you personally. What do you do? You put the proposal to the side and look for something that has a lot better upside.
  - beckingz 4y ago
    Exactly. The risk/cost profile for migrations is bad: If it goes well, decent return. If it goes poorly, catastrophic.
    - slt2021 4y ago
      lift and shift migrations dont really make sense. It would make sense to do architectural rewrite, from on-prem Oracle monolith to cloud native serverless stack for example. Digital transformation, yeah
  - kennethh 4y ago
    I worked in a bank previously and we migrated all our databases from Oracle to MS SQL Server. I think we used like 7-8 years to do it so I can understand people who are hesistant to convert. I think the advantages at the time (this was 10 years ago) was lower price for the db servers but also more people who are familiar with Sql Server compared to Oracle.
revskill 4y ago
Sharding is the only scalable way per my experience. The point about scalability here is, i can control the load as the data gets bigger.
Scarbutt 4y ago
oracle has flashback
throwaway787544 4y ago
They're like a tick. Very good at burrowing in and hard to remove. They have a lot of clients for whom a dozen million dollars is a drop in the bucket, and moving away is a decade-long millions-of-dollars project.
beachy 4y ago
Oracle has had the ability to do this for decades ("virtual private database"), so whatever is keeping them alive, it's nothing to do with this particular nifty Postgres feature.
ibejoeb 4y ago
PostgreSQL is a great product, but Oracle has so many more features. Even just in the query language. There's so much power embedded in just the model clause that would be all custom software in Postgres. If you buy Oracle, you should use Oracle. Like really lean into it. If you really need it, it will be worth the money. I don't like dealing with Oracle sales, but the product is killer.

e1g 4y ago

Every B2B client who asked us how we handle multi-tenancy also asked how we ensure their data is erased at the end of the contract. Using a shared database with RLS means you have to go through all DB backups, delete individual rows for that tenant, then re-generate the backup. That’s a non-starter, so we opted for having one DB per tenant which also makes sharding, scaling, balancing, and handling data-residency challenges easier.

axlee 4y ago
It makes BI work an absolute hellscape as well. Tradeoffs.
- mrtesthah 4y ago
  Just make the application collect the statistics you need. Put them into a separate shared DB or use one of the many existing off the shelf SaaS collection frameworks.
hobs 4y ago
Agree - performance on row level security (at least on SQL Server) is terrible, sharing by database is fairly easy.
gervwyk 4y ago
Also second this, we even split our AWS org into an AWS account per tentant. Although, this will maybe be a problem if we have +100s of clients. But it makes onboarding and off-loading simple.
- HatchedLake721 4y ago
  It depends on an annual contract value (ACV), doesn't it? You can't give an AWS account to every $99 p/m plan, but you can for enterprise $50-100k+ deals.
  - gervwyk 4y ago
    Yeah, that sums it up. I guess it means it can't be labelled as "multi-tenant" then..

gz5 4y ago

And PG supports layer 3 shut-down of link listeners and inbound fw ports. So you can combine the L7 tenancy with a secure networking architecture which eliminates the problems of managing firewalls and ACLs. One of the open source examples: https://youtu.be/s-skpw7bUfI https://youtu.be/s-skpw7bUfI

ksec 4y ago

Fortune Top 10 1 Walmart 2 Amazon 3 Apple 4 CVS Health 5 UnitedHealth Group 6 Exxon Mobil 7 Berkshire Hathaway 8 Alphabet 9 McKesson 10 AmerisourceBergen We can rule out 2,3,7,8 …