<h1 id="postgres">Postgres<a aria-hidden="true" class="anchor-heading icon-link" href="#postgres"></a></h1>
<p>Postgres is a <a href="/notes/T8TbspD4PDNyJI9oRNlmL">DBMS</a>.</p>
<ul>
<li>"SQL tells the database what information you want, and the DBMS determines the best way to provide it."</li>
</ul>
<p>Think of postgres as a <a href="/notes/4JrFhUJpywlucgow6EX7j">microservice</a> that is stateful.</p>
<ul>
<li>Further, don’t think of it as a storage layer, but rather as a concurrent data access service. The service is capable of handling data processing. When we think of Postgres only as our data layer, we tend to overlook the power that Postgres can actually bring to the table.
<ul>
<li>anal: think of The Matrix, where Neo awakes in the pod near the start of the movie. If each pod were a piece of data, the mechanical arms that move and manipulate the pods would be Postgres.
<ul>
<li>In actuality, it would be both— the whole system. Limiting our thinking to just the data also limits our thinking in terms of what we consider Postgres to be capable of.</li>
</ul>
</li>
</ul>
</li>
</ul>
<p>In PG, data consistency is maintained by using a multiversion model (<a href="/notes/r9orvg7n574j5bmwon0nb74">Multi-version Concurrency Control (MVCC)</a>).</p>
<ul>
<li>This means that each SQL statement sees a snapshot of data (a database version) as it was some time ago, regardless of the current state of the underlying data.</li>
<li><a href="https://www.postgresql.org/docs/current/mvcc-intro.html">source</a></li>
</ul>
<h3 id="performance">Performance<a aria-hidden="true" class="anchor-heading icon-link" href="#performance"></a></h3>
<p>It's possible to scale Postgres to storing a billion 1KB rows entirely in memory - This means you could quickly run queries against the full name of everyone on the planet on commodity hardware and with little fine-tuning.</p>
<ul>
<li>Postgres can easily handle 10,000 insertions per second.</li>
</ul>
<p>If we have the SQL do the data related heavy lifting, there will often be a net gain in performance. Mostly, it is because round-trip times and latency along with memory and bandwidth resources usage depend directly on the size of the result sets.</p>
<p>It's rarely a mistake to start with Postgres and then switch out the most performance critical parts of your system when the time comes.</p>
<h1 id="ue-resources">UE Resources<a aria-hidden="true" class="anchor-heading icon-link" href="#ue-resources"></a></h1>
<ul>
<li><a href="https://pgexercises.com/">Exercises for learning SQL with PG</a></li>
<li><a href="https://www.avestura.dev/blog/explaining-the-postgres-meme">PG simple explanations</a></li>
<li><a href="https://wiki.postgresql.org/wiki/Don&#x27;t_Do_This">Postgres Do's and Don'ts</a></li>
</ul>
<hr>
<strong>Children</strong>
<ol>
<li><a href="/notes/EOnDAkcu7NDTo9wWIw9Kf">Acid</a></li>
<li><a href="/notes/nO6WOZwgeQMrqpNxdSWWC">Admin</a></li>
<li><a href="/notes/ozzbLHYLnr8U2uPxzDgJW">Clause</a></li>
<li><a href="/notes/VWukxh966rq9D82EH7Zng">Connecting to Postgres</a></li>
<li><a href="/notes/QoQ415WtkzSPFhdE5hFbu">Extensions</a></li>
<li><a href="/notes/WGMO8GEMryzRBuo9qWL6E">Internals</a></li>
<li><a href="/notes/OV4weBB6tmbhEK7I4R8Jm">Lang</a></li>
<li><a href="/notes/rOd07OVZXWW4GndCpbRQW">Operators</a></li>
<li><a href="/notes/9la20oi2UOXz83OSdEBG2">Performance</a></li>
<li><a href="/notes/suSn7ObdHqW9fM34vHu1Q">Plpgsql</a></li>
<li><a href="/notes/mrH2Wh4i7MUZFAwShkCT5">Prod</a></li>
<li><a href="/notes/43eaa600-4ffa-464d-93c9-e964f0d3825c">Psql</a></li>
<li><a href="/notes/U0Q7tQ1tdBr8pAuTdyqEg">Pub Sub</a></li>
<li><a href="/notes/9m0uXnqj87eQGZBmcCpQ8">Query</a></li>
<li><a href="/notes/FvIw1Okwy0YOOrU52a8nd">Scaling</a></li>
<li><a href="/notes/3XNEnUuPXs1euYW2H6uul">Seed</a></li>
<li><a href="/notes/8gRwCXdEumzX3lJo6CZPR">System</a></li>
<li><a href="/notes/2Ws4InodJSHdxp5duh1e4">Table</a></li>
<li><a href="/notes/WtxqGhvMHBAOOwoBhd9wO">Tablespaces</a></li>
<li><a href="/notes/h77vGJXk8WQ39NIL3JQxC">Terms</a></li>
<li><a href="/notes/ITeVND035EMGB1EKv987x">Testing</a></li>
<li><a href="/notes/99F4z4W0uB7IENzAVVL1Q">Tools</a></li>
</ol>
<hr>
<strong>Backlinks</strong>
<ul>
<li><a href="/notes/wbs6c9snnonnyzm8vk2t4b9">Strategies</a></li>
<li><a href="/notes/UE8OS2s713jEk2CmjiAet">B-tree</a></li>
</ul>

