Supabase 云服务与本地部署指南及 PostgreSQL 高级特性实战

Database -> Extensions -> postgis（选择 public）

安装 postgis 插件后，PostgreSQL 就有了存储地理位置信息的能力，可以把 PostgreSQL 变成一个性能顶尖的企业级地理信息系统数据库。

创建一个表用来存储城市里面的地理位置信息：

CREATE TABLE city_map (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    geom GEOMETRY(Geometry,4326)
);

插入地理位置相关的测试数据：

INSERT INTO city_map (name, geom) VALUES 
('人民公园', ST_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 0, 0 0))',4326)),
('护城河', ST_GeomFromText('LINESTRING(-5 5, 15 5)',4326)),
('电话亭', ST_GeomFromText('POINT(12 8)',4326));

使用插件提供的函数来计算人民公园的面积：

SELECT name, ST_Area(geom) FROM city_map WHERE name = '人民公园';

使用插件提供的操作符来计算两个地理位置之间的距离（查找三个距离电话亭最近的地理元素）：

SELECT name FROM city_map ORDER BY geom <-> (SELECT geom FROM city_map WHERE name = '电话亭') LIMIT 3;

远程连接操作数据库（Navicat）

Supabase 还提供了远程连接的方式操作数据库，点击 connect，Supabase 提供了一个远程连接的地址，可以接入到任意一个数据库的客户端，或者后端代码中。

使用直连模式 direct connection 是不支持 IPv4 的，如果网络只能使用 IPv4 的话请选择 Session pooler。

在 Navicat 中的配置信息如下：密码填写创建 Supabase 项目时候的密码。

数据库连接成功。

Supabase SDK：前端直连后端

Supabase 可以将任何一个前端框架（React, Vue, App）用一行代码接入后端。通常配合鉴权功能（Authentication）使用。

创建一个 Vue 前端工程体验一下 Supabase 的 SDK 模式：

npm create vue@latest 
cd vue-project npm install npm run dev

接下来把 Supabase 的 SDK 接入到这个前端工程，安装 Supabase SDK：

npm install @supabase/supabase-js

测试 SDK，在 App.vue 文件中添加如下内容，正确填写自己的 Url 和 Key：

import { createClient } from '@supabase/supabase-js'
const supabaseUrl = 'https://vgihfcuijwcvelpeuljb.supabase.co'
const supabaseKey = process.env.SUPABASE_KEY
const supabase = createClient(supabaseUrl, supabaseKey)

在 Supabase 官网中说明了这个 key 在浏览器中使用是安全的，RLS 保证了我们即使在前端跟数据库交互也能确保数据的安全。

在 const supabase = createClient(...) 下面写如下面的代码（输入自己的邮箱和密码）：

await supabase.auth.signUp({ email: '[email protected]', password: 'test' })

重新启动程序：npm run dev

用户登录只需要把注册的代码改成如下形式：

const { data: signInData, error: signInError } = await supabase.auth.signInWithPassword({ 
    email: '[email protected]', 
    password: '781206lyh' 
})
console.log(signInData)

在控制台可以看到打印出来的用户信息。

RLS 行级安全策略

RLS 行级安全策略 是 PostgreSQL 的原生功能（约束哪些数据可以被哪些用户修改或读取）。

RLS 实践如下： Table Editor -> New tables

首先创建一个用户信息表 user_info，添加需要的字段：

需要将用户信息表和系统表关联起来，添加一个字段 user_id，类型选择 uuid。

添加外键：将 user_id 与 auth（系统表 — Authentication）里的 users（用户表）关联起来。

在 Authentication 中新建一个用户（Add user），这样系统里边就有两个用户了。

回到业务表 Table Editor 填写用户的基础信息 Insert： 填写 age, first_name, last_name 信息，其中 user_id 选择 select record 在选择不同用户的字段。

现在希望只让某个用户查看并且修改属于自己的记录，而不能去查看或者修改别人的记录。因此需要给业务表配置行级安全策略： Add RLS policy -> Create policy -> select -> Enable users to view their own data only (登录用户只能读取属于自己的数据)。

现在可以把查询语句直接写到前端代码里边，从 user_info 表中查询所有 first_name 字段值为 tech 的记录。 select('*') 表示返回匹配记录的所有字段。

const { data, error } = await supabase.from('user_info').select('*').eq('first_name', 'tech')
console.log('数据:', data)
console.log('错误:', error)

运行后数据被查询出来。 将 first_name 换成其他人的测试：数据变为空。

在控制台 -> network -> Headers 中的 Authorization 有一个 bearer，包含 JWT token（当前登录用户的令牌），这个令牌和后面的行级安全策略匹配才能查询到数据。

