PostgreSQL

Andromeda keeps everything in PostgreSQL with PostGIS and TimescaleDB. It needs one server, a role that can create databases, and room on the server for its connection pools. The broker sets the few session settings it depends on itself, on every database, so they hold on any server. Tuning the server is left to you, or to the PostgreSQL image, which does it for you.

What the broker needs

Requirement	Minimum
PostgreSQL	15
Database encoding	UTF-8
PostGIS	3.0.0
TimescaleDB	2.31.0

On a server below any of these, a tenant is refused with 500 InternalError, and the log says tenant schema contract:. The broker checks the server version, the encoding and whether the server ships postgis and timescaledb at all before it runs a single migration, so a database is never left half-migrated on a server that can't hold the schema.

Preload the TimescaleDB library with shared_preload_libraries = 'timescaledb'. Add pg_stat_statements to the same list if you want statement statistics. The broker never creates that extension, so create it yourself in the tenant databases you want statistics for.

Every tenant database gets the extensions postgis, timescaledb, ltree, pg_trgm, btree_gist and btree_gin. It also gets pgstattuple and pg_repack for maintenance when the server ships them and the role may create them. Maintenance drives only the 1.5 releases of pg_repack, and the PostgreSQL image ships 1.5.3. On a server that ships another version, maintenance reports the tables it would repack but doesn't repack them.

Roles and privileges

The role in database.connection does most of the work. It creates the tenant databases and their extensions, runs the migrations and upgrades Snapshot schemas, and it owns everything it creates. PostGIS isn't a trusted extension, so that role is normally a superuser. A role that isn't needs the following.

• CREATEDB

  To create a tenant database.

• CREATE on each tenant database

  To create Snapshot schemas in it.

• pg_stat_scan_tables

  To run pgstattuple(regclass) and pgstattuple_approx(regclass), which maintenance uses to measure B-tree and GiST index bloat and the free space in tables. Maintenance runs in automatic mode by default, and owning the tables it migrated already covers every action it takes except repacking.

• Repack rights

  To repack a table, the role must be a superuser, or own the table and also hold EXECUTE on the functions in the repack schema, SELECT on the repack.tables view, and USAGE and CREATE on the repack schema. Without them, maintenance only reports the tables it would repack.

• pg_read_all_stats or pg_monitor

  To see other sessions' activity, which maintenance reads to find long transactions and held vacuum horizons. Without it, those sessions show up without their details.

The broker creates and drops tenant databases over a connection to the database that database.connection names, which is never a tenant's own. The server needs no postgres database or any other database set aside for administration, and the PostgreSQL image doesn't create one.

Behind a pooler, the pooler login must be the same role as database.connection. Snapshot schemas are created over the serving pool, so they belong to the role that logs in through the pooler, while the role in database.connection upgrades and maintains them. Unless that role is a superuser, it can't change schemas another role owns, and the Snapshots in them are quarantined at their next branch upgrade. PgBouncer has the details.

timescaledb.telemetry_level is yours to set, and the broker neither sets nor reads it. Set it to off in postgresql.conf if the server shouldn't report to the TimescaleDB project.

TimescaleDB editions

Both TimescaleDB editions work with the broker. They differ only in how old history is stored.

Edition	Old history	Reported as
TSL (Community)	Compressed into the columnstore	TSL
Apache-2.0	Kept uncompressed	Apache-2.0

With the TSL edition, the broker converts history partitions older than temporal.storage.columnstore.convert_after into the columnstore itself, on its own schedule, and installs no TimescaleDB job for it. The broker reads the edition from the server's timescaledb.license setting, and GET /ngsi-ld/v1/version reports it as the license of the timescaledb component.

What the broker sets on each database

The broker sets four defaults with ALTER DATABASE ... SET, on the platform database and on every tenant database. A database default reaches every session, including the ones a transaction pooler opens, which a per-connection SET wouldn't.

