Support for unsigned int

[pjebs]

My company is thinking of using this product for a forecasting project.
We currently use MySQL. We are investigating timescale vs influx for this new project.

Given timescale uses postgres, we were thinking if we chose to use timescale, we could migrate our entire database from MySQL to timescale/postgres. It will be a factor in a decision making.

The problem is that we have many unsigned int 64 bit usage.

Will Timescale add unsigned int support for postgres or at least for hypertables?

[RobAtticus]

Would using an int type plus a constraint of >=0 be sufficient for your purposes? I am not sure we want to venture into creating a new type like that, even for just hypertables. Of course if it fills a need that can't otherwise be handled we'd certainly consider it.

[did-g]

Hi,

For the record there's already an unsupported extension for unsigned type:
https://github.com/petere/pguint.git

[pjebs]

@RobAtticus having a constraint still reduces the range. Our unsigned 64 bit numbers cover the full range.

@did-g We are aware of that package but because it's not backed by a "reputable" company, we are reluctant to consider it.

On a separate note, I would have thought from a business perspective covering unsigned X-bit ints is an important use case for a product that is used for collecting data. We were surprised that it wasn't already there.

[RobAtticus]

@pjebs Understood, but then I guess my question is whether that additional range is necessary for your use case? Or are you just making sure you have future proofing?

re: business perspective - it appears from PG discussions and the README on that repo that the benefit of adding uint is usually outweighed by the increased type system complexity and fragility. As far as I know, I don't think anyone else has asked for it. I am also not aware of non-theoretical use cases for it, but happy to hear otherwise.

[pjebs]

Well in our case, it's really just about migration from MySQL to Postgres. We have keys that are randomly generated uint64 similar to facebook's appscoped ids in their facebook SDK.

For us, uint64 provides a large range where we are confident of not having collisions and it's simply more efficient to store that string-based uuids.

I don't think it's theoretical given that in mysql, using unsigned ints are super-common and I'm pretty sure uint64 are not uncommon.

re: business perspective - it appears from PG discussions and the README on that repo that the benefit of adding uint is usually outweighed by the increased type system complexity and fragility. As far as I know, I don't think anyone else has asked for it. I am also not aware of non-theoretical use cases for it, but happy to hear otherwise.

Perhaps a nice compromise is implementing unsigned 64 bit but leaving all the other data types signed.

That way if someone wants a unsigned 16 bit, they can use a signed 32 bit with a check constraint.

[pjebs]

influxdata/influxdb#7801

I don't think it's theoretical because there is demand for it in influx. (and i think they have implemented it but not released it)

[RobAtticus]

Okay, will mark as an enhancement (cc @dianasaur323).

I think there are solutions for your current use that don't require uint64 -- e.g., using bytea to store the raw bytes, using uuid functionality in PG, convert back and forth from uint64 and int64, etc. But I do see some use cases I hadn't thought of in the influx thread.

[pjebs]

Well my use case had nothing to do with timescale with respect to actual timeseries data. It was purely about the ancillary benefits of using timescaledb (ie. migrating from mysql to postgres).

Maybe the ease of migrating from MySQL to Postgres itself is a massive selling point for some companies.

[RobAtticus]

Understood. Thanks for the references and potential use cases, definitely helps us figure out the priorities of these things!

[dianasaur323]

Great - thanks for the feedback. Added to our feature backlog. It sounds like the bigger question here is how to better support better MySQL migration, which we can certainly support when we get some more requests for it.

[pjebs]

@dianasaur323 I don't think that's the concern. That is just a factor in our decision making (and possibly a covert selling point for marketing purposes: using existing mysql (primary db) + moving to influx or timescale/postgres for certain projects vs just migrate everything to postgres and utilise timescale for certain projects).

The real issue is unsigned integer support (or at least unsigned 64 bit int support) which is directly relevant for your product and your chief competitor influx.

[dianasaur323]

@pjebs Understood, thanks for clarifying! We'll update you if there is more movement on the unsigned int support here. Thanks for sharing your ideas :)

[phemmer]

To provide more data on use cases:

I've been working on a plugin for telegraf to send metrics to TimescaleDB, and the lack of unsigned 64-bit integers is a sore point. Since telegraf can emit unsigned 64-bit integers, in order to support these the plugin has to create the column with "numeric" data type, which is less than ideal.

I have also seen real-world metrics that use the full uint64 range. Specifically on network equipment. So it's not just theoretical.

[phemmer]

So I attempted to use the pguint extension to obtain uint64 functionality. It does appear to work, but only in single node. If you try to use multi-node you get the error unsupported integer size 8. It appears to be an issue when the access node is serializing the data to send to the data node.

Edit: nevermind, that was a bug in the pguint extension. After fixing it works fine on multi-node.

[svenklemm]

Have you tried using https://github.com/petere/pguint? Non-partitioning columns of hypertables can be any datatype even those added by other extensions.