Walking Through Postgres

class: center, middle, inverse
background-position: center
background-size: cover
background-image: linear-gradient(to top, #434343ee, #000000cc), url("https://cdn1.medicalnewstoday.com/content/images/articles/322/322736/elephant-from-the-front.jpg")
count: false

<div style="font-size: 130px; font-family: 'Lato'; font-variant: small-caps;">
Walking Through Postgres
</div>

<div style="text-align: left; bottom: 12px; position: absolute; left: 20px; font-family: 'Lato'; opacity: 0.75;">
<h4>Wed Mar. 6th, 2019</h4>
<h4>Ellis Valentiner</h4>
(CC BY-SA 4.0) <i class="fab fa-creative-commons"></i> <i class="fab fa-creative-commons-by"></i> <i class="fab fa-creative-commons-sa"></i>
</div>

---
class: center, middle, shadow
background-position: center
background-size: cover
background-image: linear-gradient(to right, #ffffffe0 40%, #ffffffa0), url("http://d2ouvy59p0dg6k.cloudfront.net/img/original/original_ww187785.jpg")

> # _"PostgreSQL is a powerful, open source object-relational database system with over 30 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance."_

<div style="text-align: left; bottom: 12px; position: absolute; left: 20px; opacity: 0.5;">
source: postgresql.org
</div>

???

Postgres: what, why, how

History:
In 1973 Michael Stonebraker (and others; UMich)at UC Berkley began work on Ingres (Interactive Graphics and Retrieval System)

Work on Postgres began in 1986, released in late 1989 using its own query language (PostQUEL), switched to SQL in 1995 (named in Postgres95). In 1996 it was renamed to PostgreSQL (v6.0) -- but most people just call it postgres

---
background-position: center
background-size: cover
background-image: linear-gradient(to left, #ffffffbb, #ffffffdd), url("https://cdn.audubon.org/cdn/farfuture/Cx-Qe9BnTJRa_ecjojFoTA6IOM69XWH10l_9xtAj0Hc/mtime:1427798726/sites/default/files/gettyimages_142457895.jpg")

# Object-relational database systems

.middle[
.center[
![](https://upload.wikimedia.org/wikipedia/commons/7/7c/Object-Oriented_Model.svg)
]
]

<div style="text-align: left; bottom: 12px; position: absolute; left: 20px; opacity: 0.5;">
source: wikimedia.org
</div>

---
class: middle, inverse
background-position: center
background-size: cover
background-image: linear-gradient(to bottom, #000000aa, #000000dd), url("https://kids.nationalgeographic.com/content/dam/kids/photos/animals/Mammals/A-G/african-elephant-eating.adapt.945.1.jpg")

> # _"PostgreSQL is the database management system that gained more popularity in our DB-Engines Ranking within the last year than any of the other 343 monitored systems._

> # .accent-pink[_We thus declare PostgreSQL as the DBMS of the Year 2018.]"_

<div style="text-align: left; bottom: 12px; position: absolute; left: 20px; opacity: 0.5;">
source: db-engines.com
</div>

???

DB-Engines is an initiative to collect and present information on database management systems (DBMS

---
class: left, bottom
background-position: top
background-size: contain
background-image: url("images/stack-overflow-loved.png")

<div style="text-align: left; bottom: 12px; position: absolute; left: 20px; background-color: #ffffff;">
source: stackoverflow.com
</div>

???

2018 Stack Overflow Developer Survey

--
class: left, bottom
background-position: top
background-size: contain
background-image: url("images/stack-overflow-dreaded.png")

<div style="text-align: left; bottom: 12px; position: absolute; left: 20px; background-color: #ffffff;">
source: stackoverflow.com
</div>

???

2018 Stack Overflow Developer Survey

--
class: left, bottom
background-position: top
background-size: contain
background-image: url("images/stack-overflow-wanted.png")

<div style="text-align: left; bottom: 12px; position: absolute; left: 20px; background-color: #ffffff;">
source: stackoverflow.com
</div>

???

2018 Stack Overflow Developer Survey

---

```text
PostgreSQL is released under the PostgreSQL License, a liberal Open Source
license, similar to the BSD or MIT licenses.

PostgreSQL Database Management System
(formerly known as Postgres, then as Postgres95)

*Permission to use, copy, modify, and distribute this software and its
*documentation for any purpose, without fee, and without a written agreement is
*hereby granted, provided that the above copyright notice and this paragraph and
*the following two paragraphs appear in all copies.

IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY FOR
DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST
PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF
THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING,
BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A
PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN "AS IS" BASIS, AND
THE UNIVERSITY OF CALIFORNIA HAS NO OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT,
UPDATES, ENHANCEMENTS, OR MODIFICATIONS.
```

<div style="text-align: left; bottom: 12px; position: absolute; left: 20px; opacity: 0.5;">
source: postgresql.org
</div>

---
class: center, middle
background-size: cover
background-position: center
background-image: url("images/oracle.png")

<div style="text-align: left; bottom: 12px; position: absolute; left: 20px; opacity: 0.5;">
source: oracle.com
</div>

--
class: center, middle, inverse
background-size: cover
background-position: center
background-image: linear-gradient(#000000c3, #000000c3), url("images/oracle.png")

## .accent-pink[You may not disclose results of any Program benchmark tests without Oracle’s prior consent.]

???

License for another popular database

Doesn't even fit on the page, you need a lawyer just to read the license

Notice all of the restrictions

Other big companies to this too

---
class: inverse
background-size: cover
background-position: center
background-image: linear-gradient(#000000c3, #000000c3), url("https://images.unsplash.com/photo-1438866612080-aba418f9046c?ixlib=rb-1.2.1&ixid=eyJhcHBfaWQiOjEyMDd9&auto=format&fit=crop&w=2869&q=80")

# .turquoise[Postgres is …]

- ### an object relational database management system

- ### ACID compliant

- ### compliant with SQL:2016 standard

- ### open source

- ### free as in freedom

- ### free as in price

???

Atomicity - each transaction is treated as a single "unit"

Consistency - transactions can only bring the database from one valid state to another

Isolation - concurrent execution of transactions leaves the database in the same state that would have been obtained if the transactions were executed sequentially

Durability - once a transaction has been committed, it will remain committed even in the case of a system failure

SQL:2011 (or ISO/IEC 9075:2011) is the seventh revision of the ISO (1987) and ANSI (1986) standard

SQL:2016 is the latest, which added more optional features, but I couldn't find data available for that

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

PostgreSQL tries to conform with the SQL standard where such conformance does not contradict traditional features or could lead to poor architectural decisions.

"no relational database meets full conformance with this standard" -- postgres.com

---
class: center, middle, inverse
background-size: cover
background-position: center
background-image: linear-gradient(#4497f5c3, #75ddffc3), url("https://www.sciencemag.org/sites/default/files/styles/inline__699w__no_aspect/public/images/7513277910_0637bdd3ef_o.jpg")

> # _"So how do I get my hands on Postgres?"_

.right[
### — You (right now)
]

---
background-size: cover
background-position: center
background-image: url("images/postgres-website.png")

???

Binaries available:

* FreeBSD
* OpenBSD
* Red Hat
* Debian
* Ubuntu
* SuSE
* OpenSuSE
* macOS
* Solaris
* Windows

---
class: center, middle

.left[
## Hosted solutions provided by
]

---
class: center, middle, inverse
background-position: center
background-size: cover
background-image: linear-gradient(20deg, #000000a0, #00000090), linear-gradient(to right, #785F027a, #EDBD0F60), url('https://assets.nrdc.org/sites/default/files/styles/full_content--retina/public/media-uploads/wlds43_654640_2400.jpg?itok=LbhnLIk9')

<div style="font-size: 120px; font-family: Lato">
Example
</div>

---
name: dns-demo
class: top, left, inverse
background-position: center
background-size: cover
background-image: linear-gradient(20deg, #000000a0, #00000090), linear-gradient(to right, #785F027a, #EDBD0F60), url('https://assets.nrdc.org/sites/default/files/styles/full_content--retina/public/media-uploads/wlds43_654640_2400.jpg?itok=LbhnLIk9')

# Background

### We have a client who monitors networks to block malicious traffic, identify interesting patterns, etc.

### Client has focused on incoming traffic but what about outgoing traffic?

### Our client wants us to design a database to store outgoing DNS requests and query the data for interesting summaries

???

computer -> router -> modem -> servers

---
class: top, left, inverse
background-position: center
background-size: cover
background-image: linear-gradient(20deg, #000000a0, #00000090), linear-gradient(to right, #785F027a, #EDBD0F60), url('https://assets.nrdc.org/sites/default/files/styles/full_content--retina/public/media-uploads/wlds43_654640_2400.jpg?itok=LbhnLIk9')

# Background

### The client will provide data:

- ### Queries (timestamp, status, domain name, client IP)

- ### Domains (domain name, host IP)

- ### Geographic information (IP blocks, city name, lat/lon)

???

Queries contain timestamp, client, domain name

Domain names will be mapped to IP addresses if not already known

Geographic information will be used to map IP addresses to world locations

# Our Tasks

--
### 1. Configure a new Postgres database

### 2. Create a new schema and tables

### 3. Load reference data

### 4. Query the database

### 5. ???

### 6. Profit

<div style="font-size: 120px; font-family: Lato">
Walkthrough
</div>

---
class: center, middle, inverse
background-position: center
background-size: cover
background-image: linear-gradient(220deg, #f0dc8d66, #ff1c3e66), linear-gradient(220deg, #000000a0, #000000a0), url("https://images.ctfassets.net/22n7d68fswlw/2lUV1X3JY8SM24oe4mG6GI/7312b2a9a87c723cb4c91403eb1877f6/elephantqueen_0HERO")

<div style="font-size: 120px; font-family: Lato">
Recap
</div>

---
class: inverse
background-position: center
background-size: cover
background-image: linear-gradient(220deg, #f0dc8d66, #ff1c3e66), linear-gradient(220deg, #000000a0, #000000a0), url("https://images.ctfassets.net/22n7d68fswlw/2lUV1X3JY8SM24oe4mG6GI/7312b2a9a87c723cb4c91403eb1877f6/elephantqueen_0HERO")

# Postgres is

- ### an object relational database management system

- ### ACID compliant

- ### compliant with SQL:2016 standard

- ### open source

- ### free as in freedom

- ### free as in price

# Postgres has many cool features

- ### Date/time functions and operators

- ### Network address types

- ### JSON(b) support

- ### Extensions

- ### and more!

# Resources

### Citus Data - [citusdata.com](https://www.citusdata.com)

### Craig Kerstien's blog - [craigkerstiens.com](https://www.craigkerstiens.com)

### Postgres Guide - [postgresguide.com](https://www.postgresguide.com)

### Scaling Postgres - [scalingpostgres.com](https://www.scalingpostgres.com)

---
class: center, inverse
background-position: center
background-size: cover
background-image: linear-gradient(to top, #666491aa, #1B2964aa), url("https://i.redd.it/u7a30lemdo221.jpg")

# Get in touch!

.top[
.pull-left[
## @ellisvalentiner

### <i class="fab fa-github"></i> github

### <i class="fab fa-twitter" aria-hidden="true"></i> twitter

###<i class="fab fa-google" aria-hidden="true"></i> gmail
]
.pull-right[
## <br>

### <i class="fab fa-python"></i> python <i class="fab fa-r-project"></i> R

### <i class="fab fa-aws"></i> aws <i class="fab fa-docker"></i> docker

### <i class="fas fa-database"></i> databases
]
]