Working with PostgreSQL in R

PostgreSQL is a powerful, open-source, object-relational database management system (ORDBMS) that has been actively developed for over 35 years. It is known for its robustness, extensibility, SQL standards compliance, and ability to handle large-scale data workloads — making it a favourite choice for analysts, engineers, and data scientists alike.

Unlike lightweight databases such as SQLite, PostgreSQL is a full-featured server-based database system designed for multi-user concurrency, complex queries, and terabyte-scale datasets.

Why Use PostgreSQL with R?

R is exceptional for statistical computing and data analysis. PostgreSQL excels at storing, querying, and managing large structured datasets. Together they form a powerful combination:

Offload heavy queries to the database engine, pulling only summarised results into R
Persist datasets that are too large to hold in R’s memory
Share data across teams and tools using a central database server
Leverage SQL for filtering, joining, grouping, and aggregating before analysis

What This Tutorial Covers

Section	Topic
1	Installing PostgreSQL on Ubuntu and Windows
2	Creating databases, users, and tables
3	Connecting from R using `DBI` + `RPostgres`
4	Basic CRUD operations
5	Importing CSV and Parquet datasets
6	Advanced SQL: JOINs, window functions, CTEs
7	Using `dbplyr` for dplyr-style queries
8	Performance tuning with indexes and EXPLAIN
9	Security best practices
10	Disconnecting and cleanup

Datasets Used

This tutorial uses two freely available NYC Taxi datasets:

File	Description	Size
`yellow_tripdata_2023-01.parquet`	NYC Yellow Taxi trip records — January 2023	~50 MB
`taxi_zone_lookup.csv`	Mapping of location IDs to borough and zone names	~12 KB

Both can be downloaded from the NYC TLC Trip Record Data page.

Installing PostgreSQL

Ubuntu / Debian Linux

Open a terminal and run the following commands in sequence.

Step 1 — Update your package index:

sudo apt update

Step 2 — Install PostgreSQL and contrib extensions:

sudo apt install postgresql postgresql-contrib -y

Step 3 — Start the service and enable it on boot:

sudo systemctl start postgresql
sudo systemctl enable postgresql

Step 4 — Set a password for the default postgres superuser:

sudo -u postgres psql -c "ALTER USER postgres PASSWORD 'your_secure_password';"

Step 5 — Verify the installation:

sudo -u postgres psql -c "\conninfo"

Expected output:

You are connected to database "postgres" as user "postgres"
via socket in "/var/run/postgresql" at port "5432".

Installing a Specific Version

To install the latest stable release (e.g. PostgreSQL 17) from the official PGDG repository:

sudo sh -c 'echo "deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" \
  > /etc/apt/sources.list.d/pgdg.list'
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor \
  -o /etc/apt/trusted.gpg.d/postgresql.gpg
sudo apt update
sudo apt install postgresql-17 -y

Windows

Download the installer from postgresql.org/download/windows — choose the latest version.
Run the installer as Administrator.
During setup, select:
- ✅ PostgreSQL Server
- ✅ pgAdmin 4 (GUI management tool)
- ✅ Command Line Tools
Set a password for the postgres superuser — save this carefully.
Keep the default port 5432.
After installation, add the bin directory to your system PATH:
- C:\Program Files\PostgreSQL\17\bin
- Go to System Properties → Environment Variables → Path → Edit.
Start the service from Services (services.msc) — find postgresql-x64-17 and start it.

Verify from Command Prompt:

psql -U postgres -h localhost

Type \conninfo at the prompt to confirm.

Creating a Database and Schema

Via the psql Command Line

Connect to the PostgreSQL shell:

Ubuntu: sudo -u postgres psql
Windows: psql -U postgres -h localhost

Then run:

-- Create the database
CREATE DATABASE taxi_db;

-- Connect to it
\c taxi_db

-- Create a dedicated user (best practice — avoid using postgres superuser)
CREATE USER r_user WITH PASSWORD 'r_secure_pass';

-- Grant privileges on the database
GRANT ALL PRIVILEGES ON DATABASE taxi_db TO r_user;

-- Grant schema-level privileges (PostgreSQL 15+)
GRANT ALL ON SCHEMA public TO r_user;

-- Exit
\q

Via pgAdmin (GUI)

pgAdmin is the official graphical tool for PostgreSQL, installed alongside the server.

Open pgAdmin and connect to your local server.
Right-click Databases → Create → Database, name it taxi_db.
Under Login/Group Roles, create a new role r_user with a password and login permission.
Use the Query Tool (lightning bolt icon) to run SQL statements.

pgAdmin is Recommended for Beginners

pgAdmin provides point-and-click database management, a visual query editor with syntax highlighting, and tools to inspect table structure and data — much easier than working entirely in the terminal.

R Package Setup

Required Packages

Code

packages <- c(
  "DBI",          # Unified database interface
  "RPostgres",    # PostgreSQL driver for R
  "tidyverse",    # Data wrangling (includes dplyr, readr, ggplot2, etc.)
  "dbplyr",       # Translate dplyr to SQL (installed with tidyverse)
  "arrow",        # Read Parquet files efficiently
  "data.table",   # High-performance data manipulation
  "glue",         # Safe SQL string interpolation
  "keyring"       # Securely store database passwords
)

Install Missing Packages

#| label: install-packages

new_packages <- packages[!(packages %in% installed.packages()[, "Package"])]
if (length(new_packages) > 0) {
  install.packages(new_packages)
}

Load Libraries

Code

library(DBI)
library(RPostgres)
library(tidyverse)

