PostgreSQL Architecture

Based on PostgreSQL documentation and various articles, I comprehend the PostgreSQL Architecture as below:
PostgreSQL instance consists of set of Process and Memory. PostgreSQL uses a simple “process per-user” client/server model. The major processes are:

The ‘postmaster’ which is:

supervisory daemon process,
‘postmaster’ is attached to shmmem segment but refrains from […]

read more

Install and Configure pgBadger for PostgreSql 9.x on CentOS

Yes, one of the most important things is knowing how the databases are used. Indeed if we know what are the slowest queries, the most frequent queries, etc.; we can optimize the databases (by adding an index, changing a query or modifying a schema/structure) and thus improve the performance! To […]

read more

Query Optimization Techniques

Below Query Optimization Techniques should be taken care of to optimize SQL queries:

Use Index
Aggregate table – Pre-populating tables at higher levels so less amount of data need to be parsed.
Vertical partitioning
Horizontal Partitioning
Denormalization
Server Tuning
Move Queries to stored procedures
Remove unneeded views
Only query what you really need
Sort only when required
You should do […]

read more