• synchronous_commit, from database.synchronous_commit

  on by default. A write is confirmed to the client only once it is on disk, so a created entity, and a change a notification announced, survive a server crash. With off, a crash can lose up to $3 \cdot t_{\text{wal\_writer\_delay}}$ of writes that clients were already told succeeded. That is $3 \cdot 200\ \text{ms} = 600\ \text{ms}$ with PostgreSQL's default, and the notifications for those writes may already have gone out. The database stays consistent either way. local, remote-write and remote-apply are for servers with synchronous standbys.

• statement_timeout, 35 seconds by default

  $T_{\text{statement}} = T_{\text{request}} + 5\ \text{s}$, where $T_{\text{request}}$ is request_limits.timeout. A statement whose request has already timed out is work nobody is waiting for, and the margin lets the request answer first.

• client_connection_check_interval, 1 second

  A statement whose client has gone is ended at the next check, instead of running to the end.

• timescaledb.enable_chunk_skipping, on

  TimescaleDB records what a history partition holds only in a session that has this setting on, and a time-bounded temporal query skips any partition whose ranges were never recorded. So every session that writes or converts history must keep it on, your own sessions included.

The broker checks these each time it opens a database and writes only what is missing or different, so a configuration change reaches existing databases the next time each one is opened. When it does write one, it reopens that tenant's pool before serving, since open sessions keep the values they started with. Behind PgBouncer, server connections opened before the change keep their old values until PgBouncer replaces them, after server_lifetime.

How long statements run

When a request gives up

A request can time out, or lose its client, while one of its statements is still running. The broker then hands the connection back with the statement still going, and closes it if it isn't ready again within 1 second. PostgreSQL ends the statement at its next connection check, so it stops within about 2 seconds rather than at statement_timeout. Behind PgBouncer, closing the client connection makes PgBouncer close the busy server connection too, and PgBouncer logs client disconnect while server was not ready.

Work without a time limit

Some work outlasts any request on purpose, and lifts the statement timeout for its own transaction.

• The retention sweep's partition drops and deletes
• Converting a partition to the columnstore
• Copying data when a Snapshot is cloned
• Reading every entity location to build a tenant's spatial index when the tenant is loaded

Maintenance sessions have limits of their own, a statement_timeout of 30 minutes and a lock_timeout of 1 second. Maintenance explains why.

Schema changes and lock waits

A schema change needs a strong lock, and while it waits for one, every other statement on that table queues behind it. So every session that changes the schema waits at most 2 seconds for each lock. A change that doesn't get its lock, with SQLSTATE 55P03, is tried again 2 seconds later, five times in all. A lock held by a transaction shorter than about 10 seconds is still taken, and a longer one fails the change rather than stalling the tenant.

• Migrations, Snapshot upgrades and extension updates

  These connect straight to PostgreSQL, with lock_timeout = 2000ms and no statement timeout. A migration that ran out of lock attempts rolls back, so the next attempt resumes at that migration with nothing half-applied.

• Snapshot upgrades that can't get their locks

  These fail like any other. The Snapshot is quarantined and upgraded again the next time the tenant is loaded. Upgrades has the details.

Extension updates

A database keeps the TimescaleDB and PostGIS versions it was created or last updated with, whatever the server has installed since. The broker updates them, the platform database at startup and each tenant database when it is loaded. Wherever the installed version is below the one the server offers, it runs ALTER EXTENSION ... UPDATE before it migrates the schema and checks the version floor. A tenant that no request loads after a server upgrade is loaded for the update by the broker's wake pass, a few tenants at a time. So a tenant created on an older server is brought up to date with no action from you.

TimescaleDB refuses to update in a session that has already loaded the old library, and once the update commits, it closes other sessions still using the old one at their next statement. The broker runs each update on a connection of its own, and keeps its serving connections from loading the old library. Brokers that open the same database at once take turns, and the later ones find nothing left to do.

Restart the broker together with the server

Upgrading the server restarts it. A broker that keeps serving across that restart can open connections with the old library before the tenant is updated, and each of those connections fails one statement after the update.

