4 ms·

Postgres: The Batteries Included Database

6 points by plaur782 6y ago

gjvc 6y ago
I wish its timestamp type went to nanoseconds.
- grzm 6y ago
  I think the existing int64, microsecond-precision timestamptz type is a good compromise of space and performance for precision for many use cases. Indeed, there are use cases where greater precision is desired. One of the features of PostgreSQL I appreciate is the ability to extend its types. There are a number of type extensions available. See https://pgxn.org https://pgxn.org for a list of popular extensions. I don't know of an existing nanosecond-precision timestamptz type, but it's not that much of a lift to create one yourself. I've muddled through C a couple of times to do so. Often I pick an existing extension that's similar to what I want to do, and modify it. And if Rust is more to your liking, there's a toolkit for building extensions in Rust as well: https://github.com/zombodb/pgx https://github.com/zombodb/pgx A drawback of using custom extensions is you're often limited to then managing your own PostgreSQL installation as most PostgreSQL SaaS offerings don't support installation of third-party C extensions. If nanosecond precision is a core aspect of your work, then it may very well be worth the effort. Another option is to store nanoseconds in an additional integer column. If you want, you can create a composite type (https://www.postgresql.org/docs/13/rowtypes.html https://www.postgresql.org/docs/13/rowtypes.html) of timestamptz and integer, which might make things more manageable (or less, depending on your PostgreSQL driver support for composite types). This allows you to use stock PostgreSQL while you handle the data stitching in your application.
  - gjvc 6y ago
    I don't want to use an additional integer column. I don't want to use a custom extension. I don't want to have to extend the database platform where at all possible, and I sure as hello don't want to use Rust in doing so. Other than that, thank you very much.
    - grzm 6y ago
      If wishes were horses, beggars would ride. You've got options available to you. One I didn't mention (though likely unappealing to you given you don't want to use a custom extension) is to hire someone to write it for you. (I suspect if you reached out to Crunchy Data, the host of this submission, they could do the work, or put you in touch with someone who could.) There's also the possibility of successfully getting the extension included in the core distribution. Some current features started as extensions. That's a longer process of course. Choosing any of these options is more likely to actually solve your issue.

6 points by plaur782 6y ago

gjvc 6y ago

I wish its timestamp type went to nanoseconds.

grzm 6y ago
I think the existing int64, microsecond-precision timestamptz type is a good compromise of space and performance for precision for many use cases. Indeed, there are use cases where greater precision is desired. One of the features of PostgreSQL I appreciate is the ability to extend its types. There are a number of type extensions available. See https://pgxn.org https://pgxn.org for a list of popular extensions. I don't know of an existing nanosecond-precision timestamptz type, but it's not that much of a lift to create one yourself. I've muddled through C a couple of times to do so. Often I pick an existing extension that's similar to what I want to do, and modify it. And if Rust is more to your liking, there's a toolkit for building extensions in Rust as well: https://github.com/zombodb/pgx https://github.com/zombodb/pgx A drawback of using custom extensions is you're often limited to then managing your own PostgreSQL installation as most PostgreSQL SaaS offerings don't support installation of third-party C extensions. If nanosecond precision is a core aspect of your work, then it may very well be worth the effort. Another option is to store nanoseconds in an additional integer column. If you want, you can create a composite type (https://www.postgresql.org/docs/13/rowtypes.html https://www.postgresql.org/docs/13/rowtypes.html) of timestamptz and integer, which might make things more manageable (or less, depending on your PostgreSQL driver support for composite types). This allows you to use stock PostgreSQL while you handle the data stitching in your application.
- gjvc 6y ago
  I don't want to use an additional integer column. I don't want to use a custom extension. I don't want to have to extend the database platform where at all possible, and I sure as hello don't want to use Rust in doing so. Other than that, thank you very much.
  - grzm 6y ago
    If wishes were horses, beggars would ride. You've got options available to you. One I didn't mention (though likely unappealing to you given you don't want to use a custom extension) is to hire someone to write it for you. (I suspect if you reached out to Crunchy Data, the host of this submission, they could do the work, or put you in touch with someone who could.) There's also the possibility of successfully getting the extension included in the core distribution. Some current features started as extensions. That's a longer process of course. Choosing any of these options is more likely to actually solve your issue.