Invitation from Pavlo Golub

To finish out 2023, Pavlo is inviting us to think about PostgreSQL events and write about our experiences. While the world of in-person events started to open up again in 2022, there’s no doubt that 2023 has been really busy in all parts of the world, especially for our beloved PostgreSQL database.

As a retrospective, and in anticipation of things to come, Pavlo’s invitation includes many different prompts to help you get started. And there’s no doubt that PostgreSQL users old and new will benefit from hearing your experience of how to get the most value out of attending a conference.

Pavlo’s invitation…

As PostgreSQL enthusiasts, we all know that the community’s heartbeat lies in PostgreSQL Events, whether local meetups, big conferences, or virtual gatherings, that have become prevalent during the COVID-19 pandemic.

During a thrilling conference season, the PostgreSQL community is abuzz with activity. We’ve just wrapped up the PASS Data Community Summit, which featured a dedicated PostgreSQL presence that left us inspired and eager for more. The DOAG conference is underway, where numerous PostgreSQL experts are sharing their insights. And on the horizon, we eagerly anticipate the upcoming PostgreSQL Conference Europe in just a couple of weeks.

I invite you to share your PostgreSQL Events experiences in your dedicated blog post. Whether you’re a seasoned attendee, a speaker, a trainer, a sponsor, an organizer, or a first-timer, your unique perspective contributes to the canvas of the PostgreSQL community.

Read the rest of his invitation and prepare to publish your post this Friday, December 1, 2023.

Learn how to set up Postgres using Docker: this tutorial will help you install a PostgreSQL database using a Docker container.

The post How To Set Up and Run a PostgreSQL Database Using Docker appeared first on Stormatics.

Reading the title, you might think it is about multiplying INTERVAL values. Well, no, it is about something much larger.

If you are an application developer, database administrator, or Postgres internals developer, what value do you give to society? Well, you are paid or people appreciate your work, so you must provide some value. Is it food, clothing, shelter, entertainment — no, not directly. So, what is it? Time multiplication.

In this video, Rory Vaden explains that there are three things you can do in a day, urgent tasks, important tasks, and significant tasks. (The video is based on his book, Procrastinate on Purpose: 5 Permissions to Multiply Your Time.) He explains how to prevent urgent tasks from crowding out important tasks, but his big focus is on significant tasks, that is, tasks that, if done now, give you more time in the future. He calls this multiplying your time and justifies its significance because it produces benefits far into the future by freeing up time to do future tasks.

Continue Reading »

As PostgreSQL enthusiasts, we all know that the community’s heartbeat lies in PostgreSQL Events, whether local meetups, big conferences, or virtual gatherings, that have become prevalent during the COVID-19 pandemic.

During a thrilling conference season, the PostgreSQL community is abuzz with activity. We’ve just wrapped up the PASS Data Community Summit, which featured a dedicated PostgreSQL presence that left us inspired and eager for more. The DOAG conference is underway, where numerous PostgreSQL experts are sharing their insights. And on the horizon, we eagerly anticipate the upcoming PostgreSQL Conference Europe in just a couple of weeks.

I invite you to share your PostgreSQL Events experiences in your dedicated blog post. Whether you’re a seasoned attendee, a speaker, a trainer, a sponsor, an organizer, or a first-timer, your unique perspective contributes to the canvas of the PostgreSQL community.

Reflecting on Personal Experiences and Favorite Moments

Share the stories that resonate with you — the moments that left a lasting impression. Do you remember your first in-person event? Were there unexpected highlights or connections that made the event memorable for you?

Unpacking PostgreSQL Events Highlights

PostgreSQL events offer rich content, from training sessions to lightning talks. What were the standout sessions for you? Which speakers left you inspired or equipped with newfound wisdom? Share the gems you discovered during these sessions.

Comparing Virtual and In-Person Experiences

As the landscape of events evolves, so does the format. Reflect on your experiences attending both virtual and in-person events. What unique advantages and challenges does each setting present? How has the shift to virtual impacted your engagement with the PostgreSQL community?

Networking and Collaborations

For many of us, PostgreSQL events are more than just learning opportunities — they are epicenters for networking and collaboration. Discuss the importance of networking. Hav

Disclaimer

This article will contain spoilers both on how I solved 2022 Day 22's challenge "Monkey Map" using SQL, as well as general ideas on how to approach the problem. I recommend trying to solve it yourself first, using your favorite language.

AOC Day 22

Tech used:

