AFAIK txid_current returns uint 64 value (source).
However, the biggest int column type is bigint which is signed (docs).
From what I understand, NUMERIC type is slow and should be used "for storing monetary amounts and other quantities where exactness is required" (docs).

Should I store the txid_current value as text?

I need to perform mainly greater than queries on this field:

select * from t where fxid > 438298432

What would be the most efficient (from search speed POV) for full-transaction-id (unsigned integer 64)?

  • 1
    You could use a bigint and store it with an offset. If you transform your search parameters it should be possible to utilize indexes. Just a thought – Lennart Jun 20 at 15:39
  • offset = save the first 32 bits in one column and the other 32 bits in another column? – Michael Jun 20 at 17:48
  • 1
    No, I was thinking in terms of storing 0 as - (2^32) or whatever number that may be. Searching for a specific transaktion would be: SELECT... FROM T WHERE txid =? - n, where n is the offset – Lennart Jun 20 at 17:54
  • Oh I see, nice idea! – Michael Jun 20 at 17:59

I'm suspicious about your motivations, but I'd say you should either use bigint (which is the data type xid_current returns) or use xid (which is the internal data type for xid's). By way of example, see the following:

pagila=# create table xidvals as select * from txid_current();
pagila=# \d xidvals
                 Table "public.xidvals"
    Column    |  Type  | Collation | Nullable | Default
 txid_current | bigint |           |          |

pagila=# select xmin,xmax,txid_current from xidvals;
  xmin  | xmax | txid_current
 358165 |    0 |       358165
(1 row)

pagila=# select attname, atttypid::regtype from pg_attribute where attrelid = 'xidvals'::regclass and attname in ('xmax','xmin','txid_current');
   attname    | atttypid
 xmax         | xid
 xmin         | xid
 txid_current | bigint
(3 rows)
| improve this answer | |

On pg v10, The txid value is limted to 0x7FFFFFFFFFFFFFFF (9223372036854775807) which is fit in bigint.
source code (v10)

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.