[Bucardo-general] Design of new txntime field

Greg Sabino Mullane greg at endpoint.com
Sun Nov 8 02:38:26 UTC 2015

> In our database, we have 2 big tables and one of them that has about 300
> million records has an index on a text field that has 16-20 characters.
> When I have to restore a backup of the database that index takes 10 times
> longer than any other index. I know the delta and track table won't get
> that big so it might not be a problem for this app.

Yeah, these indexes should never get too huge. A timestamptz takes up eight 
bytes already, so a textualied version shouldn't be *too* much worse.

> How about using 2 fields in the primary key. One the current txntime field
> and one a serial id field.

It's crossed my mind before, but that adds a good bit of complexity 
for too little gain.

> Why even bother to use a PID when the postgres sequence does the job so well?

Or more to the point, why use a timestamp at all when we could simply 
use a bigserial, full stop? I've toyed with this idea in the past. I think 
the big problem was the mental dififculty of giving up the nice 
human-readable way of viewing what was going on. But timestamps are not 
really needed - we just need a way to map between delta rows and 
track rows. So let's throw that idea into the mix too. Would certainly 
win the space and efficiency awards.

Greg Sabino Mullane greg at endpoint.com
End Point Corporation
PGP Key: 0x14964AC8
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 163 bytes
Desc: not available
URL: <https://mail.endcrypt.com/pipermail/bucardo-general/attachments/20151107/35959ae1/attachment.sig>

More information about the Bucardo-general mailing list