PostgreSQL applications

PostgreSQL can be used with a variety of applications. The main PostgreSQL application domains can be classified into two categories:

• Online transactional processing (OLTP): OLTP is characterized by a large amount of insert , update, and delete operations, very fast processing of operations, and the maintaining of data integrity in a multi-access environment. Performance is measured in the number of transactions per second.
• Online analytical processing (OLAP): OLAP is characterized by a small amount of requests, complex queries which involve data aggregation, huge amounts of data from different sources and with different formats, data mining, and historical data analysis.

OLTP is used to model business operations such as customer relationship management (CRM). For example, the car web portal example in Chapter 01, Relational Databases, is an example of an OLTP application. OLAP applications are used for business intelligence, decision support, reporting, and planning. An OLTP database size is relatively small as compared to an OLAP database. OLTP normally follows relational model concepts, such as normalization, when designing the database, while OLAP has less relation; the schema often has the shape of a star or a snowflake. Finally, the data is denormalized.

In the car web portal example, we could have another database to store and maintain all the sellers and users historical data to analyze user preferences and seller activities. This database is an example of an OLAP application.

Unlike OLTP, OLAP's main operation is data analysis and retrieval. OLAP data is often generated by a process called ETL (extract, transform, and load) processes. ETL is used to load data in to the OLAP database from different data sources and different formats. PostgreSQL can be used out of the box for OLTP applications. For OLAP, there are many extensions and tools to support it such as foreign data wrappers (FDW), table partitioning, and recently, parallel query execution.