本地 / 私有部署

如果不想使用云服务，可以通过 Docker 自托管。

将 Docker Desktop 启动起来后，在一个新建文件夹中输入下面的命令：

# Get the code
git clone --depth 1 https://github.com/supabase/supabase
# Make your new supabase project directory
mkdir supabase-project

手动将 supabase -> docker 中的文件都复制到 supabase-project 中。

创建 .env 文件，将 .env.example 文件复制。同时需要修改 .env 文件中的前端端口，需要设的大一些。

接下来拉取镜像：执行下面的命令

# Switch to your project directory
cd supabase-project
# Pull the latest images
docker compose pull

把容器都启动起来：

docker compose up -d

访问端口（http://localhost:18000）后需要填写账号和密码，可以在 .env 中查看：

#账号 supabase
#密码 this_password_is_insecure_and_should_be_updated

PostgreSQL

下载安装

下载地址：https://www.enterprisedb.com/downloads/postgres-postgresql-downloads

没有复杂配置，都直接 next 即可。其中 PostgreSQL 的默认端口号：5432。

在下载过程中需要对数据库进行用户密码设置，完成后点击 -> next。

浏览文件路径及数据库信息，点击下一步 -> next。

如果在安装界面没有勾选 Stack Builder（可视化界面），可以点击安装目录中的 stackbuilder.exe 来进行安装。

环境变量配置： 将 PostgreSQL 安装目录中的 bin 路径复制到系统变量的 Path 变量中。

在用户变量中设置 PG_HOME 和 PGDATA。

测试是否安装成功。

登录指令格式为：psql -U 用户名 -d 数据库名 -h 主机地址 -p 5432 基础登录（默认用户 postgres，默认数据库 postgres）

运行如下命令：

psql -U postgres -p 5432

丰富的内置数据类型 + 自定义类型

PostgreSQL 被定义为'开源对象关系型数据库'（Open Source Object-Relational Database），兼具传统关系型数据库的严谨性和面向对象的灵活性，从根源上解决了很多开发痛点。

连接工具使用（Navicat）

创建新连接，选择 PostgreSQL，点击下一步。端口默认 5432（需在服务器防火墙开放 5432 端口），密码为刚才设置的 postgres 密码。

PostgreSQL 是'数据库 → schema → 表'三级结构，而 MySQL 只有'数据库 → 表'两级结构，schema 相当于数据库内的'子目录'，方便管理多组表。

Navicat 对 PostgreSQL 的 Schema 做了'默认隐藏'处理。为了让习惯 MySQL 的用户快速上手，Navicat 会自动展开默认的 public Schema，并将其下的对象直接展示。

PostgreSQL 内置上百种数据类型，覆盖日常开发所有场景，甚至包含很多 MySQL 没有的特有类型：

• 网络相关：CIDR（存储网段）、inet（IP 地址）、macaddr（Mac 地址），支持直接对网段进行包含查询（用 >> 操作符）。

示例：创建存储网段的表，插入网段数据后，可直接查询包含某个子网的所有网段。

创建一个 network_segments 的表，用于存储网络网段信息。SERIAL 是 PostgreSQL 的伪数据类型，本质上是一个自增的 INTEGER，会自动创建一个序列来生成唯一的整数值。PRIMARY KEY 表示该列是表的主键，用于唯一标识每一条记录，且不允许为空。

CIDR 是 PostgreSQL 特有的网络地址类型，专门用于存储 IPv4 或 IPv6 网段。

CREATE TABLE network_segments (
    id SERIAL PRIMARY KEY,
    segment CIDR NOT NULL,
    description TEXT
);
INSERT INTO network_segments (segment, description) VALUES ('192.168.1.0/24','本地局域网');

插入数据：

SELECT * FROM network_segments WHERE segment >> '192.168.1.128/26'::CIDR;

• 自定义类型：用 SQL 语句直接定义自定义对象类型，解决'阻抗失配'问题（避免将复杂对象压扁到二维表格）。

示例：定义 employee 类型（包含 name、age、skills 数组），再创建表时直接使用该类型作为字段，实现复杂对象的高效存储。

创建一个名为 employee 的复合数据类型：

CREATE TYPE employee AS (
    name VARCHAR,
    age INT,
    skills TEXT[]
);

创建表，使用之前定义的复合数据类型 employee。

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    employee employee
);

插入数据，构造一个复合 employee 类型的复合值，ROW() 是 PostgreSQL 复合类型构造器，ARRAY 构造文本数组，::employee 负责显示类型转换：

INSERT INTO employees (employee) VALUES ( ROW('Tech Shrimp', 30, ARRAY['Excel','English'])::employee );

