7 ms·
What I wish someone told me about Postgres
- onoke 2y agoHaha, good job. :)
- whiskey-one 2y agoReally great post! It belongs on a reading list somewhere for everyone who is using Postgres independently or as a part of their stack.
- andrewstuart 2y agoDump your schema, drop the entire thing into ChatGPT or Claude and ask it to write your Postgres query. Then ask it to rewrite the query three different ways and explain the pros and cons of each. Do the same for any existing queries in your system … drop in the schema then the query and ask for an analysis and suggested improvements.
- cyanydeez 2y agothen have corporate knock on your teams window asking why all it's IP ended up in a Chinese competitor?
- junaru 2y agoSchema != data.
- PittleyDunkin 2y agoIt's certainly data to the extent that it's copyrightable. I doubt this sentiment would stand up in court.
- jrpelkonen 2y ago“Show me your flowcharts and conceal your tables, and I shall continue to be mystified. Show me your tables, and I won't usually need your flowcharts; they'll be obvious.” - Fred Brooks
- josephg 2y agoYep, but if you think your database schema is "secret sauce", you're fooling yourself. You can almost always guess the database schema if you're given 10 minutes to play with the application.
- deleted 2y ago[deleted]
- hobs 2y agoGenerally your schema is your code, which IS your IP - your data is often NOT your IP (and often might just be a list of facts, a thing which is not copyrightable)
- bilekas 2y agoPeople have too much faith in LLM's at the moment. This might be able to give some insights, but if you're trying to LEARN something in detail, such as the pros and cons on a particular custom query on your unuiqe-ish schema.. The LLM will be prone to making stuff up by design, it wasn't trained on YOUR data.. It will give some good basic results and probably some functional queries, but it misses the mark with the finer points of optimization etc. If you don't know how to properly build the queries, go learn it properly with confidence that what you're learning is actually reliable.
- cpursley 2y agoThis is similar to what I’ve done with my sass where most of the logic lives in Postgres. I went through much of the logic and refactored it with GPT a while back and improved it significantly. It’s soooo good with SQL. Like insanely good. And these days I'm using Claude for all things code, which is significantly better than GPT.
- sgarland 2y agoAgreed that LLMs are quite good at SQL (and relational algebra). However, ask yourself this: how many programming languages do you know to at least a comfortable level? If N > 1, why not add another (SQL)? It’s not a difficult language, which is in part why LLMs are so good at it – it’s easy to predict the next token, because there are so few of them.
- cpursley 2y agoI know SQL pretty well (enough to write complex procedures) and prefer it over doing biz logic in application code (yuck ORMs). But SQL is just such a wonky syntax that I appreciate any help that I can get.
- cyanydeez 2y agoI'd say I tend to ignore the standard docs because they rarely have examples and rely on the arcane procedure of trying to decipher the super command options with all it's "[OR THIS|THAT]". I assume _someone_ can read this pseudo programming, but it's not me.
- Clubber 2y agoFWIW square brackets indicate an optional parameter and the pipe inside indicates all the parameters available; but I agree, I don't particularly care for that notation and prefer actual running SQL examples.
- hobs 2y agoRailroad-esque diagrams can be weird but they say a lot in a very short space, I would highly recommend spending a little extra time working on thinking through them, they are everywhere!
- Clubber 2y ago>Railroad-esque diagrams Now and forever I will think of railroads when I see these.
- hobs 2y agoWell, you should! But, I didn't invent anything there, that's what they are named :) Syntax diagrams (or railroad diagrams) are a way to represent a context-free grammar. They represent a graphical alternative to Backus–Naur form, EBNF, Augmented Backus–Naur form, and other text-based grammars as metalanguages... https://en.wikipedia.org/wiki/Syntax_diagram https://en.wikipedia.org/wiki/Syntax_diagram
- paulryanrogers 2y agoSometimes I find it annoying but mostly it works well for me. I've come to embrace the find feature and visually scanning over any parenthetical stuff. The alternative is they have to break it up into several quasi examples, each with their own optional modifiers.
- tomcam 2y agoI wrote this to help beginners: https://tomcam.github.io/postgres/ https://tomcam.github.io/postgres/
- nodakai 2y agoThat nested JSON query operator chains such as json_col->'foo'->'bar'->>'baz' internally return (copy) entire sub-objects at each level and can be much slower than jsonb_path_query(json_col, '$.foo.bar.baz') for large JSONB data ... although I haven't had the chance to verify this myself
- hans_castorp 2y agoYou can also use #>> operator for that: json_col #>> '{foo,bar,baz}'
- mdaniel 2y agoI got nerd-sniped on this, because I actually hadn't heard that before and would be horrified if it were true. It took some non-trivial digging to even get down into the "well, what does foo->>'bar' even map to in C?" level. I for sure am not claiming certainty, but based merely on "getIthJsonbValueFromContainer" <https://sourcegraph.com/github.com/postgres/postgres@REL_17_0/-/blob/src/backend/utils/adt/jsonb_util.c?L468 https://sourcegraph.com/github.com/postgres/postgres@REL_17_...> it does seem that they do palloc copies for at least some of the JSONB calls
- anarazel 2y agoPostgres does have infrastructure to avoid this in cases where the result is reused, and that's used in other places, e.g. array constructors / accessors. But not for jsonb at the moment.
- munk-a 2y agoWhile postgres is indeed case sensitive usually writing queries with keywords in all caps is an effort to increase legibility for visual pattern matching. It absolutely isn't needed but if I'm debugging a query of yours I will send it through my prettifier so that I can breeze through your definitions without getting hung up on minor weird syntax things. It's like prettification in any other language - visual structures that we can quickly recognize (like consistent indentation levels) make us waste less time on comprehension of the obvious so we can focus on what's important. The only thing I really object to is "actuallyUsingCaseInIdentifiers" I never want to see columns that require double quotes for me to inspect on cli.
- archsurface 2y agoMy understanding is that the caps were syntax highlighting on monochrome screens; no longer needed with colour. Can't provide a reference, it's an old memory.
- _Wintermute 2y agoMost of the SQL I write is within a string of another programming language, so it's essentially monochrome unless there's some really fancy syntax highlighting going on.
- jaredklewis 2y agoAside, but jetbrains IDEs seem to have some way to detect embedded sql and highlight it. I don’t remember configuring anything to get this feature.
- wmfiv 2y agoMore than highlight they'll do schema validation against inline SQL strings also.
- formerly_proven 2y agoIf you're working with some established framework and project structure their IDEs pull that information out of that, otherwise you'll need to at least tell it the dialect, but if you e.g. configure the database as a data source in the IDE you'll get full schema xref.
- nasretdinov 2y agoNice article! One thing I'd add is that almost all of it applies to other MVCC databases like MySQL too. While some details might be different, it too suffers from lon transactions, holds metadata locks during ALTERs, etc, all the good stuff :).
- avg_dev 2y agothis is really nice. i am glad the author put it together. i didn't know the pg docs were 3200 pages long! i have been using it for a while and try to learn as i go. i really do like the docs. and i also like to read articles on various particular subjects as i find a need to. i think the author might find it helpful to readers to add a note to https://challahscript.com/what_i_wish_someone_told_me_about_postgres#when-indexing-multiple-columns-the-order-matters https://challahscript.com/what_i_wish_someone_told_me_about_... that if someone is selecting for b alone, then an index on columns (b, a) would work fine. i think this is kind of implied when they talk about selecting on a alone, but maybe it wouldn't hurt to be extra explicit. (i didn't spend much time on the json/jsonb part since i rarely use that stuff)
- ellisv 2y agoI really wish developers cared more about normalization and stop shoving everything into a JSON(b) column.
- devin 2y agoEven if you care about it, you will still often wind up with a junk drawer of JSONB. I don't really see it as a problem unless people are writing bad queries against it instead of lifting values out of the JSONB into their own columns, etc.
- mnahkies 2y agoYeah exactly, and I'll take a JSON(B) column over TEXT with maybe valid serialised JSON, maybe RON, maybe something completely random any day
- randomdata 2y agoMost developers using these kinds of tools these days are actually building their own database management systems, just outsourcing the persistence to another DMBS, so there isn't a strong imperative to think about good design so long as it successfully satisfies the persistence need. Whether we actually should be building DMBSes on top of DMBSes is questionable, but is the current state of affairs regardless.
- tightbookkeeper 2y agoA previous employer thought that sql databases didn’t understand graphs. So they made their own system for serializing/deserializing graphs of objects into Postgres . They never used queries and instead had their own in-memory operators for traversing the graph, had to solve problems like deleting an entry and removing all references, partial graph updates. And I still don’t think it works.
- theamk 2y agoThis needs working schema migration process, including ability to undo schema change if the new column tanks the performance or breaks stuff. If there are CLI tools involved, you also need to ensure you can handle some downtime, or do synchronized version update across company, or support both old and new schemas for a while. If a database is not part of team's primary product all of this could be missing.
- marcosdumay 2y ago> Normalize your data unless you have a good reason not to Ouch. You don't want to just say that and move on. The author even linked to a page citing 10 different kinds of normalization (11 with the "non-normalized"). Most people don't even know what those are, and have no use for 7 of those. Do not send people on wild-goose chases after those higher normal forms.
- pavel_lishin 2y agoBut the author did have a paragraph explaining, in general, what they mean. And they're right! I've had to fix a few issues of this in a project I recently got moved to. There's almost never a reason to duplicate data.
- deleted 2y ago[deleted]
- hobs 2y agoI guess this is targeted towards noobs, but the answer is pretty much always 3rd normal form if you are clicking this and are not sure.
- makr17 2y agoInteresting, I would have said Boyce-Codd unless you have a good reason to vary in either direction.
- philipwhiuk 2y agoThe problem with BCNF is generally that you're enforcing a generally fairly complex and subject-to-change relation at database level rather than application logic.
- cryptonector 2y agoThe general rule is to normalize to the max, then denormalize till you get the performance that you need.
- pavel_lishin 2y agoA lot of these aren't postgres-specific. (null weirdness, index column order, etc.) For example, how nulls work - especially how interact with indexes and unique constraints - is also non-intuitive in mysql. If you have a user table with a non-nullable email column and a nullable username column, and a uniqueness constraint on something like (email, username), you'll be able to insert multiple identical emails with a null username into that table - because a null isn't equivalent to another null.
- anarazel 2y ago> If you have a user table with a non-nullable email column and a nullable username column, and a uniqueness constraint on something like (email, username), you'll be able to insert multiple identical emails with a null username into that table - because a null isn't equivalent to another null. FWIW, since 15 postgres you can influence that behaviour with NULLS [NOT] DISTINCT for constraints and unique indexes. https://www.postgresql.org/docs/devel/sql-createtable.html#SQL-CREATETABLE-PARMS-UNIQUE https://www.postgresql.org/docs/devel/sql-createtable.html#S... EDIT: Added link
- bpicolo 2y agoI think this is a good pragmatic default. The use case for the alternative is much more rare.
- pavel_lishin 2y agoI totally agree - but it's not an intuitive default.
- the5avage 2y agoFrom my experience with a lot of hilarious SQL stuff I have seen in the wild. It would be a good start to read the paper of codd and trying to understand what the relational model is. It's only 11 pages long and doing that would reduce the suffering in this world.
- GeorgeTirebiter 2y agoyou mean this paper? https://www.seas.upenn.edu/~zives/03f/cis550/codd.pdf https://www.seas.upenn.edu/~zives/03f/cis550/codd.pdf
- the5avage 2y agoYes.
- samsartor 2y agoInstead of psql, I really like https://github.com/dbcli/pgcli https://github.com/dbcli/pgcli
- mustime 2y ago[dead]
- vivzkestrel 2y agosince we are on the topic and since your article clearly mentions "Normalize your data unless you have a good reason not to" I had to ask. I am trying to build a news aggregator and I have many websites. Each of them has slightly different format. Even though I use feedparser in python, it still doesn't change how some of them put html text inside content and some of them break it down into a separate media xml attribute while retaining only basic textual summary inside a summary attribute. Do you think it makes more sense to store a single rss item as a column inside postgres or should it be stored after parsing it? I can see upsides and downsides to both approaches. Store it as XML and you have the ability to change your processing logic down the line for each stored item but you lose the flexibility of querying metadata and you also have to parse it on the fly every single time. Store it in multiple columns after processing it and it may require different logic for different websites + changing your overall python processing logic requires a lot of thinking on how it might affect some source. What do you guys recommend?
- cryptonector 2y agoWith all RDBMSes the rule is "normalize to the max, then denormalize till you get the performance that you need".
- krab 2y agoJust shooting without knowing your real needs - take this with a grain of salt. Store some parsed representation that makes it easier for you to work with (probably normalized). Keep an archive of raw data somewhere. That may be another column, table or even S3 bucket. Don't worry about schema changes but you need to not lose the original data. There are some pitfalls to schema migrations. But the schema should be the representation that works for you _at the moment_, otherwise it'll slow you down.
- sea6ear 2y agoWith the caveat that you probably shouldn't listen to me (or anyone else on here) since you are the only one who knows how much pain each choice will be ... I think that given that you are not really dealing with structured data - you've said that different sites have different structures, and I assume even with processing, you may not be able to generate identical metadata structures from each entry. I think I would go for one column of XML, plus maybe another column that holds a parsed data structure that represents the result of your processing (basically a cache holding the post-processed version of each site). Hopefully that could be re-evaluated by whatever language (Python?) you are using for your application. That way you don't have to do the full parsing each time you want to examine the entry, but you have access to something that can quickly give you whatever metadata is associated with it, but which doesn't tie you to the rigid structure of a table based database. Once you know what you are really doing with the data, then you could add additional metadata columns that are more rigid, and which can be queried directly in SQL as you identify patterns that are useful for performance.
- cryptonector 2y agoWatch out, for row/record values, if a column in the row/record is NULL then IS NULL will be true! You want to use IS [NOT] DISTINCT FROM NULL, full stop.
- deadbabe 2y agoDon’t create views that reference other views.
- christophilus 2y agoI’d never stumbled across the “don’t do this” wiki entry[0] before. Very handy. [0] https://wiki.postgresql.org/wiki/Don%27t_Do_This https://wiki.postgresql.org/wiki/Don%27t_Do_This
- datadrivenangel 2y agoThis reminds me of SQL Anti-patterns, which is a book that everyone who works with databases should read.
- gwbas1c 2y agoWhy don't they deprecate some of these features? If they're such easy stumbling blocks, seems like it makes sense to disable things like table inheritance in new schemas, and require some kind of arcane setting to re-enable them.
- mixmastamyk 2y agoSeveral of the broken are SQL standard.
- FridgeSeal 2y agoAt what point can we have an update to the standard that fixes a good number of these old hangups?
- __loam 2y agoChanging defaults can screw over existing users.
- SoftTalker 2y agoPresumably there are rare exceptions where you DO want to do the thing.
- Macha 2y agoe.g. the suggested replacement for timestamp is timestamptz, which has its own problems (notably, it eagerly converts to UTC, which means it cannot account for TZ rule changes between storing the date and reading it). If medium term scheduling across multiple countries is something that needs to work in your app, you're kind of stuck with a column with a timestamp and another column with a timezone.
- teddyh 2y ago\pset null '␀'
- RedShift1 2y agoI can't horizontally scroll on mobile, can't see the full query texts...
- hibachrach 2y ago(author here) hmm it seems to work on my device. Are you scrolling within the code block?
- RedShift1 2y agoI'm trying to but the entire page scrolls horizontally instead of just the code block.
- hibachrach 2y agoIt might be because of the larger tables. Thanks for letting me know--I'll take a look at it soon.
- Lvl999Noob 2y agoUnrelated to that issue, the right hand side TOC does not display completely. I am using firefox on windows with a default zoom of 120%. The TOC ends up going below the screen and hiding the last few entries. It also floats above the footer and hides the text there. If I may suggest, use `position: sticky` instead of `position: fixed` or some equivalent to whatever trick you used.
- ahachete 2y ago> Most notably, 'null'::jsonb = 'null'::jsonb is true whereas NULL = NULL is NULL Because 'null' in the JSON spec is a literal value (a constant), not SQL's NULL. Nothing to see here. https://datatracker.ietf.org/doc/html/rfc7159 https://datatracker.ietf.org/doc/html/rfc7159
- hibachrach 2y agoRight--it makes sense and shouldn't be changed, but it's a bit unintuitive for newcomers.
- zmmmmm 2y ago> It’s possible that adding an index will do nothing This is one of the more perplexing thing to me where Postgres ideology is a bit too strong, or at least, the way it works is too hard for me to understand (and I've tried - I'm not going to claim I'm a genius but I'm also not a moron). I hear there may be finally support for some kind of hints in upcoming versions, which would be very welcome to me. I've spent way too much time trying to divine the voodoo of why a slow query is not using indexes when it seems obvious that it should.
- sroerick 2y agoIt’s always a relief to read stuff articles this, realize I know 90% of it, and I’ve deserved the jobs I’ve had. Great and super useful notes
- cynicalsecurity 2y agoPeople who use PostgreSQL instead of MySQL just want to suffer while pretending "they are better than others".
- nimchimpsky 2y ago[dead]
- TwoNineFive 2y ago"going on a journey similar to mine" At this point "journey" is a cringe word because of it's excessive usage in blogspamverts. It tells me you are a gross aspiring influencer.
- hibachrach 2y agolol this is the first blog post i've written in 4 years and i didn't even post it here so I think you're imagining things
- philipwhiuk 2y ago> Name your tables in snake_case This bit me. It's highly tedious that case isn't preserved honestly.
- mikrotikker 2y agoShout-out to my postgres server that has been sitting untouched doing it's thing perfectly for 10 years, you're a real one
- 5Qn8mNbc2FNCiVV 2y agoYour code sections are almost unscrollable on mobile
- hibachrach 2y ago(author here) hmm it seems to work on my device. what happens when you try to scroll?
- jerrygoyal 2y agoI recently asked a similar question on reddit and got many inputs https://www.reddit.com/r/PostgreSQL/comments/1gbr0it/experienced_devs_what_advice_would_you_give_to/ https://www.reddit.com/r/PostgreSQL/comments/1gbr0it/experie...
- jedberg 2y agoMy number one tip: Vacuum every day! I didn't know this when I started, so I never vacuumed the reddit databases. Then one day I was forced to, and it took reddit down for almost a day while I waited for it to finish.
- FreakLegion 2y agoNo autovacuum? At reddit's scale I'm surprised you didn't run out of transaction IDs.
- gtaylor 2y agoAuto vacuuming is enabled now. We did have some near misses due to long running vacuums that barely completed before wraparounds, but got things tuned over time. I did none of that work but was on the team where it happened.
- jedberg 2y agoI turned it off because it would run at inopportune times. And we did run out of transaction IDs, which is why I was forced to do it. I never turned on the auto-vacuumer but I did set up a daily vacuum. Keep in mind I left Reddit 13 years ago and I’m sure they’ve made improvements since.
- leoqa 2y agoI had an interesting problem occur to the pg stats. We were migrating and had a version column, I.e key, val, version. We were migrating from version 1 to version 2, double writing into the same table. An index on (key, val, version) was being hit by our reader process using a where clause like key=k and version=1. When we flipped the reader to read version 2, the latency jumped from 30ms to 11s. Explain showed a sequential scan even though the index could serve the query. I was able to use MATERIALIZED and reorder CTEs to get the planner to do the right thing, but it caused an outage. We were autovacuuming as well. I ended up deleting the old version and rebuilding the index. My theory is that because the read load was on 50% of the data, the stats were super skewed.
- jaza 2y agoOnly some of these are really Postgres specific (use "text" / "timestamptz"; make psql more useful; copy to CSV). Most of them apply to relational databases in general (learn how to normalise!; yes, NULL is weird; learn how indexes work!; locks and long-running transactions will bite you; avoid storing and querying JSON blobs of doom). Not that that detracts from the usefulness of this article - pretty much all of them are important things to know when working with Postgres.
- khana 2y ago[dead]
- fatih-erikli-cg 2y agoStoring data in text costs less. A tcp connection to get some blog posts from another process is not necessary.
- bvrmn 2y agoOn other hand it's a mere blog post. You should not be bothered by TCP cost. But reliable data storage and reliability/restore (in case of backups) cost is a concern.
- fatih-erikli-cg 2y agoIt is mostly a blog post. A usecase for a database that holds tables and rows is very rare in real world. I know noone who uses contacts app in their mobile phones. Access is already there with checkboxes, select inputs and everything for years. Noone uses.
- abobov 2y agoMy tip is a better pager for psql: https://github.com/okbob/pspg https://github.com/okbob/pspg
- roydivision 2y agoReally like the Don't <thing> Why not? When should you? format of the Postgres Don't Do This page.
- Jean-Papoulos 2y ago>"Normalize your data" >"You don't need to write SQL in uppercase" >"What's an index ?" section From reading this, it's clear that the author never sat down and learned to use databases from the ground up. The author started using them and learned as he went, so his "tips" include things you'll be told in the first hour of any course. This doesn't hold any value for someone who's been using databases for almost any length of time.
- deleted 2y ago[deleted]
- michaelcampbell 2y ago> null weirdness Oracle enters the chat... (last I used it it considered an empty string '' the same as NULL)
- leighleighleigh 2y agoI've been learning Postgres and SQL on the job for the first time over the last six months - I can confirm I've learnt all of these the hard way! I'd also recommend reading up on the awesome pg statistics tables, and leverage them to benchmark your things like index performance and macro call speeds.