Table of Contents
PostGraphile best practices
This guide is a work in progress. If you have ideas for best practices, please use the "Suggest improvements to this page" link above to submit them, or discuss them in #documentation on the PostGraphile Discord chat.
PostgreSQL does NOT add indexes to foreign keys by default. This isn't an
issue for the forward relation (
user_id → user), but for the reverse relation
(user → things by
user_id) it can make the lookup very expensive. Always add
indexes to your foreign keys.
If you're using RLS, it's best to enable it on every table in your database. You should at least enable it on every table in your exposed schemas.
The following are fine:
-- ✅ Fine GRANT SELECT ON users TO graphql_role; -- ✅ Fine GRANT INSERT (column_1, column_2, ...) ON users TO graphql_role; -- ✅ Fine GRANT UPDATE (column_a, column_b, ...) ON users TO graphql_role; -- ✅ Fine GRANT DELETE ON users TO graphql_role;
The following should be avoided:
-- 🛑 DO NOT USE GRANT SELECT (column_a, column_b) ON users TO graphql_role; -- 🛑 DO NOT USE GRANT INSERT ON users TO graphql_role; -- 🛑 DO NOT USE GRANT UPDATE ON users TO graphql_role;
Column-level SELECT grants cause a lot of issues:
- Reduces the number of optimisations we can do
SELECT * FROM
- Cannot use
RETURNING *(e.g. what we do in CRUD operations currently)
- Cannot pass a row type into a function (like we do for computed columns)
Table-level INSERT/UPDATE grants are not advisable because they lack the explicitness that should come from such operations.
You can get a leg up on this
The long names PostGraphile uses by default are to try and avoid people getting
naming conflicts when they run PostGraphile for the first time. Once you're more
comfortable you should move to using shorter names as it's a GraphQL best
(Scan the docs for
[RECOMMENDED], but take a moment to understand why they're
recommended and yet not enabled by default - they often require greater
knowledge of PostGraphile and your database.)
Performance reasons. Specifically, under many circumstances, functions in
plpgsql) can be inlined into the call-site and are thereby
transparent to the query planner. The PostgreSQL docs
have a lot more details.
Reason: triggers in PostgreSQL run in lexicographical order.