表继承：模拟面向对象的多态性

PostgreSQL 支持表之间的继承关系，完美模拟面向对象编程中的继承特性，更贴合真实业务场景：

示例：创建 developers 表（存储软件开发人员），继承自 employees 表（存储所有员工），developers 表可拥有自己的独特字段（如编程语言），同时继承 employees 表的所有字段。插入 developer 表的数据，会自动同步到父表 employees 中，体现'开发人员首先是员工'的业务逻辑。

利用表继承特性，创建一个名为 developers 的子表，继承自 employees 表。

CREATE TABLE developers ( programming_language TEXT[]) INHERITS (employees);

插入数据：

INSERT INTO developers (employee, programming_language) VALUES ( 
    ROW('Rick', 30, ARRAY['Excel','Combat Magi'])::employee, 
    ARRAY['Python','Java']
);

原生支持 JSON 数据

PostgreSQL 原生支持 JSON/JSONB 数据类型（推荐用 JSONB，二进制存储，查询效率更高），可直接替代 MongoDB 处理 JSON 文档场景：

创建一个名为 message_log 的表，用于存储 JSON 格式的信息日志：

CREATE TABLE message_log (
    id SERIAL PRIMARY KEY,
    data JSONB
);

插入 JSONB 数据，记录一条完成的 API 请求日志：

INSERT INTO message_log (data) VALUES ( '{"timestamp":"2023-10-27T10:30:05+08:00","remote_addr":"192.168.1.10","request":"GET /api/v1/products?category=electronics&limit=10 HTTP/1.1","status":"200","request_data":{"user":"tech","age":15,"skill":["Python","Java"]}}' );

查询语句：

SELECT * FROM message_log WHERE data->>'remote_addr'='192.168.1.10';

是否替代 MongoDB，需结合项目实际场景，PostgreSQL 更适合 JSON 数据与关系型数据混合存储的场景。

全文检索

传统 SQL 的 LIKE 语法无法高效实现全文检索（无法使用索引，只能全表扫描），通常需要部署 Elasticsearch，但 PostgreSQL 可通过原生功能，实现高效全文检索，无需额外部署工具：

创建一个 documents 的表，用于存储文档类文本数据：

CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    content TEXT
);

批量插入多行文本内容：

INSERT INTO documents (content) VALUES 
('This guide explains how to use PostgreSQL''s powerful full-text search capabilities.'),
('Learn the basics of database systems and SQL.'),
('Explore advanced SQL features like window functions and common table expressions.');

向 documents 表中添加一个名为 tsv 的新列，数据类型为 TSVECTOR。TSVECTOR 是 PostgreSQL 中用于全文搜索的特殊数据类型：

ALTER TABLE documents ADD COLUMN tsv TSVECTOR GENERATED ALWAYS AS (to_tsvector('english', content)) STORED;

为 documents 表的全文搜索列 tsv 建立高性能索引：

CREATE INDEX idx_documents_tsv ON documents USING GIN (tsv);

全文搜索查询：

SELECT id, content FROM documents WHERE tsv @@ plainto_tsquery('english','powerful');

PostgreSQL 与 MySQL 的区别

索引设计：架构底层的本质区别

两者基础索引都基于 B+ 树（PostgreSQL 文档称 B-tree，功能等价 B+ 树），但存储架构天差地别。

1. MySQL：聚簇索引架构

主键索引为聚簇索引（数据本身按照主键顺序存放在 B+ 树的叶子节点上），叶子节点直接存储完整数据，主键查询效率极高。

二级索引叶子节点仅存主键 ID，查询需回表（二级索引查主键 → 主键索引查数据）。插入数据需按主键顺序调整，易引发数据页分裂，影响写入性能。

2. PostgreSQL：堆表 + 全二级索引架构

所有索引都是二级索引，数据独立存储在堆表中，索引叶子节点仅存堆表指针。主键索引与普通索引实现一致，无性能差异。

插入数据直接追加到堆表末尾，避免数据页分裂，写入更稳定。索引与数据分离，扩展性极强，支持 MySQL 没有的高级索引能力。

3. PostgreSQL 独有高级索引

GIN 通用倒排索引

传统索引是从行到值进行查找，而倒排索引建立从值到行的反向映射，适合 JSON、全文检索场景。

基于 JSON 的倒排索引就是把每行的词项提取出来，记录这些词项在哪行出现过。根据查询的信息，倒排索引能够迅速地定位到行号，对这些行号取一个交集。

创建 products 表：

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    attributes JSONB
);

插入商品测试数据：

