Postgresql performance problem and solution

Usually postgresql performance issue is because of statistics data out of date. The planner may think one table is small but actually its huge.

Diagnosis

For example:

Solution

TODO

To maintain a health database, should put slow query alert there, and setup auto vacuum.

DIY self host blog website.

Summary about self host a blog site, about $45/year

  • Why not google blogger, the custom domain doesn’t support https.
  • Domain/DNS provider – namesilo – reasonable price ($8.99 per year) and free privacy (your name/address will not show up in whois when search your domain)
  • Virtual Server provider – Scaleway – €2.99/mo. (Full control everything, but also need self maintain everything)
  • SSL cert provider “Let’s Encrypt” – free.
  • Software. Ubuntu, Mairadb, Nginx, Apache2, wordpress (to support multi domain name virtual host and easy SSL cert management)

Software architecture

  • Nginx reverse proxy, pass different domain names to different apache virtual host ports.
  • Nginx handle SSL protocol, and call to apache with http
  • IPTables firewall only open 80 and 443, hide all internal ports (mariadb, apache2)
  • WordPress may suffer 301 redirect dead loop, need add “SetEnvIf X-Forwarded-Proto https HTTPS=on” into apache configuration.