Postgres

tech

This Dendron vault of tech knowledge is organized according to domains and their sub-domains, along with specific implementation of those domains.

For instance, Git itself is a domain. Sub-domains of Git would include topics like `commit`,
`tags`, `reflog` etc., while implementations of each of those could be `cli`, `strat`
(strategies), `inner` (inner workings), and so on.

The goal of the wiki is to present data in a manner that is from the perspective
of a querying user. Here, a user is a programmer wanting to get key information
from a specific domain. For instance, if a user wants to use postgres functions
and hasn't done them in a while, they should be able to query
`postgres.functions` to see basic implementations, as well as common patterns
that have been employed in the past.

This wiki has been written with myself in mind. While learning each of these
domains, I have been sensitive to the "aha" moments and have noted down my
insights as they arose. I have refrained from capturing information that I
considered obvious or otherwise non-beneficial to my own understanding.

As a result, I have allowed myself to use potentially arcane concepts to help
explain others. For example, in my note on [[unit testing|testing.method.unit]],
I have made reference to the [[microservices|general.arch.microservice]] note.
The ability to analogize between different concepts captured in different notes
allows an opportunity to build strong generalized understandings. Given that
you'd have to understand microservices to be able to draw that same parallel
that I've already drawn, these links won't work for everyone. Since these notes
are written for myself, I have been fine with taking these liberties and leaning
on them heavily.

What I hope to gain from this wiki is the ability to step away from any
given domain for a long period of time, and be able to be passably useful for
whatever my goals are within a short period of time. Of course this is all
vague sounding, and really depends on the domain along with the ends I am
trying to reach.

To achieve this, the system should be steadfast to:
- be able to put information in relatively easily, without too much thought
	required to its location. While location is important, Dendron makes it easy
	to relocate notes, if it becomes apparent that a different place makes more
	sense.
- be able to extract the information that is needed, meaning there is a
	high-degree in confidence in the location of the information. The idea is
	that information loses a large amount of its value when it is unfindable.
	Therefore, a relatively strict ideology should be used when determining
	where a piece of information belongs.
	- Some concepts might realistically belong to multiple domains. For instance, the concept of *access modifiers* can be found in both `C#` and `Typescript`. Therefore, this note should be abstracted to a common place, such as [[OOP|paradigm.oop]].

This Dendron notebook is the sister vault to the general [Second Brain](https://tech.kyletycholiz.com).

## Tags
Throughout the garden, I have made use of tags, which give semantic meaning to the pieces of information.

- `ex.` - Denotes an *example* of the preceding piece of information
- `spec:` - Specifies that the preceding information has some degree of *speculation* to it, and may not be 100% factual. Ideally this gets clarified over time as my understanding develops. I try to go back after I have better understood the topic and clear out the notes of `spec:` tags
- `anal:` - Denotes an *analogy* of the preceding information. When I can, I attempt to link concepts to others that I have previously learned.
- `mn:` - Denotes a *mnemonic*
- `expl:` - Denotes an *explanation*

## Resources
### UE (Unexamined) Resources
Often, I come across sources of information that I believe to be high-quality. They may be recommendations or found in some other way. No matter their origin, I may be in a position where I don't have the time to fully examine them (and properly extract notes), or I may not require the information at that moment in time. In cases like these, I will add reference to a section of the note called **UE Resources**. The idea is that in the future when I am ready to examine them, I have a list of resources that I can start with. This is an alternative strategy to compiling browser bookmarks, which I've found can quickly become untenable.

### E (Examined) Resources
Once a resource has been thoroughly examined and has been mined for notes, it will be moved from *UE Resources* to *E Resources*. This is to indicate that (in my own estimation), there is nothing more to be gained from the resource that is not already in the note.

### Resources
This heading is for inexhaustible resources. 
- A prime example would be a quality website that continually posts articles.  - Another example would be a tool, such as software that measures frequencies in a room to help acoustically treat it.
