View source for Broad qualifiers, a.k.a. thinking about what you want from your database

{{#addbodyclass:tag_tech}}
{{Database related}}

===OLTP, OLAP===

tl;dr:
* OLTP basically means systems with ''immediacy'', OLAP often means bulk/batch processing

* Systems can be made to do both to some degree, but OLTP often comes with people wanting it to be as immediate as possible

* RDBMSes are mostly used for OLTP,  NoSQL has less opinion about how it's used



You'll see these two as qualifiers, as kinds of setups, and sometimes as specializations of specific kinds of systems/databases.


'''Online transaction processing (OLTP)''' refers to any system that does day-to-day transactions
* handles individual requests as they come in
* as safely/serially as configured
* as realtime as that still allows

This usually puts focus on
* operations being simple and fast
:: because if things take longer than milliseconds, it's going to scale very poorly, particularly if done serially

* consistency within systems - strongly where this is simple

* consistency between systems - where applicable and to the degree this is possible


OLTP that uses more complex data models ''tends'' to be implemented in RDBMSes, because the consistency you then often want can be done for you.
When you want scalability you often try to loosen that as much as you can get away with.



'''Online analytical processing (OLAP)''' tends to refer to "analyse a bulk of data" setups.

This often means that, mostly unlike OLTP
: collecting chunks of data collected over a larger amount of time
: often done for business reasons, rather than direct use by users.
:: classically was more about generating reports, analytics, etc. to be looked at later - There are few cases where a response coming seconds later is any problem (or days, if we're talking about reports)
:: now more frequently also to be looked at live (scale of seconds), and is starting to overlap with more complex variants of health monitoring
:: (compare each to OLTP, where responses are measured in milliseconds)



'''On mixing the two '''

When doing OLAP, you could just send batch-like queries to your one system that does everything,
but when that system is a critical, live OLTP system, 
the bulk nature of OLAP style work could slow down OLTP due to its "let's be careful about reading when something is writing" nature.

As such, there is a habit to siphon off data from an database used for OLTP to a separate database used for OLAP database (and its own processing host).

There are now also Hybrid transactional/analytical processing (HTAP) is the attempt to do both a transactional database and analytics,
which sometimes amounts to an automated variant of doing just that.



The more at-your-leisure your OLAP work, the more that classic [[ETL]] style processing covers you needs, 
but these days, there are OLAP systems that aim to do real-ish time monitoring, and more explicitly balance scale and speed (also with NoSQL-ish scaling). 
: It's often still factors slower than OLTP, or at least behind a few seconds (e.g. when running off a replicated database server)
: yet still many factors faster than generic warehousing, "throw an ETL script at it once every so often", "produce a report" style things),



OLAP might be based on structured data from a central OLTP system you have, 
or it might ingest less-structured data (like logs) in which case terms like '[[data warehouse|warehouse]]' or '[[data lake|lake]]' turn up.




As a broad qualifier, OLTP and OLAP can describe 

* a system as a whole - goals, database,  ode, the hosts used

* a type of database - e.g. 
:: RDBMS is a fairly classical case of OLTP
:: most NoSQL (e.g. kv and document stores) are still largely on the OLTP side (if only because few do much processing themselves)
:: e.g. ElasticSearch used for log analysis or monitoring (see e.g. logstash, kibana) is more on the OLAP side

* specific queries
:: e.g. some complex queries may be OLAP
:: queries may get so complex that people do some pre-structuring


It also has some applications to architecture.
OLTP often implies tiered applications, 
whereas OLAP modelings is much more varied, 
often somewhat custom (though OLAP NoSQL does exist),
from monolithic ETL batch script to complex interdependent streaming setups.

===Lakes, warehouses, swamps, pits, and graveyards===


'''Data warehouse''' means data in a processed, probably actively useful form.

This term has been around since the seventies.



'''Data lake''' means little more than '' "'not'' a data warehouse"

It often means "we're tracking things, we'll see how it's useful later"

This frequently also means storing it in raw from, again because you don't know what you want to extract from it.
{{comment|(...though if the thing is by nature already a structured document, this is more about whether it's ''indexed'' than whether it's ''parsed'')}}



In some cases, data lakes involve so little thinking ahead that
* you don't know what you have 

* you will have a really tough time figuring how to use or combine or even ''find'' this data later

* you created what you could creatively call 
:: '''data swamps''' (inaccessible)
:: '''data pits''' (won't produce clear insights)
:: '''data graveyards''' (you're not using it, and you never will)



Notes:
* searching for 'Data lake' will mostly give you IoT related results -- mostly because IoT is very ill defined beyond "collect ''all'' the data (personal or not) and see what we can do with it later".


* Data lake ''sometimes'' also includes the software that accesses the data - anything from ETL/ELT, and sometimes much fancier analytics.
: more so of this is a company trying to sell you analytics software


* In practice, though, 
** data warehouse and data lake are often used interchangeably.
** (so) when data warehousing can mean both, then ETL and ELT are both seen in data warehousing



====ETL, ELT?====


'''ETL''' (Extract, Transform, Load)
: is what [[data warehousing]] historically implies:
: "I clean up my data before entering it into the system" 

: ....in terms of data types, data validity, and change the shape of to fit a model/schema you have already settled, on at design stage

: ...often so that it can be stored in a structured database, meaning
:: you have specific goals in mind
:: so have already thought about efficient querying, and ensured faster loading
:: and you need to define that database up front

: you can design it in a way that likely queries will be fast




'''ELT''' (Extract, Load, Transform) mostly means
: "just store what we got, in the form we got it in (probably a text blob, or even a binary blob), and worry about interpreting it later, maybe never"

: [[Data lakes]] are arguably ''defined'' by this "it is not yet structured, queryable data" ELT approach {{comment|(contrasted to [[data warehouse]], which is more ETL/usable)}} 

This is in part pragmatism: 
* if there's data we don't use yet, and some we may never use, why spending time creating a model and polishing data before we can even store it - we can do that work if when we need it, ''if'' we ever want to

* it doesn't need to have the form that the original design assumption require

* we can capture any and all data, and might be able to ask more varied questions, and get more answers, if you keep the original data around.


However, the same thing that leads to these upsides (you didn't do ''anything'' yet) lead to downsides:
* you will parse and validate it on use
: ...either on every use 
:: this tends to be true for ad-hoc querying, which tends to mean mapreduce / pipeline solutions
:: this is usually also slower and/or consumes more power

: ...or on first use, e.g. if you validate-and-store into a working set for every question you work on
:: meaning you store even more data, and take more time (sort of because you're creating a warehouse for each use)


https://en.wikipedia.org/wiki/Data_warehouse

==="NoSQL versus relational"===

<!--


<!--
'''Guarantees versus speed'''

The less you guarantee, the faster you can go,
and there are multiple valuable points on that scale

For example
* If you are writing a relational database consistency manager, you ''have'' to accept that many requests take considerable time.

* When designing a memcache, there is real value in guaranteeing 
:: that you can answer all requests in under a 
:: millisecond and all responses are small so arrive quickly.
: because that means no part of your system can ever be held up by you, and you never become a bottleneck yourself. 
:: Even targeted abuse of this system will barely slow it down.
: But that means that even a "list all keys", which would usually be quite simple to implement, is something you leave out.
: And for the specific purpose of using this as a simple cache, you do not need it. Leaving it out makes it simpler, makes harder guarantees easier, which is absolutely great.
: And it's absolutely terrible at doing anything else.

* Most NoSQL is somewhere in the middle.

-->

<!--
In the end, NoSQL means very little more than "any data store that chooses a different specialization from one of the top few RDBMSes".


It ''often'' also means "don't make things transactional, or related at all, when you don't need to" which is smart, and "so we removed it completely", which is less smart.


NoSQL is more about data models we prefer, than it is about software that implements that.

Things we now include into NoSQL is technically even older than relational databases.
They just weren't called it because the term NoSQL was coined around 2010, not the 1960s.


RDBMS's specialization is, basically, consistency management. 

And when you need strict consistency, it's the only thing that will give it to you,
because AFAIK no NoSQL is as stringent.



As is the ability to apply consistency management to more complex data models - (typically relational ones, which by own our habits are typically also in quite-normalized form).

...but this is a conflict of interest that comes at the cost of scalability.


So it's a bit of a "consistent, complex, fast - choose 2" situation.

RDBMS is biased to do the first two at cost of the third.

NoSQL includes the other combinations, so whenever speed-at-scale is ''more'' important than strict consistiency, what you want lies in this domain.

But so is speed at scale, and the two are broadly incompatible. 
So NoSQL also refers to thinking about different data models,
the tradeoffs you can make explicitly, 
and the tradeoffs that using different kinds of storage makes for you.


And we ''did'' need a kick in our habits to realize how much we weren't thinking about alternatives very much.

Reasons to specialize vary, and are sometimes very real.

Also sometimes not, and if you're not thinking about it, you can make a mess for yourself without good reason.
-->

====Lookups, joins, flatness, and locality====


====Responsibilities in database versus responsibilities in code====


====Why denormalize, why not denormalize?====

=====ORMs considered good and bad=====

<!--
An Object-Relational Model

One part is simply that adding a layer of abstraction is likely to leak.
: It is, for example, quite easy to introduce an n+1 problem. Not because you can't avoid it, but because you have to break the abstraction to avoid it.


Another part of the discussion is that the object model and the relational model are fundamentally different, and you can only smush them together if you keep to certain rules - rules that you will find yourself ''needing'' to break, particularly if you love the idea of hyperscale, but which can already be an issue even a mom-and-pop webshop site.
I worry that not enough people understand that these things are ''fundamentally'' incompatible in ways we can only hide, not fix. 

-->


====On consistency====



====The n+1 problem====