postgres=# CREATE INDEX ON fgedu_embeddings USING ivfflat (embedding vector_l2_ops);
CREATE INDEX

# 2. 插入大量向量数据
postgres=# DO $$
BEGIN
FOR i IN 1..1000 LOOP
INSERT INTO fgedu_embeddings (embedding, metadata)
VALUES (
(SELECT array_agg(random()::float) FROM generate_series(1, 128)),
jsonb_build_object(‘id’, i, ‘name’, ‘item’ || i)
);
END LOOP;
END $$;
DO

# 3. 执行向量相似度查询
postgres=# EXPLAIN ANALYZE SELECT * FROM fgedu_embeddings
ORDER BY embedding <-> ‘[0.5, 0.5, 0.5, 0.5, 0.5]’
LIMIT 5;

QUERY PLAN
——————————————————————————————————–
Limit (cost=10.00..14.00 rows=5 width=68) (actual time=0.100..0.120 rows=5 loops=1)
-> Index Scan using fgedu_embeddings_embedding_idx on fgedu_embeddings (cost=10.00..2010.00 rows=2500 width=68) (actual time=0.100..0.115 rows=5 loops=1)
Order By: (embedding <-> ‘[0.5,0.5,0.5,0.5,0.5]’::vector)
Planning Time: 0.100 ms
Execution Time: 0.130 ms
(5 rows)

max_worker_processes = 8
max_parallel_workers_per_gather = 4
max_parallel_maintenance_workers = 4
parallel_leader_participation = on

# 2. 重启服务
$ systemctl restart postgresql

# 3. 创建测试表
$ psql -U postgres

postgres=# CREATE TABLE fgedu_large_table (
id serial PRIMARY KEY,
name text,
value integer,
created_at timestamp
);
CREATE TABLE

# 4. 插入大量数据
postgres=# INSERT INTO fgedu_large_table (name, value, created_at)
SELECT ‘name’ || i, i, NOW() – (i || ‘ days’)::interval
FROM generate_series(1, 1000000) i;
INSERT 0 1000000

# 5. 执行并行查询
postgres=# EXPLAIN ANALYZE SELECT count(*) FROM fgedu_large_table WHERE value > 500000;

QUERY PLAN
———————————————————————————————————–
Finalize Aggregate (cost=12634.85..12634.86 rows=1 width=8) (actual time=100.234..100.345 rows=1 loops=1)
-> Gather (cost=12634.63..12634.84 rows=2 width=8) (actual time=100.123..100.330 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=11634.63..11634.64 rows=1 width=8) (actual time=95.456..95.457 rows=1 loops=3)
-> Parallel Seq Scan on fgedu_large_table (cost=0.00..11134.63 rows=200000 width=0) (actual time=0.023..85.345 rows=166667 loops=3)
Filter: (value > 500000)
Rows Removed by Filter: 166666
Planning Time: 0.123 ms
Execution Time: 100.380 ms
(10 rows)

postgres=# CREATE TABLE fgedu_secure_data (
id serial PRIMARY KEY,
user_id integer,
data text
);
CREATE TABLE

postgres=# CREATE POLICY user_data_policy ON fgedu_secure_data
USING (user_id = current_user::integer);
CREATE POLICY

postgres=# ALTER TABLE fgedu_secure_data ENABLE ROW LEVEL SECURITY;
ALTER TABLE

# 2. 测试行级安全
postgres=# INSERT INTO fgedu_secure_data (user_id, data) VALUES (1, ‘Secret data for user 1’);
INSERT 0 1

postgres=# INSERT INTO fgedu_secure_data (user_id, data) VALUES (2, ‘Secret data for user 2’);
INSERT 0 1

# 3. 创建测试用户
postgres=# CREATE ROLE user1 WITH LOGIN PASSWORD ‘user1_pass’;
CREATE ROLE

postgres=# CREATE ROLE user2 WITH LOGIN PASSWORD ‘user2_pass’;
CREATE ROLE

# 4. 授予权限
postgres=# GRANT SELECT ON fgedu_secure_data TO user1, user2;
GRANT

# 5. 测试行级安全效果
$ psql -U user1 -d postgres

postgres=> SELECT * FROM fgedu_secure_data;
id | user_id | data
—-+———+————————
1 | 1 | Secret data for user 1
(1 row)

$ psql -U user2 -d postgres

postgres=> SELECT * FROM fgedu_secure_data;
id | user_id | data
—-+———+————————
2 | 2 | Secret data for user 2
(1 row)