
Common mistakes in PostgreSQL - kawera
https://wiki.postgresql.org/wiki/Don%27t_Do_This
======
polote
> varchar (without the (n)) or text are similar, but without the length limit.
> If you insert the same string into the three field types they will take up
> exactly the same amount of space, and you won't be able to measure any
> difference in performance.

Seriously ? everytime I create a django charfield I try to guess in my head
what is the best length for this field to optimize for performance, but sounds
like using unlimited length (textfield) field doesn't make any difference.
Good news

~~~
SigmundA
Does Postgresql have index size limits? I know in SQL server one thing that
can bite you using varchar(max) is that if later you want to index it, it can
fail if anything is over 900 bytes as that is the index size limit.

Its a run-time error too so the index may get created no problem then later
someone tries to insert a >900 bytes value and it throws.

~~~
tracker1
IIRC you can still set the index, it's just that you can't do matching if the
match/content is over the length and it can still do begins with matching.

That said, I still don't get why they didn't just change the behavior for
TEXT/NTEXT vs adding (MAX) options.

edit: I am mistaken, when setting an index, the index max is now the insert
limit (from response below).

~~~
SigmundA
If your referring to SQL Server you get on insert "Operation failed. The index
entry of length 901 bytes for the index 'xyz' exceeds the maximum length of
900 bytes."

~~~
tracker1
Thanks. It's been a long while since I've dealt with the situation.

------
jchampem
> If what you really need is a text field with an length limit then varchar(n)
> is great, but if you pick an arbitrary length and choose varchar(20) for a
> surname field you're risking production errors in the future when Hubert
> Blaine Wolfe­schlegel­stein­hausen­berger­dorff signs up for your service.

:D

