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.

  • Look up "Hybrid Transaction and Analytical Processing" (HTAP)en.wikipedia.org/wiki/… or sometimes called "Real-Time Operational Analytics" Jan 3 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.
    Jan 3 at 16:27
  • Seems good. Is there any equivalent of column store indexes in Oracle or postgresql? Jan 3 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! Jan 3 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.
    Jan 3 at 21:31

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, privacy policy and cookie policy

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