theCodingInterface

theCodingInterface

Providing quality software engineering content in the form of tutorials, applications, services, and commentary suited for developers.

Posts about Databases

Developer's Guide to PostgreSQL on Linux: Installation

PostgreSQL

This is the first article of a series featuring a collection of useful tasks for administering PostgreSQL on the Linux operating systems Ubuntu 18 LTS as well as CentOS 7. PostgreSQL is a powerful and robust Object Relational Database which is freely available as open source software and widely popular. In this article I cover how to install and initialize the PostgreSQL database cluster. I also cover the key configuration files as well as their purpose.

Read more
By Adam McQuistan on 09/27/2019
PostgreSQL Databases CentOS Ubuntu Linux

Developer's Guide to PostgreSQL on Linux: psql shell

PostgreSQL

This is the second article of a series featuring a collection of useful tasks for administering PostgreSQL version 11 on the Linux operating systems Ubuntu 18 LTS as well as CentOS 7. For those unfamiliar, PostgreSQL is a powerful and robust Object Relational Database which is freely available as open source software and widely popular. In this article I introduce the psql interactive shell using the default postgres user.

Read more
By Adam McQuistan on 09/29/2019
psql PostgreSQL Databases Linux

Developer's Guide to PostgreSQL on Linux: Creating Databases

PostgreSQL

This is the third article of a series featuring a collection of useful tasks for administering PostgreSQL on the Linux operating systems Ubuntu 18 LTS as well as CentOS 7. For those unfamiliar, PostgreSQL is a powerful and robust Object Relational Database which is freely available as open source software and widely popular. In this article I show how to create databases using both SQL statements along with the createdb utility program.

Read more
By Adam McQuistan on 09/30/2019
psql PostgreSQL Databases

Developer's Guide to PostgreSQL on Linux: Creating Accounts and Making Connections

PostgreSQL

This is the third article of a series featuring a collection of useful tasks for administering PostgreSQL on the Linux operating systems Ubuntu 18 LTS as well as CentOS 7. For those unfamiliar, PostgreSQL is a powerful and robust Object Relational Database which is freely available as open source software and widely popular. In this article I cover how to create user accounts and establish connections with these accounts.

Read more
By Adam McQuistan on 10/05/2019
psql PostgreSQL Databases CentOS Ubuntu Linux

Developer's Guide to PostgreSQL on Linux: Changing the Default Data Directory

PostgreSQL

In this fifth article demonstrates how to change the default data directory of a PostgreSQL database cluster running on the Linux Ubuntu 18 LTS and Centos 7 operating systems. This article shifts focus to improving the scalability, robustness and, performance made possible by moving the data directory to it's own area of the file system. Isolating the data directory also reduces file access contention with other OS activities and allows for easier resizing.

Read more
By Adam McQuistan on 10/06/2019
psql PostgreSQL Databases CentOS Ubuntu Linux

Exploring Online Analytical Processing Databases plus Extract, Transform and, Load in PostgreSQL

Data Engineering

In this article I give an introduction to Online Analytical Processing databases comparing them against traditional Online Transaction Processing Systems. Emphasis is put on designing and building Star Schemas and Reporting tables using Data Engineering processes like Extract, Transform and Load all within a Aurora PostgreSQL database.

Read more
By Adam McQuistan on 02/17/2021
Data Engineering AWS PostgreSQL Databases DevOps

Introduction to Redshift using Pagila Sample Dataset Including ETL from Postgres using AWS Glue

Data Engineering

In this article I give a practical introductory tutorial to using Amazon Redshift as an OLAP Data Warehouse solution for the popular Pagila Movie Rental dataset. I start with a basic overview of the unique architecture Redshift uses to accomplish its scalable and robust use case as an enterprise cloud data warehouse. Then armed with this basic knowledge of Redshift architecture I move on to give a practical example of designing a schema optimal for Redshift based off the Pagila sample dataset.

Read more
By Adam McQuistan on 03/05/2021
AWS Glue Redshift Data Engineering AWS AWS S3 psql PostgreSQL Databases DevOps Python

How To Use Window Functions in SQL

Data Engineering

When it comes to quantitative analysis on data in database tables standard SQL provides a set of aggregate functions like SUM(), MAX(), and MIN(). There are two main ways these functions get used in practice: (i) collapsing the table data down to represent the aggregate calculation result set or, (ii) presenting the aggregate calculation per row maintaining the granularity of the complete table. Windowing functions are used to accomplish this second option and will the focus of this article.

Read more
By Adam McQuistan on 03/29/2021
Data Engineering PostgreSQL Databases

Navigation

theCodingInterface