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): Characterized by a large amount of SELECT, 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. The operations are often executed in a fraction of a second and often grouped in logical groups, also known as transactions.
• Online analytical processing (OLAP): Characterized by a small amount of requests, complex queries that involve data aggregation, huge amounts of data from different sources with different formats, data mining, and historical data analysis. 
• Hybrid transactional/analytical processing (HTAP): A hybrid architecture to break the wall between OLTP and OLAP. 

OLTP is used to model business operations, such as customer relationship management (CRM). For instance, the car web portal example in Chapter 1, Relational Databases, is an example of an OLTP application. OLAP applications are used for business intelligence, decision support, reporting, and planning. An OLTP database's size is relatively small 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 had another database to store and maintain all the historical data of the sellers and users, which we could have used 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 extract, transform, and load (ETL). 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.