[Bucardo-general] Design of new txntime field

[Greg Sabino Mullane]

> 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>