Articles

Migrate to PostgreSQL 9.6 with pg_upgrade

Published on Nov. 20, 2017 by Gabriel Bordeaux

Introduction You can migrate from a version of PostgreSQL to another with pg_dump (see my article on how to migrate from 9.4 ro 9.5 as ...

Read More

Migrate to PostgreSQL 10 with pg_upgrade

Published on Nov. 9, 2017 by Gabriel Bordeaux

Introduction You can migrate from a version of PostgreSQL to another with pg_dump or with pg_upgrade. pg_upgrade allows you to migrate with or without a ...

Read More

Install PostgreSQL 9.5 on Debian/Ubuntu servers

Published on Oct. 5, 2017 by Gabriel Bordeaux

Introduction PostgreSQL 10 was released on 05/10/2017. Install PostgreSQL 10 step by step Add the PostgreSQL repository to your sources echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg ...

Read More

GCE BigQuery vs AWS Redshift vs AWS Athen

Published on Jul. 20, 2017 by Gabriel Bordeaux

Updates 7/20/17 update Following Tino Tereshko's advice (he is Big Data Lead at Google Cloud Office of CTO), I added the metrics for BigQuery standard ...

Read More

Setup a cascading PostgreSQL replication in a few easy steps

Published on Jul. 19, 2017 by Gabriel Bordeaux

Introduction Setting up a cascading replication means to create a downstream slave from an existing master-slave setup. If you want to create a regular PostgreSQL ...

Read More

Easy replication setup with PostgreSQL

Published on Jul. 19, 2017 by Gabriel Bordeaux

Introduction Replicating a PostgreSQL server to a slave is an easy process with PostgreSQL. The documentation below explains how to create a new database slave ...

Read More

PostgreSQL's subqueries: multiple columns, better and faster results

Published on Mar. 27, 2017 by Gabriel Bordeaux

Introduction Subqueries are used every day by most developers and DBAs. We will present below several solutions to write the same subquery and get the ...

Read More

10 tips and tricks for PostgreSQL (part 1)

Published on Mar. 27, 2017 by Gabriel Bordeaux

Index Here are a few tips and tricks for PostgreSQL I have been using for some time. I invite you to have a look at ...

Read More

Store IP addresses in PostgreSQL with IP4R

Published on Mar. 27, 2017 by Gabriel Bordeaux

Introduction IP4R is a PostgreSQL extension which supports 6 data types allowing you to store IPv4 & IPv6 addresses in a PostgreSQL table. As IP4R ...

Read More

Easy migrating from PostgreSQL 9.3 to 9.4 for Debian servers

Published on Mar. 27, 2017 by Gabriel Bordeaux

Introduction PostgreSQL 9.4 was released on 12/18/2014. More information about the release is available on the official PostgreSQL documentation. This article is written to provide ...

Read More

Migrate from PostgreSQL 9.4 to 9.5 with easy steps

Published on Mar. 27, 2017 by Gabriel Bordeaux

Introduction PostgreSQL 9.5 was released on 01/07/2016. A list of new and upgraded features is available on the official documentation. The guide below provides a ...

Read More

Migrate to PHP7 with Nginx + PHP-FPM

Published on Mar. 27, 2017 by Gabriel Bordeaux

Introduction PHP 7 has been recently released. It offers new features and benchmarks that show a decrease in memory consumption and great improvements in response ...

Read More

Weighted ntile with PostgreSQL

Published on Mar. 27, 2017 by Gabriel Bordeaux

Introduction The PostgreSQL window function ntile is a ranking function dedicated to split data into a specified number of groups. It can allow you to ...

Read More

Introduction to JSON support in PostgreSQL

Published on Mar. 27, 2017 by Gabriel Bordeaux

Introduction Since the version 9.2, PostgreSQL supports the data type JSON. You can manipulate JSON in PostgreSQL with several functions and operators. Here is a ...

Read More

Introduction to GPG

Published on Mar. 27, 2017 by Gabriel Bordeaux

What is GPG? GPG is a GPL alternative to PGP. It is a powerful encryption tool created by Werner Koch. You can read more about ...

Read More

Window Functions in PostgreSQL

Published on Mar. 27, 2017 by Gabriel Bordeaux

Introduction Window functions looks at "windows" of your data while processing it. They give you "the ability to perform calculations across sets of rows that ...

Read More

How to do ORDER BY RANDOM() on large tables?

Published on Mar. 27, 2017 by Gabriel Bordeaux

Introduction Getting a random row from a PostgreSQL table has numerous use cases. To process an instruction like "ORDER BY RANDOM()", PostgreSQL has to fetch ...

Read More

Average ignoring extremes (outliers) with PostgreSQL

Published on Mar. 27, 2017 by Gabriel Bordeaux

Introduction In some cases, you may want a "trimmed average" instead of a regular average. For example, if you try to calculate the average net ...

Read More

ORDER BY [my custom order] in PostgreSQL

Published on Mar. 27, 2017 by Gabriel Bordeaux

Introduction Below you will find a little tutorial on how to sort data in a custom order. For example, if you have a list of ...

Read More

Improve the quality of your database using Foreign Keys in PostgreSQL

Published on Mar. 27, 2017 by Gabriel Bordeaux

Introduction A foreign key is a constraint that specifies that the value of a column (or a group of columns) must match the values of ...

Read More

PostgreSQL: Search dates on large tables with pg_IdFromDate()

Published on Mar. 27, 2017 by Gabriel Bordeaux

Introduction pg_IdFromDate() is a small set of PostgreSQL stored procedures allowing you to search a row based on a date or a timestamp in a ...

Read More

10 tips and tricks for PostgreSQL (part 2)

Published on Mar. 27, 2017 by Gabriel Bordeaux

Index This article is the 2nd part of my "10 tips and tricks for PostgreSQL". If you did not read the first part yet, you ...

Read More

Group by a range of values with PostgreSQL

Published on Mar. 27, 2017 by Gabriel Bordeaux

Introduction Here is a simple solution on how to group data within a range. As a basic example, you may have a list of team ...

Read More

Yield management: How to maximize your profit and minimize your customer's satisfaction rate

Published on Mar. 27, 2017 by Gabriel Bordeaux

Yield what? The concept Let's use an airplane as an example. On a plane, there are 100 seats. A company will start selling tickets 3 ...

Read More

Install PostgreSQL 9.5 on Debian/Ubuntu servers

Published on Mar. 27, 2017 by Gabriel Bordeaux

Introduction PostgreSQL 9.5 was released on 01/07/2016. More information about the release is available on the official PostgreSQL documentation. Install PostgreSQL 9.5 step by step ...

Read More

Check if you have an Index on your IDs in PostgreSQL

Published on Mar. 27, 2017 by Gabriel Bordeaux

Why? PostgreSQL does not automatically create an index for your ID column. This article will explain why you should always create an Index on your ...

Read More
RSS