1

Usually companies have separate databases for OLTP and OLAP (Row stores and column stores) where data is loaded through ETL jobs to another database for analytical processing. I am particularly interested to know if there is any way we can define which tables or objects to store for OLAP and which tables or objects to store for OLTP queries in any RDBMS like oracle or postgres on any level of granularity in the same database.

6
  • Look up "Hybrid Transaction and Analytical Processing" (HTAP)en.wikipedia.org/wiki/… or sometimes called "Real-Time Operational Analytics" Commented Jan 3, 2021 at 16:08
  • Also worth noting certain database systems, like Microsoft SQL Server, offer features to make querying your OLTP database performant for OLAP type queries, such as Columnstore Indexes.
    – J.D.
    Commented Jan 3, 2021 at 16:27
  • Seems good. Is there any equivalent of column store indexes in Oracle or postgresql? Commented Jan 3, 2021 at 18:48
  • 1
    You might want to alert the users with whom you wish to communicate by putting the @ symbol in front of their handle - e.g. to signal to me that you have responded to a comment of mine, you'd put @ +Vérace (no space) in your own comment beneath theirs - HTH! Some people will add a comment and move on unless they receive a response!
    – Vérace
    Commented Jan 3, 2021 at 21:03
  • Verace is correct, I wouldn't've seen your follow up question if I didn't poke my head back in. I'm no expert on Oracle or PostgreSQL but it sounds like Oracle offers something similar called In-Memory Column Store Architecture. PostgreSQL isn't as out-of-box and I believe requires installing some sort of addon (some that are paid) to access column storage, for example swarm64.
    – J.D.
    Commented Jan 3, 2021 at 21:31

1 Answer 1

0

Oracle includes an in-memory columnar store that lets you combine OLAP-style queries on top of a plain tabular data mode without requiring any transformation

See https://docs.oracle.com/en/database/oracle/oracle-database/12.2/inmem/intro-to-in-memory-column-store.html#GUID-BFA53515-7643-41E5-A296-654AB4A9F9E7

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.