MicroPosts

• Size of a column in Postgres

  pg_column_size ( "any" ) → integer "Shows the number of bytes used to store any individual data value. If applied directly to a table column value, this reflects any compression that was done."

  Example:

  SELECT name, pg_column_size(name) FROM table
  

• Find unused indexes in Postgres

  How to use the `pg_stat_user_indexes` table

  SELECT * FROM pg_stat_user_indexes WHERE relname = 'TABLE_NAME':

  • idx_scan: how many times the index was used
  • idx_tup_read: how many index entries were returned using the index
  • idx_tup_fetch: how many rows were returned using the index

  You want:

  • idx_scan to move up (ie, the index is used)
  • idx_tup_read/idx_tup_fetch to move up but not too much (ie, the index is highly selective)

  Source: sgerogia.github.io/Postgres-Index-And-Queries/