Hi!
This topic was already discussed (at least one time) in 2011. See [1] for details. I'd like to raise that again.
Currently, we have table in system catalog with ACL, but without TOAST. Thus, it might happen that we can't fit new ACL item, because row becomes too large for in-line storage.
You can easily reproduce this situation in psql.
create table t (col int);
\copy (select 'create user u' || i || ';' from generate_series(1,10000) i) to 'script.sql'
\i script.sql
\copy (select 'grant all on t to u' || i || ';' from generate_series(1,10000) i) to 'script.sql'
\i script.sql
Eventually GRANT statements start to raise error.
psql:script.sql:2447: ERROR: row is too big: size 8168, maximum size 8160
I understand that we shouldn't endorse users to behave like this. We should rather advise them to evade adding too many ACL items to single object by using roles. And that would be way easier to manage too.
However, current PostgreSQL behavior is rather unexpected and undocumented. Thus, I would say it's a bug. This bug would be nice to fix even if long ACL lists would work slower than normal.
In the discussion to the post [1] Tom comments that he isn't excited about out-of-line ACL entry unless it's proven that performance doesn't completely tank in this case.
I've done some basic experiments in this field on my laptop. Attached draft patch adds TOAST to all system catalog tables with ACL. I've run pgbench with custom script "SELECT * FROM t;" where t is empty table with long ACL entry. I've compared results with 1000 ACL items (in-line storage) and 10000 ACL items (out-of-line storage).
Also, I've notice performance degradation of GRANT statements themselves. 1000 GRANT statements are executed in 1.5 seconds while 10000 GRANT statements are executed in 42 seconds. In average single GRANT statements becomes 2.8 times slower. That's significant degradation, but it doesn't seem to be fatal degradation for me.
Results of pgbench are presented in following table.
Number of ACL items | -M simple | -M prepared
----------------------------+-----------+-------------
1000 (in-line storage) | 6623 | 7242
10000 (out-of-line storage) | 14498 | 17827
So, it's 2.1-2.4 times degradation in this case. That's very significant degradation, but I wouldn't day that "performance completely tank".
Any thoughts? Should we consider TOASTing ACL entries or should we give up with this?
Links: