从SQL Server过渡到PostgreSQL：理解模式的差异
从SQL Server过渡到PostgreSQL：理解模式的差异
前言
随着越来越多的企业转向开源技术，商业数据库管理员和开发者也逐渐面临向PostgreSQL迁移的需求。 虽然SQL Server和PostgreSQL共享许多数据库管理系统（RDBMS）的基本概念，但它们在处理某些结构上的差异可能会让人感到困惑，其中最显著的就是模式（Schema）的概念。
在SQL Server中，模式提供了一种灵活的对象组织方式，虽然和PostgreSQL的模式结构有一定相似性，但两者在用户、权限和对象管理的细节上存在一些重要区别。
什么是Schema（模式）？
在我们探讨SQL Server和PostgreSQL之间的区别之前，先简单定义一下“模式”。在数据库系统中，模式是数据库对象（如表、视图、索引、存储过程等）的集合。模式为这些对象提供了一个逻辑命名空间，确保每个对象在该命名空间内是唯一的。 虽然SQL Server和PostgreSQL都使用模式来组织数据库对象，但它们之间的关系以及模式在实际管理中的作用存在一些显著的区别。
SQL Server的模式方法：灵活跟用户松耦合
- 模式作为命名空间
在SQL Server中，模式提供了一种将对象组织在一起的方式，并且独立于用户账户。每个模式都可以由多个用户访问，而不像一些其他的RDBMS那样与用户一一对应紧耦合，例如Oracle。SQL Server中的模式不仅是一个逻辑分组，还通过这种方式为数据库提供了更灵活的权限管理。
例如，SQL Server中默认的dbo（Database Owner）模式就是一个常见的命名空间，几乎所有用户都可以在该模式中创建对象。此外，SQL Server允许数据库管理员为不同的功能或部门创建不同的模式，例如HR.Employees或Sales.Customers，从而使对象在同一个数据库中逻辑分离，方便管理和权限分配。
- 对象的完全限定名
在SQL Server中，表或视图的完全限定名通常使用schema_name.object_name的格式，例如dbo.Employees。这种命名方式允许数据库管理员按功能或业务部门组织数据库对象，而无需将每个模式与用户严格绑定，也就是说模式跟用户不是一对一关系，另外也可以忽略不写模式，而直接写对象名object_name。
PostgreSQL的模式方法：灵活的命名空间模型
- 模式作为命名空间
与SQL Server类似，PostgreSQL中的模式也是命名空间，但与用户账户完全解耦。PostgreSQL中的模式可以包含多个用户创建的对象，而多个模式中的对象也可以被同一用户拥有或访问。它为数据库提供了模块化的结构，让企业能够根据功能、部门或项目来分配模式。
举个例子，如果在PostgreSQL中为HR部门创建了一个模式，hr.employees表的完全限定名将反映该模式和对象的关系，类似SQL Server中的schema_name.object_name结构。不过，PostgreSQL允许更细粒度的权限管理，用户可以根据业务需求被授予对特定模式或对象的权限。
- 解耦用户和模式
在PostgreSQL中，用户和模式之间没有任何强制绑定关系。一个用户可以跨多个模式拥有对象，而多个用户也可以共享同一个模式。这种灵活性极大地提升了PostgreSQL在多用户、多部门协作中的适应能力。
PostgreSQL允许数据库管理员通过设置搜索路径来简化对象访问，避免在查询中必须总是指定模式名称，跟SQL Server一样，可以不必过度依赖用户和模式之间的绑定关系。
SQL Server和PostgreSQL模式的主要区别
- 模式与用户的关系
SQL Server: SQL Server中的模式独立于用户，用户可以跨多个模式拥有或访问对象。模式是命名空间，主要用于逻辑组织和权限控制。
PostgreSQL: PostgreSQL同样解耦了模式与用户的关系，多个用户可以拥有同一模式中的对象。比SQL Server更加灵活，允许更模块化的数据库设计。
- 模式的创建与管理
SQL Server: 在SQL Server中，模式通常是在数据库创建时自动生成（如dbo），管理员可以显式创建新的模式，并分配给不同的对象。
PostgreSQL: 在PostgreSQL中，模式可以通过CREATE SCHEMA命令创建，并允许根据需要在单一数据库中创建多个模式，为对象分组提供灵活性。
- 对象组织方式
SQL Server: SQL Server中，模式用于组织相关对象（如表、视图、存储过程等），这使得管理权限和逻辑分组变得更加简单。模式可以根据业务需求进行自定义命名。
PostgreSQL: PostgreSQL中的模式也是用于组织数据库对象的逻辑分组。
- 访问控制
SQL Server: SQL Server的访问控制通过角色和模式实现。用户可以被授予访问特定模式或数据库对象的权限。
PostgreSQL: PostgreSQL的访问控制也很灵活，支持在模式级别和对象级别进行权限管理。用户可以通过角色拥有跨模式的对象访问权限。
实际操作
- 利用搜索路径
PostgreSQL的搜索路径功能允许简化查询，避免重复指定模式名称。通过正确配置搜索路径，您可以提高工作效率。
- 使用角色进行权限管理
PostgreSQL的角色系统为跨多个模式的权限管理提供了极大的灵活性。您可以根据业务需求创建不同的角色，并将相应的访问权限分配给这些角色。
总结
从SQL Server过渡到PostgreSQL基本上没有太大的差异。在模式在SQL Server和PostgreSQL中都是用于逻辑分组。
PostgreSQL跟SQL Server一样，拥有模式灵活的特性，而且解耦了用户关系，使得数据库的管理和组织更加模块化。
参考文章
https://www.postgresql.org/docs/current/sql-createschema.html
https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/create-a-database-schema?view=sql-server-ver16
https://www.sqlshack.com/a-walkthrough-of-sql-schema/
https://www.tutorialsteacher.com/sqlserver/database-schema
https://www.postgresql.org/docs/current/ddl-schemas.html
https://www.cybertec-postgresql.com/en/what-is-a-schema-in-postgresql/