5 ms·

PostgreSQL – Don't Do This

75 points by lobo_tuerto 3y ago

revskill 3y ago
No, i suggest reverse these kinds of advice and DO THIS instead. Why ? Because IF it's said not to do this, it should not be implemented in first place. This is a trap. For example, "Don't use table inheritance", you should always use this to reuse the table definition. If not, what's the alternative ?
- laerus 3y ago
  The alternative is to just copy paste the table definition or do composition with foreign keys. Seems like field inheritance was invented only because we are too lazy, I have not seen yet valid cases for inheritance in Postgres or any OOP language.
  - mattashii 3y ago
    Until the implementation of declarative partitioning, inheritance was the only way to do table partitioning. Although the partitioning use case has mostly been replaced, that doesn't mean it can just be removed as there are existing systems using inheritance to manage their data.
- remram 3y ago
  > it should not be implemented in first place Some of it exists for important reasons, that are documented on the page. For example, respecting the SQL standard, or maintaining compatibility with database from a time/system without the replacement feature, or for special circumstances that don't apply to you.
thermin 3y ago
I'm baffled by the insistence that "users" (backend services, really) should avoid timestamp in favor of timestampz. I literally DO NOT understand, how is this good advice. My backend service exists in some abstraction of a Linux environment, with a fixed timezone, most commonly UTC. It maintains a pool of connections to Postgres, all sharing the same Postgres user, and the same timezone. Why on earth would I prefer a timestampz to timestamp? Why would I even involve the server timezone into this equation? I want to store timestamps in a uniform way, I store them in UTC as timestamps. If I stored them in a timestampz column, if the server timezone were to adjust, I'd get literally different values. If I want to store timezone values per user (of my service), or per operation (that my users perform), then again, surely I have to handle that users move and change their timezones. Again, how does timestampz with its reliance on the connection timezone serve me?
- deleted 3y ago
  [deleted]
- Arch485 3y ago
  This is actually incorrect, as per Postgres docs `timestamptz` is always stored as a UTC timestamp. The benefit to this vs a regular timestamp is that when you insert/update a timestamp value, Postgres can then convert that timestamp to UTC if necessary before storing it, and if you select a timestamp value Postgres can convert it from UTC to the timezone you want. If your connection is set to use UTC, and you always handle UTC timestamps, there probably isn't much practical difference between `timestamp` and `timestamptz`, however.
  - PaoloBarbolini 3y ago
    Also if you're worried about the server having a different timestamp and possibly not having configured the connection timestamp correctly, as the following test shows Postgres always specifies the timezone of the timestamp it's giving you server=# CREATE TABLE test1 (date TIMESTAMPTZ NOT NULL); CREATE TABLE server=# INSERT INTO test1 VALUES (NOW()); INSERT 0 1 server=# SELECT * FROM test1; date ------------------------------ 2023-06-01 08:00:30.40968+02 (1 row) server=# SET timezone = 'UTC'; server=# SELECT * FROM test1; date ------------------------------ 2023-06-01 06:00:30.40968+00 (1 row)
  - foverzar 3y ago
    > If your connection is set to use UTC, and you always handle UTC timestamps Aren't timestamps always supposed to be utc?
    - remram 3y ago
      I understood timestamps to be "seconds since 0:00 UTC January 1 1970", so absolute points in time that are not timezone-dependent. Wikipedia agrees with us too. Of course there is no official definition, and the term has evolved from an older concept (actual rubber stamps) so it's not surprising that others use it differently...
Arch485 3y ago
Another "valid" use of `timestamp` as opposed to `timestamptz` is for local times that don't all share the same time zone. For example, if a user can create an event scheduled in their timezone, you probably would want to use a `timestamp` and not `timestamptz`. This way, if the user schedules the event for e.g. 5:00pm, and then the time zone changes (think DST or similar), the event is still at 5:00pm (it won't get shifted like a `timestamptz` would).
- xprn 3y ago
  The article, although doesn’t mention the shift caused by TZ, does mention that the main difference between the two are that they’re a “point in time” vs “a photo of a calendar and clock”. A (local) event’s time does sound like a photo rather than a point
  - nirimda 3y ago
    I think the time of a local event sounds much more important, and a photo of a calendar and a clock sounds much more frivolous. Also, you can't take photos of events that haven't happened yet, and it's precisely with events that haven't happened yet that you most need to store a timestamp and a timezone rather than a postgres "timestamp with timezone" (which does not involve a timezone at all). So the analogy doesn't really tell you how to use it well.
    - mrrubberburner 3y ago
      Future events are trivially handled by timestamptz. PG takes your future timestamp and your client connections timezone converts to a utc constant and likewise casts the utc time to your timezone on reads.
- mrrubberburner 3y ago
  If a user doesn't want to follow dst then they can say so and follow standard time by configuring their client connection. I’m struggling to think of a case where I would want an alarm clock setting or a dinner reservation to not be adjusted for the locally observed time.
pmontra 3y ago
My favorite section is https://wiki.postgresql.org/wiki/Don%27t_Do_This#Text_storage https://wiki.postgresql.org/wiki/Don%27t_Do_This#Text_storag... that is using text instead of char and varchar. Of course the default of the PostgreSQL driver for several ORMs of several languages is varchar(255).
- ants_a 3y ago
  As a counterpoint: https://brandur.org/text https://brandur.org/text

