PostgreSQL column storage and row storage

Listing advantages ：

1、 There is no row in the column 1666 Column restrictions

2、 Scanning a large number of records stored in columns saves resources than row storage

3、 The column storage compression ratio is high , Save space

4、 The calculation of a large amount of data stored in the column can be performed using vectorization , Efficient

Bank deposit advantage ：

1、 Query... Save multiple rows fast

2、 Bank deposit DML Efficient

Simply speaking , Bank deposit fit OLTP Business , Listing is suitable for OLAP Business .

If the business is a mixed load , Both high concurrency SQL, What about real-time analysis of business ？

Oracle How to do it ：

in memory column store, It's actually two copies of storage , One on disk （ Bank deposit ）, A list stored in memory .

according to SQL, The optimizer selects whether to scan column storage or row storage .( Usually planNODE Row selectivity of data scanning in , Number of lines output , Number of output columns, etc )

Oracle in memory column store It's the idea of two copies of storage .

PostgreSQL How to deal with mixed business scenarios ？

At present PG Already there. SMP Optimizer functions executed in parallel , Rich aggregate functions , Window functions, etc , There are already good OLAP processing capacity . If the data storage organization can be supported in place , It is bound to give OLAP The ability to bring about a greater qualitative leap , To better fit OLTP OLAP Mixed business scenarios .