── Attaching core tidyverse packages ──────────────────────── tidyverse 2.0.0 ──
✔ dplyr     1.2.1     ✔ readr     2.2.0
✔ forcats   1.0.1     ✔ stringr   1.6.0
✔ ggplot2   4.0.3     ✔ tibble    3.3.1
✔ lubridate 1.9.5     ✔ tidyr     1.3.2
✔ purrr     1.2.2     
── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──
✖ dplyr::filter() masks stats::filter()
✖ dplyr::lag()    masks stats::lag()
ℹ Use the conflicted package (<http://conflicted.r-lib.org/>) to force all conflicts to become errors

Code

library(dbplyr)


Attaching package: 'dbplyr'

The following objects are masked from 'package:dplyr':

    ident, sql

Code

library(arrow)


Attaching package: 'arrow'

The following object is masked from 'package:lubridate':

    duration

The following object is masked from 'package:utils':

    timestamp

Code

library(data.table)


Attaching package: 'data.table'

The following objects are masked from 'package:lubridate':

    hour, isoweek, isoyear, mday, minute, month, quarter, second, wday,
    week, yday, year

The following objects are masked from 'package:dplyr':

    between, first, last

The following object is masked from 'package:purrr':

    transpose

The following object is masked from 'package:base':

    %notin%

Code

library(glue)

Verify Package Versions

Code

pkg_versions <- sapply(packages, function(p) {
  if (p %in% installed.packages()[, "Package"]) {
    as.character(packageVersion(p))
  } else {
    "NOT INSTALLED"
  }
})

data.frame(
  Package = names(pkg_versions),
  Version = pkg_versions,
  row.names = NULL
)

     Package Version
1        DBI   1.3.0
2  RPostgres  1.4.10
3  tidyverse   2.0.0
4     dbplyr   2.5.2
5      arrow  24.0.0
6 data.table  1.18.4
7       glue   1.8.1
8    keyring   1.4.1

Connecting to PostgreSQL from R

About DBI and RPostgres

R uses a two-layer architecture for database connections:

DBI — Defines a standard interface (functions like dbConnect(), dbGetQuery(), dbWriteTable()) that works with any supported database.
RPostgres — Provides the PostgreSQL-specific driver. Built in C++ via Rcpp for high performance.

This means code using DBI functions is largely portable — you can switch databases (PostgreSQL → SQLite → MySQL) by changing only the driver.

Basic Connection

Code

con <- dbConnect(
  drv      = RPostgres::Postgres(),
  dbname   = "taxi_db",
  host     = "localhost",
  port     = 5432,
  user     = "r_user",
  password = "r_secure_pass"   # See security section for better approaches
)

# Verify connection
dbGetQuery(con, "SELECT version();")

                                                                                                                                   version
1 PostgreSQL 14.22 (Ubuntu 14.22-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04.3) 11.4.0, 64-bit

Secure Connections Using .Renviron

Never hardcode passwords in scripts. Instead, store credentials in your .Renviron file:

# In ~/.Renviron (one entry per line, no spaces around =)
PG_HOST=localhost
PG_PORT=5432
PG_DB=taxi_db
PG_USER=r_user
PG_PASSWORD=r_secure_pass

Then in R:

#| label: connect-secure

con <- dbConnect(
  drv      = RPostgres::Postgres(),
  dbname   = Sys.getenv("PG_DB"),
  host     = Sys.getenv("PG_HOST"),
  port     = as.integer(Sys.getenv("PG_PORT")),
  user     = Sys.getenv("PG_USER"),
  password = Sys.getenv("PG_PASSWORD")
)

Reload .Renviron Without Restarting R

readRenviron("~/.Renviron")

Using `keyring` for OS-Level Secret Storage

The keyring package stores secrets in your operating system’s secure credential store (macOS Keychain, Windows Credential Manager, Linux Secret Service):

```{r #| label: connect-keyring

library(keyring)

Store the password once (run interactively)

key_set(“postgresql”, username = “r_user”)

Then connect using the stored secret

con <- dbConnect( drv = RPostgres::Postgres(), dbname = “taxi_db”, host = “localhost”, port = 5432, user = “r_user”, password = key_get(“postgresql”, username = “r_user”) )


### Check Connection Status


::: {.cell}

```{.r .cell-code}
# List tables in the connected database
dbListTables(con)

character(0)

Code

# Check if a specific table exists
dbExistsTable(con, "taxi_zones")

[1] FALSE

Code

# Get connection info
dbGetInfo(con)

$dbname
[1] "taxi_db"

$host
[1] "localhost"

$port
[1] "5432"

$username
[1] "r_user"

$protocol.version
[1] 3

$server.version
[1] 140022

$db.version
[1] 140022

$pid
[1] 415805

:::

Basic CRUD Operations

CRUD stands for Create, Read, Update, Delete — the four fundamental database operations.

Create a Table

Code

dbExecute(con, "
  CREATE TABLE IF NOT EXISTS employees (
    id         SERIAL PRIMARY KEY,
    name       VARCHAR(100) NOT NULL,
    department VARCHAR(50),
    salary     NUMERIC(10, 2),
    hired_on   DATE DEFAULT CURRENT_DATE
  );
")

[1] 0

Key SQL Data Types in PostgreSQL

R Type	PostgreSQL Type	Notes
`integer`	`INTEGER` or `SERIAL`	`SERIAL` auto-increments
`numeric`	`NUMERIC(p, s)`	Exact decimal; use for money
`character`	`VARCHAR(n)`	Variable-length string
`logical`	`BOOLEAN`	TRUE / FALSE
`Date`	`DATE`	YYYY-MM-DD
`POSIXct`	`TIMESTAMP`	Date + time

Insert Data

Single row using SQL:

Code

dbExecute(con, "
  INSERT INTO employees (name, department, salary, hired_on)
  VALUES ('Alice Chen', 'Analytics', 85000.00, '2022-03-15');
")

[1] 1

Multiple rows at once:

Code

dbExecute(con, "
  INSERT INTO employees (name, department, salary, hired_on) VALUES
    ('Bob Smith',    'Engineering', 92000.00, '2021-07-01'),
    ('Carol White',  'Analytics',   78000.00, '2023-01-10'),
    ('David Lee',    'Marketing',   67000.00, '2020-11-22'),
    ('Eve Johnson',  'Engineering', 95000.00, '2019-06-30');
")

[1] 4

Write an R data frame directly (most practical approach):

Code

new_hires <- data.frame(
  name       = c("Frank Brown", "Grace Kim"),
  department = c("Analytics", "Engineering"),
  salary     = c(81000.00, 88000.00),
  hired_on   = as.Date(c("2024-02-01", "2024-03-15"))
)

dbWriteTable(
  conn      = con,
  name      = "employees",
  value     = new_hires,
  append    = TRUE,   # Add to existing table
  row.names = FALSE
)

Read Data

Fetch all rows:

Code

employees <- dbGetQuery(con, "SELECT * FROM employees ORDER BY id;")
print(employees)

  id        name  department salary   hired_on
1  1  Alice Chen   Analytics  85000 2022-03-15
2  2   Bob Smith Engineering  92000 2021-07-01
3  3 Carol White   Analytics  78000 2023-01-10
4  4   David Lee   Marketing  67000 2020-11-22
5  5 Eve Johnson Engineering  95000 2019-06-30
6  6 Frank Brown   Analytics  81000 2024-02-01
7  7   Grace Kim Engineering  88000 2024-03-15

Fetch with conditions:

Code

analytics_team <- dbGetQuery(con, "
  SELECT name, salary
  FROM employees
  WHERE department = 'Analytics'
  ORDER BY salary DESC;
")
print(analytics_team)

         name salary
1  Alice Chen  85000
2 Frank Brown  81000
3 Carol White  78000

Parameterised queries (safe against SQL injection):

Code

dept <- "Engineering"

result <- dbGetQuery(
  conn    = con,
  statement = "SELECT * FROM employees WHERE department = $1;",
  params  = list(dept)
)
print(result)

  id        name  department salary   hired_on
1  2   Bob Smith Engineering  92000 2021-07-01
2  5 Eve Johnson Engineering  95000 2019-06-30
3  7   Grace Kim Engineering  88000 2024-03-15

Always Use Parameterised Queries for User Input

Never build SQL strings using paste() with user-supplied values. This opens the door to SQL injection attacks. Use $1, $2, … placeholders with the params argument instead.

Fetch a single value:

Code

avg_salary <- dbGetQuery(con, "SELECT ROUND(AVG(salary), 2) AS avg_salary FROM employees;")
cat("Average salary: $", avg_salary$avg_salary, "\n")

Average salary: $ 83714.29

Stream large results (low memory):

Code

# Use dbSendQuery + dbFetch for chunked reading
query <- dbSendQuery(con, "SELECT * FROM employees;")

chunks <- list()
while (!dbHasCompleted(query)) {
  chunk <- dbFetch(query, n = 3)   # Read 3 rows at a time
  chunks <- c(chunks, list(chunk))
}
dbClearResult(query)

all_data <- bind_rows(chunks)
print(all_data)

  id        name  department salary   hired_on
1  1  Alice Chen   Analytics  85000 2022-03-15
2  2   Bob Smith Engineering  92000 2021-07-01
3  3 Carol White   Analytics  78000 2023-01-10
4  4   David Lee   Marketing  67000 2020-11-22
5  5 Eve Johnson Engineering  95000 2019-06-30
6  6 Frank Brown   Analytics  81000 2024-02-01
7  7   Grace Kim Engineering  88000 2024-03-15

Update Data

Code

# Give all Analytics employees a 10% raise
rows_affected <- dbExecute(con, "
  UPDATE employees
  SET salary = salary * 1.10
  WHERE department = 'Analytics';
")
cat("Rows updated:", rows_affected, "\n")

Rows updated: 3

Code

# Verify
dbGetQuery(con, "SELECT name, salary FROM employees WHERE department = 'Analytics';")

         name salary
1  Alice Chen  93500
2 Carol White  85800
3 Frank Brown  89100

Delete Data

Code

# Delete employees hired before 2021
dbExecute(con, "
  DELETE FROM employees
  WHERE hired_on < '2021-01-01';
")

[1] 2

Code

# Confirm deletion
dbGetQuery(con, "SELECT COUNT(*) AS remaining FROM employees;")

  remaining
1         5

Drop a Table

Code

# Only drop if it exists (safe to re-run)
dbExecute(con, "DROP TABLE IF EXISTS employees;")

[1] 0

Importing Real-World Datasets

We now import the two NYC taxi datasets into PostgreSQL.

Setup: File Paths

Code

data_folder <- "/home/zia207/WebSites/R_Website/High_Performance_Computing_R/R_Markdown/Data/"   # Adjust to your path

taxi_zones_file    <- file.path(data_folder, "taxi_zone_lookup.csv")
yellow_trips_file  <- file.path(data_folder, "yellow_tripdata_2023-01.parquet")

Import taxi_zone_lookup.csv

This is a small reference table (~265 rows). Read with readr and write directly.

Code

# Read CSV into R
zones <- read_csv(taxi_zones_file, col_types = cols(
  LocationID   = col_integer(),
  Borough      = col_character(),
  Zone         = col_character(),
  service_zone = col_character()
))

# Rename to snake_case for PostgreSQL
names(zones) <- tolower(names(zones))
names(zones)[names(zones) == "locationid"] <- "location_id"

# Create the table (explicit schema is better than letting DBI infer)
dbExecute(con, "DROP TABLE IF EXISTS taxi_zones;")

NOTICE:  table "taxi_zones" does not exist, skipping

[1] 0

Code

dbExecute(con, "
  CREATE TABLE taxi_zones (
    location_id  INTEGER PRIMARY KEY,
    borough      VARCHAR(50),
    zone         VARCHAR(100),
    service_zone VARCHAR(50)
  );
")

[1] 0

Code

# Insert data
dbWriteTable(con, "taxi_zones", zones, append = TRUE, row.names = FALSE)

# Verify
dbGetQuery(con, "SELECT COUNT(*) AS n_zones FROM taxi_zones;")

  n_zones
1     265

Code

dbGetQuery(con, "SELECT * FROM taxi_zones LIMIT 5;")

  location_id       borough                    zone service_zone
1           1           EWR          Newark Airport          EWR
2           2        Queens             Jamaica Bay    Boro Zone
3           3         Bronx Allerton/Pelham Gardens    Boro Zone
4           4     Manhattan           Alphabet City  Yellow Zone
5           5 Staten Island           Arden Heights    Boro Zone

Import yellow_tripdata_2023-01.parquet (Large File)

Parquet files can contain millions of rows. We use arrow to read in batches and insert chunk-by-chunk to stay within memory limits.

Code

# Drop and recreate with explicit types
dbExecute(con, "DROP TABLE IF EXISTS yellow_trips;")

NOTICE:  table "yellow_trips" does not exist, skipping

[1] 0

Code

dbExecute(con, "
  CREATE TABLE yellow_trips (
    vendorid                INTEGER,
    tpep_pickup_datetime    TIMESTAMP,
    tpep_dropoff_datetime   TIMESTAMP,
    passenger_count         INTEGER,
    trip_distance           NUMERIC(8, 2),
    ratecodeid              INTEGER,
    store_and_fwd_flag      VARCHAR(1),
    pulocationid            INTEGER,
    dolocationid            INTEGER,
    payment_type            INTEGER,
    fare_amount             NUMERIC(10, 2),
    extra                   NUMERIC(8, 2),
    mta_tax                 NUMERIC(8, 2),
    tip_amount              NUMERIC(10, 2),
    tolls_amount            NUMERIC(10, 2),
    improvement_surcharge   NUMERIC(8, 2),
    total_amount            NUMERIC(10, 2),
    congestion_surcharge    NUMERIC(8, 2),
    airport_fee             NUMERIC(8, 2)
  );
")

[1] 0

Code

# Open Parquet as an Arrow Dataset (does NOT load into memory yet)
dataset <- arrow::open_dataset(yellow_trips_file)

cat("Total rows in Parquet file:", nrow(dataset), "\n")

Total rows in Parquet file: 3066766

Code

cat("Columns:", paste(names(dataset), collapse = ", "), "\n")

Columns: VendorID, tpep_pickup_datetime, tpep_dropoff_datetime, passenger_count, trip_distance, RatecodeID, store_and_fwd_flag, PULocationID, DOLocationID, payment_type, fare_amount, extra, mta_tax, tip_amount, tolls_amount, improvement_surcharge, total_amount, congestion_surcharge, airport_fee

Code

# Define chunk size (rows per batch; adjust based on available RAM)
chunk_size <- 100000

# Collect full data into R — for large files use batched approach below
trips_df <- dataset |>
  collect() |>
  rename_with(tolower)   # lowercase all column names

# Insert in chunks to PostgreSQL
n_rows  <- nrow(trips_df)
n_chunks <- ceiling(n_rows / chunk_size)

cat("Inserting", n_rows, "rows in", n_chunks, "chunks...\n")

Inserting 3066766 rows in 31 chunks...

Code

for (i in seq_len(n_chunks)) {
  start_row <- (i - 1) * chunk_size + 1
  end_row   <- min(i * chunk_size, n_rows)
  chunk     <- trips_df[start_row:end_row, ]
  
  dbWriteTable(con, "yellow_trips", chunk, append = TRUE, row.names = FALSE)
  cat("  Chunk", i, "/", n_chunks, "inserted (rows", start_row, "-", end_row, ")\n")
}

  Chunk 1 / 31 inserted (rows 1 - 1e+05 )
  Chunk 2 / 31 inserted (rows 100001 - 2e+05 )
  Chunk 3 / 31 inserted (rows 200001 - 3e+05 )
  Chunk 4 / 31 inserted (rows 300001 - 4e+05 )
  Chunk 5 / 31 inserted (rows 400001 - 5e+05 )
  Chunk 6 / 31 inserted (rows 500001 - 6e+05 )
  Chunk 7 / 31 inserted (rows 600001 - 7e+05 )
  Chunk 8 / 31 inserted (rows 700001 - 8e+05 )
  Chunk 9 / 31 inserted (rows 800001 - 9e+05 )
  Chunk 10 / 31 inserted (rows 900001 - 1e+06 )
  Chunk 11 / 31 inserted (rows 1000001 - 1100000 )
  Chunk 12 / 31 inserted (rows 1100001 - 1200000 )
  Chunk 13 / 31 inserted (rows 1200001 - 1300000 )
  Chunk 14 / 31 inserted (rows 1300001 - 1400000 )
  Chunk 15 / 31 inserted (rows 1400001 - 1500000 )
  Chunk 16 / 31 inserted (rows 1500001 - 1600000 )
  Chunk 17 / 31 inserted (rows 1600001 - 1700000 )
  Chunk 18 / 31 inserted (rows 1700001 - 1800000 )
  Chunk 19 / 31 inserted (rows 1800001 - 1900000 )
  Chunk 20 / 31 inserted (rows 1900001 - 2e+06 )
  Chunk 21 / 31 inserted (rows 2000001 - 2100000 )
  Chunk 22 / 31 inserted (rows 2100001 - 2200000 )
  Chunk 23 / 31 inserted (rows 2200001 - 2300000 )
  Chunk 24 / 31 inserted (rows 2300001 - 2400000 )
  Chunk 25 / 31 inserted (rows 2400001 - 2500000 )
  Chunk 26 / 31 inserted (rows 2500001 - 2600000 )
  Chunk 27 / 31 inserted (rows 2600001 - 2700000 )
  Chunk 28 / 31 inserted (rows 2700001 - 2800000 )
  Chunk 29 / 31 inserted (rows 2800001 - 2900000 )
  Chunk 30 / 31 inserted (rows 2900001 - 3e+06 )
  Chunk 31 / 31 inserted (rows 3000001 - 3066766 )

Code

cat("Import complete.\n")

Import complete.

Alternative: Very Large Files (> 1 GB)

For files too large to fit in memory, use Arrow’s RecordBatch streaming reader:

reader <- arrow::ParquetFileReader$create(yellow_trips_file)
n_groups <- reader$num_row_groups

for (i in seq_len(n_groups)) {
  batch <- reader$ReadRowGroup(i - 1)   # 0-indexed
  chunk <- as.data.frame(batch)
  names(chunk) <- tolower(names(chunk))
  dbWriteTable(con, "yellow_trips", chunk, append = TRUE, row.names = FALSE)
  cat("Row group", i, "of", n_groups, "inserted\n")
}

Verify Imported Data

Code

# Row counts
dbGetQuery(con, "SELECT COUNT(*) AS trip_count FROM yellow_trips;")

  trip_count
1    3066766

Code

dbGetQuery(con, "SELECT COUNT(*) AS zone_count FROM taxi_zones;")

  zone_count
1        265

Code

# Preview trips
dbGetQuery(con, "SELECT * FROM yellow_trips LIMIT 5;")

  vendorid tpep_pickup_datetime tpep_dropoff_datetime passenger_count
1        2  2023-01-01 00:32:10   2023-01-01 00:40:36               1
2        2  2023-01-01 00:55:08   2023-01-01 01:01:27               1
3        2  2023-01-01 00:25:04   2023-01-01 00:37:49               1
4        1  2023-01-01 00:03:48   2023-01-01 00:13:25               0
5        2  2023-01-01 00:10:29   2023-01-01 00:21:19               1
  trip_distance ratecodeid store_and_fwd_flag pulocationid dolocationid
1          0.97          1                  N          161          141
2          1.10          1                  N           43          237
3          2.51          1                  N           48          238
4          1.90          1                  N          138            7
5          1.43          1                  N          107           79
  payment_type fare_amount extra mta_tax tip_amount tolls_amount
1            2         9.3  1.00     0.5       0.00            0
2            1         7.9  1.00     0.5       4.00            0
3            1        14.9  1.00     0.5      15.00            0
4            1        12.1  7.25     0.5       0.00            0
5            1        11.4  1.00     0.5       3.28            0
  improvement_surcharge total_amount congestion_surcharge airport_fee
1                     1        14.30                  2.5        0.00
2                     1        16.90                  2.5        0.00
3                     1        34.90                  2.5        0.00
4                     1        20.85                  0.0        1.25
5                     1        19.68                  2.5        0.00

Code

# Check date range
dbGetQuery(con, "
  SELECT 
    MIN(tpep_pickup_datetime) AS earliest,
    MAX(tpep_pickup_datetime) AS latest
  FROM yellow_trips;
")

             earliest              latest
1 2008-12-31 23:01:42 2023-02-01 00:56:53

Advanced SQL Queries from R

JOINs — Trips by Borough

Code

trips_by_borough <- dbGetQuery(con, "
  SELECT 
    z.borough,
    COUNT(*)                         AS trip_count,
    ROUND(AVG(t.fare_amount), 2)     AS avg_fare,
    ROUND(AVG(t.trip_distance), 2)   AS avg_distance_miles
  FROM yellow_trips t
  JOIN taxi_zones z
    ON t.pulocationid = z.location_id
  GROUP BY z.borough
  ORDER BY trip_count DESC;
")

print(trips_by_borough)

        borough trip_count avg_fare avg_distance_miles
1     Manhattan    2715369    14.78               2.88
2        Queens     286645    49.98              12.32
3       Unknown      41763    29.13               7.36
4      Brooklyn      18076    26.81               5.68
5         Bronx       4162    30.24               5.30
6           EWR        410    87.99               1.59
7 Staten Island        341    48.74              11.36

Time-Series — Trips per Hour of Day

Code

hourly_demand <- dbGetQuery(con, "
  SELECT 
    EXTRACT(HOUR FROM tpep_pickup_datetime) AS pickup_hour,
    COUNT(*)                                AS trip_count
  FROM yellow_trips
  GROUP BY pickup_hour
  ORDER BY pickup_hour;
")

# Plot
ggplot(hourly_demand, aes(x = pickup_hour, y = trip_count)) +
  geom_col(fill = "#2C7BB6", colour = "white") +
  scale_x_continuous(breaks = 0:23) +
  scale_y_continuous(labels = scales::comma) +
  labs(
    title    = "NYC Yellow Taxi Demand by Hour of Day (January 2023)",
    subtitle = "Total trip pickups per hour",
    x        = "Hour of Day (0 = midnight)",
    y        = "Number of Trips"
  ) +
  theme_minimal(base_size = 13)

Window Functions — Top Zones by Average Tip

Window functions let you compute rankings, running totals, and moving averages without collapsing rows:

Code

top_tip_zones <- dbGetQuery(con, "
  WITH zone_tips AS (
    SELECT
      z.zone,
      z.borough,
      ROUND(AVG(t.tip_amount), 2)     AS avg_tip,
      ROUND(AVG(t.fare_amount), 2)    AS avg_fare,
      COUNT(*)                        AS trip_count
    FROM yellow_trips t
    JOIN taxi_zones z ON t.dolocationid = z.location_id
    WHERE t.payment_type = 1       -- Credit card (tips are recorded)
      AND t.trip_distance > 0
    GROUP BY z.zone, z.borough
    HAVING COUNT(*) > 100          -- Minimum 100 trips for reliability
  )
  SELECT
    zone,
    borough,
    avg_tip,
    avg_fare,
    trip_count,
    RANK() OVER (ORDER BY avg_tip DESC) AS rank_by_tip
  FROM zone_tips
  ORDER BY rank_by_tip
  LIMIT 15;
")

print(top_tip_zones)

                                  zone  borough avg_tip avg_fare trip_count
1                       Newark Airport      EWR   17.68    94.40       5710
2                                 <NA>  Unknown   15.06   100.59       6734
3                          JFK Airport   Queens   13.15    67.50      20121
4                      Windsor Terrace Brooklyn   11.33    53.86       1017
5  Riverdale/North Riverdale/Fieldston    Bronx   11.00    57.34        799
6                           Kensington Brooklyn   10.79    55.97        573
7                        Rockaway Park   Queens   10.60    60.43        193
8                    LaGuardia Airport   Queens   10.46    43.96      25130
9                            Bay Ridge Brooklyn   10.27    57.85       1359
10                    Sunset Park East Brooklyn   10.09    62.11        326
11                    Sunset Park West Brooklyn    9.96    52.00       1015
12                Flatbush/Ditmas Park Brooklyn    9.79    52.88       1803
13          Spuyten Duyvil/Kingsbridge    Bronx    9.65    51.96        877
14                             Belmont    Bronx    9.56    52.00        212
15                          Park Slope Brooklyn    9.44    45.64       6800
   rank_by_tip
1            1
2            2
3            3
4            4
5            5
6            6
7            7
8            8
9            9
10          10
11          11
12          12
13          13
14          14
15          15

Common Table Expressions (CTEs) — Peak vs Off-Peak Comparison

Code

peak_comparison <- dbGetQuery(con, "
  WITH trip_periods AS (
    SELECT
      fare_amount,
      tip_amount,
      trip_distance,
      CASE
        WHEN EXTRACT(HOUR FROM tpep_pickup_datetime) BETWEEN 7  AND 9  THEN 'Morning Peak'
        WHEN EXTRACT(HOUR FROM tpep_pickup_datetime) BETWEEN 16 AND 19 THEN 'Evening Peak'
        ELSE 'Off-Peak'
      END AS period
    FROM yellow_trips
    WHERE fare_amount > 0 AND trip_distance > 0
  )
  SELECT
    period,
    COUNT(*)                          AS trips,
    ROUND(AVG(fare_amount), 2)        AS avg_fare,
    ROUND(AVG(tip_amount), 2)         AS avg_tip,
    ROUND(AVG(trip_distance), 2)      AS avg_distance
  FROM trip_periods
  GROUP BY period
  ORDER BY avg_fare DESC;
")

print(peak_comparison)

        period   trips avg_fare avg_tip avg_distance
1     Off-Peak 1873488    18.83    3.39         3.75
2 Evening Peak  797088    18.18    3.53         3.92
3 Morning Peak  327806    17.96    3.20         4.59

Payment Type Analysis

Code

payment_labels <- data.frame(
  payment_type = 1:6,
  payment_name = c("Credit Card", "Cash", "No Charge",
                   "Dispute", "Unknown", "Voided Trip")
)

payment_summary <- dbGetQuery(con, "
  SELECT
    payment_type,
    COUNT(*)                      AS trip_count,
    ROUND(SUM(fare_amount), 2)    AS total_fare,
    ROUND(AVG(tip_amount), 2)     AS avg_tip
  FROM yellow_trips
  GROUP BY payment_type
  ORDER BY trip_count DESC;
")

payment_summary <- payment_summary |>
  left_join(payment_labels, by = "payment_type") |>
  select(payment_name, everything(), -payment_type)

print(payment_summary)

  payment_name trip_count  total_fare avg_tip
1  Credit Card    2411462 44997989.58    4.17
2         Cash     532241  9624404.65    0.00
3         <NA>      71743  1493804.50    3.73
4      Dispute      33297    65076.98    0.05
5    No Charge      18023   146225.83    0.03

Using dbplyr for dplyr-Style Database Queries

dbplyr translates dplyr verbs into SQL automatically. This is ideal for analysts more comfortable with tidyverse syntax.

Create Lazy Table References

Code

# These don't load data — they just reference the tables
trips_tbl <- tbl(con, "yellow_trips")
zones_tbl <- tbl(con, "taxi_zones")

# Show dimensions
trips_tbl |> count()    # Runs SELECT COUNT(*) ...

# Source:   SQL [?? x 1]
# Database: postgres  [r_user@localhost:5432/taxi_db]
        n
  <int64>
1 3066766

Filter, Select, and Summarise

Code

avg_fare_by_borough <- trips_tbl |>
  left_join(zones_tbl, by = c("pulocationid" = "location_id")) |>
  filter(trip_distance > 1, fare_amount > 0) |>
  group_by(borough) |>
  summarise(
    trips        = n(),
    avg_fare     = mean(fare_amount, na.rm = TRUE),
    avg_distance = mean(trip_distance, na.rm = TRUE),
    avg_tip      = mean(tip_amount, na.rm = TRUE)
  ) |>
  arrange(desc(trips)) |>
  collect()   # <-- Executes query and pulls result into R

print(avg_fare_by_borough)

# A tibble: 7 × 5
  borough         trips avg_fare avg_distance avg_tip
  <chr>         <int64>    <dbl>        <dbl>   <dbl>
1 Manhattan     2022475     17.3         3.59   3.27 
2 Queens         268289     52.7        13.0    8.27 
3 Unknown         32330     31.1         9.30   5.53 
4 Brooklyn        14139     28.3         7.14   3.20 
5 Bronx            2805     31.9         7.70   0.588
6 Staten Island     207     60.5        18.5    0.998
7 EWR                36     85.5        16.4   11.6

Inspect the Generated SQL

Code

query <- trips_tbl |>
  filter(trip_distance > 5) |>
  group_by(payment_type) |>
  summarise(avg_fare = mean(fare_amount, na.rm = TRUE))

# Print the SQL without executing
show_query(query)

<SQL>
SELECT "payment_type", AVG("fare_amount") AS "avg_fare"
FROM (
  SELECT "yellow_trips".*
  FROM "yellow_trips"
  WHERE ("trip_distance" > 5.0)
) AS "q01"
GROUP BY "payment_type"

Time-Based Grouping with dbplyr

Code

daily_trips <- trips_tbl |>
  mutate(trip_date = sql("tpep_pickup_datetime::date")) |>
  group_by(trip_date) |>
  summarise(
    trips    = n(),
    revenue  = sum(total_amount, na.rm = TRUE)
  ) |>
  arrange(trip_date) |>
  collect()

# Plot
ggplot(daily_trips, aes(x = as.Date(trip_date), y = trips)) +
  geom_line(colour = "#E63946", linewidth = 0.8) +
  geom_smooth(method = "loess", se = FALSE, colour = "#457B9D") +
  scale_y_continuous(labels = scales::comma) +
  scale_x_date(date_labels = "%b %d") +
  labs(
    title = "Daily Yellow Taxi Trips — January 2023",
    x     = "Date",
    y     = "Trip Count"
  ) +
  theme_minimal(base_size = 13)

`geom_smooth()` using formula = 'y ~ x'

Performance Tuning

Creating Indexes

Without indexes, PostgreSQL performs a full table scan for every query — slow for millions of rows. Add indexes on columns used in WHERE, JOIN, and ORDER BY clauses.

Code

# Index on pickup location (used in JOINs)
dbExecute(con, "
  CREATE INDEX IF NOT EXISTS idx_trips_pulocationid
  ON yellow_trips (pulocationid);
")

[1] 0

Code

# Index on dropoff location
dbExecute(con, "
  CREATE INDEX IF NOT EXISTS idx_trips_dolocationid
  ON yellow_trips (dolocationid);
")

[1] 0

Code

# Index on pickup timestamp (for time-range queries)
dbExecute(con, "
  CREATE INDEX IF NOT EXISTS idx_trips_pickup_datetime
  ON yellow_trips (tpep_pickup_datetime);
")

[1] 0

Code

# Composite index for common filter + join pattern
dbExecute(con, "
  CREATE INDEX IF NOT EXISTS idx_trips_location_time
  ON yellow_trips (pulocationid, tpep_pickup_datetime);
")

[1] 0

Code

cat("Indexes created.\n")

Indexes created.

EXPLAIN ANALYZE — Query Performance Diagnostics

EXPLAIN ANALYZE shows exactly how PostgreSQL executes a query, including whether indexes are used and how long each step takes.

Code

explain_result <- dbGetQuery(con, "
  EXPLAIN ANALYZE
  SELECT z.borough, COUNT(*) AS trips
  FROM yellow_trips t
  JOIN taxi_zones z ON t.pulocationid = z.location_id
  GROUP BY z.borough
  ORDER BY trips DESC;
")

cat(explain_result[[1]], sep = "\n")

Sort  (cost=73344.62..73344.64 rows=7 width=16) (actual time=397.965..404.106 rows=7 loops=1)
  Sort Key: (count(*)) DESC
  Sort Method: quicksort  Memory: 25kB
  ->  Finalize GroupAggregate  (cost=73342.75..73344.52 rows=7 width=16) (actual time=397.955..404.101 rows=7 loops=1)
        Group Key: z.borough
        ->  Gather Merge  (cost=73342.75..73344.38 rows=14 width=16) (actual time=397.948..404.093 rows=19 loops=1)
              Workers Planned: 2
              Workers Launched: 2
              ->  Sort  (cost=72342.72..72342.74 rows=7 width=16) (actual time=390.494..390.497 rows=6 loops=3)
                    Sort Key: z.borough
                    Sort Method: quicksort  Memory: 25kB
                    Worker 0:  Sort Method: quicksort  Memory: 25kB
                    Worker 1:  Sort Method: quicksort  Memory: 25kB
                    ->  Partial HashAggregate  (cost=72342.56..72342.63 rows=7 width=16) (actual time=390.435..390.438 rows=6 loops=3)
                          Group Key: z.borough
                          Batches: 1  Memory Usage: 24kB
                          Worker 0:  Batches: 1  Memory Usage: 24kB
                          Worker 1:  Batches: 1  Memory Usage: 24kB
                          ->  Hash Join  (cost=9.39..65953.46 rows=1277819 width=8) (actual time=0.388..256.090 rows=1022255 loops=3)
                                Hash Cond: (t.pulocationid = z.location_id)
                                ->  Parallel Index Only Scan using idx_trips_pulocationid on yellow_trips t  (cost=0.43..62536.58 rows=1277819 width=4) (actual time=0.089..130.002 rows=1022255 loops=3)
                                      Heap Fetches: 366776
                                ->  Hash  (cost=5.65..5.65 rows=265 width=12) (actual time=0.234..0.235 rows=265 loops=3)
                                      Buckets: 1024  Batches: 1  Memory Usage: 21kB
                                      ->  Seq Scan on taxi_zones z  (cost=0.00..5.65 rows=265 width=12) (actual time=0.031..0.118 rows=265 loops=3)
Planning Time: 1.260 ms
Execution Time: 404.165 ms

Look for: - Index Scan (fast) vs Seq Scan (slow — no index used) - actual time — milliseconds per node - rows — estimated vs actual row counts

VACUUM and ANALYZE

After large inserts or deletes, run maintenance:

Code

# Update table statistics (helps query planner)
dbExecute(con, "ANALYZE yellow_trips;")

[1] 0

Code

# Reclaim storage from deleted rows (cannot run inside a transaction)
# Run this outside of a transaction block in psql:
# VACUUM ANALYZE yellow_trips;

Connection Pooling with `pool`

For Shiny apps or multi-user scenarios, use the pool package to manage multiple connections efficiently:

```{r #| label: connection-pooling

install.packages(“pool”)

library(pool)

pool <- dbPool( drv = RPostgres::Postgres(), dbname = Sys.getenv(“PG_DB”), host = Sys.getenv(“PG_HOST”), port = as.integer(Sys.getenv(“PG_PORT”)), user = Sys.getenv(“PG_USER”), password = Sys.getenv(“PG_PASSWORD”), minSize = 2, # Keep at least 2 connections open maxSize = 10 # Allow up to 10 concurrent connections )

Use just like a regular connection

result <- dbGetQuery(pool, “SELECT COUNT(*) FROM yellow_trips;“)

Close the pool when done (e.g., when Shiny app shuts down)

poolClose(pool)



## Transactions and Error Handling

Transactions group multiple statements into an atomic unit — either all succeed or all are rolled back.


::: {.cell}

```{.r .cell-code}
tryCatch({
  dbBegin(con)

  dbExecute(con, "
    UPDATE employees SET salary = salary * 1.05 WHERE department = 'Engineering';
  ")
  dbExecute(con, "
    INSERT INTO audit_log (event, logged_at)
    VALUES ('Engineering salary increase 5%', NOW());
  ")

  dbCommit(con)
  cat("Transaction committed successfully.\n")

}, error = function(e) {
  dbRollback(con)
  cat("Transaction rolled back due to error:", conditionMessage(e), "\n")
})

Transaction rolled back due to error: Failed to prepare query : ERROR:  relation "employees" does not exist
LINE 2:     UPDATE employees SET salary = salary * 1.05 WHERE depart...
                   ^

:::

Safe Query Wrapper

Code

safe_query <- function(con, sql, params = NULL) {
  tryCatch(
    {
      if (is.null(params)) {
        dbGetQuery(con, sql)
      } else {
        dbGetQuery(con, sql, params = params)
      }
    },
    error = function(e) {
      message("Query failed: ", conditionMessage(e))
      NULL
    }
  )
}

# Usage
result <- safe_query(con, "SELECT * FROM yellow_trips LIMIT 5;")

Database Metadata and Inspection

Code

# List all tables in the database
dbListTables(con)

[1] "taxi_zones"   "yellow_trips"

Code

# List columns in a table
dbListFields(con, "yellow_trips")

 [1] "vendorid"              "tpep_pickup_datetime"  "tpep_dropoff_datetime"
 [4] "passenger_count"       "trip_distance"         "ratecodeid"           
 [7] "store_and_fwd_flag"    "pulocationid"          "dolocationid"         
[10] "payment_type"          "fare_amount"           "extra"                
[13] "mta_tax"               "tip_amount"            "tolls_amount"         
[16] "improvement_surcharge" "total_amount"          "congestion_surcharge" 
[19] "airport_fee"

Code

# Detailed column info via SQL
dbGetQuery(con, "
  SELECT 
    column_name,
    data_type,
    character_maximum_length,
    is_nullable
  FROM information_schema.columns
  WHERE table_name = 'yellow_trips'
  ORDER BY ordinal_position;
")

             column_name                   data_type character_maximum_length
1               vendorid                     integer                       NA
2   tpep_pickup_datetime timestamp without time zone                       NA
3  tpep_dropoff_datetime timestamp without time zone                       NA
4        passenger_count                     integer                       NA
5          trip_distance                     numeric                       NA
6             ratecodeid                     integer                       NA
7     store_and_fwd_flag           character varying                        1
8           pulocationid                     integer                       NA
9           dolocationid                     integer                       NA
10          payment_type                     integer                       NA
11           fare_amount                     numeric                       NA
12                 extra                     numeric                       NA
13               mta_tax                     numeric                       NA
14            tip_amount                     numeric                       NA
15          tolls_amount                     numeric                       NA
16 improvement_surcharge                     numeric                       NA
17          total_amount                     numeric                       NA
18  congestion_surcharge                     numeric                       NA
19           airport_fee                     numeric                       NA
   is_nullable
1          YES
2          YES
3          YES
4          YES
5          YES
6          YES
7          YES
8          YES
9          YES
10         YES
11         YES
12         YES
13         YES
14         YES
15         YES
16         YES
17         YES
18         YES
19         YES

Code

# List indexes
dbGetQuery(con, "
  SELECT
    indexname,
    indexdef
  FROM pg_indexes
  WHERE tablename = 'yellow_trips';
")

                  indexname
1    idx_trips_pulocationid
2    idx_trips_dolocationid
3 idx_trips_pickup_datetime
4   idx_trips_location_time
                                                                                                      indexdef
1                        CREATE INDEX idx_trips_pulocationid ON public.yellow_trips USING btree (pulocationid)
2                        CREATE INDEX idx_trips_dolocationid ON public.yellow_trips USING btree (dolocationid)
3             CREATE INDEX idx_trips_pickup_datetime ON public.yellow_trips USING btree (tpep_pickup_datetime)
4 CREATE INDEX idx_trips_location_time ON public.yellow_trips USING btree (pulocationid, tpep_pickup_datetime)

Code

# Table size on disk
dbGetQuery(con, "
  SELECT
    pg_size_pretty(pg_total_relation_size('yellow_trips')) AS total_size,
    pg_size_pretty(pg_relation_size('yellow_trips'))       AS table_size,
    pg_size_pretty(pg_indexes_size('yellow_trips'))        AS index_size;
")

  total_size table_size index_size
1     584 MB     399 MB     185 MB

Writing Query Results Back to PostgreSQL

Code

# Create a summary in R
borough_summary <- dbGetQuery(con, "
  SELECT
    z.borough,
    COUNT(*)                         AS trip_count,
    ROUND(AVG(t.fare_amount), 2)     AS avg_fare,
    ROUND(SUM(t.total_amount), 2)    AS total_revenue
  FROM yellow_trips t
  JOIN taxi_zones z ON t.pulocationid = z.location_id
  GROUP BY z.borough
  ORDER BY trip_count DESC;
")

# Write summary back as a new table
dbWriteTable(
  conn      = con,
  name      = "borough_summary",
  value     = borough_summary,
  overwrite = TRUE,
  row.names = FALSE
)

# Verify
dbGetQuery(con, "SELECT * FROM borough_summary;")

        borough trip_count avg_fare total_revenue
1     Manhattan    2715369    14.78   61071964.50
2        Queens     286645    49.98   19283937.36
3       Unknown      41763    29.13    1704629.49
4      Brooklyn      18076    26.81     596810.31
5         Bronx       4162    30.24     143734.99
6           EWR        410    87.99      42794.04
7 Staten Island        341    48.74      21321.53

Security Best Practices

Practice	Why It Matters
Never hardcode passwords in scripts	Scripts are often committed to Git
Use `.Renviron` or `keyring`	Keeps credentials out of source code
Create a dedicated database user	Avoid using the `postgres` superuser
Grant minimum required privileges	Principle of least privilege
Use parameterised queries	Prevents SQL injection
Encrypt connections with SSL	Protects data in transit
Rotate passwords regularly	Reduces risk from credential leaks

Enabling SSL Connections

```{r #| label: ssl-connection

con_ssl <- dbConnect( drv = RPostgres::Postgres(), dbname = Sys.getenv(“PG_DB”), host = Sys.getenv(“PG_HOST”), port = 5432, user = Sys.getenv(“PG_USER”), password = Sys.getenv(“PG_PASSWORD”), sslmode = “require” # Options: disable, allow, prefer, require, verify-ca, verify-full )


### Revoking Unnecessary Privileges

```sql
-- In psql as postgres superuser
REVOKE ALL ON DATABASE taxi_db FROM PUBLIC;
GRANT CONNECT ON DATABASE taxi_db TO r_user;
GRANT USAGE ON SCHEMA public TO r_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO r_user;

Cleanup and Disconnecting

Code

# Drop working tables (in development / tutorial contexts)
dbExecute(con, "DROP TABLE IF EXISTS yellow_trips;")

[1] 0

Code

dbExecute(con, "DROP TABLE IF EXISTS taxi_zones;")

[1] 0

Code

dbExecute(con, "DROP TABLE IF EXISTS borough_summary;")

[1] 0

Code

dbExecute(con, "DROP TABLE IF EXISTS employees;")

NOTICE:  table "employees" does not exist, skipping

[1] 0

Code

# Always disconnect when done — releases the server connection
dbDisconnect(con)
cat("Disconnected from PostgreSQL.\n")

Disconnected from PostgreSQL.

Quick Reference Cheat Sheet

DBI Functions

Function	Purpose
`dbConnect()`	Open a database connection
`dbDisconnect()`	Close the connection
`dbGetQuery()`	Run a SELECT query, return data frame
`dbExecute()`	Run INSERT/UPDATE/DELETE/DDL, return row count
`dbWriteTable()`	Write an R data frame to a table
`dbReadTable()`	Read a whole table into R
`dbListTables()`	List all tables in the database
`dbListFields()`	List columns in a table
`dbExistsTable()`	Check if a table exists
`dbBegin()`	Start a transaction
`dbCommit()`	Commit a transaction
`dbRollback()`	Roll back a transaction
`dbSendQuery()`	Send query (for streaming / batched fetch)
`dbFetch()`	Fetch rows from a sent query
`dbClearResult()`	Release resources from a sent query

Common PostgreSQL SQL Patterns

-- Count rows
SELECT COUNT(*) FROM table_name;

-- Distinct values
SELECT DISTINCT column FROM table_name;

-- Top N rows
SELECT * FROM table_name ORDER BY column DESC LIMIT 10;

-- Conditional aggregation
SELECT
  SUM(CASE WHEN condition THEN value ELSE 0 END) AS conditional_sum
FROM table_name;

-- Date truncation
SELECT DATE_TRUNC('hour', timestamp_col) AS hour, COUNT(*)
FROM table_name GROUP BY 1;

-- String formatting
SELECT TO_CHAR(amount, 'FM$999,999.00') AS formatted_amount FROM table_name;

-- Window function: running total
SELECT date, amount,
  SUM(amount) OVER (ORDER BY date) AS running_total
FROM table_name;

Summary

This tutorial covered the full workflow for using PostgreSQL with R:

Installation — Setting up PostgreSQL on Ubuntu and Windows
Database creation — Using psql and pgAdmin
R connection — DBI + RPostgres, secure credential management
CRUD operations — Create, Read, Update, Delete with dbExecute() and dbGetQuery()
Data import — CSV with readr, Parquet with arrow, chunked insertion for large files
Advanced SQL — JOINs, CTEs, window functions, time-series queries
dbplyr — Writing dplyr code that runs as SQL on the server
Performance — Indexes, EXPLAIN ANALYZE, connection pooling
Transactions — Atomic multi-statement operations with error handling
Security — .Renviron, parameterised queries, SSL, least-privilege users

Next Steps

Explore dbplyr for advanced SQL translation
Use pool for production Shiny apps
Consider TimescaleDB for time-series extensions
Learn PostgreSQL COPY command for ultra-fast bulk imports
Investigate PostGIS for geospatial data (mapping taxi zones, route analysis)

Working with PostgreSQL in R

Why Use PostgreSQL with R?

What This Tutorial Covers

Datasets Used

Installing PostgreSQL

Ubuntu / Debian Linux

Windows

Creating a Database and Schema

Via the psql Command Line

Via pgAdmin (GUI)

R Package Setup

Required Packages

Install Missing Packages

Load Libraries

Verify Package Versions

Connecting to PostgreSQL from R

About DBI and RPostgres

Basic Connection

Secure Connections Using .Renviron

Using keyring for OS-Level Secret Storage

Store the password once (run interactively)

key_set(“postgresql”, username = “r_user”)

Then connect using the stored secret

Basic CRUD Operations

Create a Table

Insert Data

Read Data

Update Data

Delete Data

Drop a Table

Importing Real-World Datasets

Setup: File Paths

Import taxi_zone_lookup.csv

Import yellow_tripdata_2023-01.parquet (Large File)

Verify Imported Data

Advanced SQL Queries from R

JOINs — Trips by Borough

Time-Series — Trips per Hour of Day

Window Functions — Top Zones by Average Tip

Common Table Expressions (CTEs) — Peak vs Off-Peak Comparison

Payment Type Analysis

Using dbplyr for dplyr-Style Database Queries

Create Lazy Table References

Filter, Select, and Summarise

Inspect the Generated SQL

Time-Based Grouping with dbplyr

Performance Tuning

Creating Indexes

EXPLAIN ANALYZE — Query Performance Diagnostics

VACUUM and ANALYZE

Connection Pooling with pool

install.packages(“pool”)

Use just like a regular connection

Close the pool when done (e.g., when Shiny app shuts down)

Safe Query Wrapper

Database Metadata and Inspection

Writing Query Results Back to PostgreSQL

Security Best Practices

Enabling SSL Connections

Cleanup and Disconnecting

Quick Reference Cheat Sheet

DBI Functions

Common PostgreSQL SQL Patterns

Summary

Next Steps

Using `keyring` for OS-Level Secret Storage

Connection Pooling with `pool`