[scottcodie]

I've spent my entire career developing databases (oracle, cassandra, my own database startup). Knowing if your workload is read or write heavy is one of the first questions when evaluating database choice, and is critical for tuning options. I would give this article hate just because it feels partially written by AI and the title needs a possessive 'your' in it, but its core ideas are sound and frame the issue correctly.

[EGreg]

What about asking whether it should be a row database (sqlite) or column database (duckdb)...

for a data lake or analytics prefer columns? / read heavy

what would a row based db be better for? OLTP / write heavy?

[gigatexal]

General rule of thumb is OLAP (DuckDB, BigQuery, redshift, etc) db’s are better at reads (think analytics) and OLTP (Postgres and MySQL and salute) ones are better for writes (think order systems, point of sale).

Things get muddied when things like the HTAP stuff are bandied about where they promise the best of both worlds.

[ownagefool]

Ordering System is a good example because you typically want both. Your base logic will probably exist in OLTP with joins and normalised data, and you'll generally have local on-device OLTP databases.

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 ).

[gigatexal]

> 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 ).

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.

[mike_hearn]

Right, you want both, which is why databases like Oracle can store data in both forms. You can enable columnar formats on tables for both on disk and in-memory modes, where those columns can then be processed at high speed with lots of SIMD operations, but the data is kept consistent between 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

[gigatexal]

Getting into bed with Oracle is like selling your soul to loan sharks. Probably good in the beginning and only if you have a lot of money.

[gigatexal]

FWIW SQLserver can do the same with its column store tables. Idk though. I stopped using such when I moved to data Eng and we just use open things (clickhouse, DuckDB, etc) except for snowflake.

[citrin_ru]

Column storage (e.g. ClickHouse) is well suited for storing logs and log-like data even if writes (insert-only) > reads.

[biehl]

Do you happen to know similar queries for Oracle?