Maintenance Tasks

  • Keep autovacuum enabled most of the time
  • VACUUM regularly as well
  • Check for unused indexes

Warnings: (unless you know what you are doing …)

  • Avoid using VACUUM FULL
  • REINDEX CONCURRENT does not exist (yet)
  • Do not use HASH INDEXES
  • Do not use fsync = off

RTFM

  • PostgreSQL docs are about 2000 pages
  • Technically accurate
  • Written and maintained by the developers

Security

  • Superuser is too powerful for most use cases (SECURITY DEFINER functions)
  • Use a distinct userid for replication
  • GRANT minimal access rights

Upgrades

  • Maintenance releases happen about every 3 months
  • For best security – upgrade to latest maintenance release
  • Major release upgrades are harder
  • UDR technology will make Major release upgrades much easier from 9.4+

Extensions

  • PostgreSQL is designed to be extensible
  • Many new features enabled via extensions (EG: pgaudit, postgis)
  • Use them!

Scripts

  • GUIs do not allow you to apply changes in a transaction or easily record your actions
  • Use scripts for any administrative changes (trainers opinion)
  • Test them, before applying

Schema Change: adding a foreign key now split into two parts:-

  1. With write-lock
  2. Background task
  • ALTER TABLE foo ADD FOREIGN KEY … REFERENCES bar NOT VALID;
  • ALTER TABLE foo VALIDATE CONSTRAINT fook;

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s