18 ms·
Common mistakes in PostgreSQL
- Shorel 7y agoDamn, I wanted to try this table inheritance thing some day.
- superdeeda 7y agoI feel like every programming-related application, library, etc. should have a page like this.
- vbezhenar 7y agoBetter approach would be using warnings.
- givehimagun 7y agoAgree 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.
- keymone 7y ago“Things we should have deprecated, but didn’t”
- have_faith 7y agoAs 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 7y agolegacy 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 7y agoThat 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 7y agosorry, 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?
- Retra 7y agoThis is controversial because people run businesses on 20-year old software, and they don't want to run their business into the ground every time some outside developer wants to clean up their code base. People's livelihood is at stake. They prefer software that remains solvent. Natural selection has produced a world where people value backwards compatibility. And you should always be able to upgrade the software you have without fear of breakage, because maintaining a fork and/or deciding which versions to use is often untenable as it relies on specialized knowledge which may not exist in your organization. If you as a software dev want to maintain a clean code base with no deprecated features, you are free to build one. That's not what this is, and you shouldn't expect anyone else to use it if it were.
- segmondy 7y agoNot true, Rule's are very powerful especially for refactoring tables with 0 downtime.
- irrational 7y ago"Things that should not be in the SQL Standard, but are." FTFY
- codegeek 7y ago"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.
- monsieurbanana 7y agoThey give the reason on the previous entry (about timetz): > Why not? > Even the manual tells you it's only implemented for SQL compliance.
- deleted 7y ago[deleted]
- ape4 7y ago
- mark_l_watson 7y agoGreat 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 7y agoAgreed! 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.
- treyfitty 7y agoIs 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
- kijin 7y agoThe performance characteristics of `char` vs. `varchar` vs. `text` certainly depends on the implementation details of each RDBMS. PostgreSQL recommends that you use `text` for basically every textual column, but other databases can be very different. Some won't even allow you to add an index to a `text` column. The various timestamp-related data types are also very PostgreSQL-specific.
- ellisv 7y agoNot necessarily. Different RDBMSs have different implementations so what isn’t bad in one might be comparatively bad in another.
- combatentropy 7y agoNo, 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 7y ago> 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.
- dsfyu404ed 7y agoClearly the guy who set up the website I'm refactoring didn't read this.
- lisper 7y agoTitle 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.
- dangerface 7y ago> Don't Do This (postgresql.org) I think the average person can figure it out.
- fwr 7y agoEach of these could be hosted on postgresql.org: > Don't Do This: Mistakes we've made in licensing > Don't Do This: A case for considering MongoDB > Don't Do This: Handbook for social behavior at events & conferences
- codetrotter 7y agoI suggest “Don’t do this — A list of common mistakes when using PostgreSQL”
- arethuza 7y agoAs the domain is displayed and is postgresql.org doesn't that rather hint at what the content might be?
- orev 7y agoNot when using a feed reader. Yes, RSS is still a thing.
- markdog12 7y agoAnything similar for MySQL?
- LeonM 7y agoDon't use --password for the CLI
- tyingq 7y agoDon't use UNIX_TIMESTAMP() or FROM_UNIXTIME(). Both overflow in 2038.
- perlgeek 7y agoI hoped you were kidding me, but no: $ perl -wE 'say scalar localtime 2177449200' Sat Jan 1 00:00:00 2039 > select FROM_UNIXTIME(2177449200); +---------------------------+ | FROM_UNIXTIME(2177449200) | +---------------------------+ | NULL | +---------------------------+ 1 row in set (0.000 sec) I work on a code base where the word UNIX_TIMESTAMP occurs 400 times :(
- segmondy 7y agoYes, don't use MySQL, use Postgres.
- BenMorganIO 7y agoWhen to Use MySQL? Never.
- skc 7y agoMan, I just started using pgsql and I fall foul of at least 3 of these.
- lucb1e 7y agoI 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...).
- polote 7y ago> 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
- Sharlin 7y agoWith Postgres, yes. Unless there's a real semantic need for a max length, you should always just use `text`.
- moring 7y agoI sometimes use a limit (constraint or varchar(n)) to enforce an upper bound when the domain doesn't really demand one, but you'd rather get an error that a longer string in your database. Say, 100 characters for a login name or 1000 characters for a real person's name. Even if somebody actually shows up with a longer name, you know that your application will have other problems in that case (e.g. the UI explodes or something), but you prevent the occasional bug or other problem from writing megabytes into the name field, and causing follow-up problems.
- fabian2k 7y agoEven then you should use text, just with a CHECK constraint. That's easier to change later than a varchar column.
- radiowave 7y agoYes. The most complicated migrations I've ever done have involved increasing the length of varchar(n) columns, because every dependent view needed to be dropped and recreated (and all of their dependents, and so on, recursively) within the migration transaction, in the right sequence. If I had just used the text datatype in the first place (along with a check constraint), these migrations would have been so much simpler.
- localhostdotdev 7y agodiscussion 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
- moring 7y agoI 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-misunderstanding-092811 https://it.toolbox.com/blogs/josh-berkus/zone-of-misundersta... which explains that "with time zone" doesn't store a time zone.
- dullgiulio 7y agoI 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.
- afiori 7y agoI am not saying it is the best way to handle things, but for cases where physical locations are well defined they make sense. I can imagine Walmart or Ikea saying "We open at 8 am worldwide".
- dullgiulio 7y agoYour example is a time without a date, which of course does not need a timezone. Any use of the word time above actually meant datetime.
- jchampem 7y ago> 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 Wolfeschlegelsteinhausenbergerdorff signs up for your service. :D [https://en.wikipedia.org/wiki/Hubert_Blaine_Wolfeschlegelsteinhausenbergerdorff_Sr. https://en.wikipedia.org/wiki/Hubert_Blaine_Wolfeschlegelste...]
- dtjohnnyb 7y ago(The period at the end gets cut off)[https://en.wikipedia.org/wiki/Hubert_Blaine_Wolfeschlegelsteinhausenbergerdorff_Sr. https://en.wikipedia.org/wiki/Hubert_Blaine_Wolfeschlegelste...]
- JudgeWapner 7y agoUI devs HATE him!
- TremendousJudge 7y agoA man named Null: https://www.wired.com/2015/11/null/ https://www.wired.com/2015/11/null/
- aargh_aargh 7y agoI'd have Hubert over Bobby Tables any day!
- mjirv 7y agoAnyone know which of these are true for Redshift as well?
- chadash 7y agoSome 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 7y agoThe 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 7y ago`numeric` is a decimal type with an optional fixed-precision mode, so it works fine with decimal currencies.
- masklinn 7y ago> 1) Don't use timestamp (without time zone) The problem with this one is both timestamp and timestamptz are problematic: * timestamp will implicitly discard input timezone information * timetamptz doesn't store a timezone, it converts everything to UTC and stores that; furthermore it assumes any data without timezone information is in the "timezone" system parameter and converts from that on input and to that on output Storing everything as UTC is problematic because it's completely unreliable for all future events e.g. it's plain broken for calendaring and reminders.
- typenil 7y agoFunny 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 7y ago... 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 7y agoIf 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.
- hawaiian 7y agoI find how it's dismissed as a fad pretty humorous and actually a bit insightful. >Table inheritance was a part of a fad wherein the database was closely coupled to object-oriented code. It turned out that coupling things that closely didn't actually produce the desired results.
- elsurudo 7y agoI recently used table inheritance to great effect on a project, so I wouldn't write it off completely. My use-case was a bit different as it involved zero-downtime data imports from separate sources (concurrently), but it proved a great tool for the job.
- dgellow 7y agoAny tool available to check a schema or a set of sql scripts for those common mistakes?
- dekhn 7y agoam 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 7y agoThis 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 7y agoYou sure? All the projects I've worked on have used snake case. I've only seen newcomers to programming use capitalized table names.
- zeroimpl 7y agoAgreed. I work on databases where column names are capitalized throughout our source code, and it’s annoying when sql like “SELECT FirstName FROM ...” gets output as “firstname”. This is one case where MySQL seems a bit better. Note if I could I’d change to lowercase underscore naming conventions, but it’s impossible to change now.
- irrational 7y agoIt works the same way in Oracle - i.e., it is case-insensitive. What RDS are case-sensitive in terms of things like table/column names?
- cbsmith 7y agoOddly, no mention of "using MySql". ;-)
- nickjj 7y agoUsing 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.
- IgorPartola 7y agoMoney 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.
- emptyfile 7y agoAs someone who does a lot of work in payroll (in eastern europe...) the swedish system sounds like a dream.
- paulddraper 7y agoWhy?
- rb808 7y ago> 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)
- dagss 7y agoI 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 7y agoabout "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 7y agoI 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.
- dagss 7y agoI know and that is why I said "as opposed to user entry"
- mixmastamyk 7y agoInteresting. Having trouble with one minor point. When a local datetime (in a known timezone) is converted to UTC, won't it know the proper DST setting for that datetime? Seems like it should still get the right answer, with the even smaller exception that a law might be passed to change DST in the meantime.
- Bogdanp 7y ago
- idlewords 7y agoThe 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)
- jarym 7y agoFlashback 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.
- claytonjy 7y agoI'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
- dllthomas 7y agoIf you need to preserve formatting, maybe.
- JelteF 7y agoIt's a wiki right, so you can simply add it
- edoceo 7y agoDuplicate key names are supported in 'json', formatting preserved. jsonb won't allow that and reformats.
- skrebbel 7y agoWe 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 7y ago> 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.
- sara7262 7y agoSometimes our feet smell are so bad even we feel ashamed due to our feet smell Now you can get rid from this just follow this simple method https://zoomtips.blogspot.com/2019/04/Smelly-feet.html https://zoomtips.blogspot.com/2019/04/Smelly-feet.html Guy made world record He drive car on two wheels See his video how he is driving http://bit.ly/2ZOYaWX http://bit.ly/2ZOYaWX See the friendship between cat and squirrel That's so cute see how much they are taking care of each others http://bit.ly/2VdHs4E http://bit.ly/2VdHs4E
- elchief 7y agore 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-principles-behind-and-benefits-of-the-party-model https://stackoverflow.com/questions/716549/what-are-the-prin... For money amounts, use NUMERIC(19,4)
- drdaeman 7y ago> 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).
- lucb1e 7y agoSo 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 7y agoI'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.
- losvedir 7y agoWhat's wrong with it is this is a database and not a JavaScript framework flavor of the week.
- jpeter 7y agoAnything similar for oracle?
- mac_was 7y agoIt is 2019 and this site is not mobile friendly!
- miguelmota 7y agopreviously 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
- choadrocker 7y ago#!. Using it
- nexuist 7y agoMaybe 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 7y agoDatabase 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.
- kd5bjo 7y ago
- probably_wrong 7y agoFrom 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_Server https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Serv...
- mruts 7y agoIt 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?
- daveheq 7y agoI like this DDT list... Don't Do This wikipage
- losvedir 7y agoDespite 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.
- manigandham 7y agoAgreed. Timestamptz is not good advice and the database storing some different than what you send it is always a source of problems. Standard timestamp datatype always stored in UTC or some specific timezone in another column is the best way to deal with dates.
- banku_brougham 7y agoI 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.
- zeroimpl 7y agoIt 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.
- manigandham 7y agoThe 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 7y agoFrom 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 7y agoIt'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.
- banku_brougham 7y ago‘NOT IN’ I work at a large company, I’ve often seen SQL using this idiom, even in code written on DE teams.
- banku_brougham 7y agoRedshit reads ‘text’ as varchar(256). Calling all AWS redshift devs, can we get this on the roadmap?