Optimizations, before and after
All three designs went through one optimization pass. This page shows two sets of Docker Postgres 18 runs side by side.
The first set is from before the pass (untagged runs), and the second set is from after the pass (tagged opt).
The machine, the configurations and the pgbench settings are the same, with one run for each.
The results before and after are identical, because every per-policy checksum matches. Only the cost changed.
The design colors are the same as everywhere else: A tuple core, B policy first, C compiled model. In the charts a hollow dot is before and a filled dot is after.
The optimizations report is the written analysis. It gives the claim of each optimization and shows how the claim held up. The Postgres versions page shows the other Postgres versions. The dashboard shows the rest of the numbers.
Takeaways
Section titled “Takeaways”The code computes these takeaways from the data below.
- Biggest single win: B nesting write: 107 to 0.62 ms (170x faster). Per design, the largest gain on the key workloads is A sharing page, wide teams: 125 to 14.4 ms (8.6x faster); B nesting write: 107 to 0.62 ms (170x faster); C nesting write: 300 to 57.7 ms (5.2x faster).
- Who gained how much: A is more than 20% faster on 4 of 5 key workloads; B is more than 20% faster on 5 of 5 key workloads; C is more than 20% faster on 4 of 5 key workloads.
- RBAC did not move: 14 of 15 RBAC measurements are within 20% of their old values.
- Ranking: 6 of 11 workload classes changed order or tie pattern. The slowest design is unchanged on Parents, projection page; Parents, single-branch page; Nesting write; Seed time: parent links; Seed time: wide sharing.
- What is still expensive: nesting a team still costs 57.7 ms on C against 0.15 ms on A and 0.62 ms on B.
Key workloads
Section titled “Key workloads”Key workloads, before and after
Before: PG 18 (docker, untagged); after: PG 18 (docker, optimized) (Postgres 18.6). p50 in milliseconds on a log axis, lower is better. The sharing reads use the medium wide shape, the others the large default (1M documents).
- A Tuple core
- B Policy first
- C Compiled model
- D Per-model tables
- E Cedar
- before
- after
Takeaway. Largest changes: B nesting write: 107 to 0.62 ms (170x faster); A sharing page, wide teams: 125 to 14.4 ms (8.6x faster); B parents, single-branch page: 102 to 13.7 ms (7.5x faster); A sharing id list, wide teams: 29.1 to 4.44 ms (6.5x faster). Not moved (within 20%): A nesting write (0.15 to 0.15 ms), C check through a nested team (0.30 to 0.30 ms).
Table view
| Workload | Configuration | Design | Before p50 ms | After p50 ms | Change |
|---|---|---|---|---|---|
| Sharing: first page | medium wide | A | 125 | 14.4 | 8.6x faster |
| Sharing: first page | medium wide | B | 36.9 | 17.8 | 2.1x faster |
| Sharing: first page | medium wide | C | 10.3 | 8.20 | 1.3x faster |
| Sharing: list visible ids | medium wide | A | 29.1 | 4.44 | 6.5x faster |
| Sharing: list visible ids | medium wide | B | 11.4 | 8.41 | 1.4x faster |
| Sharing: list visible ids | medium wide | C | 4.25 | 3.41 | 1.2x faster |
| Parents, single branch: first page | large default | A | 240 | 45.6 | 5.3x faster |
| Parents, single branch: first page | large default | B | 102 | 13.7 | 7.5x faster |
| Parents, single branch: first page | large default | C | 9.26 | 4.94 | 1.9x faster |
| Nesting: nest a team | large default | A | 0.15 | 0.15 | within 20% |
| Nesting: nest a team | large default | B | 107 | 0.62 | 170x faster |
| Nesting: nest a team | large default | C | 300 | 57.7 | 5.2x faster |
| Nesting: check through a nested team | large default | A | 0.52 | 0.31 | 1.7x faster |
| Nesting: check through a nested team | large default | B | 1.14 | 0.32 | 3.6x faster |
| Nesting: check through a nested team | large default | C | 0.30 | 0.30 | within 20% |
Raw report: per-run reports (pg18-docker-*, pg18-docker-*-opt)
RBAC, the control group
Section titled “RBAC, the control group”RBAC workloads, before and after
Control group, Before: PG 18 (docker, untagged); after: PG 18 (docker, optimized) (Postgres 18.6). Large default. p50 in milliseconds on a log axis; the dots overlap when nothing changed.
- A Tuple core
- B Policy first
- C Compiled model
- D Per-model tables
- E Cedar
- before
- after
Takeaway. 14 of 15 RBAC measurements (5 workloads, 3 designs) are within 20% of their old value; 1 moved by more than that. The largest relative change is C rbac_update_one: 0.45 to 0.36 ms. 2 of 15 got slower, by at most 0.04 ms; the optimizations target sharing, parents and nesting, so RBAC is the control group.
Table view
| Workload | Design | Before p50 ms | After p50 ms | Change |
|---|---|---|---|---|
| rbac_check (large default) | A | 0.26 | 0.30 | within 20% |
| rbac_check (large default) | B | 0.26 | 0.27 | within 20% |
| rbac_check (large default) | C | 0.25 | 0.24 | within 20% |
| rbac_select_page (large default) | A | 0.76 | 0.76 | within 20% |
| rbac_select_page (large default) | B | 0.77 | 0.73 | within 20% |
| rbac_select_page (large default) | C | 0.73 | 0.67 | within 20% |
| rbac_select_count (large default) | A | 0.85 | 0.78 | within 20% |
| rbac_select_count (large default) | B | 0.85 | 0.78 | within 20% |
| rbac_select_count (large default) | C | 0.82 | 0.73 | within 20% |
| rbac_update_one (large default) | A | 0.53 | 0.47 | within 20% |
| rbac_update_one (large default) | B | 0.52 | 0.47 | within 20% |
| rbac_update_one (large default) | C | 0.45 | 0.36 | 1.3x faster |
| write_assign_role (large default) | A | 0.16 | 0.15 | within 20% |
| write_assign_role (large default) | B | 0.14 | 0.14 | within 20% |
| write_assign_role (large default) | C | 0.18 | 0.17 | within 20% |
Raw report: per-run reports
Seed time and storage
Section titled “Seed time and storage”Seed time and storage, before and after
Before: PG 18 (docker, untagged); after: PG 18 (docker, optimized) (Postgres 18.6). Top: seconds to load a phase. Bottom: cumulative size of the permissions tables (MB) after that phase. Log axes, lower is better.
- A Tuple core
- B Policy first
- C Compiled model
- D Per-model tables
- E Cedar
- before
- after
Seed time
Storage
Takeaway. Seeding: C seed time: wide sharing: 176 to 24.7 s (7.1x faster); B seed time: parent links: 121 to 44.8 s (2.7x faster); B seed time: wide sharing: 26.1 to 12.6 s (2.1x faster). Storage: B storage: wide sharing: 226 to 105 MB (2.1x faster); B storage: parent links: 763 to 378 MB (2.0x faster). A storage: parent links grew 398 to 404 MB, A storage: wide sharing grew 97.1 to 97.4 MB (a new index).
Table view
| Measure | Configuration | Design | Before | After | Change |
|---|---|---|---|---|---|
| Seed: per-document parent links | large default | A | 27.3 s | 31.0 s | within 20% |
| Seed: per-document parent links | large default | B | 121 s | 44.8 s | 2.7x faster |
| Seed: per-document parent links | large default | C | 17.1 s | 17.6 s | within 20% |
| Seed: sharing | medium wide | A | 7.75 s | 5.88 s | 1.3x faster |
| Seed: sharing | medium wide | B | 26.1 s | 12.6 s | 2.1x faster |
| Seed: sharing | medium wide | C | 176 s | 24.7 s | 7.1x faster |
| Storage after the parents seed | large default | A | 398 MB | 404 MB | within 20% |
| Storage after the parents seed | large default | B | 763 MB | 378 MB | 2.0x faster |
| Storage after the parents seed | large default | C | 182 MB | 182 MB | within 20% |
| Storage after the sharing seed | medium wide | A | 97.1 MB | 97.4 MB | within 20% |
| Storage after the sharing seed | medium wide | B | 226 MB | 105 MB | 2.1x faster |
| Storage after the sharing seed | medium wide | C | 192 MB | 192 MB | within 20% |
Raw report: per-run reports (Storage and seed cost)
Ranking changes
Section titled “Ranking changes”Ranking, before and after
PG 18 (docker) against PG 18 (docker, optimized). Best first. Designs within 20% of the best in a group are tied (=); "<" means the next group is slower.
| Workload class | Before | After | Changed |
|---|---|---|---|
| RBAC, first page large default | A=B=C 0.76 / 0.77 / 0.73 ms | A=B=C 0.76 / 0.73 / 0.67 ms | same |
| Sharing page, default teams large default | B=C<A 1.45 / 1.16 / 1.00 ms | A=B=C 1.03 / 1.00 / 0.93 ms | changed |
| Sharing page, wide teams medium wide | C<B<A 125 / 36.9 / 10.3 ms | C<A<B 14.4 / 17.8 / 8.20 ms | changed |
| Sharing id list, wide teams medium wide | C<B<A 29.1 / 11.4 / 4.25 ms | C<A<B 4.44 / 8.41 / 3.41 ms | changed |
| Parents, projection page large default | C<B<A 2.09 / 1.30 / 1.05 ms | B=C<A 1.33 / 1.15 / 1.05 ms | changed |
| Parents, single-branch page large default | C<B<A 240 / 102 / 9.26 ms | C<B<A 45.6 / 13.7 / 4.94 ms | same |
| Nesting write large default | A<B<C 0.15 / 107 / 300 ms | A<B<C 0.15 / 0.62 / 57.7 ms | same |
| Check through a nested team large default | C<A<B 0.52 / 1.14 / 0.30 ms | A=B=C 0.31 / 0.32 / 0.30 ms | changed |
| Storage: parent links large default | C<A<B 398 / 763 / 182 MB | C<A=B 404 / 378 / 182 MB | changed |
| Seed time: parent links large default | C<A<B 27.3 / 121 / 17.1 s | C<A<B 31.0 / 44.8 / 17.6 s | same |
| Seed time: wide sharing medium wide | A<B<C 7.75 / 26.1 / 176 s | A<B<C 5.88 / 12.6 / 24.7 s | same |
Values under each ranking are A / B / C.
Takeaway. 6 of 11 workload classes changed order or tie pattern. Changed: Sharing page, default teams; Sharing page, wide teams; Sharing id list, wide teams; Parents, projection page; Check through a nested team; Storage: parent links. Now a tie between all three designs: Sharing page, default teams; Check through a nested team.
Same speedups on 16, 17 and 18?
Section titled “Same speedups on 16, 17 and 18?”The same speedups on every Postgres version?
Speedup = before p50 / after p50, Docker, key workloads. Page main version is 18. Below 1.0x means slower after.
| Workload | Design | PG 16 | PG 17 | PG 18 |
|---|---|---|---|---|
| Sharing page, wide teams medium wide | A | 6.7x 98.6 to 14.7 ms | 5.1x 104 to 20.2 ms | 8.6x 125 to 14.4 ms |
| Sharing page, wide teams medium wide | B | 2.8x 33.6 to 11.9 ms | 1.8x 33.8 to 18.9 ms | 2.1x 36.9 to 17.8 ms |
| Sharing page, wide teams medium wide | C | 1.1x 9.40 to 8.89 ms | 1.2x 9.59 to 8.27 ms | 1.3x 10.3 to 8.20 ms |
| Sharing id list, wide teams medium wide | A | 5.4x 24.5 to 4.57 ms | 3.6x 26.1 to 7.18 ms | 6.5x 29.1 to 4.44 ms |
| Sharing id list, wide teams medium wide | B | 2.0x 8.96 to 4.47 ms | 1.0x 9.02 to 9.26 ms | 1.4x 11.4 to 8.41 ms |
| Sharing id list, wide teams medium wide | C | 1.2x 4.08 to 3.40 ms | 1.2x 4.23 to 3.39 ms | 1.2x 4.25 to 3.41 ms |
| Parents, single-branch page large default | A | 5.3x 242 to 46.0 ms | 5.2x 246 to 47.0 ms | 5.3x 240 to 45.6 ms |
| Parents, single-branch page large default | B | 6.3x 95.4 to 15.3 ms | 4.7x 94.7 to 20.1 ms | 7.5x 102 to 13.7 ms |
| Parents, single-branch page large default | C | 2.0x 10.7 to 5.26 ms | 2.1x 10.5 to 5.04 ms | 1.9x 9.26 to 4.94 ms |
| Nesting write large default | A | 1.0x 0.16 to 0.16 ms | 1.0x 0.15 to 0.15 ms | 1.0x 0.15 to 0.15 ms |
| Nesting write large default | B | 150x 107 to 0.72 ms | 160x 112 to 0.70 ms | 170x 107 to 0.62 ms |
| Nesting write large default | C | 5.4x 299 to 55.0 ms | 5.3x 303 to 56.9 ms | 5.2x 300 to 57.7 ms |
| Check through a nested team large default | A | 2.2x 0.67 to 0.30 ms | 2.2x 0.69 to 0.31 ms | 1.7x 0.52 to 0.31 ms |
| Check through a nested team large default | B | 4.1x 1.30 to 0.32 ms | 4.1x 1.31 to 0.32 ms | 3.6x 1.14 to 0.32 ms |
| Check through a nested team large default | C | 1.0x 0.33 to 0.32 ms | 1.0x 0.33 to 0.32 ms | 1.0x 0.30 to 0.30 ms |
Takeaway. 9 of 15 design and workload pairs have a speedup within 30% across Postgres 16, 17, 18. The biggest spread is B sharing id list, wide teams: 1.0x on 17 against 2.0x on 16 (9.02 to 9.26 ms against 8.96 to 4.47 ms).