Posts about Postgres Tutorials

The end is nigh! PostgreSQL has substantially tightened restrictions on the use of the "public" schema.

Here, a standard login user (not superuser) tries to make a table, as one does:

user=> CREATE TABLE mydata (id integer);

ERROR:  permission denied for schema public
LINE 1: CREATE TABLE mydata (id integer);

Today I'm excited to introduce a new place for devs to polish their Postgres skills, a Postgres Playground and Tutorials from Crunchy Data. What is the playground? Put simply it is:

1. Postgres running in your local web browser
2. With canned datasets you can load
3. Guided tutorials to follow along to learn about the power of Postgres

The PostgreSQL BRIN index is a specialized index for (as the documentation says) "handling very large tables in which certain columns have some natural correlation with their physical location within the table".

For data of that sort, BRIN indexes offer extremely low insert costs (good for high velocity data) and extremely small index sizes (good for high volume data).

But what data has this "natural correlation"?

Most frequently, data with a timestamp that is continuously adding new rows.

• A log table
• A table of GPS track points
• A table of IoT sensor measurements

It should be the easiest thing in the world: you are modeling your data and you need a column for some numbers, what type do you use?

PostgreSQL offers a lot of different number types, and they all have advantages and limitations. You want the number type that is going to:

• Store your data using the smallest amount of space
• Represent your data with the smallest amount of error
• Manipulate your data using the correct logic

Logical replication has been around since Postgres 10 and it's just one of the most useful things out there. Need to get your data to a business analytics engine? Want to create an ETL pipeline from Postgres to your data warehouse? Want to test a new version of Postgres locally with your data? Want to consolidate a few databases into one? Logical replication can do all those things!

It is a replication tool, which means it will copy some or all of your data to a new location and continue updating data from the primary source. This allows you to have accurate copies of your data in different locations in perpetuity.

There are two types of replication in Postgres, so just at a super high level some notes for you:

• Streaming replication

One of the things that makes Postgres so awesome for software development is the incredibly useful system of constraints. Constraints are a way to tell Postgres which kinds of data can be inserted into tables, columns, or rows.

As an application developer, you're going to build in this logic to your application as well and that’s great. However…adding this logic into your database protects your data long-term from bad data, null statements, or application code that isn't working quite right and does not conform to your data requirements. Constraints are also great for catching outliers and things you didn’t account for in application code but you know need to be caught before an insert statement.

To illustrate the major constraint types, I’m going to show you an example database schema where you’re building a room reservation system with a table for users, a table for rooms, and reservations tables referencing users and rooms along with a start and end time.

We can set up these first two tables for users and rooms without any constraints, let’s add this:

CREATE TABLE users (
    id serial PRIMARY KEY,
    name text,
    email text
);

CREATE TABLE rooms (
    id serial PRIMARY KEY,
    number text
);

If you’ve read Crunchy blogs recently you probably noticed by now that we’re all big fans of indexing. Indexing is key to optimizing your database workloads and reducing query times. Postgres now supports quite a few types

"Who is in charge of this database?"

Everyone on the DBA team shook their head and someone asked, "Is it Oracle or SQL server?"

"I think it is called My SQL," the development manager said.

During my 20 years as a database administrator, that is often how I ended up learning new RDBMS systems. As a result, I know first-hand how challenging it can be to learn another system when you get thrown into the deep end.

New databases are added to a production environment in different ways. A new software application is purchased and the installation requires (or builds) a new database. A developer builds a prototype application for a business unit. When they start using it, it quickly turns into an important system and the database gets handed to the production administrators to maintain and fix when it breaks. Sometimes a company determines that they need to move to a standard database system and away from another. This can require upskilling a whole team of administrators to learn the new system.

You might have been told, "Congratulations! You are now in charge of this database. I know you are an Oracle DBA, but PostgreSQL is almost the same thing so you should be able to handle it."

Yes, they are both relational database management systems

The idea of writing a database query that can then go out to an external sourcemay not occur to someone who is not a DBA early on. That is: instead of figuring out how to grab then load multiple data sets into the same store, or configuring your application backend to connect to a bunch of disparate sources, why not use query JOINs like you usually would across tables within one database?

In case you're not familiar, the dblink module

Early in on my SQL journey, I thought that searching for a piece of text in the database mostly involved querying like this:

SELECT col FROM table WHERE col LIKE '%some_value%';

Then I would throw in some wildcard operators or regular expressions if I wanted to get more specific.

Later on, I worked with a client who wanted search functionality in an app, so LIKE