AcuGIS

Think of Postgres permissioning a series of independent gates (authentication > database > schema > object > row): 1. can the role even connect? (pg_hba.conf) 2. can the role use this database? (CONNECT privilege) 3. can the role see this schema? (USAGE privilege) 4. can the role read/write this object? (SELECT/INSERT/UPDATE/DELETE privileges) 5. can the role see this row? (row-level security policies) Postgres's permission system is complicated when implemented haphazardly. When implemented thoughtfully, most of the confusion disappears. For a basic `SELECT`, a role needs USAGE on a schema and SELECT on a table.

Before Postgres 18, the planner would not consider multi-column b-tree indexes unless they used the left most column. With the new skip scan feature, a multi-column index can be used to answer queries with any of the columns.

Postgres contrib week! Let's talk about things packaged with Postgres but you have to turn them on to use them. Contrib extensions are part of the Postgres 'contrib' library. So extension code is already there and packaged with a Postgres install from the community, you just do 'CREATE EXTENSION' to turn it on. Extensions outside contrib have to be compiled or built with Postgres before you can run CREATE EXTENSION.

Postgres EXPLAIN plans have some key information in the sort key and method.

Postgres INNER JOIN vs CROSS JOIN. 🚫 SELECT * FROM users, accounts; ... that's a CROSS JOIN and a Cartesian product. ✅ SELECT * FROM users u JOIN accounts a ON u.user_id = a.user_id; ... that's an INNER JOIN and links two tables on matching rows.

Almost every operation, even basic queries in Postgres, use locks to manage access to resources. There are a number of lock types, many of which do not block reads or writes but will block DDL options to change the schema. Some high level basics.