75 points by lobo_tuerto 3y ago

No, i suggest reverse these kinds of advice and DO THIS instead. Why ? Because IF it's said not to do this, it should not be implemented in first place. This is a trap. For example, "Don't use table inheritance", you should always use this to reuse the table definition. If not, what's the alternative ?

laerus 3y ago
The alternative is to just copy paste the table definition or do composition with foreign keys. Seems like field inheritance was invented only because we are too lazy, I have not seen yet valid cases for inheritance in Postgres or any OOP language.
- mattashii 3y ago
  Until the implementation of declarative partitioning, inheritance was the only way to do table partitioning. Although the partitioning use case has mostly been replaced, that doesn't mean it can just be removed as there are existing systems using inheritance to manage their data.
remram 3y ago
> it should not be implemented in first place Some of it exists for important reasons, that are documented on the page. For example, respecting the SQL standard, or maintaining compatibility with database from a time/system without the replacement feature, or for special circumstances that don't apply to you.

thermin 3y ago

I'm baffled by the insistence that "users" (backend services, really) should avoid timestamp in favor of timestampz. I literally DO NOT understand, how is this good advice. My backend service exists in some abstraction of a Linux environment, with a fixed timezone, most commonly UTC. It maintains a pool of connections to Postgres, all sharing the same Postgres user, and the same timezone. Why on earth would I prefer a timestampz to timestamp? Why would I even involve the server timezone into this equation? I want to store timestamps in a uniform way, I store them in UTC as timestamps. If I stored them in a timestampz column, if the server timezone were to adjust, I'd get literally different values. If I want to store timezone values per user (of my service), or per operation (that my users perform), then again, surely I have to handle that users move and change their timezones. Again, how does timestampz with its reliance on the connection timezone serve me?

deleted 3y ago
[deleted]
Arch485 3y ago
This is actually incorrect, as per Postgres docs `timestamptz` is always stored as a UTC timestamp. The benefit to this vs a regular timestamp is that when you insert/update a timestamp value, Postgres can then convert that timestamp to UTC if necessary before storing it, and if you select a timestamp value Postgres can convert it from UTC to the timezone you want. If your connection is set to use UTC, and you always handle UTC timestamps, there probably isn't much practical difference between `timestamp` and `timestamptz`, however.
- PaoloBarbolini 3y ago
  Also if you're worried about the server having a different timestamp and possibly not having configured the connection timestamp correctly, as the following test shows Postgres always specifies the timezone of the timestamp it's giving you server=# CREATE TABLE test1 (date TIMESTAMPTZ NOT NULL); CREATE TABLE server=# INSERT INTO test1 VALUES (NOW()); INSERT 0 1 server=# SELECT * FROM test1; date ------------------------------ 2023-06-01 08:00:30.40968+02 (1 row) server=# SET timezone = 'UTC'; server=# SELECT * FROM test1; date ------------------------------ 2023-06-01 06:00:30.40968+00 (1 row)
- foverzar 3y ago
  > If your connection is set to use UTC, and you always handle UTC timestamps Aren't timestamps always supposed to be utc?
  - remram 3y ago
    I understood timestamps to be "seconds since 0:00 UTC January 1 1970", so absolute points in time that are not timezone-dependent. Wikipedia agrees with us too. Of course there is no official definition, and the term has evolved from an older concept (actual rubber stamps) so it's not surprising that others use it differently...

Arch485 3y ago

Another "valid" use of `timestamp` as opposed to `timestamptz` is for local times that don't all share the same time zone. For example, if a user can create an event scheduled in their timezone, you probably would want to use a `timestamp` and not `timestamptz`. This way, if the user schedules the event for e.g. 5:00pm, and then the time zone changes (think DST or similar), the event is still at 5:00pm (it won't get shifted like a `timestamptz` would).

xprn 3y ago
The article, although doesn’t mention the shift caused by TZ, does mention that the main difference between the two are that they’re a “point in time” vs “a photo of a calendar and clock”. A (local) event’s time does sound like a photo rather than a point
- nirimda 3y ago
  I think the time of a local event sounds much more important, and a photo of a calendar and a clock sounds much more frivolous. Also, you can't take photos of events that haven't happened yet, and it's precisely with events that haven't happened yet that you most need to store a timestamp and a timezone rather than a postgres "timestamp with timezone" (which does not involve a timezone at all). So the analogy doesn't really tell you how to use it well.
  - mrrubberburner 3y ago
    Future events are trivially handled by timestamptz. PG takes your future timestamp and your client connections timezone converts to a utc constant and likewise casts the utc time to your timezone on reads.
mrrubberburner 3y ago
If a user doesn't want to follow dst then they can say so and follow standard time by configuring their client connection. I’m struggling to think of a case where I would want an alarm clock setting or a dinner reservation to not be adjusted for the locally observed time.

pmontra 3y ago

My favorite section is https://wiki.postgresql.org/wiki/Don%27t_Do_This#Text_storage https://wiki.postgresql.org/wiki/Don%27t_Do_This#Text_storag... that is using text instead of char and varchar. Of course the default of the PostgreSQL driver for several ORMs of several languages is varchar(255).

ants_a 3y ago
As a counterpoint: https://brandur.org/text https://brandur.org/text