Postgres int数组vs一个中间表，用于保存访问控制列表的用户id

Question

我即将为一个大型web应用程序实现记录级别的安全性，我正在讨论是使用一个单独的表来保存对一个对象(行)的引用和允许访问它的用户id，还是使用PostgreSql(9.4+)int数组并保存数组中的用户id列表。由于我要处理数百万张唱片，我想在性能方面做到这一点。

我认为它处理数组的方式是，对于所有用户允许的所有访问，访问列表要么为空，要么包含许多用户ID。数组的最大长度只能与公司拥有的用户一样大(尽管如果允许所有用户，也可以为null/空)。

如果有什么不同的话，后端由Django/Python提供支持，并且DB目前是PostgreSql 9.4(如果需要，我可以很容易地升级到最新版本)。

在这个用例中，什么会更具性能？数组的想法对我来说很有吸引力，因为它看起来很简单，但我不想为此付出巨大的性能牺牲。

Answer 1

我自己出去测试了一下。我创建了一个简单的用户表，它只有一个序列id，一个由10Mil行分布的记录表跨1万用户和5个帐户。记录表有一个account字段(多租户)和一个integer[]的acl列，以及一个单独的访问表，以限制对用户的访问。记录还有一个私有布尔标志确定记录对于ACL上的用户+用户是公共的还是私有的。

记录由10Mil行和随机ACL列表填充——有些是空的，有些是可变长度的价值观假设用户可以看到记录，如果：

• 它没有ACL并且不是私有的
• 它有ACL，但用户在其中

同样适用于表方法：如果用户存在于访问表中，或者如果左联接为null(未指定访问权限)并且私有标志为false，则允许访问。私有标志是根据ACL为空/没有ACL的行随机填充的。

运行下面的最后两个查询(在解释分析中)，并使用对于数组方法来说，限制似乎是一个明显的赢家。当然，使用的数据因为这两种方法并不相似，因为它是分别随机创建的两次，但我假设有了这个数行，我可以计算出一个足够接近的赢家。

如果可以的话，请批评并纠正我的测试。

-- create the intarray extension.
create extension intarray;
-- Create users table. Just an id will do fot this test
create table users (
id serial primary key
);
-- A records table. This one is controled by the ACL
create table records (
id serial primary key,
account integer,
private bool default false,
acl integer[]
);
-- access table to limit record access for users. this is the second option vs array.
create table access (
record_id integer,
user_id integer
);

-- generate 10000 users
insert into users
select from generate_series(1,10000);

-- some indexes on both methods:
create index aclindex on records using gin(acl gin__int_ops);
create index accounts on records (account);
create index useraccess on access(record_id, user_id);

-- function to geneate random acls
DROP FUNCTION IF EXISTS make_acl();
CREATE FUNCTION make_acl() RETURNS integer[] AS $$
DECLARE
acl integer[];
count integer;
rand integer;
done bool;
BEGIN
count := (trunc(random() * 9 ));
WHILE count != 0 LOOP
rand := (trunc(random() * 9999 + 1));
acl := array_append(acl, rand);
count := count -1;
END LOOP;
RETURN acl;
END;
$$ LANGUAGE PLPGSQL VOLATILE;
SELECT make_acl();
-- populate records table
insert into records(acl, account)
SELECT make_acl(), (trunc(random() * 5 + 1))
from generate_series(1,10000000);
-- set private randomly on all records without ACL, and true where acl exists
UPDATE records set private = (trunc(random() * 10 + 1)) > 5
WHERE acl is null;
update records set private = true where acl is not null;
-- populate access table
insert into access(record_id, user_id)
select  (trunc(random() * 99999 + 1)), (trunc(random() * 9999 + 1))
from generate_series(1, 10000000);
-- Select using access table
explain analyze
select records.id
from records
left join access on records.id=access.record_id
where records.account = 1
and ((records.private = true and (access.user_id = 25 or access.user_id is null)) 
or records.private = false);

-- Select using ACL array
explain analyze
select * from records
where account = 1
and ((private = true and (acl @> array [25] or acl is null)) or private = false);

结果：

中间表方法的结果：

QUERY PLAN                                                                    
-------------------------------------------------------------------------------------------------------------------------------------------------
Hash Right Join  (cost=275544.43..634615.43 rows=1933800 width=4) (actual time=1765.044..6152.780 rows=2093789 loops=1)
Hash Cond: (access.record_id = records.id)
Filter: ((records.private AND ((access.user_id = 25) OR (access.user_id IS NULL))) OR (NOT records.private))
Rows Removed by Filter: 1878666
->  Seq Scan on access  (cost=0.00..183085.23 rows=9458923 width=8) (actual time=100.433..842.632 rows=10000000 loops=1)
->  Hash  (cost=243059.43..243059.43 rows=1980000 width=5) (actual time=1662.915..1662.915 rows=2001411 loops=1)
Buckets: 16384  Batches: 32  Memory Usage: 2281kB
->  Bitmap Heap Scan on records  (cost=37065.43..243059.43 rows=1980000 width=5) (actual time=219.929..1349.931 rows=2001411 loops=1)
Recheck Cond: (account = 1)
Rows Removed by Index Recheck: 4713065
Heap Blocks: exact=50052 lossy=105473
->  Bitmap Index Scan on accounts  (cost=0.00..36570.43 rows=1980000 width=0) (actual time=210.267..210.267 rows=2001411 loops=1)
Index Cond: (account = 1)
Planning time: 0.283 ms
Execution time: 6208.045 ms
(15 rows)

阵列方法的结果：

QUERY PLAN                                                              
-------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on records  (cost=37053.90..247997.90 rows=1933853 width=48) (actual time=215.179..1769.639 rows=223663 loops=1)
Recheck Cond: (account = 1)
Rows Removed by Index Recheck: 4713065
Filter: ((private AND ((acl @> '{25}'::integer[]) OR (acl IS NULL))) OR (NOT private))
Rows Removed by Filter: 1777748
Heap Blocks: exact=50052 lossy=105473
->  Bitmap Index Scan on accounts  (cost=0.00..36570.43 rows=1980000 width=0) (actual time=205.250..205.250 rows=2001411 loops=1)
Index Cond: (account = 1)
Planning time: 0.106 ms
Execution time: 1775.743 ms
(10 rows)