Creation and clean up of WAL files in the primary's pg_wal folder (pg_xlog prior to PG10) is a normal part of PostgreSQL operation. The WAL files on the primary are used to ensure data consistency during crash recovery. Use of write-ahead logs (also called redo logs or transaction logs in other products) is common for data stores that must provide durability and consistency of data when writing to storage. The same technique is used in modern journaling and log-structured filesystems.

As the DB is operating, blocks of data are first written serially and synchronously as WAL files, then some time later, usually a very short time later, written to the DB data files. Once the data contained in these WAL files has been flushed out to their final destination in the data files, they are no longer needed by the primary. At some point, depending on your configuration, the primary will remove or recycle the WAL files whose data has been committed to the DB. This is necessary to keep the primary's disk from filling up. However, these WAL files are also what streaming replicas read when they are replicating data from the primary. If the replica is able to keep up with the primary, using these WAL files generally isn't an issue.

If the replica falls behind or is disconnected from the primary for an extended period of time, the primary may have already removed or recycled the WAL file(s) that a replica needs (but see Streaming Replication Slots

Most queries against a database are short lived. Whether you're inserting a new record or querying for a list of upcoming tasks for a user, you're not typically aggregating millions of records or sending back thousands of rows to the end user. A typical short lived query in Postgres can easily be accomplished in a few milliseconds or less. For the typical application, this means a well tuned production Postgres

In today's blog post I am going to show you how to easily spin up PostGIS in your Kubernetes or OpenShift cluster using the PostgreSQL Operator

In our last installment, we covered the use of a constraint trigger to enforce data quality by looking at geometry spatial relationships.

For this installment, we'll start with basic relationships and then look at more complex use cases: deferred constraints, and full table-level data structures.

Linear Network Constraints

Let's start with a simple road network.

CREATE TABLE roads (
  pk bigint PRIMARY KEY,
  name text NOT NULL,
  geom geometry(LineString, 3005) NOT NULL
    CONSTRAINT geom_no_zero_length CHECK (ST_Length(geom) > 0)
    CONSTRAINT geom_no_self_intersection CHECK (ST_IsSimple(geom))
);

If constraints in general have caught your interest, our interactive learning portal has a whole section on the use of non-spatial constraints, even a video walkthrough!

In our last installment

At a company where most all people have some Postgres expertise you can easily learn something new from your coworkers every day about Postgres. In my first week I saw a question in our internal slack that I could guess an answer to, but it wasn't definitive.

It was "Why have composite types? Why would you use them?". I threw in an answer a few others did as well, but collectively we didn't have anything definitive but all these seemed like valid cases.

But first, what are composite types?

This post is the second in a two-part series -- read the first here: Going Back to Basics with PostgreSQL Data Types.

In my last post, I shared some interesting (and at times surprising) things that I learned while digging into data types in PostgreSQL. Data types like numeric, integer, date, and char/varchar exist in every other relational database system since the need to work with such data is pretty much a given. The implementation may vary somewhat between systems, but generally there are standard ways you’ll want to process and analyze these types of data (e.g. perform mathematical calculations, find the length of a character string, cast from one type to another, etc).

In Postgres, we have a few more data types that may not be as well known even for experienced developers. Let’s take a quick look at arrays, enum, and range types.

Array type

When I first started to learn how to code, I was introduced to the concept of data types: a 6 is not the same as "6", because the former is numeric (typically an integer type, with some variations in terminology based on the language) and the latter a string; "true" is not necessarily the same as true, because true can be a Boolean value in some languages.

Underneath the code, these pieces of data are really just a combination of ones and zeros, but declaring their types allows them to play a particular role. A value’s data type tells the computer when an expression like 6+6 should return a 12, or 66! That said, it wasn’t until I started learning a little bit more about enterprise databases

Greetings friends of Crunchy Data, it is my pleasure to announce the initial release of our application developer portal. An awesome team has been working behind the scenes to bring together this nice little website to help application developers find all their Postgres needs in one place.

Our goal is to become a single-stop resource for application developers looking to work with PostgreSQL. We have released three main parts to the site that form the foundation for future growth. Let’s go over the main pieces and then talk a bit about future directions.

Documentation

Let's say you needed to document all of the tables in your PostgreSQL database. You wanted the output of the \d psql meta-command for all of the tables so you could put it in a shared documentation area. However, there were a lot of tables and you did not want to have to type all the commands that you needed. Before I explain how I can help, we will need to set up a simple database and provide some background on the \d