January 17, 2020
Sleeping with Postgres

Do you want to sleep with PostgreSQL or maybe just have your session sleep for a bit? Well, we have an answer for you, the pg_sleep() function. It's a pretty simple function and does what you would expect and sleeps for a specified period of time. As you can see the SQL takes approximately 2 […]

Read More
December 1, 2019
Common SQL mistakes and how to avoid them.

In addition to DBAs and data engineers, SQL is used by analysts, data scientists, product managers, programmers, and designers. There are varying skill levels within these groups and below are some common mistakes I see. Dividing Integers In PostgreSQL dividing an integer by an integer will result in an integer. This is not always apparent […]

Read More
November 26, 2019
How do I change column default value in PostgreSQL

Once a table is created you can alter its configuration and set default values for a column. When altering a table an setting a default value only new rows will receive the new default value. Below is the general syntax. To remove the default value you can use a similar SQL statement. According to the […]

Read More
November 20, 2019
Extract the domain name from a string

Have you ever needed to extract the domain name from a string using PostgreSQL? There are various methods to accomplish this task like using PL/Python and a URL parsing library such as urlparse, but that is a little more complex. Below is probably the simplest method and the regex is portable to other systems/languages. This […]

Read More
November 20, 2019
PostgreSQL 12.1 has been released.

In addition to PostgreSQL 12.1 the community also released versions 11.6, 10.11, 9.6.16, 9.5.20, and 9.4.25. As a rule of thumb, for production workloads, I avoid any new major version until a .1 version has been released. I have already tested PostgreSQL 12 in an evaluation environment and have not run into any issue. It […]

Read More
November 10, 2019
PostgreSQL around the web - November 2019

PostgreSQL 12 Initial Query Performance Impressions With the latest major version freshly released, it’s again time to gauge its performance. I’ve already been doing this for years, and I have my scripts ready, so it’s not too much work for me – but rather, for the machines. The v12 release, as always, adds quite a […]

Read More
February 16, 2019
Change in behavior with fsync()

Starting with PostgreSQL 11.2, 10.7, 9.6.12, 9.5.16, and 9.4.21 the fsync() behavior has changed. Here is what the PostgreSQL group had to say: When available in an operating system and enabled in the configuration file (which it is by default), PostgreSQL uses the kernel function fsync() to help ensure that data is written to a […]

Read More
January 29, 2019
Microsoft acquires Citus

Microsoft recently acquired Citus Data. Citus is an innovative open-source extension to PostgreSQL that transforms PostgreSQL into a distributed database, dramatically increasing performance and scale for application developers. Citus has a few different products. Citus Community which is an open source extension to PostgreSQL. Citus Cloud is a fully-managed database as a service. Last but […]

Read More
September 16, 2018
Defend your data quality with constraints

In computer science, garbage in, garbage out is where bad, or nonsense input data produces nonsense output or "garbage".  How do you protect your database from allowing garbage in? Using a constraint is an excellent method for keeping your data clean.  Constraints are essentially rules that your data has to follow. Datatypes as database constraints Datatypes […]

Read More
September 2, 2018
Tracking and Managing PostgreSQL Connections

PostgreSQL connections are process based not threaded like some other databases.  Like everything else there are pros and cons to this method.  One of the cons is that each PostgreSQL connection consumes more resources when compared to threaded connections.  If you have a need to have a lot of connections to your database you may […]

Read More