OLTP vs OLAP

Giriş

Veri tabanları iki farklı iş için tasarlanır. Açıklaması şöyle

As you probably know, databases are divided into two types: Online Transactional Processing (OLTP) and Online Analytical Processing (OLAP).

Bu ikisinin farklılıklar şöyle

OLTP and OLAP describe two very different data processing methods, and, therefore, they have different database requirements.

Characteristics OLTP OLAP

Data queried in one request Small (a few rows) Large

Real-time update         Yes         No

Transactions         Yes         No

Concurrency         High         Low

Query pattern         Similar Varies a lot

Özetlersek açıklaması şöyle

OLTP systems are born to handle transaction data: they enable the real-time execution of large transactions with many concurrent queries and require fast response times. On the other hand, OLAP requires running complex queries on large numbers of records. OLAP systems have high throughput but do not offer low query latencies or high queries per second (QPS) like the OLTP systems.

1. OLTP

Açıklaması şöyle

Years ago, databases made little distinction between OLTP and OLAP. Instead, one database processed both types of requests. However, as the data volume grew, it became difficult to process two types of workloads in a single database. Most significantly, the different workload types interfered with each other.

Thus, to meet the special needs of OLAP workloads, people designed a separate database that only processed OLAP workloads. They exported data from OLTP databases to OLAP databases, and processed the OLAP workloads there. Separating the OLTP and OLAP workloads resolved the conflicts between the two workloads, but it also introduced external data replication. During the replication, it was hard to ensure that data was consistent and in real time.

2. OLAP - Eski/Arşiv Veri

OLAP eski ve arşiv veriyle uğraşır. Transaction miktarı azdır ve daha çok sorgu yapar. Sorgular karmaşıktır ve aggregation miktarı çoktur.

OLAP ismindeki online eskiden kalan bir kelime. Açıklaması şöyle

It is simply a remnant of olden times, when it was used in contrast to batch processing. "Online" here means "interactive", that is, requests to the database are processed as they come and responses are given more or less immediately, or at least as soon as they are available. Batch processing would collect requests into, well, batches, and execute them on schedule; responses would be given after the entire batch execution (e.g. next morning).

Örnek

Bir örnek şöyle

PostGre is usually used in OLTP scenarios with a bit of OLAP.

That is fine as long as you understand the difference between them.

For example, if a public-facing endpoint contains a very complex query that loads a lot of data and does complex operations, it’s probably a good indicator that it should be treated as OLAP and that endpoint refactored to adopt a different strategy.

Örnek

Açıklaması şöyle

Redshift supports some SQL functions and queries which would generally only be necessary with large data warehouse applications. For example, PERCENTILE_CONT computes a linear interpolation to return a percentile.

Şöyle yaparız

SELECT
    TOP 10 salesid,
    sum(pricepaid),
    percentile_cont(0.6) WITHIN GROUP (
        ORDER BY 
            salesid
    ),
    median (salesid)
FROM
    sales
GROUP BY
    salesid,
    pricepaid;

Örnek
OLAP veri tabanlarında one-to-many olması beklenen ilişkiler bile çok fazla normalize edilmiş olabilir.

Normalization Nedir yazısına bakabilirsiniz.

Aşağıdaki örnekte User, Department isimli iki farlı tablo var. Daha sonra bu iki farklı tabloyu birleştiren UserDepartmentTable tablosu var.

UsersTable

UserID  FirstName   LastName
234     John        Doe
516     Jane        Doe
123     Foo         Bar

DepartmentsTable

DepartmentID   Name
1              Sales
2              HR
3              IT

UserDepartmentTable

UserDepartmentID   UserID   Department
1                  234      2
2                  516      2
3                  123      1

RequestTable

RequestID   UserID   <...>
1           516      blah
2           516      blah
3           234      blah