Using Postgres and TimescaleDB with Node.js

JaxNode June 2021

About Me

fek.io/blog

youtube.com/c/polyglotengineer

github.com/davidfekke

@jaxnode @polyglotengine1

Databases

  • Relational 
  • Column
  • Document
  • Graph
  • Event Store

SQL vs NoSQL

-

Single Point of Failure

Slow

Difficult to Scale

Data broken up in Tables

Bad support JSON docs

+

Use Standard SQL

Lots of experienced DBAs

Efficient Data space

ACID

SQL

  • Simple set based language
  • Four Commands
  • SELECT, INSERT, UPDATE & DELETE
  • JOIN tables in a single query
  • Manipulate many rows with one statement
  • Create Indexes and Views
  • Stored Procedures

SQL Database Vendors

  • Oracle
  • IBM DB2
  • Microsoft SQL Server
  • SQLite
  • MySQL MariaDB
  • Sybase
  • Postgres TimescaleDB
  • AWS RDS

One Database to Rule them All!

PostgreSQL

  • Successor to Ingres at Cal Berkeley
  • Started in 1985
  • Open Source
  • ACID compliant
  • Can store JSON Documents
  • PostGIS
  • Supports Extensions

TimescaleDB

  • Extension to Postgres
  • Meant for high volume insert
  • Strength is time series data
  • Perform along NoSQL competition
  • Hypertables
  • Works with Chunks of data

Query Tools Recommended

  • pgAdmin
  • TablePlus
  • pSequel
  • HeidiSQL
  • dbForge
  • SQLPro

ACID

  • Atomicity: if one part fails, they all do
  • Consistency: Data must be valid
  • Isolation: all transactions can't interfere with others
  • Durability: Will not loose data
SELECT *
FROM Users
WHERE clue > 0;
-- results: 0

SQL Statements

SELECT 
	id, firstname, lastname, age
FROM
	person
WHERE
	age >= 32;
SELECT p.firstname, p.lastname, p.age, t.username
FROM person p
	JOIN user_table t
    	ON p.id = t.person_id
WHERE p.age > 32;
SELECT p.firstname, p.lastname, p.age, t.username
FROM person p
	LEFT JOIN user_table t
    	ON p.id = t.person_id
WHERE p.age > 32;
id firstname lastname username
1 John Smith NULL
2 Bob MacKenzie bmac
3 Jane Doe NULL

Data Definition Language

  • DDL for short
  • Create Tables
  • Create Indexes
  • Create Views/ Materialized Views
  • Create Stored Procedures
CREATE TABLE stock (
    id SERIAL PRIMARY KEY,
    symbol TEXT NOT NULL,
    name TEXT NOT NULL,
    exchange TEXT NOT NULL,
    is_etf BOOLEAN NOT NULL
);
CREATE INDEX ON mention (stock_id, dt DESC);
CREATE VIEW comedies AS
    SELECT *
    FROM films
    WHERE kind = 'Comedy';
create or replace procedure movemoney(
   sender int,
   receiver int, 
   amount dec
)
language plpgsql    
as $$
begin
    -- subtracting the amount from the sender's account 
    update bank_accounts 
    set balance = balance - amount 
    where id = sender;

    -- adding the amount to the receiver's account
    update bank_accounts 
    set balance = balance + amount 
    where id = receiver;

    commit;
end;$$
create function get_disc_count(len_from int, len_to int)
returns int
language plpgsql
as
$$
declare
   disc_count integer;
begin
   select count(*) 
   into disc_count
   from disc
   where length between len_from and len_to;
   
   return disc_count;
end;
$$;

Postgres Extras

  • JSONB Documents
  • JSONB indexing
  • PostGIS
  • Location Based Search

JSONB Functions

  • @> Contains
  • -> Returns JSON object
  • --> Returns text
  • ? Existence
  • jsonb_each
  • jsonb_object_keys
  • jsonb_extract_path

PostGIS

  • Type of GEOGRAPHY(POINT,4326)
  • Latitude and Longitude
  • Can store Lines and Multi-points
  • INSERT INTO t (name, geo) VALUES ('me', POINT(-109 29))
  • All calculations done on spheroid

Timescale Extras

  • Used by companies collecting real time data
  • Optimizations around time
  • 10-100% faster than normal Postgres
  • Uses Hypertables
  • Data inserted into Chunks
  • Horizontally scale to Petabytes
CREATE TABLE mention (
    stock_id INTEGER,
    dt TIMESTAMP WITHOUT TIME ZONE NOT NULL,
    message TEXT NOT NULL,
    source TEXT NOT NULL,
    url TEXT NOT NULL,
    PRIMARY KEY (stock_id, dt),
    CONSTRAINT fk_mention_stock FOREIGN KEY (stock_id) REFERENCES stock (id)
);

CREATE INDEX ON mention (stock_id, dt DESC);

-- Hypertable Created Here
SELECT create_hypertable('mention', 'dt');

Node.js and Postgres

  • use the 'pg' module
  • > npm i pg --save
  • Use Connection Pools
  • Can also use Clients, but harder to manage
import path from 'path';
import { promises as fsPromises } from 'fs';
import PG from 'pg';

const Pool = PG.Pool;

const pool = new Pool({
    user: 'postgres',
    host: 'localhost',
    database: 'etfdb',
    password: 'password',
    port: 5432
});
  
try {
    const nysedata = await fsPromises.readFile('NYSE_screener.csv', 'UTF-8');
    const nyseArray = nysedata.split('\r\n'); 
    nyseArray.shift();
    for (let item of nyseArray) {
        const row = item.split(',');
        console.log(`NASDAQ Symbol: ${row[0]}, name: ${row[1]}`);
        await pool.query(insert_query,[row[0], row[1],'NYSE', 'false']);
    }
} catch (err) {
    console.error(err);
}

pool.end();

Demo

Links

  • postgres.org
  • timescaledb.com
  • fek.io/blog/using-postgres-and-timescale-db-with-node-js-series
  • fek.io/blog/using-postgres-and-timescale-db-with-node-js-series-part-2
  • fek.io/blog/using-postgres-and-timescale-db-with-node-js-series-part-3

Questions?

Using Postgres and TimescaleDB with Node.js

By David Fekke

Made with Slides.com

Using Postgres and TimescaleDB with Node.js

  • 761

More from David Fekke