for a data lake or analytics prefer columns? / read heavy
what would a row based db be better for? OLTP / write heavy?
Things get muddied when things like the HTAP stuff are bandied about where they promise the best of both worlds.
Reporting on your Ordering System is an OLAP problem though. Generally an OLAP database stores data on disk in a way that it only needs to read the selected columns and the performance is better with wider columns, i.e. lots of duplicated data ( JOINs are slow ).
So like, you select * from Customer, Order, Items, Device, Staff, stick it in your OLAP database that's where customers should generate reports. This both makes reporting more performant, but it also removes the problem from the critical path of your POS device syncing and working.
This has the added benefit that updating your product name won't update the historical log of what was done at the time, because what was done at the time was done at the time ( but you can still map on like productId if you think the data is relevant. )
At scale you want to pop the writes on a queue and design those devices to be as async as possible.
This is what happens when you just build it pure OLTP.
https://www.linkedin.com/pulse/nobody-expects-thundering-her...
This was an ~£19m ARR POS company dying because of architecture, now doing £150m+ ARR. ( the GTV of the workloads are multiple times that, but I can't remember them ).
https://www.oracle.com/database/in-memory/
https://www.oracle.com/database/technologies/exadata/softwar...
That eliminates the complexity of running two databases and keeping them in sync.
Disclosure: work part time for them, own stock
This sounds like the one big table approach. Which in my experience is very difficult to do right and only makes sense in the data mart sense.
Google’s Adsense data model I’m BigQuery is like this and works well but gets so wide it’s difficult. Then again when you imbed things like arrays and structs and can unnest as needed avoiding joins can be nice.
I’ve found star schemas to work out just fine in data marts. Just do them properly. Join as needed. And a good engine will handle the rest. We’ve has no issues with a similar model in Snowflake for example. Of course YMMV.