# PostgreSQL Transport Design


Each queue in PostgreSQL Transport is implemented as table inside a database. Depending on the endpoint configuration, each endpoint could use multiple queues, and therefore tables, e.g. for [callbacks](/transports/sql/callbacks.md).

## Structure

The queue table consists of the following columns

### ID

The `Id` is a `uuid` generated by the sending code. It is not used by the transport itself.

### Expires

The `Expires` column contains the optional date and time when the message is going to expire. An expired message is dropped by the transport.

### Headers

The `Headers` column contains a JSON representation of message headers.

### Body

The `Body` column contains the serialized message body.

### BodyString

The `BodyString` column contains the message body formatted in a human-readable format. It must be explicitly enabled using transport configuration options:

<!-- snippet: MessageBodyString-config -->

```cs
var transport = new PostgreSqlTransport("connectionString")
{
    CreateMessageBodyComputedColumn = true
};
```

<!-- endsnippet -->

### Seq

The `Seq` column is used to define the FIFO order of the queue. It is auto-incremented by PostgreSQL(`serial`). The receive message SQL query returns a message with the lowest value of `Seq` that is not locked by any other concurrent receive operation.

## Behavior

The following section describes the runtime behavior of PostgreSQL transport when sending and receiving messages.

### Sending

Messages are sent by executing an `insert` command against the queue table.

### Receiving

Messages are received by executing a `delete` command against the queue table. The `delete` is limited to a row with the lowest `Seq` not locked by any other concurrent `delete`. This ensures that multiple threads within an endpoint instance, and multiple instances of the same scaled-out endpoint, can operate at full speed without conflicts.

PostgreSQL transport operates in two modes: *peek* and *receive*. It starts in the *peek* mode and checks via `max(seq) - min(seq)` the number of pending messages. If the number is greater than zero, it switches to the *receive* mode and starts spawning receive tasks that use the `delete` command to receive messages.

The maximum number of concurrent receive tasks never exceeds the value set by `LimitMessageProcessingConcurrencyTo` (the number of tasks does not translate to the number of running threads which is controlled by the TPL scheduling mechanisms).

When all tasks are done the transport switches back to the *peek* mode.

Under certain conditions, the initial estimate of the number of pending messages might be wrong e.g. when there is more than one instance of a scaled-out endpoint consuming messages from the same queue. In this case, one of the receive tasks will fail (i.e. `delete` will return no results). When this happens, the transport immediately switches back to the *peek* mode.

The [default peek interval](#behavior-queue-peek-settings-peek-delay-configuration), if no peek has yet been run or the previous peek returned no messages in the queue, is 1 second. The recommended range for this setting is between 100 milliseconds to 10 seconds. If a value higher than the maximum recommended settings is used, a warning message will be logged. A value less than 100 milliseconds is rejected because it puts unnecessary stress on the database, while a value larger than 10 seconds should be used with caution as it may result in messages backing up in the queue.

> [!WARNING]
> If the queue peek interval is configured, it must not be set larger than the [Time-To-Be-Received](/nservicebus/messaging/discard-old-messages.md)(TTBR) to ensure that such messages are not discarded.

### Queue peek settings

#### Peek delay configuration

Use the following code:

<!-- snippet: postgresql-queue-peeker-config-delay -->

```cs
var transport = new PostgreSqlTransport("connectionString")
{
    QueuePeeker =
    {
        Delay = TimeSpan.FromSeconds(5)
    }
};
```

<!-- endsnippet -->

Read more information about [tuning endpoint message processing](/nservicebus/operations/tuning.md).