• The file_fdw extension to read the input
• Unlogged tables
• Sequences
• Building and modifying arrays via regexp_split_to_array and array_remove
• More ASCII animation!

The first step is to read the text-based input file into a Postgres table:

CREATE EXTENSION if not exists file_fdw;

CREATE SERVER if not exists aoc2022 foreign data wrapper file_fdw;

DROP SCHEMA if exists aoc2022_day22_monkeymap CASCADE;
CREATE SCHEMA aoc2022_day22_monkeymap;
SET search_path = aoc2022_day22_monkeymap;

CREATE FOREIGN TABLE aoc_day22 (line text)
  SERVER aoc2022 options(filename '/tmp/aoc2022.day22.input'
--  SERVER aoc2022 options(filename '/tmp/aoc2022.day22.testinput'
);

AOC Day 22 - Part One

This puzzle asks us to chart a route through a maze, following specific directions about how far to walk and when to turn. The input file looks like this:

        ...#
        .#..
        #...
        ....
...#.......#
........#...
..#....#....
..........#.
        ...#....
        .....#..
        .#......
        ......#.

10R5L5R10L4R5L5

This is the small test file: the actual one is always much larger and more complex. We can see it is divided into two parts: the maze, and the instructions. Our first step will be to translate that input into SQL tables. For now, we will only focus on the map part, which we will put into a new table:

CREATE UNLOGGED TABLE monkeymap (
  id INT GENERATED ALWAYS AS IDENTITY,
  y SMALLINT,
  x SMALLINT,
  item CHAR(1),
  eswn TEXT[]
);

We will need some supporting sequences, and then we can read the file line for line and transform it into the columns above:

CREATE SEQUENCE aoc;
CREATE SEQUENCE aoc2;

WITH x AS (SELECT nextval('aoc') AS myrow, setval('aoc2',1,false), line

An introduction to database generated columns, using PostgreSQL and the new GeneratedField added in Django 5.0.

Authentication is the process of verifying the identity of a user or system attempting to access a database. In the realm of PostgreSQL, authentication serves as the first line of defense, ensuring that only authorized individuals or applications gain entry. As the gateway to sensitive data, robust authentication is imperative, safeguarding against unauthorized access and […]

The post Unlocking Secure Connections: A Guide to PostgreSQL Authentication Methods appeared first on Stormatics.

TIMESTAMPs are very precise and flexible in Postgres, but sometimes users want to do index lookups of TIMESTAMP values with less precision, i.e., by date. To illustrate this, let's create a sample table with 100k TIMESTAMP values:

CREATE TEMPORARY TABLE date_test (event_time TIMESTAMP WITH TIME ZONE);
 
ANALYZE date_test;
 
INSERT INTO date_test
SELECT
(
    SELECT '2023-03-01 00:00:00'::timestamptz +
           (floor(random() *
            (extract(EPOCH FROM '2023-04-01'::timestamptz) -
             extract(EPOCH FROM '2023-03-01'::timestamptz)) +
            b * 0)::integer || 'seconds')::interval
)
FROM generate_series(1, 100000) AS a(b);

Continue Reading »

In recent blog post, I outlined a problem I hit with pg_dump. Specifically, pg_dump was picking up and using ~/.pgpass with pg_dump from PostgreSQL 12-14, but with PostgreSQL 15-16, it was failing.

In this blog post:

• FreeBSD 13.2
• PostgreSQL server 12 / 16
• PostgreSQL client 12-16
• Bacula 9.6.7

Today we figured out why: $HOME.

$HOME for the script was set to /

In PostgreSQL < 15, the code used the database to determine HOME and then pick up ~/.pgpass

Stumbling through the environment variables

In PostgreSQL 16, the code first checks $HOME (as shown in this commit).

The order is now:

1. PGPASSFILE
2. $HOME/.pgpass (starting with v15)
3. ~/.pgpass (i.e. the home directory from /etc/passwd)

I stumbled across the problem this morning when pg_dump wasn’t running with a 16 client against a 16 server.

The output was:

22-Nov 13:36 bacula-dir JobId 361250: Start Backup JobId 361250, Job=BackupCatalog.2023-11-22_13.36.38_39
22-Nov 13:36 bacula-dir JobId 361250: There are no more Jobs associated with Volume "FullAutoNoNextPool-04-17758". Marking it purged.
22-Nov 13:36 bacula-dir JobId 361250: All records pruned from Volume "FullAutoNoNextPool-04-17758"; marking it "Purged"
22-Nov 13:36 bacula-dir JobId 361250: Recycled volume "FullAutoNoNextPool-04-17758"
22-Nov 13:36 bacula-dir JobId 361250: Using Device "vDrive-FullFileNoNextPool-0" to write.
22-Nov 13:36 dbclone-fd JobId 361250: shell command: run ClientRunBeforeJob "/usr/local/bacula/dump_catalog.sh"
22-Nov 13:36 dbclone-fd JobId 361250: ClientRunBeforeJob: Password: 
22-Nov 13:36 dbclone-fd JobId 361250: ClientRunBeforeJob: pg_dump: error: connection to server at "pg03.int.unixathome.org" (10.55.0.34), port 5432 failed: fe_sendauth: no password supplied
22-Nov 13:36 bacula-sd-04 JobId 361250: Recycled volume "FullAutoNoNextPool-04-17758" on File device "vDrive-FullFileNoNextPool-0" (/usr/local/bacula/volumes/FullFileNoNextPool), all previous data lost.
22-Nov 13:36 bacula-dir JobId 3612

Disclaimer

This article will contain spoilers both on how I solved 2022 Day 21's challenge "Monkey Math" using SQL, as well as general ideas on how to approach the problem. I recommend trying to solve it yourself first, using your favorite language.

AOC Day 21

Tech used:

• The file_fdw extension to read the input
• Functions such as regexp_substr
• Unlogged tables

As always, we will use file_fdw to put our text input into a virtual Postgres table:

CREATE EXTENSION if not exists file_fdw;

CREATE SERVER if not exists aoc2022 foreign data wrapper file_fdw;

DROP SCHEMA if exists aoc2022_day21_monkeymath CASCADE;
CREATE SCHEMA aoc2022_day21_monkeymath;
SET search_path = aoc2022_day21_monkeymath;

CREATE FOREIGN TABLE aoc_day21 (id text, action text)
  SERVER aoc2022 options(filename '/tmp/aoc2022.day21.input',
--  SERVER aoc2022 options(filename '/tmp/aoc2022.day21.testinput',
  FORMAT 'csv', DELIMITER ':');

AOC Day 21 - Part One

The puzzle directions are odd but parseable:

Each monkey is given a job: either to yell a specific number or to yell
the result of a math operation. All of the number-yelling monkeys
know their number from the start; however, the math operation monkeys
need to wait for two other monkeys to yell a number, and those two
other monkeys might also be waiting on other monkeys.

We don't speak monkey, but the elephants we freed in the previous rounds do. This puzzle is pretty straightforward. First, let's pull apart the text strings in the puzzle, which look like this:

cgrb: gzwb * rcfd
gfbz: bwgp - qlfm
jrbf: 2
gvvg: rjch + tjdp
vwsh: grwp * ddsv
tpwb: 1

We will separate the data in each line and store one monkey per row in a new unlogged table. As each row is guaranteed to have a colon, we declared the foreign table as a csv with a delimiter of a colon, which saves us a step. But we still need to break apart the other items into specific columns. Some simple regular expression functions can help us do this:

CREATE UNLOGGED TABLE puzzle (
  id

Most people who use PostgreSQL on a regular basis may have seen pg_stat_activity, which is a system view. It gives you real-time information about what is happening on your database server. It has grown over the years to provide ever more information about system processes, database connections, parallelism and a lot more.

However, if you are running a Citus deployment, pg_stat_activity doesn’t provide enough information to understand what’s going on. While pg_stat_activity is the perfect tool to inspect a single server, a Citus deployment usually consists of many servers. In that case, it’s hard to see what all the shards are doing, and how a query is distributed.

citus_stat_activity: Monitoring Citus

The solution to the problem is the citus_stat_activity view. When Citus is enabled, this view is available by default and provides the counterpart to pg_stat_activity on the cluster level. The following listing shows what the view looks like:

postgres=# \d citus_stat_activity
                    View "pg_catalog.citus_stat_activity"
      Column      |           Type           | Collation | Nullable | Default 
------------------+--------------------------+-----------+----------+---------
 global_pid       | bigint                   |           |          | 
 nodeid           | integer                  |           |          | 
 is_worker_query  | boolean                  |           |          | 
 datid            | oid                      |           |          | 
 datname          | name                     |           |          | 
 pid              | integer                  |           |          | 
 leader_pid       | integer                  |           |          | 
 usesysid         | oid                      |           |          | 
 usename          | name                     |           |          | 
 application_name | text                     |           |          | 
 client_addr      | inet                     |           |          | 
 client_hostname  | text                     

Another edition of our complete book is out there!

Learn PostgreSQL - second edition

On the last Halloween, the second edition of our book Learn PostgreSQL has been released!

I’m very proud of all the work me, and my friend Enrico (co-author), have done to not only and merely update this revision of the book, which now cover PostgreSQL 16, but also to provide new content, examples and most notably, a new approach to help readers understanding the concepts expressed in the book.

In fact, with this new edition, readers will have access to a set of Docker containers that can be used to quickly fire up a PostgreSQL instance and get hands on the examples and exercises!

Moreover, every chapter now has a Verify your knowledge ending section, made of questions and short answers to point the reader to the most important concepts of the chapter itself.

While the overall structure of the chapter has remained the same, we got the great chance to improve almost all the content in order to better explain concepts and terminology.

I strongly believe *this is not a simple *update of the book, rather it is a full upgrade! **

And after almost a month in the wild, the reviews for the book confirm my feelings{target=”_blank”}!

Similarly to the previous edition, a GitHub repository with all the main examples, the Docker containers, and other gadgets is available.

And as always, me and Enrico will enjoy any feedback and errata that can help us improve, and other readers to get a better experience.

This morning I encountered this error message:

20-Nov 03:25 dbclone-fd JobId 361156: ClientRunBeforeJob: pg_dump: error: connection to server at
"pg02.int.example.org" (10.55.0.32), port 5432 failed: fe_sendauth: no password supplied

In this post:

1. FreeBSD 13.2
2. PostgreSQL 12.16 (server – pg02)
3. PostgreSQL 16.1 (client – dbclone)

The backstory:

• dbclone dumps the bacula database hosted on pg02.
• The script to do this is: /usr/local/bacula/dump_catalog.sh
• The host, user, and database name are stored in

• /usr/local/bacula/config.sh

• The password is stored in /root/.pgass

This script ran correctly on Saturday night. On Sunday night, it failed.

On Sunday, I upgraded the PostgreSQL client on dbclone from postgresql12-client-12.17 to postgresql16-client-16.1.

The script

[17:38 dbclone dan ~] % sudo cat /usr/local/bacula/dump_catalog.sh                              
#!/bin/sh

CONF=/usr/local/bacula/config.sh
DIR=/usr/home/rsyncer/backups/bacula-database/postgresql

if [ -r $CONF ]
then
      . $CONF
else
   echo Configurtion file does not exist: $CONF
   exit 3
fi

#/usr/local/bin/pg_dump -Z0 -Fc --host ${HOST} --username ${USER} ${DB} --no-password > ${DIR}/${DB}.dump.tmp
/usr/local/bin/pg_dump "sslmode=require host=${HOST} user=${USER} dbname=${DB}" --no-password -Z0 -Fc  > ${DIR}/${DB}.dump.tmp

DUMP_RESULT=$?
if [ ${DUMP_RESULT} != 0 ]; then
  exit ${DUMP_RESULT}
fi

mv ${DIR}/${DB}.dump.tmp ${DIR}/${DB}.dump

# allow rsyncer users to read this file
/usr/sbin/chown rsyncer:rsyncer ${DIR}/${DB}.dump

The commented out pg_dump is what was there before the error. The new invocation using sslmode was added today while working on this issue.

The configuration file

The configuration file contains:

[17:39 dbclone dan ~] % cat /usr/local/bacula/config.sh
#!/bin/sh

# this is the configuration file for other scripts in this directory

USER=bacula
HOST=pg02.int.example.org
DB=bacula

.pgpass

Here is the .pgpass file in q

The Postgres mailing lists are full of practical discussions, but two years ago there was a 77-email thread titled "The tragedy of SQL" that questioned the utility of the SQL query language; t is worth a review. While opening with "A fun philosophical discussion," it states:

The world's economic output would be substantially higher (5%?) if our industry had settled on almost anything other than SQL for relational databases.

It disparages object-relational mappers and suggests Datalog as an alternative query language.

Continue Reading »

Debian has a long history of supporting diverse machine architectures, but the number of architectures supported on apt.postgresql.org, the repository for PostgreSQL on Debian and Ubuntu, has only been growing slowly. So far, this has been amd64 (Intel/AMD x86_64), ppc64el (IBM POWER), and arm64 (Arm aarch64). The old i386 (Intel 32-bit x86) port is still there, but only for the oldest release (and Debian unstable).

Today, I am happy to announce that the family is growing again, with the addition of IBM’s z-series architecture, called “s390x” in Debian terms.

A build machine has been provided by Marist College’s LinuxOne cloud. Preparations to get the architecture set up have been running since earlier this year. Initially, there were some stability problems with the newer PostgreSQL versions, but these have been tackled with patches provided by the PostgreSQL hackers community. Thanks to CYBERTEC PostgreSQL for supporting me working on this!

The s390x architecture is now fully supported for Debian and Ubuntu on apt.postgresql.org.

CYBERTEC PostgreSQL helps customers running PostgreSQL on many different platforms. Talk to us for support!

In order to receive regular updates on important changes in PostgreSQL, subscribe to our newsletter, or follow us on Twitter, Facebook, or LinkedIn.

The post PostgreSQL on s390x appeared first on CYBERTEC.

The PostGIS development team is pleased to provide bug fix and performance enhancements 3.4.1, 3.3.5, 3.2.6, 3.1.10, 3.0.10 for the 3.4, 3.3, 3.2, 3.1, 3.0 stable branches.

This post supports my session titled PostgreSQL: Extensions Shape the Future at PASS Data Community Summit 2023 on November 15. Thank you to everyone who joined this session during PASS. I believe the audio recording with slides should be made available at some point a few months in the future.

Slides

The following download is the PDF version of the slide deck.

• Extensions Shape the Future slides (PDF)

1. Overview

Write-Ahead Logging (WAL) is a standard method used in PostgreSQL to ensure data integrity. Many key features rely on this WAL design, such as streaming replication, and Point-In-Time recovery, etc. While there is a detailed online book explaining how WAL works in PostgreSQL, there is a lack of detailed documentation or blogs describing the process of adding a new XLOG record to WAL. This blog aims to share the steps on how to add a piece of customized information as an XLOG record for use during WAL REDO.

2. WAL REDO

WAL not only ensures data integrity but is also crucial for replication and recovery. Sometimes, you might consider adding a piece of information as an XLOG to help achieve specific tasks. All the REDO functions implemented by the PostgreSQL community are defined in `src/include/access/rmgrlist.h`.

/* symbol name, textual name, redo, desc, identify, startup, cleanup */
PG_RMGR(RM_XLOG_ID, "XLOG", xlog_redo, xlog_desc, xlog_identify, NULL, NULL)
PG_RMGR(RM_XACT_ID, "Transaction", xact_redo, xact_desc, xact_identify, NULL, NULL)
PG_RMGR(RM_SMGR_ID, "Storage", smgr_redo, smgr_desc, smgr_identify, NULL, NULL)
PG_RMGR(RM_CLOG_ID, "CLOG", clog_redo, clog_desc, clog_identify, NULL, NULL)
PG_RMGR(RM_DBASE_ID, "Database", dbase_redo, dbase_desc, dbase_identify, NULL, NULL)
PG_RMGR(RM_TBLSPC_ID, "Tablespace", tblspc_redo, tblspc_desc, tblspc_identify, NULL, NULL)
PG_RMGR(RM_MULTIXACT_ID, "MultiXact", multixact_redo, multixact_desc, multixact_identify, NULL, NULL)
PG_RMGR(RM_RELMAP_ID, "RelMap", relmap_redo, relmap_desc, relmap_identify, NULL, NULL)
PG_RMGR(RM_STANDBY_ID, "Standby", standby_redo, standby_desc, standby_identify, NULL, NULL)
PG_RMGR(RM_HEAP2_ID, "Heap2", heap2_redo, heap2_desc, heap2_identify, NULL, NULL)
PG_RMGR(RM_HEAP_ID, "Heap", heap_redo, heap_desc, heap_identify, NULL, NULL)
PG_RMGR(RM_BTREE_ID, "Btree", btree_redo, btree_desc, btree_identify, NULL, NULL)
PG_RMGR(RM_HASH_ID, "Hash", hash_redo, hash_desc, hash_identify, NULL, NULL)
PG_RMGR(RM_GIN_ID, "Gin", gin_r

Introduction

In my previous blog about table access method here, we discussed the basis of PostgreSQL’s table access method APIs and the difference between a heap tuple and Tuple Table Slot (TTS). In this blog, let’s talk more about the particular API calls that helps PostgreSQL core to achieve sequential scan.

APIs Involved

To achieve a sequential scan, the following table access method API callbacks will be involved:

• relation_size()
• slot_callbacks()
• scan_begin()
• scan_getnextslot()
• scan_end()

By default, PostgreSQL uses heap access method, and it implements the above APIs as:

• table_block_relation_size()
• heapam_slot_callbacks()
• heap_beginscan()
• heap_getnextslot()
• heap_endscan()

these are located in src/backend/access/heap/heapam_handler.c.

Basic Workflow

API Function Calls

table_block_relation_size(Relation rel, ForkNumber forkNumber):

This is the first function to be called during the query planning phase and it is supposed to physically examine the size of the given Relation rel and ForkNumber forkNumber. It shall returns the total number of blocks (8KB size by default) to the caller so that it can plan the best query strategy based on how large of a table we are working with.

What is a relation?

A relation, in most cases, represents a table, an index or a view. In our case, it represents a table and it contains important information about this table, such as the object identifiers (OIDs) for database, table space, number of attributes, other flags…etc

What is a fork number?

A relation is composed of multiple forks

• MAIN_FORKNUM (0: stores user data)
• FSM_FORKNUM (1: stores free space map data)
• VISIBILITY_FORKNUM (2: visibility data)
• INIT_FORKNUM (3: used to reset WAL-logged table)

more on fork number here

heapam_slot_callbacks()

This function is called to return an appropriate TTS Operations so executor is able to convert a heap tuple to Tuple Table Slot (TTS

How to optimize your data storage and retrieval strategies. Two common techniques for improving database performance and manageability are indexing and partitioning.

The post An Introduction to Indexing and Partitioning in PostgreSQL appeared first on Stormatics.

When a single query is run outside of a transaction block, it is clear how transaction visibility should behave — only transactions committed before the query started should be visible. Changes made by other sessions during the query, even if committed during the query, should not be visible. Slide 11 of my MVCC Unmasked presentation illustrates this.

For queries run in transaction blocks, the ideal behavior is less clear, so Postgres supports three options for controlling transaction block behavior. The default, READ COMMITTED, causes each new query in a transaction block to get a new visibility snapshot, meaning that each new query sees changes that were committed by other sessions before the query started.

This might not be ideal, so Postgres supports REPEATABLE READ. In this mode, instead of getting a new snapshot at the start of each query in a transaction block, a snapshot is taken when the first query in the transaction block is issued, and this snapshot is used for all queries in the transaction block. This means that changes committed by other sessions during the transaction block's execution are not seen by any statements in the block. This is ideal for read-only transactions, such as those used for reporting. It guarantees that all statements see a consistent view of the database.

Continue Reading »

Postgres is a robust data platform. Yes, it's more than a boring old relational database. It has rich indexing, data types (including JSON), and so much more. It also has support for a variety of extensions that can further broaden it's already great functionality. Two of those extensions when coupled together make Postgres a very compelling approach for IoT architectures. Today we're going to start from the ground up on how you would design your architecture with Postgres along with the Citus and pg_partman extensions.

Citus and sharding

Citus is an extension that allows you to shard your database across multiple nodes, while allowing your application to remain largely unaware. Citus can be beneficial to your app if:

• You expect to outgrow the performance a single Postgres instance can deliver
• Your schema/data model can be mapped cleanly to Citus
• The queries/workload pattern can be mapped cleanly to Citus

Lucky for us, IOT workloads check the boxes for all of the above.

Starting with our IoT dataset

We're going to begin with a simple schema that relates to vehicles and tracks a few basic measurements against them. We'll also have a table that tracks the location of the vehicle at the time of each sensor sampling as well.

CREATE TABLE sensor_data (
  id SERIAL,
  car_id VARCHAR(17) NOT NULL,
  sensor_type VARCHAR(20) NOT NULL,
  sensor_value INT NOT NULL,
  timestamp TIMESTAMP WITH TIME ZONE NOT NULL
);

CREATE TABLE location_data (
  id SERIAL,
  car_id VARCHAR(17) NOT NULL,
  latitude float8,
  longitude float8,
  timestamp TIMESTAMP WITH TIME ZONE NOT NULL
);

While our above schema is simply it's not unrealistic of many IoT data models, though yours could be more complex.

How do we shard this dataset?

The key to sharding is that you can push down most of your joins to the node where the data is located. If you're having to move data in between nodes in order to join your performance will suffer. In the case of IoT workloads device_id is a very common pattern

Altering or dropping tables which are in logical replication is not a good idea if the changes are not uniform across a TRAKTOR cluster. So, TABLEs in a replicaset are now protected against DROP TABLE and ALTER TABLE by default using PostgreSQL event triggers.

Recently I met Kelvin Omereshone, based in Nigeria, for a session on his show Teach Kelvin Your Thing (TKYT). Here’s the description of the show:

Teach Kelvin Your Thing was created out of a need for me to learn not just new technologies but how folks who know these technologies use them.

This was a fun opportunity to contribute to Kelvin’s catalog of more than 50 recorded sessions! The sessions mainly cover web development with JavaScript tech, until this one! Kelvin let me know this was the first TKYT session outside of JavaScript. Maybe we’ll inspire some people to try Ruby!

Besides TKYT, Kelvin is a prolific blogger, producer, writer, and an upcoming author! Kelvin is an experienced Sails framework programmer, and announced the recent honor of becoming the lead maintainer for the framework.

Kelvin and decided to talk about High Performance PostgreSQL for Rails.

The session is called High Performance PostgreSQL for Rails applications with Andrew Atkinson and is on YouTube. The recorded session is embedded below. Although we barely scratched the surface of the topic ideas Kelvin had, I’ve written up his questions as a bonus Q&A below the video. Note for the video: unfortunately my fancy microphone wasn’t used (by mistake). Apologies on the audio.

Q&A

With a one hour session, we only made it through some basics with the Active Record ORM, SQL queries, query planning, and efficient indexes. While those are some of the key ingredients to good performance, there’s much more.

The following questions explore more performance related topics beyond what we covered in the session.

How do you optimize PostgreSQL for high performance in a Rails application?

Achieving and sustaining high performance with web applications requires removing latency wherever possible in the request and response cycle. For the database portion, you’ll want to understand your SQL queries well, make sure they’re narrowly scoped, and that indexes are optimized to support them. Besides that, the p

LATERAL is a powerful SQL feature that allows virtual tables to be created in FROM clauses that reference real or virtual tables that appeared previously in the same FROM clause. Here is a simple example:

CREATE TABLE test (x) AS SELECT generate_series(1,3);
 
SELECT * FROM test AS test1 (x) JOIN LATERAL (SELECT test1.x + 1) AS test2 (x) ON (true);
 x | x
---+---
 1 | 2
 2 | 3
 3 | 4

Continue Reading »

Discover how Multi-Version Concurrency Control (MVCC) works in Postgresql with its isolation level. Learn internal concepts regarding MVCC implementation in psotgresql and how concurrent transaction works

The post Database Concurrency: Two phase Locking (2PL) to MVCC – Part 2 appeared first on Stormatics.

If you're building a B2B app chances are it's multi-tenant, meaning one customer data is separated and doesn't intermingle with other customer data. When building the app itself you'll typically have some URL route defining the specific tenant. When it comes to your database there are a number of design patterns that you can use that offer different advantages, trade-offs, and scaling opportunities.

When you begin building your app, time to market is essential, but you don't want to make decisions that are hard to reverse. We're first going to take a look at the most common design patterns for multi-tenant database design in Postgres, followed by some guidance on how to design your data model so that you're prepared from day 1 for massive scale (should you need it).

Three approaches to multi-tenant database design

One database per customer

The first option is simply to have a separate database per customer. In this case you could have complete copies of your web application running for them as well. This gives you the maximum amount of isolation, you can encrypt your databases with different keys, scale them independently of each other, upgrade them at different times, leave old versions of your app and migrations on one while still updating other customers.

If this all sounds great - slow down and beware. Maintaining different versions of your database and different versions of your schema, figuring out how to run migrations across multiple different databases (a seemingly solved problem for most frameworks when dealing with a single database) can become a significant pain at larger scale.

What is a larger scale? Managing 5 databases is fine, managing 10 you're probably okay, but if you anticipate 50 customers or more steer clear from this approach.

Schema per customer

The second approach is to use schemas. In many ways this approach is not dissimilar from the one database per customer. But there are both some pros and cons of this approach...

Pros:

• Can easily join acro

In PostgreSQL 16, a new feature has been added: reserved_connections. What is the idea behind it? Back in the old days, DBA’s and software developers tended to work as a superuser (= postgres user). However, this is not ideal for security reasons. Therefore the PostgreSQL community has worked hard to reduce the necessity of connecting as a superuser. Over the years, features have been added to reduce the need of using this highly privileged way of connecting to the system, and reserved connections are another method.

In this short blog post, you will be introduced to the reserved_connections setting and its additional roles in PostgreSQL 16.

Adjusting connection parameters in PostgreSQL

In PostgreSQL 16, we can see 3 major parameters controlling connections in general:

postgres=# SHOW max_connections;
 max_connections 
-----------------
 100
(1 row)

postgres=# SHOW superuser_reserved_connections;
 superuser_reserved_connections 
--------------------------------
 3
(1 row)

postgres=# SHOW reserved_connections;
 reserved_connections 
----------------------
 0
(1 row)

The max_connections setting controls the number of overall connections allowed. This is a hard limit and changing this variable needs a restart. Out of this pool of connections, superuser_reserved_connections will only be available to the superuser.

In our example, this means that we can create 97 “normal” connections and 3 superuser ones (or 100 superuser ones, which is not recommended at all). Why is that important? The idea is that there are always spare connections available in order to perform maintenance and other tasks.

PostgreSQL 16 will provide us with a new, additional setting: reserved_connections. For many years, the idea has been circulated that it’s best to limit the use of superusers. Therefore, a new role called pg_use_reserved_connections has been introduced. If a normal (non-superuser) is assigned to this role, it’s possible to access the pool of reserved connections. In other words:

We create a

Introduction

PostgreSQL, a powerful open-source relational database, is known for its robustness and extensibility. One critical aspect of database management is ensuring reliable backup and recovery processes. While PostgreSQL provides tools like pg_basebackup and WAL archiving for this purpose, there’s a powerful external tool called pg_rman that takes this process to the next level. In this blog post, we’ll explore what pg_rman is, how to perform a simple backup with pg_rman.

What is pg_rman?

pg_rman is an advanced backup and recovery manager designed specifically for PostgreSQL databases. Developed by the open source community, it provides a range of features to simplify the backup and restore process.

Key features:

1. Full backup
   pg_rman supports full backups, allowing you to capture the entire database at a specific point in time. This is crucial for disaster recovery and cloning databases for testing. For example,

$ pg_rman backup --backup-mode=full -B /path/to/backupdir -D /path/to/pgdatadir -d postgres --progress

Where,

-B /path/to/backupdir: Specifies the location where the backup will be stored.
-D /path/to/pgdatadir: Specifies the location where the original pgdata cluster need to be backed up.
–backup-mode=full: Indicates that this is a full backup.
-d postgres: Specifies the user to perform this backup.
–progress: Displays progress during the backup process.

2. Incremental backup
   In addition to full backups, pg_rman offers the ability to perform incremental backups. This means you can save disk space and time by only backing up the changes since the last full or incremental backup. For example,

$ pg_rman backup --backup-mode=incremental -B /path/to/backupdir -D /path/to/pgdatadir -d postgres --progress

Where,

–backup-mode=incremental: Indicates that this is an incremental backup. pg_rman will only backup changes since the last full or incremental backup.

3. Archive Log backup
   Managing WAL (Write-Ahead Loggin

Image credit: Generated with Bing Image Creator

In databases, indexes are data structures that speed up finding specific values in a table column. The analogous task in vector databases consists of finding the (approximate) nearest-neighbors of a given vector. So, to accomplish this task fast, we can similarly create specialized vector indexes.

But, speeding up a query is not just about blindly creating an index. When deciding whether to create one or more indexes on a table, several factors need to be considered—for example, the size of the tables, whether the table is modified frequently, how the table is used in queries, and so on. Similar considerations apply to vector indexes.

In today's post, let us explore vector indexes and their tradeoffs in the context of Postgres and Pgvector. In particular, let us compare their build time, size, and speed, and, based on that, derive some guidelines to decide which one to choose for a given application.

Indexes in Pgvector​

Pgvector is an open-source Postgres extension for similarity search. It allows for exact and approximate nearest-neighbor search. In particular, for ANN it offers two types of indexes: IVFFlat and HNSW. Let us briefly discuss them.

IVFFlat​

The IVFFlat (Inverted File with Flat Compression) index works by dividing the vectors in the table into multiple lists. The algorithm calculates a number of centroids and finds the clusters around those centroids. So, there is a list for each centroid, and the elements of these lists are the vectors that make up its corresponding cluster.

When searching for the K nearest vectors, instead of calculating the distance to all vectors, the search space is narrowed to only a subset of the lists, thus reducing the number of computations. Which lists are the candidates? The ones whose centroid is closer to the search vector.

IVFFlat generates lists based on clusters.

So, we can infer that the effectiveness of the index depends on two parameters: the number/size of the