• Don’t use BETWEEN (why)

• Prefer = to LIKE

LIKE compares characters, and can be paired with wildcard operators like %, whereas the = operator compares strings and numbers for exact matches. The = can take advantage of indexed columns. (source)

*

• Prefer EXIST to IN

If you just need to verify the existence of a value in a table, prefer EXISTS to IN, as the EXISTS process exits as soon as it finds the search value, whereas IN will scan the entire table. IN should be used for finding values in lists. Similarly, prefer NOT EXISTS to NOT IN. (source)

• singular (e.g. team not teams) (Here is why)

• snake_case (e.g. block_theme_version)

• double underscore for n-n tables (e.g. user__organization)

• snake_case (for example: created_at. Not createdAt or CreatedAt) Because in PostgreSQL keywords and unquoted identifiers are case insensitive and is the source of many mistakes.

• double underscore for PK and FK columns (e.g. (PK) user__id, (PK) user__id, (FK) organization__id, (FK) organization__id)

  • why?

    • leverage using(column__id)

    • easier to grasp for PK/FK the table name part (the part before __) for snake_case columns

    • Column are case-sensitive in postgresql but SQL queries are case insensitive

• NOT NULL by default, NULL is the exception (think of it as the maybe Monad)

• No abbreviation unless it’s both well-known and very long like i18n

• No reserved keywords (Complete list)

• Use UUID as PK and FK (Where is why), (do not use serial) rely on gen_random_uuid() (benchmark)

• Note that when you use Postgres native UUID v4 type instead of bigserial, table size grows by 25% and insert rate drops to 25%.

• If you choose bigserial than distinguish internal and external ids (e.g. gitlab internal schema design names column "iid" those that are publicly shared to the end user). Don’t forget to add an index CREATE UNIQUE INDEX index_issues_on_project_id_and_iid ON public.issues USING btree (project_id, iid);

• Use text or citext (variable unlimited length) with check constraint instead of varchar(n) or char(n).

• text type with CHECK constraint allows you to evolve the schema easily compared to character varying or varchar(n) when you have length checks. ([source](https://shekhargulati.com/2022/07/08/my-notes-on-gitlabs-postgres-schema-design/))

• Use timestamptz everywhere you need to store a date (e.g. created_at TIMESTAMPTZ DEFAULT now() (Here is why)) and leverage the BRIN index on it

• updated_at TIMESTAMPTZ DEFAULT now() unless you plan to leverage (learn more) event-sourcing

• deleted_at TIMESTAMPTZ DEFAULT NULL:

  • unless you plan to leverage event-sourcing

  • don’t forget to deleted_at

• Comment each column, explain your rational, explain your decisions, should be in plain english

• Boolean columns must start with either is or has.

• Don’t use char(n) even for fixed-length identifiers

• {table_name}_{column_name}_pk in case of a single column PK

• {table_name}_{column_name1}_{column_name2}_{column_name3}_pk in case of multiple columns as primary key (column_name1, column_name2, column_name3)

• {from_table_name}_{from_column_name}_{to_table_name}_{to_column_name}__fk

• Always specify ON DELETE ON UPDATE in order to force you to think about reference consequences

• {from_table_name}_{from_column_name}_key in case of a single column unique constraint

• {from_table_name}_{from_column_name1}_{from_column_name2}_{from_column_name3}__key in case of multiple columns as unique (column_name1, column_name2, column_name3)

They are 3 types of functions, notify functions and private functions and public functions

• notify, format: notify[schema_name][table_name][event] (e.g. notify_authentication_user_created(user_id)): should only format the notification message underneath and use pg_notify. Beware of the 8000 characters limit, only send metadata (ids), data should be asked by workers through the API. If you really wish to send data then pg_kafka might be a better alternative.

• private, format: _[_function_name_] (e.g. _reset_failed_login): must never be exposed through the public schema. Used mainly for consistency and business-rules

• public, format [_function_name_] (e.g. log_in(email, password)): must be exposed through the public schema.

Every parameter name must ends with $. This will prevent any "Reference to XXX is ambiguous" issue.

Don’t use enums, you will have issue over time because you cannot remove element from an enum. If your enums represent various state, leverage a state machine. Use a library like lib_fsm.

Always use true and false, without single-quote.

PostgreSQL documentation says that TRUE and FALSE should be prefered because they are more SQL compliant but hey, LET’S STOP YELLING WHEN WE WRITE SQL SHALL WE?

• Multi-line string must be represented with $$my string$$

• prefer jsonb to json and sql arrays. Jsonb has improved query performance and efficient storage

• A metadata jsonb column is a great way to let the end-user store arbitrary key-value data to these objects. (e.g. https://documentation.hook0.com/docs/metadata https://stripe.com/docs/api/metadata )

metadata key-value pair must be limited in space you can use a trigger for that:

(translation in progress)

• utiliser BNCF (au dessus de la 3NF) (cf normal form)

• leverage using, so instead of:

use:

• don’t use PostgreSQL enums you will have issues when you need to remove some values over time. Use a dedicated table instead.

• use the right PostgreSQL types:

• constraint should be inside your database as much as possible:

• use row-level-security to ensure R/U/D access on each table rows

(source)

todo.

Since we do want to limit everything in space and time, configure statement_timeout on role to let your database abort any statement that takes more than the specified amount of time (in ms).

• Removing PostgreSQL Bottlenecks Caused by High Traffic

Your cache hit ratio tells you how often your data is served from in memory vs. having to go to disk. Serving from memory vs. going to disk will be orders of magnitude faster, thus the more you can keep in memory the better. Of course you could provision an instance with as much memory as you have data, but you don’t necessarily have to. Instead watching your cache hit ratio and ensuring it is at 99% is a good metric for proper performance. (Source)

Under the covers Postgres is essentially a giant append only log. When you write data it appends to the log, when you update data it marks the old record as invalid and writes a new one, when you delete data it just marks it invalid. Later Postgres comes through and vacuums those dead records (also known as tuples). All those unvacuumed dead tuples are what is known as bloat. Bloat can slow down other writes and create other issues. Paying attention to your bloat and when it is getting out of hand can be key for tuning vacuum on your database. (Source)

Postgres makes it simply to query for unused indexes so you can easily give yourself back some performance by removing them (Source)

pg_stat_statements is useful for monitoring your database query performance. It records a lot of valuable stats about which queries are run, how fast they return, how many times their run, etc. Checking in on this set of queries regularly can tell you where is best to add indexes or optimize your application so your query calls may not be so excessive. (Source)

• Stripe own schema

• pg_stat_statements

• A fast PostgreSQL Log Analyzer

• PostgreSQL Performance Monitoring

• How to do Zero-downtime migrations

• Zero-downtime migrations best practices

• 12 Common Mistakes and Missed Optimization Opportunities in SQL

• Don’t apply migrations on application startup

• Google Cloud PostgreSQL

  • Pros

  • Cons

    • No support for plv8

    • Any features that require superuser privileges are not supported

    • postgres role is not a superuser

      • Can create roles

      • Can not select from tables that are restricted by default like pg_shadow

      • Thus can not edit pg_catalog.pg_class (in order to change row level security activation for example)

      • Can read from all necessary tables other than pg_authid