How does this compare to having a separate Postgres schema per tenant? You'd need to make your app a bit more complicated (to set the right schema before every query), but you won't need to denormalise.

This is really a judgement call. If you are expecting volumes of tenants in the thousands, a schema per tenant is slightly crazy. I would personally only think of a schema or database per tenant if I had either customisation issues (If most of the sale is in professional services) or worse if we have data regulation issues (an Indian tenant, an EU tenant and a US tenenat would be challenging to manage the complex data transfer laws around, and it might be easier to have geographically located servers)

But unless you have few small expensive clients then I cannot see much value in multi-tenanting via schema or database. It's hard enough keeping that in mind when using Dev/UAT/prod schemas !

Schema per tenant from the application's point of view is much the same as database per tenant. The key difference is that sharing objects is easier if you have common procedures/data as you can keep objects that are not specific to each tenant in a separate schema (commonly the default schema, "public" in postgres or "dbo" in SQL Server).

Is the primary key in the hierarchical database a composite key of the full "traditional pk" lineage?

Or do you keep the actual PK on the adgroup table the traditional key (ie "adgroupid") then you just also throw customerid on adgroup (even though you will never join adgroup directly to customer) so you can shard each table by customerid?

What is the advantage of using a composite PK instead of the traditional PK so long as all ancestral ids are also stored on the table (so sharding is collocated correctly).

Yes, the primary key in the hierarchical database is a composite key of the full lineage.

The motivation for that is enforcing uniqueness. When the primary key (PK) contains the sharding key, the database can easily push down the PK constraint to the related shard and enforce the PK's uniqueness.

It might be among DBAs, but not so much among others who might be interested. You have got to see the "clever" tricks some people come up with to solve some basic db problems.

Is not clear to me how they make it. If I understood, the idea is not use a column to differentiate tenants (because costly joins) but use "hierarchical database model.". But how is that? How is the structure of the tables?