The update needs the extension's owner, and PostGIS needs a superuser. A role without the privilege logs extension_update_refused and keeps the installed version. If that leaves TimescaleDB below 2.31.0, the tenant is refused until you update it yourself, as the owner.

psql -X -d andromeda_tenant_example -c 'ALTER EXTENSION timescaledb UPDATE'

-X keeps a .psqlrc from loading the old library first.

pg_repack is tracked the same way, but it ships no update scripts, so it can't be updated in place. A database whose pg_repack is behind the version the server ships has the extension dropped and created again. A superuser does this as the first statement of a connection of its own. While a repack's objects are still in the database, the drop is refused, and the broker logs extension_recreate_deferred and tries again the next time it opens the tenant. The wake pass also loads a tenant whose pg_repack has not yet been tried against the version the server ships, as it does for a tenant behind on TimescaleDB or PostGIS.

Connection capacity

The broker's pools must fit in the connections the server allows its role. GET /ngsi-ld/v1/version reports whether they do, on its postgresql component. Other members are left out here.

{
    "component": "postgresql",
    "connections": {
        "serverMaxConnections": 200,
        "reservedForOthers": 3,
        "brokerCeiling": 152,
        "verdict": "sufficient"
    }
}

• serverMaxConnections

  The server's max_connections.

• reservedForOthers

  The connections the broker's role can't use. That is superuser_reserved_connections unless the role is a superuser, plus reserved_connections unless it is a superuser or a member of pg_use_reserved_connections.

• brokerCeiling

  How many connections the broker's pools hold if every one of them is full at once.

• verdict

  exceeded when the ceiling doesn't fit, and the broker then also logs database_connection_capacity_exceeded at startup.

How the ceiling is counted

Some pools always connect straight to PostgreSQL. The rest go through [database.pooler] when one is in front, and then the pooler sizes its own server connections, so they leave the ceiling.

Pool	Connects	Size	Default
Platform	Direct	database.platform_max_connections	32
Maintenance	Direct	3 on the platform database, 1 in a tenant	4
Repack	Direct	2 in the tenant being repacked	2
Tenants loading	Direct	2 for each of tenancy.build.max_concurrent	16
Default tenant	Through a pooler	tenancy.default_tenant.max_connections	32
Loaded tenants	Through a pooler	tenancy.databases.max_connections for each of tenancy.residency.max_resident	64
Due-work checks	Through a pooler	2	2

With every default, the ceiling is 152 connections, 54 of them direct. With tenancy.mode = "disabled" only the default tenant exists, so the loaded tenants' pools drop out and the ceiling is 88. Behind a pooler, only the 54 direct connections count. Leave room on top of the ceiling for your own tools.

To keep a monitoring or admin role able to connect when the broker's pools hold every other slot, set reserved_connections and grant pg_use_reserved_connections to that role. Granting it to the broker's own role helps only when its requests connect as another role, as behind a pooler with a login of its own. Otherwise the broker's pools take the reserved slots too.

Server settings to consider

The PostgreSQL image sets temp_file_limit, idle_session_timeout and the background workers for you, and leaves backups to you. On a server of your own, PostgreSQL tuning gives the image's settings as one include file. These deserve a thought either way.

• Backups

  Use a physical backup tool with WAL archiving, such as pgBackRest or WAL-G, and both work with TimescaleDB. A logical dump of a TimescaleDB database needs timescaledb_pre_restore() and timescaledb_post_restore() around the restore, which makes physical backups the simpler choice.

• temp_file_limit

  Without a limit, one query that spills to disk can fill the data volume and stop every tenant. A limit turns that into one failed request.

• idle_session_timeout

  Closes forgotten interactive sessions. The broker's pools close connections that have been idle for 30 seconds, so a timeout of an hour or so never touches them.

• commit_delay and commit_siblings

  With synchronous_commit = on, they let concurrent commits share one disk flush. That can raise throughput under many small concurrent writes, and it adds latency when there are few. Measure them under your own load before you keep them.

