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.

Postgres assumes columns are independent. When they're not, the planner multiplies probabilities, which can estimate incorrectly. For example, a city paired with a country ```sql SELECT * FROM facilities WHERE city = 'Lyon' AND country = 'France'; ``` Assuming the following: ``` P(country='France') = 0.0605 P(city='Lyon') = 0.000103 Estimated rows = 3,132,540 * 0.0605 * 0.000103 = 19 Actual rows = 5,816 ``` It multiplied the probabilities as if city and country were unrelated. But `city = 'Lyon'` almost guarantees `country = 'France'`. The estimate is off by 300x. Extended statistics ```sql CREATE STATISTICS city_country_stats (dependencies) ON city, country FROM facilities; ANALYZE facilities; ``` Now the planner knows the dependency exists and won't multiply blindly. Three types of statistics • `dependencies`: Column A determines Column B (i.e. city → country, zip → state) • `ndistinct`: GROUP BY overestimates distinct combos (i.e. `GROUP BY category, subcategory`) • `mcv`: Need exact frequencies for value pairs (i.e. `WHERE org = 'X' AND event = 'Y'`)

Hey developers - you can create dynamic columns in Postgres to make your queries faster and avoid repetitive calculations. If you’re constantly calculating something like sales tax, totals, normalized addresses or numbers - generated columns can be a huge help. If you want to store a table for total amount, which is a until price plus sales tax, that would look like this: ALTER TABLE coffee_shop_sales ADD COLUMN total_sale_amt numeric GENERATED ALWAYS AS ((unit_price * (1 + sales_tax_pct / 100) * transaction_qty)) STORED; Stored columns automatically stay up to date - so when source data is updated, the column is automatically recalculated and updated to reflect the new values based on the updated source data. To normalize phone number that come in from the application in a variety of formats: ALTER TABLE contacts ADD COLUMN normalized_phone text GENERATED ALWAYS AS (REGEXP_REPLACE(phone, '[^0-9]', '', 'g')) STORED; Indexing - yes! You can also create an indexes on the generated column, which speeds up query execution when the column is used in filtering or ordering: Benefits of generated columns ⚡ Performance: These can speed up queries with frequent calculations ✅ Data Integrity: Generated columns ensure the consistency of calculated data, avoiding manual mistakes when writing expressions in queries. There are two types of generated columns: Stored: The value of the generated column is physically stored with the table and calculated whenever the row is inserted or updated. This can improve performance because the computation does not need to be done at query time. These ADD COLUMN statements have STORED at the end. Virtual: For these, the value of the generated column is calculated on-the-fly when the column is queried, but it is not stored physically. This is good for cases when you want some kind of repeated column logic but the stored value doesn't make sense - maybe space or other concerns. These ADD COLUMN calls have VIRTUAL at the end. By using generated columns, you simplify complex expressions in your queries, reduce computation during query time, and potentially improve performance with indexing.