[[https://en.wikipedia.org/wiki/Hubert_Blaine_Wolfeschlegelste...](https://en.wikipedia.org/wiki/Hubert_Blaine_Wolfeschlegelsteinhausenbergerdorff_Sr.)]

~~~
JudgeWapner
_UI devs HATE him!_

~~~
TremendousJudge
A man named Null:
[https://www.wired.com/2015/11/null/](https://www.wired.com/2015/11/null/)

------
typenil
Funny how an article popped up on HN last week that mentioned table
inheritance in a list of obscure Postgres features you should try out.

If I'd read this first, I wouldn't have wasted Saturday finding out for myself
why you shouldn't use it.

~~~
perlgeek
... or if you had read the HN comments on that article:
[https://news.ycombinator.com/item?id=19768864](https://news.ycombinator.com/item?id=19768864)
:-)

~~~
zeroimpl
If they supported foreign keys, I’m pretty sure I’d have used it many times.
It seems like a good way to efficiently represent multiple distinct types
which have some shared columns and some non-shared columns.

------
codegeek
"Don't use CURRENT_TIME. Never"

Honest question. If we should never use it, why have it in the first place as
an option ? Is it some type of backward compatibility issue ? This list is
great btw. Why we shouldn't do something is a great way to learn stuff.

~~~
dgellow
I have the same question for things such as

> Don't use psql -W or psql --password

If I do a psql --help I see

    
    
      Connection options:
        -W, --password  force password prompt (should happen automatically)
    
    

Nothing tells me I shouldn’t use it.

~~~
LeonM
The documentation even states:

> This option is never essential, since psql will automatically prompt for a
> password if the server demands password authentication. However, psql will
> waste a connection attempt finding out that the server wants a password. In
> some cases it is worth typing -W to avoid the extra connection attempt.

So this is definitely confusing.

What they probably mean is that you shouldn't do something like
--password=mypassword, which will work but is obviously dangerous to do.

~~~
ambentzen
Don't do that either, but -W will always ask for a password even when one is
not required. Got a keylogger or someone watching over your shoulder, and they
now have your password even if the server is not actually asking for it. All
that insecurity just to, potentially, save a roundtrip to the server.

~~~
yellowapple
Why would I ever _not_ want the server to require a password, though? Like, if
your server ain't asking for a password at all, then why even bother with the
keylogger?

~~~
anarazel
Peer authentication for local postgres servers / poolers (authenticate using
the OS users, doesn't work remotely, and yes, it's safe), kerberos / [gs]sspi
authentication, client certificates.

~~~
yellowapple
Ah, right, forgot about those options.

Still, if you know that you're connecting to a server that's setup with
password-based auth, then what would be the point of waiting? You'll
inevitably have to provide a password as some point anyway, so the keylogger
argument seems silly.

And if someone has the ability to install a keylogger on a machine that uses
peer or certificate auth, then that someone almost certainly already has the
ability to just connect directly using that same peer or certificate auth.

------
IgorPartola
Money is always funny to represent. I did a bunch of research on this for my
budgeting app. Turns out that while most countries use two decimal points for
their cent/cent equivalent, some use 0, 1, or 3. In addition, at least one
country (Sweden I think), doesn't allow values like 0.01. Their smallest
denomination is 0.05, and everything is a multiple of it. I ended up storing
the data as a decimal type with 3 digits after the decimal, along with the
currency. Then I periodically extract the locale data from a Linux timezones
package and the app has a giant lookup table for how to represent and parse
each currency + value combo. This changes from time to time as currencies get
updated and governments change.

~~~
rb808
> In addition, at least one country (Sweden I think), doesn't allow values
> like 0.01.

There are a few countries where they no longer bother with small coins to
represent .01, so cash transactions are rounded to nearest .05 or similar, but
the .01 is still valid for electronic transactions. I expect that is the
situation in Sweden. (I wish US did this too)

~~~
kentosi
And Australia. We got rid of 1c and 2c coins back in the 90s.

~~~
axman6
But electronic transactions are still processed to the cent.

------
dagss
I don't get the rules on "timestamptz".

Normally I would want to enforce the use of UTC for points in time (as opposed
to user entry) across backend code and APIs. If the column name is
"eventtime"... then it is (of course) UTC...

I guess one could use timestamptz with a constraint saying that timezone is
UTC, but is there a point? Timestamps in DBs should never be non-UTC...

~~~
eurg
about "never not UTC" (but otherwise unrelated to the OP):

There was an article recently, describing a very important, and also very
common, "exception":

If you want to schedule/synchronize real-world appointments/events, you need
both the _local time_ and the political zone, like 2018-12-11 9am at
Europe/London.

The reason is that between time of entry and time of event, the defined
timezone for that area may change. As a result, your previously translated
appointment would not match up the local time.

Which is bad, because humans, when meeting, or events, when being announced,
announce the local time. Not UTC. And they stick to the local time.

~~~
pmontra
I remember that post. It's not a remote chance. Every EU country will have to
choose whether to stay forever in DST or forever in standard time, probably in
2021. Some of them will likely change time zone as a result.

------
mark_l_watson
Great resource! I have used PostgeSQL since, well forever. And I didn’t know
the advice on text storage. Shame on me, but now I know.

~~~
cmwright
Agreed! The ORM I'm currently using has been setting text for all my `string`
columns by default and I was feeling the need to go fix a few, so this is one
of those magical times when procrastinating on an issue paid off in my favour.

On that note though, I wonder what impact (if any) this has on an index
covering the column? I could be off here but from my mysql days I was under
the impression the index would be more efficient if there was a length limit
on the column.

------
superdeeda
I feel like every programming-related application, library, etc. should have a
page like this.

~~~
vbezhenar
Better approach would be using warnings.

~~~
givehimagun
Agree with you AND the op. I think it's really nice to receive warnings but we
also need a corresponding - don't do this, but do this instead. Developers
want to follow best practices...we just need to make it easier across the
board to figure out what the better way to do that thing is. I like Airbnb's
linting guide which shows don't and do examples for each rule.

Warnings (necessary and useful for quicker feedback loops) only tell you not
do something.

------
claytonjy
I'm surprised there's nothing in there about JSON; their page on json/jsonb
[0] seems pretty clear that json has been all-but-superceded by jsonb.

When would json be preferable to jsonb? I can imagine cases where write-speed
is essential and fancier queries and indices aren't needed, but that seems
rare.

[0] [https://www.postgresql.org/docs/current/datatype-
json.html](https://www.postgresql.org/docs/current/datatype-json.html)

~~~
skrebbel
We recently migrated a large jsonb column to json because, iirc, both the
write and the read speeds were much better when there was no (de)serialization
involved.

My current understanding is that json is faster and bigger than jsonb.

~~~
malisper
> My current understanding is that json is faster and bigger than jsonb.

FYI, it's the other way around. When you use the json type, Postgres validates
it and stores the raw string. Operations on json are very slow. Whenever you
extract a field from a json column, Postgres has to parse the full json.

On the other hand, jsonb is stored in a custom binary format. This makes
operations a lot faster as Postgres does not have to parse the full json.
There's also some other advantages such as jsonb stores the keys in sorted
order making it possible to do a binary search to find a specific key in a
lookup.

There are two primary downsides of jsonb. First, it doesn't preserve
whitespace. This can be a problem for some use cases. Second, since there is a
lot of metadata in jsonb, jsonb is larger. Postgres stores information like
the offsets of every key and value.

~~~
skrebbel
Good point. We don't extract individual fields or query on them. We just
schlep the data in and out, and for that, json appears to be faster.

~~~
marton78
In that case, couldn't you just use text?

------
lucb1e
So what would be wrong with the following announcement:

"In three years, varchar no longer takes an argument, char and money do no
longer exist, psql's password flag will be removed, all tables will be
lowercased, BETWEEN will require fully qualified values, table inheritance
will be disabled unless you specify a config value, and this page will be
shortened to only include NOT IN and SQL_ASCII. In one year, a lot of these
things will raise warnings. In two years, it will raise suppressible errors."

(I left out a few for brevity, but only a few -- this would actually resolve
most of this page.)

Most of this strikes me as common things (varchar and psql --password in
particular), and aside from NOT IN there appear to be easy solutions to make
people stop using it without big changes. It's not like python3 where you have
to change a core thing like text handling, it's removing a flag from your
cronjobs (--password), omitting a value (varchar(n)), or telling the database
what you really meant (BETWEEN X AND '2019-01-01' ->
'2019-01-01T23:59:59.999'). For stuff without obvious fixes, a "do not do
this" page is much more reasonable.

~~~
perlgeek
I'm sure that there are far too many use cases of varchar(255) out there for
anybody to deprecate such a feature without causing huge pain to everybody
involved.

Just look at the python 2 to python 3 transition, which is still ongoing, more
than 10 years after the python 3.0 release.

Also, warnings usually simply don't work. Developers or admins google the
warning, see that it can be switched off, and switch it off. DDL is often
generated (for example for migrations), and you might not want to add a
special case for postgres if you can avoid it. Suppressing a warning is
_definitively_ easier than changing code generation in potentially non-
compatible ways.

Plus, I haven't looked it up, but given how prevalent VARCHAR(N) is, it might
be part of the SQL standard.

------
treyfitty
Is it safe to assume most/all of these best practices are advisable across all
flavors of SQL? I can imagine it getting overwhelming when interviewing for
jobs that nitpick specific code using specific implementations of SQL

~~~
combatentropy
No, many of these are specific to Postgres. I don't know of any other database
that has Postgres's rewrite system called Rules. Also other databases handle
mixed-case identifiers differently. PostgreSQL is one of the databases that
adheres the SQL standard most closely, which makes you think that many of
these things would apply to other databases. But that's just it. Databases
follow the standard willy nilly, so that may be a reason that in fact Postgres
is different from them.

And yet, yes! The best practice, obviously, is to always use Postgres. So ---
what other flavors of SQL?

More seriously, I have read that SQLite is often similar to Postgres. Its main
author actually has said in a talk that when making decisions he asks, "What
would Postgres do?" Also I have heard that Postgres is similar to Oracle, so
much that you could move from Oracle to Postgres with a thin layer of
compatibility. In fact I think that's what
[https://www.enterprisedb.com/](https://www.enterprisedb.com/) is.

~~~
paulryanrogers
> PostgreSQL is one of the databases that adheres the SQL standard most
> closely...

Minor nit, Pg doesn't have SQL standard stored procedures; a.k.a. SQL/PSM.
Mysql and DB2 do however.

~~~
cobythedog
The current version (11) does: [https://www.postgresql.org/docs/11/sql-
createprocedure.html](https://www.postgresql.org/docs/11/sql-
createprocedure.html)

~~~
paulryanrogers
That is the command to create one, but the syntax of the definition body is
what does not comply with SQL/PSM.

Looks like there is an extension but it hasn't been updated in 8 years as
doesn't appear to be production ready yet[0]

[0] [https://github.com/okbob/plpsm0](https://github.com/okbob/plpsm0)

------
losvedir
Despite the recommendation timestamptz is not actually good for timezones. I
think it converts to UTC but doesn't actually store the time zone!!!

That's fine for, eg, recording a timestamp, but doesn't work for a scheduled
event. What if you want to be able to create a new event for "same time next
week"? Impossible without having the time zone.

I had an app where it mattered so we stored the datetime with a timestamp (as
UTC) and had a separate column for timezone.

I don't really see the point of timestamptz. In most cases when you're just
storing a moment in time, convert it on the frontend and store it as UTC. If
the time zone actually matters (scheduled, future events) then timestamptz
doesn't cut it, and you need to store the timezone separately anyway, so again
might as well just use UTC timestamp again.

Eh, I guess if your programming language doesn't have good time zone support
you could lean on postgres for the conversion to UTC, but in most apps you'll
have to deal with time and zones on the frontend anyway.

~~~
banku_brougham
I disagree. Perhaps I don’t understand your complaint fully.

Timestamptz column values are the UTC timestamp, but the returned time zone
aware result will depend on your db settings, or even local time of your
client. Which i think is what everyone would want.

Storing an extra column time zone with a timestamp is just an inconvenient way
to implement timestamptz.

------
elchief
re table inheritance, don't confuse SQL Table Inheritance with PostgreSQL
Table Inheritance. They are different things. SQL Table Inheritance is a good
thing, and should be used when needed, such as for the Party Model

[https://stackoverflow.com/questions/716549/what-are-the-
prin...](https://stackoverflow.com/questions/716549/what-are-the-principles-
behind-and-benefits-of-the-party-model)

For money amounts, use NUMERIC(19,4)

~~~
drdaeman
> For money amounts, use NUMERIC(19,4)

Note, if you'll ever encounter yourself dealing with cryptocurrencies you
would probably found yourself wanting to have at least 18 decimal places (BTC
and lots of coins have 8, and ETH has 18).

------
probably_wrong
From the title, I thought they would list the one mistake I've seen junior
sysadmins make: not changing the default values for resource allocation [1] in
a fresh install.

Good to know that I don't need to calculate the size of my varchars, though.

[1]
[https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Serv...](https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server)

------
moring
I actually disagree with "Don't use timestamp (without time zone)". The Java
class library (and before it, Joda time) distinguishes "instants" and "locals"
which is pretty much the same as PostgreSQL's "with time zone" and "without
time zone". They are just different data types with different use cases, and
saying that one is preferred is misleading.

I like to compare them to bytes and characters. You would use bytes if you
need bytes, and you would use characters if you need characters. They are
unrelated data types unless a character encoding is specified. Likewise,
instants and locals are unrelated data types unless a time zone is specified.

BTW, the manual page links to [https://it.toolbox.com/blogs/josh-berkus/zone-
of-misundersta...](https://it.toolbox.com/blogs/josh-berkus/zone-of-
misunderstanding-092811) which explains that "with time zone" doesn't store a
time zone.

~~~
dullgiulio
I cannot but disagree: there is no such a thing as time without a timezone. It
is just an undefined time. The "local" timezone could be anything and could
change any time. Not recording it is just data loss.

Java Instant is a timestamp in UTC, but it's all too easy to make mistakes in
serialization as the timezone information is just not present. I have spent a
few hours fixing exactly that yesterday in my day job.

The page you link says that with timezone makes sure to convert to and from
UTC, which is just a transparent optimizing implementation detail. If you
don't do timezone conversions at DB level, you have to make sure your
applications do, which is a recipe for disaster.

~~~
scatters
There absolutely is such a thing as a time without timezone. If I schedule an
event for a time in the future, I want it to occur when a wall clock in that
location reads the time that I specified, even if local government alters time
zone rules for that location in the interim.

If I set an alarm on my phone for 6am, I want it to go off at 6am in whatever
time zone I happen to be in, even if I travel after setting the alarm.

~~~
perlgeek
But you are tying it to a timezone: the timezone that will be valid at the
place and date that the meeting is at.

Basically, a timestamp should be tied to a timezone or a location, or at worst
an indirection (like you being at a specific location). And since we don't
tend to have practical support for tying timestamps to locations, we tie them
to timezones instead.

~~~
fphilipe
I've written a post about timestamp vs timestamptz and the only valid reason
for timestamp I could come up with is exactly this, scheduling events in the
future: [https://phili.pe/posts/timestamps-and-time-zones-in-
postgres...](https://phili.pe/posts/timestamps-and-time-zones-in-
postgresql/#a-valid-use-case-for-timestamp-without-time-zone)

------
nickjj
Using text with check constraints is a good one.

Setting up the constraints is a tiny bit more work, but it's worth it in the
end.

It's especially nice when your ORM or web framework knows to deal with all of
this at both the migration and validation levels. Then you can have both
database level protection and nice human readable errors in your app.

------
chadash
Some of the more surprising ones (to me):

1) Don't use timestamp (without time zone)

2) Don't use varchar(n) by default - just use _varchar_ or _text_ without any
length specified

3) Don't use money - The money data type isn't actually very good for storing
monetary values. Numeric, or (rarely) integer may be better.

~~~
chopin
The money one is a bit worrying, at least if "numerical" includes floating
point types. It's almost never a good idea to use these for monetary values.

If you are going to have different currencies I almost always would go for
integer types (I don't understand the "rarely" advice here) as the fraction
may vary greatly for different currencies. It's better to derive the
fractional from the currency.

~~~
colanderman
`numeric` is a decimal type with an optional fixed-precision mode, so it works
fine with decimal currencies.

------
markdog12
Anything similar for MySQL?

~~~
segmondy
Yes, don't use MySQL, use Postgres.

~~~
bagol
Why?

~~~
marton78
It's a well established fact that PostgreSQL is superior to MySQL in just
about every category that appears in any comparison. Sometimes MySQL is just
plain broken, e.g. that schema changes cannot be rolled back in a transaction.

------
dgellow
Any tool available to check a schema or a set of sql scripts for those common
mistakes?

------
jarym
Flashback to the time I encountered a project where they'd been storing text
in bytea columns 'for performance'.

To make matters worse, there were no indexes on anything other than on primary
keys.

------
zeroimpl
It looks like the only safe way to use the Rule system is if you replace the
query with exactly one new query. Basically what a view does.

They should really deprecate all other forms. I’ve wasted time reading up on
this feature several times, always to conclude there is a better solution to
my problem.

------
nexuist
Maybe I'm thinking of this wrong, but this is one of the strong suites of
NoSQL IMO. Especially when you're starting to build a new service, where
you're not sure what exactly your data structure is until the whole system
comes together, it's really easy to iterate and add new columns, change
existing ones, etc. As long as your code treats each item as its own entity
(rather than a row in a predefined table), you can continue to try new formats
until you eventually lock in something that does everything you need.

SQL is much less forgiving. Database migrations are terrifying, but the longer
you put it off the longer migrating will take (since new rows will continue to
accumulate).

Perhaps the solution is "plan out your data structures ahead of time", but
given that we're human, I think it's really difficult to do so correctly on
the first try. For example, you may have an int column to keep track of the
number of "Likes" an item on your service gets, but it may not be until later
that you realize you also need to keep track of who liked what in order to
prevent users from liking something twice. This is an elementary example, but
the point is that when you design tables, especially if you design them around
the frontend, you end up leaving out some critical columns that the backend
needs to implement its features. Whenever you end up discovering this, you
have to not only add that column to a new table but also retroactively insert
every existing row into the new table. At best this is O(n) but if you're
using a database there's a good chance your n will be quite larger.

You could argue that maybe in the startup stage your n will still be small,
since you won't have a billion users off the bat, but I would counter that you
usually don't realize what you need to change until someone else breaks
something (assuming you have tests for your code already) or requests
something new, at which point you likely have an established user pool.

~~~
sipos
Database migrations are not hard when you have hardly any data, when you are
still building something, and they aren't _that_ hard unless you have enormous
amounts. On the other hand, having data where records have different
attributes is annoying to have to handle everywhere at runtime, especially if
it is because of something you have changed, and no new records are missing
it. You just end up with code littered with handling of differences due to old
design.

This is, IMHO, not a good reason to use NoSQL.

------
mjirv
Anyone know which of these are true for Redshift as well?

------
mruts
It seems like there’s a lot of things you should never do. Why don’t they just
remove said things and make everyone’s life simpler?

------
banku_brougham
Redshit reads ‘text’ as varchar(256). Calling all AWS redshift devs, can we
get this on the roadmap?

------
banku_brougham
‘NOT IN’

I work at a large company, I’ve often seen SQL using this idiom, even in code
written on DE teams.

------
miguelmota
previously posted

[https://news.ycombinator.com/item?id=19662816](https://news.ycombinator.com/item?id=19662816)

[https://news.ycombinator.com/item?id=17485297](https://news.ycombinator.com/item?id=17485297)

------
skc
Man, I just started using pgsql and I fall foul of at least 3 of these.

~~~
lucb1e
I am going to give pgsql a try soon and I'm not surprised. The password flag
is something I would totally have set. CamelCasing tables is something I would
do. Between and table inheritance were recommended in a post last week, so I
would totally have tried these out too.

A lot of this stuff just needs to be deprecated (case-sensitive tables,
password flag...) or come with (bigger?) warning labels (between query, table
inheritance...).

------
Shorel
Damn, I wanted to try this table inheritance thing some day.

------
dekhn
am I the only person bothered by table names being converted to lowercase and
having to painfully quote the table names that are capitalized?

~~~
Seb-C
This is one of the main things I hate in PgSQL... Arbitrarily converting
identifiers in any way is a very bad practice in my opinion. I should be the
one to decide how I want to design my database and namings, not some arbitrary
rule. I especially want my database and my code to be consistent, and
snake_case is far from being the most used convention...

~~~
BenMorganIO
You sure? All the projects I've worked on have used snake case. I've only seen
newcomers to programming use capitalized table names.

~~~
dekhn
I'm working with data where the columns in another DB are already CamelCase
and I want to maintain consistency. I'm not a newcomer- I've been programming
for 30+ years.

------
daveheq
I like this DDT list... Don't Do This wikipage

------
jpeter
Anything similar for oracle?

------
mac_was
It is 2019 and this site is not mobile friendly!

------
choadrocker
#!. Using it

------
lisper
Title should be "A list of things not to do when using Postgres" or something
like that. "Don't do this" without elaboration is the very paragon of click-
bait.

~~~
arethuza
As the domain is displayed and is postgresql.org doesn't that rather hint at
what the content might be?

~~~
orev
Not when using a feed reader. Yes, RSS is still a thing.

~~~
saturn_vk
Doesn't your feed reader show the domain?

~~~
orev
No, not on a phone. Using Feedly, which is quite a popular reader after Google
Reader died. On phone you need to go into the comments before seeing the HN
page which has the link in it.

------
keymone
“Things we should have deprecated, but didn’t”

~~~
have_faith
As a novice in the backend world (I'm a front-end guy), it makes it odd to
read stuff like this when learning a technology for the first time. I know a
lot of the reasoning is "legacy" as to why things won't have been deprecated
but it does add to the confusion when trying to learn not just what do I need
to do to perform some task, but why am I doing it this way etc.

~~~
keymone
legacy code and backwards compatibility are dumb excuses to not cleanup the
codebase and make bad practices like those listed impossible.

there's always deprecation notices, deprecation flags and "not giving a shit
that somebody's 20 year old codebase breaks on today's version of $project".

~~~
zbentley
That attitude has killed countless software projects. Conversely, many
projects only exist because they value backwards compatibility above almost
all else.

"Bad practices" often don't start out as bad practices; circumstances change.
Additionally, some "bad" things are more like "dangerous if improperly
handled" things (e.g. Rules), so making them impossible cripples users.

Lastly, there exist some useful, maintained libraries/tools with a substantial
proportion (or majority) of "client" codebases at or greater than 20 years of
age. Just because something has been around for a long time is not an excuse
to break it. Just because something has not been maintained does not mean it
is necessarily poor engineering or deserves to suffer for the passage of time.

~~~
keymone
sorry, i don't buy it. it's not a matter of attitude, it's just common sense
and sanity. if i maintain a library X and i recognize that some behavior is
producing dangerous/harmful results - no sane person should ever complain
about me deprecating something i consider dangerous/harmful.

either don't upgrade or fork away, don't make others suffer for your
unwillingness to change.

the Rules example is mentioned often - i suggest not to take everything
written on the internet so literally, or is it really impossible to convey
sarcasm without /s?

a better example commenters could focus on is the type `money` - if best
advice from postgres about handling money is to _not_ use `money` type - it
shouldn't be part of postgres. how is that in any way controversial?

~~~
astine
" _sorry, i don 't buy it. it's not a matter of attitude, it's just common
sense and sanity._"

It's not 'common sense' or 'sanity' to break existing code bases. Companies
and individuals have generally spend millions on their software and breaking
changes cost time and money. There's a reason that Python 3, for example, took
over a decade to be fully adopted. Breaking changes should only be implemented
with considerable evaluation of the actual need and of existing options. If
users cannot rely on you to not break their systems, they will move on to
someone they can rely on.

" _either don 't upgrade or fork away, don't make others suffer for your
unwillingness to change._"

I manage a number of databases, mostly of a financial nature, and I can tell
you that if I were using a database system developed the way you suggest I'd
be in trouble. On the one hand, I have to keep my data secure so I have to
patch my systems on a regular basis so I can't use a version of a database
system that isn't being actively updated. On the other hand, I have to manage
hundreds of thousands of lines of mission critical code, most of which I
didn't write, and a large amount of data which absolutely, under no
circumstances, can be lost, if I want to keep my job. To that end, I can risk
breaking changes and usually wouldn't have the bandwidth to accommodate them
if I could. There's a reason that many big companies are still using
mainframes despite their being better options: rule number one as a system
administrator is to not break the existing system.

" _a better example commenters could focus on is the type `money` - if best
advice from postgres about handling money is to not use `money` type - it
shouldn 't be part of postgres. how is that in any way controversial?_"

Let's actually take this example seriously. Let's say that there is an off-
the-shelf accounting system that uses PostgreSQL and because of bad decisions
made in the past, uses the 'money' datatype internally. Presumably they've
worked around its limitations so it's no longer an issue for them. Let's say
that PostgreSQL decides to remove this datatype in the next release of the
database. Well, suddenly not only does that accounting system no longer work,
bu every company that uses the software is SOL. In addition, any integrations
and custom code that worked with that accounting system are now broken. This
is a loss of millions of dollars in software dev time at the very least.
There's also a possibility of dataloss, which when talking about accounting
systems is a matter of legal compliance. This is a much worse outcome than
people accidentally using 'money' in new development in future. Considering
that the later problem can be ameliorated with just a warning, that seems like
the more obvious trade-off.

~~~
keymone
> suddenly not only does that accounting system no longer work

sigh..

have i suggested anywhere to suddenly make releases with features removed?
does everybody in this thread have a mental blocker on the word "deprecation"
or something?

there are ways to remove features properly and gradually. deprecation
warnings, hiding the feature behind deprecation flag, moving the feature out
into a plugin, etc.

i honestly wasn't expecting to have to respond to such strawman comments.

~~~
astine
" _have i suggested anywhere to suddenly make releases with features removed?_
"

Yes you clearly did:

" _legacy code and backwards compatibility are dumb excuses to not cleanup the
codebase and make bad practices like those listed impossible._ "

" _...it shouldn 't be part of postgres..._"

" _" not giving a shit that somebody's 20 year old codebase breaks on today's
version of $project"._"

------
localhostdotdev
discussion on lobsters where I posted it recently:
[https://lobste.rs/s/m4f2ke/don_t_do_this](https://lobste.rs/s/m4f2ke/don_t_do_this)

------
dsfyu404ed
Clearly the guy who set up the website I'm refactoring didn't read this.

------
cbsmith
Oddly, no mention of "using MySql". ;-)

~~~
hnbroseph
what do you mean?

~~~
cbsmith
That it'd be a mistake to not use PostgreSQL.

------
idlewords
The biggest of these mistakes, in my experience, is mentioning in a public
forum that you use MySQL.

(Disclaimer: I picked between the two at random and only continue to use the
thing I picked because I now know it well and it is adequate for my needs)

------
manigandham
The timestamptz data type is horrible advice.

It is not actually a data type, it's just timezone but the database will
convert what you send to UTC. Any time a database decides to store something
different than what you provide will be an endless source of bugs. It also
doesn't store the timezone so you need a separate column for that anyway if
necessary.

Use normal `timestamp`. Always use UTC in your code for any "instant in time"
and a separate column for user-facing appointment scheduling.

~~~
CraftThatBlock
From what I understand, it's to prevent application level errors where you do
send it with a timezone, as you mentioned. This avoids the conversion (since
it is stored as UTC) at the application level

~~~
manigandham
It's changing what you send and you only get back what you expect if you are
explicit in your SQL statements or have the same timezone in the connection.

It's an extra risk that offers nothing. Just use UTC in your code, there's no
reason not to and is much better advice. If you don't use UTC, at least you
can still recover and convert easily if you are using the standard `timestamp`
type since it's exactly what you saved.

The `timestamptz` type is a mistake and one of the few things where PG gets
overly ambitious in helping, only to cause more pain that it's worth.