• TimescaleDB background workers

  TimescaleDB runs a scheduler in every database that has the extension, so one per tenant, and each scheduler takes one of max_worker_processes and one of timescaledb.max_background_workers. The broker runs no TimescaleDB job, so a database without a scheduler loses nothing the broker relies on. TimescaleDB does install one job of its own in every such database, job 3, which deletes old entries from its job history. Once the schedulers hold every background worker, the server logs failed to launch job 3 with out of background workers, which is harmless. Size timescaledb.max_background_workers above the number of tenant databases to keep the line away, and for the jobs you add yourself.

Logical replication

Logical replication needs a replica identity on every relation. Every relation of the tenant schema has a primary key except the four census ledgers, entity_type_census, entity_type_attribute_census, attribute_census and stream_type_census. Those are append ledgers that deletion folds, with no natural key, so they use REPLICA IDENTITY FULL. attribute_history is a TimescaleDB hypertable, and its rows live in chunks that TimescaleDB creates and drops, which a publication doesn't follow the way it follows ordinary tables.

Host settings

Some settings belong to the machine rather than to PostgreSQL.

• Huge pages

  Reserve vm.nr_hugepages for the server's shared_memory_size_in_huge_pages, then set huge_pages = on. Without them, every backend builds its own page tables for the shared buffers.

• Transparent huge pages

  Set to madvise or never, since always causes latency stalls.

• vm.dirty_background_bytes and vm.dirty_bytes

  A few hundred megabytes each, so dirty pages are written out steadily rather than in bursts that stall checkpoints.

• vm.overcommit_memory = 2 and a low vm.swappiness

• TCP keepalives

  Set tcp_keepalives_idle, tcp_keepalives_interval and tcp_keepalives_count where connections cross a NAT or a load balancer that drops idle connections.

Checking it works

Ask the broker what it found.

curl -s http://localhost:1056/ngsi-ld/v1/version

The answer lists the postgresql, postgis and timescaledb components with their versions, the TimescaleDB license, and the connections object described above. "verdict": "sufficient" means the pools fit.

Then check, in PostgreSQL, the defaults the broker set on its databases.

SELECT d.datname, s.setconfig
FROM pg_db_role_setting s
JOIN pg_database d ON d.oid = s.setdatabase
ORDER BY d.datname;

With the default configuration, every database the broker serves lists synchronous_commit=on, statement_timeout=35000ms, client_connection_check_interval=1000ms and timescaledb.enable_chunk_skipping=on. A database missing from the list hasn't been opened by a broker yet.

What to watch

• database_connection_capacity_exceeded at warn, at startup

  The broker's pools, all full, need more connections than the server allows it. Raise max_connections or shrink the pools.

• extension_update_refused at warn

  The broker's role can't update TimescaleDB or PostGIS. Update them as the owner, or the tenant may be refused once the version floor is raised.

• extension_install_refused at warn

  The broker's role was refused when it tried to create pgstattuple or pg_repack. Create pgstattuple yourself if you want maintenance to measure B-tree and GiST index bloat and the free space in tables, and pg_repack if you want it to repack tables. Everything else maintenance does runs without them.

• extension_recreate_deferred or extension_recreate_refused at warn

  pg_repack in a tenant database is behind the version the server ships, and the broker couldn't drop and recreate it. The first means a repack's objects are still in the database, and it clears once the repack or its recovery finishes. The second means the broker's role isn't a superuser, so a superuser has to recreate the extension.

• tenant schema contract: in the log, and 500 for every request to a tenant

  The server or the database doesn't meet a requirement above, and the line says which. Upgrades covers moving to a newer server.

If it goes wrong

• Time-bounded temporal queries miss history written outside the broker

  The session that wrote or converted it had timescaledb.enable_chunk_skipping off. Keep the database default on for every session.

• Requests fail under load with PostgreSQL's too_many_connections, and verdict is exceeded

  The server runs out of connections before the pools are full. Raise max_connections, lower the pool sizes, or put PgBouncer in front.

• A migration keeps failing with SQLSTATE 55P03

  A long transaction holds a lock the migration needs. End that transaction, then restart the broker.