PostgreSQL Performance Consideration

1. Query Optimization:
   • Analyze Query with analyze: Collect statistics on tables and columns.
   • Using explain: Shows the execution plan of SQL statement.
   • Using vacuum: Garbage collection to reclaim unused hard disk space.
2. Locking System (table, row)
3. Indexes (btree, hash, GIN, GIST, BRW)
4. Normalization & De-normalization
5. Clustering
6. Replication
7. Table inheritance
8. Constraint execution
9. SQL constructs:
   • Common Table Expression
   • Window Functions
   • Recursive Queries
10. Unlogged Tables
11. Temporary Tables
12. Materialized Views
13. Using distinct with consideration
14. Using joins with consideration
15. Vertical Scale
16. Database Caching
17. Sharding
18. Partitioning + Trigram Extension + GIN