For those already familiar with this topic and wonder about the heading, hold on and continue reading. One of the exercises we do in our PostgreSQL DBA Essentials workshop is to create a tablespace. This is to demonstrate how tablespaces work in PostgreSQL and, especially to show the symbolic link that gets created in $PGDATA/pg_tblspc. In one of the last workshops, two attendees noticed that they got the same OID and asked if that is normal or just a coincidence. As I could not immediately answer to that question, I had to do some research and this is the topic of this post.

To start with, lets create a fresh cluster and start it up:

postgres@debian11pg:/home/postgres/ [pgdev] mkdir /var/tmp/dummy
postgres@debian11pg:/home/postgres/ [pgdev] cd /var/tmp/dummy/
postgres@debian11pg:/var/tmp/dummy/ [pgdev] initdb .
postgres@debian11pg:/var/tmp/dummy/ [pgdev] export PGPORT=7777
postgres@debian11pg:/var/tmp/dummy/ [pgdev] pg_ctl -D . start
postgres@debian11pg:/var/tmp/dummy/ [pgdev] psql
psql (15devel)
Type "help" for help.

postgres=# 

If we create a tablespace now, what OID will it get?

postgres=# ! mkdir /var/tmp/tbs
postgres=# create tablespace tbs1 location '/var/tmp/tbs';
CREATE TABLESPACE
postgres=# ! ls /var/tmp/dummy/pg_tblspc
16384
postgres=# select oid from pg_tablespace where spcname = 'tbs1';
  oid  
-------
 16384
(1 row)

Indeed, it gets the same OID as we have it in the workshop slides and this was true for all attendees of the workshop. Of course they followed all the same exercises but it anyway was somehow surprising, we never paid any attention to that. Dropping and re-creating the tablespace increases the OID by one:

postgres=# drop tablespace tbs1;
DROP TABLESPACE
postgres=# create tablespace tbs1 location '/var/tmp/tbs';
CREATE TABLESPACE
postgres=# select oid from pg_tablespace where spcname = 'tbs1';
  oid  
-------
 16385
(1 row)

This makes sense, new object new OID. To make it short, lets do another test: Re-create the cluster and create a table instead of a tablespace:

postgres=# q
postgres@debian11pg:/var/tmp/dummy/ [pgdev] pg_ctl -D . stop
postgres@debian11pg:/var/tmp/dummy/ [pgdev] rm -rf *
postgres@debian11pg:/var/tmp/dummy/ [pgdev] initdb .
postgres@debian11pg:/var/tmp/dummy/ [pgdev] pg_ctl -D . start
postgres@debian11pg:/var/tmp/dummy/ [pgdev] psql
psql (15devel)
Type "help" for help.

postgres=# create table t1 ( a int );
CREATE TABLE
postgres=# select oid from pg_class where relname = 't1';
  oid  
-------
 16384
(1 row)

Now the table gets this magic number assigned and if we create a tablespace afterwards it will be a higher OID:

postgres=# ! rm -rf /var/tmp/tbs/*
postgres=# create tablespace tbs1 location '/var/tmp/tbs';
CREATE TABLESPACE
postgres=# select oid from pg_tablespace where spcname = 'tbs1';
  oid  
-------
 16388
(1 row)

Grepping over the source code confirms this: The first object that gets created in normal operation gets 16384. You can find this comment in “src/include/access/transam.h”:

 *              OIDs beginning at 16384 are assigned from the OID generator
 *              during normal multiuser operation.  (We force the generator up to
 *              16384 as soon as we are in normal operation.)

So, by now we know that the first object that is created in normal operation gets OID 16384. The next one will get 16385 then, right? Again, lets start from scratch and create two tables: The first one will get 16384 and the second one probably 16385, correct?

postgres=# q
postgres@debian11pg:/var/tmp/dummy/ [pgdev] pg_ctl -D . stop
postgres@debian11pg:/var/tmp/dummy/ [pgdev] rm -rf *
postgres@debian11pg:/var/tmp/dummy/ [pgdev] initdb .
postgres@debian11pg:/var/tmp/dummy/ [pgdev] pg_ctl -D . start
postgres@debian11pg:/var/tmp/dummy/ [pgdev] psql
psql (15devel)
Type "help" for help.

postgres=# create table t1 ( a int );
CREATE TABLE
postgres=# create table t2 ( a int );
CREATE TABLE
postgres=# select oid,relname from pg_class where relname in ('t1','t2');
  oid  | relname 
-------+---------
 16384 | t1
 16387 | t2
(2 rows)

It is not as simple as I thought, we have a gap of two OIDs. Why is that, two OIDs seem to be reserved for something else. Creating a new tablespace afterwards also shows a gap of two:

postgres=# ! rm -rf /var/tmp/tbs/*
postgres=# create tablespace tbs location '/var/tmp/tbs/';
CREATE TABLESPACE
postgres=# select oid from pg_tablespace where spcname = 'tbs';
  oid  
-------
 16390
(1 row)

We did not see this gap when we created one tablespace and another one right after above. We can confirm this once more by creating another tablespace:

postgres=# ! mkdir /var/tmp/tbs2
postgres=# create tablespace tbs2 location '/var/tmp/tbs2/';
CREATE TABLESPACE
postgres=# select oid from pg_tablespace where spcname = 'tbs2';
  oid  
-------
 16391
(1 row)

No gap here, so the gap seems to be related to the object that gets created. A table seems to reserve two additional OIDs by default, a tablespace does not. What could be the reason for that? The two tables we’ve created currently have these assignments:

postgres=# select oid,relname from pg_class where relname in ('t1','t2');
  oid  | relname 
-------+---------
 16384 | t1
 16387 | t2
(2 rows)

As did not find anything related to this in the source code (I know it must be there) I had to ask the community. What really happens here is, that two types get created when you create a table, the table’s base type and one type that represents one row of the table:

postgres=# select oid, typname, typtype from pg_type where typname like '%t1' or typname like '%t2' order by oid;
  oid  | typname | typtype 
-------+---------+---------
    21 | int2    | b
  1005 | _int2   | b
 16385 | _t1     | b
 16386 | t1      | c
 16388 | _t2     | b
 16389 | t2      | c
(6 rows)

This is mentioned in the documentation and I should have read that before posting to the list 🙁