INSERT INTO products (name, attributes) VALUES 
('笔记本电脑','{"brand": "Apple", "origin": "USA", "cpu": "M4"}'),
('智能手机','{"brand": "Xiaomi", "color": "black", "weight_g": 300}'),
('咖啡豆','{"color": "brown", "origin": "Colombia", "weight_g": 500}');

创建一个名为 products_attr_gin 的索引，指定索引建立在 products 表上，指定使用 GIN 类型索引，指定索引的对象是 attributes 这一 JSONB 列。

CREATE INDEX products_attr_gin ON products USING GIN (attributes);

查询 JSONB 数据：

SELECT * FROM products WHERE attributes @> '{"brand":"Apple"}';

• GiST 通用搜索树

GiST 不是具体索引，而是索引扩展框架，任何数据类型只要能够实现 GiST 要求的接口，就可以利用 GiST 框架创建索引。

GiST 索引非常适合对于非线性数据，比如几何图形，地理位置，IP 地址，时间范围等进行是否有重叠，还有距离远近的查询。

GiST 索引内部也是平衡树，但是跟 B 树不同，节点里面存储的不是具体的值，而是一个范围。

创建一个名为 project_plan 的表：

CREATE TABLE project_plan (
    id SERIAL PRIMARY KEY,
    plan_period DATERANGE NOT NULL -- DATERANGE 类型表示一个日期范围
);

插入多条日期范围数据：

INSERT INTO project_plan (plan_period) VALUES 
('[2023-01-01, 2023-01-05)'),
('[2023-01-03, 2023-01-07)'),
('[2023-01-10, 2023-01-12)'),
('[2023-01-08, 2023-01-09)'),
('[2023-01-02, 2023-01-06)');

为 DATERANGE 类型列创建 GiST 索引，创建一个名为 plan_gist 的索引，指定索引建立在 project_plan 表上，指定使用 GiST 索引类型，指定索引的对象是 plan_period 这一 DATERANGE 列。

GiST 索引可以极大地加速对 DATERANGE 数据的查询操作，例如使用 &&（重叠）、@>（包含）、<@（被包含）等操作符来检查日期范围的关系。

CREATE INDEX plan_gist ON project_plan USING GIST (plan_period);

查询与指定日期范围重叠的项目计划：

SELECT id, plan_period FROM project_plan WHERE plan_period && '[2023-01-08, 2023-01-13)'::DATERANGE;

• 部分索引（条件索引）

上面图片展示的表中有用户名，电话，active 三个字段，如果想删除一个字段，可以把 active 字段设置成 N（逻辑删除）。

有一个需求需要在电话号码这个字段上面建立索引，并且希望电话号码在表里是不重复的（加上 unique 表示唯一索引）。

现在把 ID 为 2 的用户删除掉，但是因为唯一索引的缘故，这个电话号码在表里一直被占用着，没有办法再插入一条跟之前电话号码相同的数据。PostgreSQL 应对这种场景会把这个索引变成一个部分索引，这个索引仅对 active 等于 Y 的记录生效，被逻辑删除的数据不再纳入索引的管理。

MySQL 不支持部分索引，只能使用生成列或者索引表达式的方式变通实现，在磁盘占用还有查询速度方面的表现不如部分索引。

• 表达式索引

直接对计算结果建索引。PostgreSQL 原生支持；

CREATE INDEX users_email_lower ON users ((lower(email)));
SELECT * FROM users WHERE LOWER(email)='[email protected]';

数据一致性

事务是保障数据一致性的核心机制，事务把一系列的操作作为一个整体单元，它们要么全部成功提交，要么全部失败回滚。

在 PostgreSQL 内部所有关于数据库结构的信息，比如表，表的列，表的索引等等都存储在一系列特殊的系统表里面。

• 比如 pg_class 存储了表，索引，视图等对象的信息。
• pg_attribute 存储了所有表的列的信息。

当我们执行表结构的修改语句（DDL 数据库定义语句）时，等同于往几个系统表里面修改几条数据。

建表的操作等同于往系统表里进行了一条插入，所有对表的操作都能被事务管理。

CREATE TABLE products2 (id SERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL);
SELECT * FROM pg_class WHERE relname ='products2';

可延迟约束

PostgreSQL 支持：约束检查延迟到事务提交时，可解决唯一性字段互换等'先有鸡还是先有蛋'问题。MySQL 无此功能，操作中临时违反约束会直接报错。

事务隔离级别行为差异

PostgreSQL 默认：读已提交（RC），读写行为一致，符合直觉。 MySQL 默认：可重复读（RR），存在读写快照不一致问题：同一事务内，SELECT 读旧快照查不到数据，UPDATE 却能修改当前数据。高并发下间隙锁会导致性能退化，大厂实践中通常强制改为 RC 级别。

扩展性