25 ms·

Ways to shoot yourself in the foot with Postgres

743 points by philbo 3y ago

PoopedMyPants56 3y ago
[dead]
alberth 3y ago
This configuration tuner will help address some of the articles points. https://pgtune.leopard.in.ua/ https://pgtune.leopard.in.ua/
- re-thc 3y ago
  If only there were profiles for popular cloud providers.
NegativeLatency 3y ago
Any good guides like this for MySQL? Unfortunately having to use it instead of Postgres at my current job.
- Scarbutt 3y ago
  They seem pretty generic, the same ideas/concepts can probably be taken over to mysql.
  - lbriner 3y ago
    This is definitely not true. They are not generic at all. * work_mem is postgres specific * stored procs and functions don't perform as badly in SQL Server * Triggers behave differently * NOTIFY is postgres specific etc.
- grafelic 3y ago
  Check out https://www.percona.com/blog https://www.percona.com/blog (filter for MySQL) I have been a heavy user of the Percona distribution of MySQL for many years and highly recommend it. I think that most of the MySQL articles on percona.com are relevant for other MySQL distributions. As a side note, depending on the software stack, ProxySQL can have a major positive impact on performance and scalability.
moring 3y ago
> 9: Compare indexed columns with IS NOT DISTINCT FROM Does anybody know why this is the case? Usually, an index is not used if the semantics of the index do not match the semantics of the query, so "using" it cannot ever produce correct results. But the workaround presented seems to have identical semantics to IS DISTINCT FROM and still uses the index, so why isn't IS DISTINCT FROM using the index then?
- hans_castorp 3y ago
  > Does anybody know why this is the case? Most of the time the answer to that is: because nobody cared enough or had time enough to implement it
  - moring 3y ago
    That might be the case, but my experience with databases (and especially PostgreSQL) is that most of the time I actually misunderstood the exact semantics of either the operation or the index. That would be a good chance to learn something :)
- singlow 3y ago
  My first guess would be that IS NOT DISTINCT FROM considers nulls to match. Indexes might not contain null values. I don't know of postgres indexes are sparse or can be non-sparse? It would be hard to do use a sparse index and find null matches. I would expect this could be optimized away if the column is also not null, but then why would you use the operator in that case so maybe no one has handled it.
  - singlow 3y ago
    Hmm - actually read some docs and I think that the implementation of IS NOT DISTINCT FROM was originally implemented one way when nulls were not indexed, but then hasn't been fixed since null indexing was implemented.
  - whakim 3y ago
    Indices in Postgres do contain NULL values. My guess is that it's possible to make IS DISTINCT FROM an indexable operator, but it wouldn't be straightforward given that NULLs are a bit of a special case for indexing comparisons in the sense that they totally ignore data types. IS DISTINCT FROM would probably have to redefine a bunch of existing opclasses to account for the possibility of NULL.
natmaka 3y ago
Pertinent: https://wiki.postgresql.org/wiki/Don%27t_Do_This https://wiki.postgresql.org/wiki/Don%27t_Do_This
- damagednoob 3y ago
  There's something ironic about having so many features that you have a dedicated page telling users which ones not to use.
  - mbork_pl 3y ago
    To be fair, some of them exist in PostgreSQL because it tries rather hard to conform to the SQL standard.
  - ainar-g 3y ago
    PostgreSQL has been around for almost 27 years (and even longer, if you include the Ingress and Post-Ingress eras). And things, well things, they tend to accumulate, to quote Trent Reznor, heh. On a related note, one could say that the C++ Core Guidelines[1] at least partially represent such a list. 55 matches for “don't use” and 247 for “avoid”, although not all of them are about language features, obviously. [1]: https://isocpp.github.io/CppCoreGuidelines/CppCoreGuidelines https://isocpp.github.io/CppCoreGuidelines/CppCoreGuidelines
  - PeterisP 3y ago
    I think that every platform that's old and has backwards compatibility has to have such a page - because there inevitably will be features for which we (now!) know that there are better ways to achieve the same goal, but they have to stay there for compatibility reasons.
- raspasov 3y ago
  If only every piece of software had a page like that.
- orthoxerox 3y ago
  I honestly expected the list to be longer.
xupybd 3y ago
These are some good tips but I've not hit these performance issues. I work on smaller scale applications. One has been in production since 2012 the database performs very well. I guess I need to get out and work for bigger companies to experience this.
- winrid 3y ago
  Yes, there's nothing quite like the query planner deciding to try something new and suddenly 100 application servers are DDOSing your primary :)
  - turtles3 3y ago
    This may be irrational but it's something that worries me about using postgres in production. Sure as a developer I love all the features, but the fact that the query planner can suddenly decide on a radically different (and incredibly inefficient) query plan makes it hard to trust. In some ways, a dumber query planner that needs coercing into the right query plan is more reassuring, in that you know it'll keep using that query plan unless explicitly told otherwise.
    - funcDropShadow 3y ago
      But that dumber query planner will bite you when your data changes. If the relative size of multiple tables change the query might have to change to be still efficient. Postgres query planner handles that just fine. I've used Postgres for years multi TB databases and I've experienced a problem with Postgres suddenly changing plans.
      - klysm 3y ago
        Is there a “never” missing from your last sentence?
        funcDropShadow 3y ago
        Yes :( And it is too late for editing it.
sa46 3y ago
Some additional techniques for triggers I've found helpful: - Triggers for validation are awesome. Avoid triggers for logic if you can help it -- harder to debug and update than a server sending SQL and easier than you might think to cause performance problems and cascading triggers. - Use custom error codes in validation triggers and add as much context as possible to the message when raising the exception. Future you will thank you. RAISE EXCEPTION USING ERRCODE = 'SR010', MESSAGE = 'cannot add a draft invoice ' || new.invoice_id || ' to route ' || new.route_id; - Postgres exceptions abort transactions, so if using explicit transactions, make sure you have a defer Rollback() so you don't return an aborted transaction to the server connection pool. - For better trigger performance, prefer statement-level triggers [1] or conditional before-row-based triggers. [1]: https://www.postgresql.org/docs/current/trigger-definition.html https://www.postgresql.org/docs/current/trigger-definition.h...
CodeCompost 3y ago
Postgres doesn't automatically create indexes for foreign keys. This may come as a surprise if you're more familiar with other databases, so pay attention to the implications as it can hurt you in a few ways. I don't know of any database system that does this. In the case for SQL Server, the foreign keys usually get added by the ORM layer (Entity Framework migrations if you're using dotnet).
- berkut 3y ago
  MySQL InnoDB does I believe...
  - singlow 3y ago
    It will create an index if no existing index meets the requirements of the foreign key.
deusex_ 3y ago
Another distinct set of similar tips that I found useful https://medium.com/productboard-engineering/eight-tips-to-relieve-those-postgres-headaches-c92fcdd7d065 https://medium.com/productboard-engineering/eight-tips-to-re...
hrdwdmrbl 3y ago
I’ve even found “Is not null” queries on a Boolean column to be slow so I made a generated column that’s Boolean.
- hans_castorp 3y ago
  So you added a generated boolean column that uses the value from a another boolean column? Sounds like a strange concept. Did you try a filtered index using WHERE ... IS NOT NULL instead? Or maybe an index on an expression coalesce(the_column, false) and then use that expression the where clause?
vrglvrglvrgl 3y ago
[dead]
jasfi 3y ago
PL/pgSQL needs some styling improvements: 1. Get a better name, PL/pgSQL doesn't exactly roll off the tongue. 2. Get rid of those $$ at the start and end of any PL/pgSQL, it's just verbose and ugly.
- jaggederest 3y ago
  Dollar quoting is just the easiest way to write strings with lots of escape characters, it's not required (I think it's the best way to do pl/pgsql though). You can also tag dollar-quoted strings: https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING https://www.postgresql.org/docs/current/sql-syntax-lexical.h...
- hans_castorp 3y ago
  > Get rid of those $$ at the start and end of any PL/pgSQL, it's just verbose and ugly. The Postgres parser itself doesn't know the language rules and thus can not parse the source code. The body of the function/procedure is passed to the language handler as a string without Postgres looking at it. The procedural code (PL/pgSQL, PL/python, PL/perl, plv8, ...) will not be parsed until the function/procedure is actually executed. Today there might be better ways to implement such a dynamic system to register new languages, but I guess it will be huge effort to change this to a way that would understand the old and the new syntax. Since Postgres 14, at least "LANGUAGE SQL" functions/procedures can be written without using dollar quoting if the SQL/PSM syntax is used.
jaggederest 3y ago
Sometimes you must `EXPLAIN ANALYZE` expensive queries in production, sadly. The behavior of postgres (even on non-bitwise copies) can be different under load. The biggest way I have seen this be true is with fragmented tables/indexes - same data but organized more compactly can change planner behavior. Article actually touches on another way that can be true - if your `work_mem` is drastically different the planner may prefer not to use an index, for example, and there are similar settings. Even with identical settings PG may choose different plans, it can be a bit tempermental in my experience, so sometimes you have to run the nonperformant query in offpeak production to understand performance.
- samokhvalov 3y ago
  What do you do if you need to check index ideas, or new table design?
  - jaggederest 3y ago
    Generally indexes are cheap, if built concurrently, so I often build all the possible indexes (for relatively modest sized data types, load, and tables) and look at the planner statistics in production to validate which indexes are useful for the query load. That only works if you have a read-heavy usage pattern, for write-heavy usage patterns it can sometimes be better not to have an index at all (one of the things he alludes to in the article about missing foreign key indexes - indexes come with a write cost, so on a write-only table, foreign key indexes are a bad idea) but as with everything real timing data is the key. For new table design in Serious Business Databases I do two or three step: offline replica with the same data and settings (but not 100% identical), usually in a transaction on a logical replica or main production (postgres supports DDL transactions, which is a big help, but if you alter a table in a transaction it can block tons of other transactions). It's important to carefully construct your alter table / create table / create index statements to ensure that everything is concurrent and won't block, of course, and there are plenty of pitfalls there, but definitely doable.
    - glasshug 3y ago
call_me_g0d 3y ago
I could never warm up to PostgresSQL. Guess I'll always stick with MySQL
- ainar-g 3y ago
  Can I ask why? I generally only see the “I switched from MySQL to PostgreSQL and loving it” comments in my info-bubble, so it'd be interesting to know what people who prefer to use MySQL feel is still lacking in PostgreSQL.
  - mbork_pl 3y ago
    Not the parent, but I have a friend who knows both Postgres and MySQL and prefers MySQL because "it is simpler and hence there are fewer ways things can get broken". (Not that I necessarily agree, but he seems to have a point.)
    - hot_gril 3y ago
      How is MySQL simpler? I'm a big fan of simplicity even at the cost of features, but they seem like the same level of complexity.
  - mschuster91 3y ago
    From an admin perspective: Updates are a hot, complex mess which means I put them off until it's no longer feasible to do so (=because some software requires a newer version). MySQL is easy: apt-get update/docker stop && docker rm && docker run/kubectl apply, depending on your stack that is literally all you need to do. PostgreSQL in contrast is hell. You have to shut down the existing database server, install the new one in parallel, manually do the upgrade (that involves copying the whole dataset), remove the old stuff, and then start back up again. Or you have to export the whole database into an SQL dump and import it on a fresh instance. In any case, way more involved, way more likely to go bonkers, and way, WAY more downtime needed.
    - reitanuki 3y ago
albertopv 3y ago
Two years ago I moved to a new company using Postgres as THE relational db, coming from years of Sql Server I found poor query plan issues troubleshooting tools. Anyway, I don't know if it's the same in Postgres, but in Sql Server an OR condition like that could kill your performance quite easily in a relatively complex query, often I had to refactor my query to a UNION (usually with ALL to avoid a distinct step, but it depends on the specific case).
- baq 3y ago
  Similar in postgres, depending on version. SQL Server is a damn fine DB if you can afford it. Highly recommended.
  - jaggederest 3y ago
    SQL Server is one of the descendants of Ingres, and PostgreSQL is, as the name might suggest, the successor project for database research after Ingres. They're both great databases really, it's a fun connection in their mutual history.
    - albertopv 3y ago
      Wow, I didn't know!
    - thomasjudge 3y ago
      Sybase, actually
      - jaggederest 3y ago
        The first Sybase grew out of the Ingres project at UCB, so transitively, SQL Server is also a descendant.
        johnthescott 3y ago
        bob epstein was vp at brittion-lee when he left to form sybase. BLI built a relational database machine (IDM), which was influenced by ingres but not much was inherited, code wise. sybase used a VM/pcode architecture, very much not like ingres. https://www.google.com/search?q=britton+lee+inc+wikipedia https://www.google.com/search?q=britton+lee+inc+wikipedia
it_citizen 3y ago
First way to shoot myself in the foot: not using it. Too often, I ruled out Postgres as a solution to a certain problem before even trying and jumped to more specialized solutions or moved the problem to the application layer. It took me years to stop underestimating what this awesome software can do.
- fastball 3y ago
  I am of the firm opinion that Postgres + Redis are basically the only DBs you ever need.
  - nimchimpsky 3y ago
    [dead]
  - jaggederest 3y ago
    Agreed, and I'm over in the corner sharpening an axe, looking at Redis with highly malicious intent, too. I think many of the things people use Redis for could be accomplished with a small postgres server and a decent schema e.g. `create table redislike (id bigserial primary key, contents jsonb);`
    - code_biologist 3y ago
      You can stuff some pretty insane stuff in Redis keys beside ints, giant multi MB strings with globby query patterns. Your general point is dead on.
  - klysm 3y ago
    What do you use redis for?
    - white_dragon88 3y ago
      Caching frequently fetched complex objects to mitigate load on the Postgres DB.
ak39 3y ago
"2. Push all your application logic into Postgres functions and procedures" Why are functions and procedures (an abstraction layer at db layer) considered harmful to performance when the same abstraction layer will be required at the application layer (introducing out of process overhead and possibly network traffic)? I don't agree with this advice. (Or I don't understand it.)
- maxloh 3y ago
  It is always more easier to scale horizontally at application layer (just adding more servers) than at database layer (which involves syncing data between multiple database instances).
  - ak39 3y ago
    You can horizontally scale the database directly.
    - beebmam 3y ago
      Depends on the scenario
  - Zanfa 3y ago
    In my experience more often than not, Postgres performance problems aren't really caused by the database, but either badly designed schemas or queries. For a lot of developers, the thinking goes that 10s of millions of rows sounds like a lot like big data, so they must start building microservices, distributed systems, use K/V stores and horizontally scale a la Google, whereas their entire dataset could actually fit in RAM of a single server and they could avoid the majority of the complexity if they just scaled vertically, which is usually much, much easier.
    - smcleod 3y ago
      Came here to say exactly this. Over the last 12~ years working with PostgreSQL I've dealt with quite a few performance related issues - almost all were poorly written queries.
baq 3y ago
I’d add ’not reading the table of contents of the manual’ to the list. I’ve probably worked with hundreds of people now who use a database daily either in code or just to explore data and can count on two hands (optimistically…) the number of folks who actually read the fine manual in any other way than googling something specific. Pro tip: read it so you know what to google for!
- nelsonic 3y ago
  This. https://www.postgresql.org/docs/current/ https://www.postgresql.org/docs/current/
- xupybd 3y ago
  It took me too long to understand this. I always felt pressure to get things done so skipped reading the manual. Turns out I would have gotten more done had I just read the manual.
  - jaggederest 3y ago
    There's the hoary old cliche about "if I was given three hours to cut down a tree with an axe, I would spend the first hour sharpening the axe" but in many cases it's really true. You can look like a superhero just by pointing out some small feature that makes life easier. One time I pointed out that, rather than reordering the tables to make loading work with foreign key constraints, we could pause trigger execution, load all the data, and then resume the triggers. pg_dump can even do it for you if you pick the options right (might be default), in addition to natively ordering the data correctly, but if you're messing around with large SQL files anyway, it's helpful..
    - j45 3y ago
      Clever architecture and approach that borders on a series or layers of simple decisions can often put perform clever coding and maintain a greater degree of flexibility.
      - 3y ago
chwa982 3y ago
[flagged]
- forgotmypw17 3y ago
  Hypercode.com is worth a look, i think
- lbriner 3y ago
  The first suggestion is not to gatecrash a random thread in a random forum with an off-topic question. You could have asked it as a HN question and got better results and not distracted from the topic being discussed.
afhammad 3y ago
From my understanding, `work_mem` is the maximum available memory per operation and not just per connection. If you have a stored procedure with loops and/or many nested operations, that can quickly get quite big. One trick worth noting, is that you can override the working memory at the transaction level. If you have a query you know needs more memory (e.g doing a distinct or plain sorting on a large table), within a transaction you can do: `set local work_mem = '50MB'` That will override the setting for operations inside this transaction only.
- philbo 3y ago
  This is a great tip, I had no idea there was `set local work_mem`. Thanks!
matthijs 3y ago
Using `truncate` in combination with `cascade` is another that I found unexpected: "Automatically truncate all tables that have foreign-key references to any of the named tables, or to any tables added to the group due to CASCADE." So it will simply clear out other tables that reference table to truncate, even if you have `on delete set null` and the foreign key column is null. https://www.postgresql.org/docs/current/sql-truncate.html https://www.postgresql.org/docs/current/sql-truncate.html
- jaggederest 3y ago
  Another footgun is that, while `DELETE FROM table_name` is transactional, TRUNCATE is not transactional. Once you push the truncate button, that data is gone in every transaction everywhere all at once. You should be very hesitant about using TRUNCATE on a production database unless that table (and all related foreign keyed tables) are truly ephemeral. Even if the data is cleared every night at midnight, for example, is there going to be a 10 second analysis transaction running across the midnight boundary that will fail hard with suddenly missing rows? Running a full delete on the rows and vacuum will still result in a tiny file on storage and doesn't wake me up in a cold sweat when I have a flashback. Even renaming the table is in many ways safer.
  - hans_castorp 3y ago
    TRUNCATE is absolutely transactional. You can rollback a TRUNCATE statement if you run it in a transaction. https://dbfiddle.uk/xkgzxMUU https://dbfiddle.uk/xkgzxMUU The only difference to other DML statements is, that it will put an exclusive lock on the table. So until the TRUNCATE is committed, no other transaction can read from the table.
    - petepete 3y ago
      > The only difference to other DML statements is Truncate is DDL. It's like dropping and recreating the table in a single operation.
      - deleted 3y ago
dathinab 3y ago
one bad surprised I had had was people but understanding transactions. Or more specifically transaction isolation levels.
arichard123 3y ago
Here's one, postgres default has this: seq_page_cost = 1.0 random_page_cost = 4.0 Which is fine if you are using spinning disks to store your data. It makes postgresql prefer sequential scans over index usage I think it's time the default were changed to suit SSDs, where a random page cost is the same as a sequential one. seq_page_cost = 1.0 random_page_cost = 1.0
- jashmatthews 3y ago
  The defaults do suck but common storage options like SSDs or Elastic Block Storage still do sequential IO substantially faster than random.
  - masklinn 3y ago
    Yes but nowhere near the extent rotating rust did. You may want to set random page costs higher than 1.0, in part because DB/FS-level pages and SSD blocks are completely different (and going through a block will be more efficient than having to hit multiple blocks), but probably 1.5 to 2.5. Interestingly enough according to some folks “seek” on EBS is highly concurrent, whereas “scan” is slow and more erratic, so you may want to set random_page_cost lower than on SSDs in order to favour seeks.
    - gshulegaard 3y ago
      I wouldn't set random_page_cost lower than seq_page_cost. It can cause the query planner to do wacky things (I learned the hard way). The documentation mentions it, but not as strongly as I think is warranted given how erratic my PostgreSQL cluster started behaving after I made that configuration change. > Although the system will let you set random_page_cost to less than seq_page_cost, it is not physically sensible to do so. However, setting them equal makes sense if the database is entirely cached in RAM, since in that case there is no penalty for touching pages out of sequence. Also, in a heavily-cached database you should lower both values relative to the CPU parameters, since the cost of fetching a page already in RAM is much smaller than it would normally be. https://www.postgresql.org/docs/current/runtime-config-query.html https://www.postgresql.org/docs/current/runtime-config-query... Curious though, lowering both values is something I haven't done before but now I am curious about.
h1fra 3y ago
Few tips I gathered along the years: - Configure Vacuum and maintenance_work_mem regularly if your DB size increases, if you allocate too much or too often it can clog up your memory. - If you plan on deleting more than a 10000 rows regularly, maybe you should look at partition, it's surprisingly very slow to delete that "much" data. And even more with foreign key. - Index on Boolean is useless, it's an easy mistake that will take memory and space disk for nothing. - Broad indices are easier to maintain but if you can have multiple smaller indices with WHERE condition it will be much faster - You can speed up, by a huge margin, big string indices with md5/hash index (only relevant for exact match) - Postgres as a queue is definitely working and scales pretty far - Related: be sure to understand the difference between transaction vs explicit locking, a lot of people assume too much from transaction and it will eventually breaks in prod.
- masklinn 3y ago
  > Index on Boolean is useless, it's an easy mistake that will take memory and space disk for nothing. However if the field is highly biased (e.g. 90 or 99% one value) it can be useful to create a partial index on the rarer value. Though even better is to create a partial index on the other stuff filtered by that value, especially if the smaller set is the commonly queried one (e.g. soft-deletes).
  - Waterluvian 3y ago
    Yeah. Finding ”open” tickets, for example. There’s actually some really good cases to index on a Boolean.
    - j45 3y ago
      Nice example.
    - WirelessGigabit 3y ago
      We work in different places. Here the index in closed tickets would be smaller. But you know, some sales guy called and they want this little feature NOW.
topmax 3y ago
What about `pg_notify`? I just want to use it to replace my kafka server which is lite overload but costs much.
- klysm 3y ago
  Take a look into logical replication. You can even send messages over it
- MrPowerGamerBR 3y ago
  If you are using NOTIFY/LISTEN, keep track to check if your database does not have any long running queries. If you end up getting PostgreSQL to vacuum freeze your tables while the long running query is active, PostgreSQL will delete files from the pg_xact folder and that will bork out any LISTEN query, until you fully restart the database.
  - philsnow 3y ago
    > PostgreSQL will delete files from the pg_xact folder and that will bork out any LISTEN query, until you fully restart the database that sounds like a bug; are you aware of an issue/ticket tracking that?
    - MrPowerGamerBR 3y ago
      While I haven't found an "issue" talking about this (sorry, I don't know how PostgreSQL tracks open bugs), they do know about it since I already seen that issue being talked about on PostgreSQL's mailing list. Here's my issue on StackExchange, for anyone that wants to delve deeper into my issue: https://dba.stackexchange.com/questions/325104/error-could-not-access-status-of-transaction-could-not-open-file-pg-xact-0029 https://dba.stackexchange.com/questions/325104/error-could-n... Here's an thread talking about the issue, while OP's issue doesn't seem to match exactly what I was experiencing, one of the replies describes my exact issue: https://postgrespro.com/list/thread-id/2546853 https://postgrespro.com/list/thread-id/2546853
oofnik 3y ago
At my first DevOps job we had defined a function which deleted old partitions from time to time. If you invoked it manually because of some automation failure with the wrong arguments, it had a habit of nuking production data, which, needless to say, happened at least once. Naturally, the function was called footgun. We can pretend that the proliferation of managed databases, newfangled NoSQL datastores, and other abstractions preclude the need for accumulated empirical observation across a range of tech stacks and time, but sometimes there's really no substitute for greybeard wisdom.
ahachete 3y ago
The recommendation for work_mem doesn't account for all the possible cases. It is already noted elsewhere on this thread [1] that the use of memory per connection could be higher than work_mem, and this is true even if you don't use stored procedures, as the memory incurred can be on a per-query node. So it can be a multiple of work_mem per connection. But there's a factor that even worsens this: parallel query, which is typically enabled by default, and will add another multiple to work_mem. Tuning work_mem is a hard art, and requires a delicate balance between trying to optimize some query's performance (that could avoid touching disk or using some indexes) vs the risk of causing db-wide errors like OOMs (very dangerous) or running out of SHM (errors only on queries being run, but still not desirable). So I normally lean on being quite conservative (db stability first!) so I divide the available memory (server memory - OS memory - shared_buffers and other PG buffers memory) by the number of connections, also divided by the parallelism and by another multiple factor --and then leave some additional room. In any case I'd recommend reviewing the detailed information, suggestions and links on the topic on postgresqlCONF [2] (disclaimer: a free project built by my company) [1]: https://news.ycombinator.com/item?id=35697986 https://news.ycombinator.com/item?id=35697986 [2]: https://postgresqlco.nf/doc/en/param/work_mem/ https://postgresqlco.nf/doc/en/param/work_mem/ (edit: formatting)
slig 3y ago
Anyone using StackGres/PGO can confirm if they have better defaults?
peter_retief 3y ago
Handed down to me by the grey beards! I like that. Postgres default settings are not the best, I remember moving from mysql to postgres some time back and discovered this. I always wondered why the default would not work best for most deployments? Anyone know why this is so?
rwmj 3y ago
The main tip I learned from using PostgreSQL (or relational databases in general) is never use an ORM. They cause far more trouble than they are worth and it's far easier to see what is going on when you're writing SQL queries directly.
- eitland 3y ago
  Might be a good idea if you arr the only one on the team or if everyone is like you. If not, be prepared to check for sql injection vulnerabilities the first n PRs from any new team member. Also to explain how to do it and your reasoning for it.
  - rwmj 3y ago
    Kind of goes without saying that any framework/library you use must not allow you to write SQL injection vulnerabilities, and if it does you should stop using it right now.
    - steve-chavez 3y ago
      SQL injection is always possible with an ORM, since they always allow executing raw SQL as an escape hatch.
      - eitland 3y ago
        True. But unlike with the alternative that many end on, raw sql, it doesn't funnel anyone into a place where writing "select * from sometable where id=" + id feels like a logical next step unless you actually have studied the subject or read the manual ;-)
- sodapopcan 3y ago
  ORMs can be great in many situations. Any ORM worth its salt has a way to spit out exactly what SQL it is generating. I have worked on apps, though, where we end with a disproportionate amount of raw SQL so obviously in those scenarios they become useless. It's almost always been writing some sort of reporting system in Postgres instead of using a column store, though.
eqefqe 3y ago
Things that Postgresql should address: - Start compiling queries and functions to native code and hash them just like other DBs - Improve connection scalability by using tasks instead of threads (each thread needs a lot of RAM!) - Automatically maintained clustered index (like in SQL Server) would be nice to have
reese_john 3y ago
> With that in place you could acquire events from the queue like so: UPDATE event_queue SET acquired_at = now() WHERE id IN ( SELECT id FROM event_queue WHERE acquired_at IS NULL ORDER BY occurred_at LIMIT 1000 -- Set this limit according to your usage ) RETURNING *; Would you need a FOR UPDATE in that subquery?
- Mavvie 3y ago
  No, because they're setting acquired_at which marks it as "handled". You only need FOR UPDATE (SKIP LOCKED) if you want to process the event inside a transaction; but the approach in the article is to just bulk "grab the events and mark them as done, atomically"
xnickb 3y ago
The "solution" for the number 4 is a pretty bad footgun in itself, as it is obviously prone to race conditions. Didn't read further.
- dpedu 3y ago
  Even within a transaction?
- philbo 3y ago
  Yep, a few people have made the same comment. I'll make a correction to the post soon, sorry that it upset you. As obvious as it seemed, I had no idea. Thanks for pointing it out though, feedback from experts is the big payoff to posting from my p.o.v. and now I know a thing which I didn't know before.
Topgamer7 3y ago
I found one yesterday. Names can only be like 62 characters long. Then it silently truncates then. At least when creating triggers that is.
ttfkam 3y ago
I'm surprised to see a discussion of queueing in Postgres without mentioning SKIP LOCKED.
fbn79 3y ago
I'm using last stable postgres but I can confirm you that point 7 still exists. here my case https://dba.stackexchange.com/questions/323960/query-using-view-very-slow-when-the-same-query-is-fast-using-with https://dba.stackexchange.com/questions/323960/query-using-v...
Calamitous 3y ago
Most common one I’ve seen in the last 5-10 years: using a JSON column instead of putting in a lookup table, or instead of properly analyzing and normalizing your data. That’s a mistake that you’ll be paying for for a while.
- lopatin 3y ago
  How much slower is it in your experience?
  - pramsey 3y ago
    If you let the JSON blobs grow past the page size the cost of recovering TOAST tuples can be 10x reading main storage. Also JSON is fundamentally de-normalized so you can incur scan and read costs just from hauling out duplicate values where a nice normalized lookup would be snappy. And finally JSON recovery is going to pull the whole object every time, even though you are probably only interested in one element of the object, so again, higher recovery times compared to an equivalent normalized model.
  - Calamitous 3y ago
    It’s not even the speed, it’s writing queries. The syntax is clunkier, and since you don’t have a nice clean schema, you end up doing a lot of jumping through hoops to check for the presence of keys/proper data types, etc. Or doing your basic data validation in your app instead of letting the database do its job.
keitmo 3y ago
Here's one that bit us a few years ago: SEQUENCEs, used to implement SERIAL and BIGSERIAL primary keys, are not transacted. "BEGIN; {insert 1,000,000 rows}; ROLLBACK" always adds 1,000,000 to the table's primary key SEQUENCE, despite the ROLLBACK. Likewise for upsert (via INSERT ON CONFLICT). The end result: A table's SERIAL (32-bit signed integer) primary key can overflow even when it contains far fewer than 2^31 rows.
- andorov 3y ago
  this also matters if you do a lot of upserts on a table that are predominantly updates. postgres requests an id from the sequence for each row of the incoming data ahead of time since it doesn't know which rows are updates and which are inserts. the sequence doesn't reset down for the unused so this can eat through it unexpectedly quickly. if you hit the max integer for the sequence and need space to implement a fundamental fix you can quickly change the sequence to start at -1 and go down. there's no issue with negative ids since they're also integers.
- skeletal88 3y ago
  But that is the point of serials, that they ignore transactions and are monotonically increasing.
- hot_gril 3y ago
  Yes. Another reason I blindly use `bigserial` as the PK for everything.
Mavvie 3y ago
> Setting acquired_at on read guarantees that each event is handled only once. After they've been handled, you can then delete the acquired events in batches too (there are better options than Postgres for permanently storing historical event data of unbounded size). This bothers me. It's technically true, but ignores a lot of nuance/complexity around real-world event processing needs. This approach means you will never be able to retry event processing in case it fails (or your server is shut down/crashes). So you either have to update the logic to also process events where "acquired_at is older than some timeout", which breaks your "handled only once" guarantee, or you can change to a SELECT FOR UPDATE SKIP LOCKED approach which has its own problems like higher database resource usage (but at least it won't process a slow job twice at the same time).
- philbo 3y ago
  Yep, a few people have mentioned this to me here and on Reddit. I didn't know about the issues with the approach I proposed, so was pleased to read the comments. Will add a correction to the post as soon as I have a sec, thanks.
  - Mavvie 3y ago
    Thank you! It was a great article, and definitely pointed out a few things I'm doing wrong in my Postgres setups.
claytongulick 3y ago
My rule of thumb is to use DB functions for writes that require good data consistency, and for reads that are join heavy. For things like formatting and transforming data, I prefer to have that work done on the client (web browser) if it's for a presentation concern, or middle tier if it's for an integration. My theory there is "don't make the database work hard". Use it for what it's great at: fast reads and data consistency. DB functions and sprocs are a great way to reduce latency and load, and boost performance if used for the right things. An easy rule of thumb is that if my function isn't using SQL as the language, I need to really think about whether it belongs in the DB. That all being said, I do use udfs to construct JSON results for some reports also, when it makes sense based on the params and number of round-trips to the server it would cause in the middle or UI tiers. Even though it violates some of the things above, it's really damn convenient and usually limited to low-traffic queries.
philsnow 3y ago
A writing tip: even in lists of "don't"s like this, find a way to write directives/imperatives in a positive sense. Asking readers to keep mentally flipping the sense of the thing you're telling them to do just adds cognitive load and makes it harder for them to pay attention to what you want them to pay attention to. Write "do"s, not "don't"s.
- blocko 3y ago
  I mostly agree, but there is one slight benefit: whenever I read one of these articles, each topic acts as a little quiz where I get to test whether I think it's a do or a don't before seeing the explanation
- hot_gril 3y ago
  Glad I wasn't the only one confused by this.
- philbo 3y ago
  Yeah, a lot of people have said this. I was wary of asserting "do's" because I'm not a Postgres expert. It felt more honest to phrase stuff in terms of my own mistakes and "don't do what I did", but of course that's confusing. And then I ruined it by still asserting "do's" in the bodies of some sections.
  - hot_gril 3y ago
    I think the "don't"s are fine, but each bullet should say "don't" in that case.
    - philsnow 3y ago
      That would also work well, yeah
lbriner 3y ago
One thing that caught me out is that if you are doing an operation on a string like "lower(email)" then the query planner will not use an index on the email column, instead you would need an index on "lower(email)", which is fine if you always access it in the same way but otherwise requires multiple indexes to get coverage in all your scenarios. There are also plenty of other weird planner choices which I can't work out but which were much easier to understand in SQL Server. Sometimes, the smallest change will stop using an index e.g. using LIMIT on a query can completely bypass an index.
deleted 3y ago
[deleted]
hot_gril 3y ago
The two biggest ones imo were not mentioned: 1. Contrary to popular belief, Postgres isn't fully ACID (specifically the "I") with the default isolation mode. For example, selecting the sum of a column then inserting conditionally on that creates a race condition. Serializable mode is fully isolated, but it has many caveats and shouldn't be used often, so you should instead become familiar with what's isolated and what isn't. See https://www.postgresql.org/docs/current/transaction-iso.html https://www.postgresql.org/docs/current/transaction-iso.html 2. timestamp (without time zone) is bad; always use timestamptz, no exceptions. Unintuitively, timestamp (without time zone) is the one that makes your DB's time zone affect your selected data. Neither one actually stores a time zone, it's just a difference in output formatting. This is a moot point if your DB's locale is set to UTC, but that's not the default.
- klysm 3y ago
  > it has many caveats and shouldn't be used often I’d argue it isn’t used enough given its isolation advantages
  - hot_gril 3y ago
    Serializable mode is only useful in low throughput cases. You take a big performance hit and can easily have a bunch of queries waiting one each other until the queue fills up. I haven't used it in forever, I just make sure my stuff works with the default isolation mode. You also can't slap it on like a band-aid for unsure users. The DB client has to implement retry logic, which most Postgres libs don't seem to have, and the code has to careful not to cause side effects inside the retriable area. Spanner and some other DBMSs are fully serializable always. They do a better job of that than Postgres, I guess cause they focus on it, but they're still a lot slower in the end. What would be cool is some extension to watch your non-serializable transactions for concurrency anomalies like 1% of the time and use that tiny sample to alert you of race conditions. Like a thread sanitizer does for code. Does that exist??
    - klysm 3y ago
      Yes you take the performance hit, but many workloads are low enough throughput for it to be completely fine. I’m not sure how you could detect such anomalies though without running the same serialization checking logic within postgres
jwsteigerwalt 3y ago
This list conceptually applies to just about any RDBMS. NOTIFY is incredibly powerful and when used prudently, can be a fantastic solution to some business problems.
anthonyfloyd 3y ago
[dead]
TheSniffer55 3y ago
[dead]
DoodieButt33 3y ago
[dead]
McPoopie24 3y ago
[dead]
TheBrickStall 3y ago
[dead]
StinkyPete56 3y ago
[dead]
PooFace45 3y ago
[dead]
CrazyLegs78 3y ago
[dead]
BennyNana67 3y ago
[dead]
LegsOnKegs 3y ago
[dead]
DirtyHarry67 3y ago
[dead]
KissMyAxe 3y ago
[dead]
FatsoKid34 3y ago
[dead]
CallMeMaybe45 3y ago
[dead]
DeezPeez099 3y ago
[dead]
SnoopD5678 3y ago
[dead]
DeeDawg7768 3y ago
[dead]
DontBeASnoop 3y ago
[dead]
ForeverBored9 3y ago
[dead]
StinkyFeet900 3y ago
[dead]
TacoBelle78 3y ago
[dead]
BeerGut09 3y ago
[dead]
SmellyPants70 3y ago
[dead]
LazyBum17 3y ago
[dead]
DirtyPutter08 3y ago
[dead]
HairyHarry09 3y ago
[dead]
2OldForIt 3y ago
[dead]
SillyDuckling0 3y ago
[dead]
TeacherPet09 3y ago
[dead]
LuckyDuck09 3y ago
[dead]
SlyGoose3 3y ago
[dead]
SnitchySnitch12 3y ago
[dead]
NotABaby70 3y ago
[dead]
RockinOut 3y ago
[dead]
CantTouchThis1 3y ago
[dead]
DontBeAScared 3y ago
[dead]
OnTheRocks4 3y ago
[dead]
TacoBellDiego 3y ago
[dead]
SupremeDud09 3y ago
[dead]
SereneBeauty7 3y ago
[dead]
PlayfulSpirit5 3y ago
[dead]
QuietConfidence 3y ago
[dead]
RefreshingView 3y ago
[dead]
SimplySweet4 3y ago
[dead]
SultrySiren6 3y ago
[dead]
FunnyGuy567 3y ago
[dead]
TropicalDelight 3y ago
[dead]
WonderWeirdo0 3y ago
[dead]
WickedlyCool 3y ago
[dead]

743 points by philbo 3y ago

[dead]

This configuration tuner will help address some of the articles points. https://pgtune.leopard.in.ua/ https://pgtune.leopard.in.ua/

re-thc 3y ago
If only there were profiles for popular cloud providers.

NegativeLatency 3y ago

Any good guides like this for MySQL? Unfortunately having to use it instead of Postgres at my current job.

Scarbutt 3y ago
They seem pretty generic, the same ideas/concepts can probably be taken over to mysql.
- lbriner 3y ago
  This is definitely not true. They are not generic at all. * work_mem is postgres specific * stored procs and functions don't perform as badly in SQL Server * Triggers behave differently * NOTIFY is postgres specific etc.
grafelic 3y ago
Check out https://www.percona.com/blog https://www.percona.com/blog (filter for MySQL) I have been a heavy user of the Percona distribution of MySQL for many years and highly recommend it. I think that most of the MySQL articles on percona.com are relevant for other MySQL distributions. As a side note, depending on the software stack, ProxySQL can have a major positive impact on performance and scalability.

moring 3y ago

> 9: Compare indexed columns with IS NOT DISTINCT FROM Does anybody know why this is the case? Usually, an index is not used if the semantics of the index do not match the semantics of the query, so "using" it cannot ever produce correct results. But the workaround presented seems to have identical semantics to IS DISTINCT FROM and still uses the index, so why isn't IS DISTINCT FROM using the index then?

hans_castorp 3y ago
> Does anybody know why this is the case? Most of the time the answer to that is: because nobody cared enough or had time enough to implement it
- moring 3y ago
  That might be the case, but my experience with databases (and especially PostgreSQL) is that most of the time I actually misunderstood the exact semantics of either the operation or the index. That would be a good chance to learn something :)
singlow 3y ago
My first guess would be that IS NOT DISTINCT FROM considers nulls to match. Indexes might not contain null values. I don't know of postgres indexes are sparse or can be non-sparse? It would be hard to do use a sparse index and find null matches. I would expect this could be optimized away if the column is also not null, but then why would you use the operator in that case so maybe no one has handled it.
- singlow 3y ago
  Hmm - actually read some docs and I think that the implementation of IS NOT DISTINCT FROM was originally implemented one way when nulls were not indexed, but then hasn't been fixed since null indexing was implemented.
- whakim 3y ago
  Indices in Postgres do contain NULL values. My guess is that it's possible to make IS DISTINCT FROM an indexable operator, but it wouldn't be straightforward given that NULLs are a bit of a special case for indexing comparisons in the sense that they totally ignore data types. IS DISTINCT FROM would probably have to redefine a bunch of existing opclasses to account for the possibility of NULL.

natmaka 3y ago

Pertinent: https://wiki.postgresql.org/wiki/Don%27t_Do_This https://wiki.postgresql.org/wiki/Don%27t_Do_This

damagednoob 3y ago
There's something ironic about having so many features that you have a dedicated page telling users which ones not to use.
- mbork_pl 3y ago
  To be fair, some of them exist in PostgreSQL because it tries rather hard to conform to the SQL standard.
- ainar-g 3y ago
  PostgreSQL has been around for almost 27 years (and even longer, if you include the Ingress and Post-Ingress eras). And things, well things, they tend to accumulate, to quote Trent Reznor, heh. On a related note, one could say that the C++ Core Guidelines[1] at least partially represent such a list. 55 matches for “don't use” and 247 for “avoid”, although not all of them are about language features, obviously. [1]: https://isocpp.github.io/CppCoreGuidelines/CppCoreGuidelines https://isocpp.github.io/CppCoreGuidelines/CppCoreGuidelines
- PeterisP 3y ago
  I think that every platform that's old and has backwards compatibility has to have such a page - because there inevitably will be features for which we (now!) know that there are better ways to achieve the same goal, but they have to stay there for compatibility reasons.
raspasov 3y ago
If only every piece of software had a page like that.
orthoxerox 3y ago
I honestly expected the list to be longer.

xupybd 3y ago

These are some good tips but I've not hit these performance issues. I work on smaller scale applications. One has been in production since 2012 the database performs very well. I guess I need to get out and work for bigger companies to experience this.

winrid 3y ago
Yes, there's nothing quite like the query planner deciding to try something new and suddenly 100 application servers are DDOSing your primary :)
- turtles3 3y ago
  This may be irrational but it's something that worries me about using postgres in production. Sure as a developer I love all the features, but the fact that the query planner can suddenly decide on a radically different (and incredibly inefficient) query plan makes it hard to trust. In some ways, a dumber query planner that needs coercing into the right query plan is more reassuring, in that you know it'll keep using that query plan unless explicitly told otherwise.
  - funcDropShadow 3y ago
    But that dumber query planner will bite you when your data changes. If the relative size of multiple tables change the query might have to change to be still efficient. Postgres query planner handles that just fine. I've used Postgres for years multi TB databases and I've experienced a problem with Postgres suddenly changing plans.
    - klysm 3y ago
      Is there a “never” missing from your last sentence?
      - funcDropShadow 3y ago
        Yes :( And it is too late for editing it.

sa46 3y ago

Some additional techniques for triggers I've found helpful: - Triggers for validation are awesome. Avoid triggers for logic if you can help it -- harder to debug and update than a server sending SQL and easier than you might think to cause performance problems and cascading triggers. - Use custom error codes in validation triggers and add as much context as possible to the message when raising the exception. Future you will thank you. RAISE EXCEPTION USING ERRCODE = 'SR010', MESSAGE = 'cannot add a draft invoice ' || new.invoice_id || ' to route ' || new.route_id; - Postgres exceptions abort transactions, so if using explicit transactions, make sure you have a defer Rollback() so you don't return an aborted transaction to the server connection pool. - For better trigger performance, prefer statement-level triggers [1] or conditional before-row-based triggers. [1]: https://www.postgresql.org/docs/current/trigger-definition.html https://www.postgresql.org/docs/current/trigger-definition.h...

CodeCompost 3y ago

Postgres doesn't automatically create indexes for foreign keys. This may come as a surprise if you're more familiar with other databases, so pay attention to the implications as it can hurt you in a few ways. I don't know of any database system that does this. In the case for SQL Server, the foreign keys usually get added by the ORM layer (Entity Framework migrations if you're using dotnet).

berkut 3y ago
MySQL InnoDB does I believe...
- singlow 3y ago
  It will create an index if no existing index meets the requirements of the foreign key.

deusex_ 3y ago

Another distinct set of similar tips that I found useful https://medium.com/productboard-engineering/eight-tips-to-relieve-those-postgres-headaches-c92fcdd7d065 https://medium.com/productboard-engineering/eight-tips-to-re...

hrdwdmrbl 3y ago

I’ve even found “Is not null” queries on a Boolean column to be slow so I made a generated column that’s Boolean.

hans_castorp 3y ago
So you added a generated boolean column that uses the value from a another boolean column? Sounds like a strange concept. Did you try a filtered index using WHERE ... IS NOT NULL instead? Or maybe an index on an expression coalesce(the_column, false) and then use that expression the where clause?

vrglvrglvrgl 3y ago

[dead]

jasfi 3y ago

PL/pgSQL needs some styling improvements: 1. Get a better name, PL/pgSQL doesn't exactly roll off the tongue. 2. Get rid of those $$ at the start and end of any PL/pgSQL, it's just verbose and ugly.

jaggederest 3y ago
Dollar quoting is just the easiest way to write strings with lots of escape characters, it's not required (I think it's the best way to do pl/pgsql though). You can also tag dollar-quoted strings: https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING https://www.postgresql.org/docs/current/sql-syntax-lexical.h...
hans_castorp 3y ago
> Get rid of those $$ at the start and end of any PL/pgSQL, it's just verbose and ugly. The Postgres parser itself doesn't know the language rules and thus can not parse the source code. The body of the function/procedure is passed to the language handler as a string without Postgres looking at it. The procedural code (PL/pgSQL, PL/python, PL/perl, plv8, ...) will not be parsed until the function/procedure is actually executed. Today there might be better ways to implement such a dynamic system to register new languages, but I guess it will be huge effort to change this to a way that would understand the old and the new syntax. Since Postgres 14, at least "LANGUAGE SQL" functions/procedures can be written without using dollar quoting if the SQL/PSM syntax is used.

jaggederest 3y ago

Sometimes you must `EXPLAIN ANALYZE` expensive queries in production, sadly. The behavior of postgres (even on non-bitwise copies) can be different under load. The biggest way I have seen this be true is with fragmented tables/indexes - same data but organized more compactly can change planner behavior. Article actually touches on another way that can be true - if your `work_mem` is drastically different the planner may prefer not to use an index, for example, and there are similar settings. Even with identical settings PG may choose different plans, it can be a bit tempermental in my experience, so sometimes you have to run the nonperformant query in offpeak production to understand performance.

samokhvalov 3y ago
What do you do if you need to check index ideas, or new table design?
- jaggederest 3y ago
  Generally indexes are cheap, if built concurrently, so I often build all the possible indexes (for relatively modest sized data types, load, and tables) and look at the planner statistics in production to validate which indexes are useful for the query load. That only works if you have a read-heavy usage pattern, for write-heavy usage patterns it can sometimes be better not to have an index at all (one of the things he alludes to in the article about missing foreign key indexes - indexes come with a write cost, so on a write-only table, foreign key indexes are a bad idea) but as with everything real timing data is the key. For new table design in Serious Business Databases I do two or three step: offline replica with the same data and settings (but not 100% identical), usually in a transaction on a logical replica or main production (postgres supports DDL transactions, which is a big help, but if you alter a table in a transaction it can block tons of other transactions). It's important to carefully construct your alter table / create table / create index statements to ensure that everything is concurrent and won't block, of course, and there are plenty of pitfalls there, but definitely doable.
  - glasshug 3y ago

call_me_g0d 3y ago

I could never warm up to PostgresSQL. Guess I'll always stick with MySQL

ainar-g 3y ago
Can I ask why? I generally only see the “I switched from MySQL to PostgreSQL and loving it” comments in my info-bubble, so it'd be interesting to know what people who prefer to use MySQL feel is still lacking in PostgreSQL.
- mbork_pl 3y ago
  Not the parent, but I have a friend who knows both Postgres and MySQL and prefers MySQL because "it is simpler and hence there are fewer ways things can get broken". (Not that I necessarily agree, but he seems to have a point.)
  - hot_gril 3y ago
    How is MySQL simpler? I'm a big fan of simplicity even at the cost of features, but they seem like the same level of complexity.
- mschuster91 3y ago
  From an admin perspective: Updates are a hot, complex mess which means I put them off until it's no longer feasible to do so (=because some software requires a newer version). MySQL is easy: apt-get update/docker stop && docker rm && docker run/kubectl apply, depending on your stack that is literally all you need to do. PostgreSQL in contrast is hell. You have to shut down the existing database server, install the new one in parallel, manually do the upgrade (that involves copying the whole dataset), remove the old stuff, and then start back up again. Or you have to export the whole database into an SQL dump and import it on a fresh instance. In any case, way more involved, way more likely to go bonkers, and way, WAY more downtime needed.
  - reitanuki 3y ago

albertopv 3y ago

Two years ago I moved to a new company using Postgres as THE relational db, coming from years of Sql Server I found poor query plan issues troubleshooting tools. Anyway, I don't know if it's the same in Postgres, but in Sql Server an OR condition like that could kill your performance quite easily in a relatively complex query, often I had to refactor my query to a UNION (usually with ALL to avoid a distinct step, but it depends on the specific case).

baq 3y ago
Similar in postgres, depending on version. SQL Server is a damn fine DB if you can afford it. Highly recommended.
- jaggederest 3y ago
  SQL Server is one of the descendants of Ingres, and PostgreSQL is, as the name might suggest, the successor project for database research after Ingres. They're both great databases really, it's a fun connection in their mutual history.
  - albertopv 3y ago
    Wow, I didn't know!
  - thomasjudge 3y ago
    Sybase, actually
    - jaggederest 3y ago
      The first Sybase grew out of the Ingres project at UCB, so transitively, SQL Server is also a descendant.
      - johnthescott 3y ago
        bob epstein was vp at brittion-lee when he left to form sybase. BLI built a relational database machine (IDM), which was influenced by ingres but not much was inherited, code wise. sybase used a VM/pcode architecture, very much not like ingres. https://www.google.com/search?q=britton+lee+inc+wikipedia https://www.google.com/search?q=britton+lee+inc+wikipedia

it_citizen 3y ago

First way to shoot myself in the foot: not using it. Too often, I ruled out Postgres as a solution to a certain problem before even trying and jumped to more specialized solutions or moved the problem to the application layer. It took me years to stop underestimating what this awesome software can do.

fastball 3y ago
I am of the firm opinion that Postgres + Redis are basically the only DBs you ever need.
- nimchimpsky 3y ago
  [dead]
- jaggederest 3y ago
  Agreed, and I'm over in the corner sharpening an axe, looking at Redis with highly malicious intent, too. I think many of the things people use Redis for could be accomplished with a small postgres server and a decent schema e.g. `create table redislike (id bigserial primary key, contents jsonb);`
  - code_biologist 3y ago
    You can stuff some pretty insane stuff in Redis keys beside ints, giant multi MB strings with globby query patterns. Your general point is dead on.
- klysm 3y ago
  What do you use redis for?
  - white_dragon88 3y ago
    Caching frequently fetched complex objects to mitigate load on the Postgres DB.

ak39 3y ago

"2. Push all your application logic into Postgres functions and procedures" Why are functions and procedures (an abstraction layer at db layer) considered harmful to performance when the same abstraction layer will be required at the application layer (introducing out of process overhead and possibly network traffic)? I don't agree with this advice. (Or I don't understand it.)

maxloh 3y ago
It is always more easier to scale horizontally at application layer (just adding more servers) than at database layer (which involves syncing data between multiple database instances).
- ak39 3y ago
  You can horizontally scale the database directly.
  - beebmam 3y ago
    Depends on the scenario
- Zanfa 3y ago
  In my experience more often than not, Postgres performance problems aren't really caused by the database, but either badly designed schemas or queries. For a lot of developers, the thinking goes that 10s of millions of rows sounds like a lot like big data, so they must start building microservices, distributed systems, use K/V stores and horizontally scale a la Google, whereas their entire dataset could actually fit in RAM of a single server and they could avoid the majority of the complexity if they just scaled vertically, which is usually much, much easier.
  - smcleod 3y ago
    Came here to say exactly this. Over the last 12~ years working with PostgreSQL I've dealt with quite a few performance related issues - almost all were poorly written queries.

baq 3y ago

I’d add ’not reading the table of contents of the manual’ to the list. I’ve probably worked with hundreds of people now who use a database daily either in code or just to explore data and can count on two hands (optimistically…) the number of folks who actually read the fine manual in any other way than googling something specific. Pro tip: read it so you know what to google for!

nelsonic 3y ago
This. https://www.postgresql.org/docs/current/ https://www.postgresql.org/docs/current/
xupybd 3y ago
It took me too long to understand this. I always felt pressure to get things done so skipped reading the manual. Turns out I would have gotten more done had I just read the manual.
- jaggederest 3y ago
  There's the hoary old cliche about "if I was given three hours to cut down a tree with an axe, I would spend the first hour sharpening the axe" but in many cases it's really true. You can look like a superhero just by pointing out some small feature that makes life easier. One time I pointed out that, rather than reordering the tables to make loading work with foreign key constraints, we could pause trigger execution, load all the data, and then resume the triggers. pg_dump can even do it for you if you pick the options right (might be default), in addition to natively ordering the data correctly, but if you're messing around with large SQL files anyway, it's helpful..
  - j45 3y ago
    Clever architecture and approach that borders on a series or layers of simple decisions can often put perform clever coding and maintain a greater degree of flexibility.
    - 3y ago

chwa982 3y ago

[flagged]

forgotmypw17 3y ago
Hypercode.com is worth a look, i think
lbriner 3y ago
The first suggestion is not to gatecrash a random thread in a random forum with an off-topic question. You could have asked it as a HN question and got better results and not distracted from the topic being discussed.

afhammad 3y ago

From my understanding, `work_mem` is the maximum available memory per operation and not just per connection. If you have a stored procedure with loops and/or many nested operations, that can quickly get quite big. One trick worth noting, is that you can override the working memory at the transaction level. If you have a query you know needs more memory (e.g doing a distinct or plain sorting on a large table), within a transaction you can do: `set local work_mem = '50MB'` That will override the setting for operations inside this transaction only.

philbo 3y ago
This is a great tip, I had no idea there was `set local work_mem`. Thanks!

matthijs 3y ago

Using `truncate` in combination with `cascade` is another that I found unexpected: "Automatically truncate all tables that have foreign-key references to any of the named tables, or to any tables added to the group due to CASCADE." So it will simply clear out other tables that reference table to truncate, even if you have `on delete set null` and the foreign key column is null. https://www.postgresql.org/docs/current/sql-truncate.html https://www.postgresql.org/docs/current/sql-truncate.html

jaggederest 3y ago
Another footgun is that, while `DELETE FROM table_name` is transactional, TRUNCATE is not transactional. Once you push the truncate button, that data is gone in every transaction everywhere all at once. You should be very hesitant about using TRUNCATE on a production database unless that table (and all related foreign keyed tables) are truly ephemeral. Even if the data is cleared every night at midnight, for example, is there going to be a 10 second analysis transaction running across the midnight boundary that will fail hard with suddenly missing rows? Running a full delete on the rows and vacuum will still result in a tiny file on storage and doesn't wake me up in a cold sweat when I have a flashback. Even renaming the table is in many ways safer.
- hans_castorp 3y ago
  TRUNCATE is absolutely transactional. You can rollback a TRUNCATE statement if you run it in a transaction. https://dbfiddle.uk/xkgzxMUU https://dbfiddle.uk/xkgzxMUU The only difference to other DML statements is, that it will put an exclusive lock on the table. So until the TRUNCATE is committed, no other transaction can read from the table.
  - petepete 3y ago
    > The only difference to other DML statements is Truncate is DDL. It's like dropping and recreating the table in a single operation.
    - deleted 3y ago

dathinab 3y ago

one bad surprised I had had was people but understanding transactions. Or more specifically transaction isolation levels.

arichard123 3y ago

Here's one, postgres default has this: seq_page_cost = 1.0 random_page_cost = 4.0 Which is fine if you are using spinning disks to store your data. It makes postgresql prefer sequential scans over index usage I think it's time the default were changed to suit SSDs, where a random page cost is the same as a sequential one. seq_page_cost = 1.0 random_page_cost = 1.0

jashmatthews 3y ago
The defaults do suck but common storage options like SSDs or Elastic Block Storage still do sequential IO substantially faster than random.
- masklinn 3y ago
  Yes but nowhere near the extent rotating rust did. You may want to set random page costs higher than 1.0, in part because DB/FS-level pages and SSD blocks are completely different (and going through a block will be more efficient than having to hit multiple blocks), but probably 1.5 to 2.5. Interestingly enough according to some folks “seek” on EBS is highly concurrent, whereas “scan” is slow and more erratic, so you may want to set random_page_cost lower than on SSDs in order to favour seeks.
  - gshulegaard 3y ago
    I wouldn't set random_page_cost lower than seq_page_cost. It can cause the query planner to do wacky things (I learned the hard way). The documentation mentions it, but not as strongly as I think is warranted given how erratic my PostgreSQL cluster started behaving after I made that configuration change. > Although the system will let you set random_page_cost to less than seq_page_cost, it is not physically sensible to do so. However, setting them equal makes sense if the database is entirely cached in RAM, since in that case there is no penalty for touching pages out of sequence. Also, in a heavily-cached database you should lower both values relative to the CPU parameters, since the cost of fetching a page already in RAM is much smaller than it would normally be. https://www.postgresql.org/docs/current/runtime-config-query.html https://www.postgresql.org/docs/current/runtime-config-query... Curious though, lowering both values is something I haven't done before but now I am curious about.

h1fra 3y ago

Few tips I gathered along the years: - Configure Vacuum and maintenance_work_mem regularly if your DB size increases, if you allocate too much or too often it can clog up your memory. - If you plan on deleting more than a 10000 rows regularly, maybe you should look at partition, it's surprisingly very slow to delete that "much" data. And even more with foreign key. - Index on Boolean is useless, it's an easy mistake that will take memory and space disk for nothing. - Broad indices are easier to maintain but if you can have multiple smaller indices with WHERE condition it will be much faster - You can speed up, by a huge margin, big string indices with md5/hash index (only relevant for exact match) - Postgres as a queue is definitely working and scales pretty far - Related: be sure to understand the difference between transaction vs explicit locking, a lot of people assume too much from transaction and it will eventually breaks in prod.

masklinn 3y ago
> Index on Boolean is useless, it's an easy mistake that will take memory and space disk for nothing. However if the field is highly biased (e.g. 90 or 99% one value) it can be useful to create a partial index on the rarer value. Though even better is to create a partial index on the other stuff filtered by that value, especially if the smaller set is the commonly queried one (e.g. soft-deletes).
- Waterluvian 3y ago
  Yeah. Finding ”open” tickets, for example. There’s actually some really good cases to index on a Boolean.
  - j45 3y ago
    Nice example.
  - WirelessGigabit 3y ago
    We work in different places. Here the index in closed tickets would be smaller. But you know, some sales guy called and they want this little feature NOW.

topmax 3y ago

What about `pg_notify`? I just want to use it to replace my kafka server which is lite overload but costs much.

klysm 3y ago
Take a look into logical replication. You can even send messages over it
MrPowerGamerBR 3y ago
If you are using NOTIFY/LISTEN, keep track to check if your database does not have any long running queries. If you end up getting PostgreSQL to vacuum freeze your tables while the long running query is active, PostgreSQL will delete files from the pg_xact folder and that will bork out any LISTEN query, until you fully restart the database.
- philsnow 3y ago
  > PostgreSQL will delete files from the pg_xact folder and that will bork out any LISTEN query, until you fully restart the database that sounds like a bug; are you aware of an issue/ticket tracking that?
  - MrPowerGamerBR 3y ago
    While I haven't found an "issue" talking about this (sorry, I don't know how PostgreSQL tracks open bugs), they do know about it since I already seen that issue being talked about on PostgreSQL's mailing list. Here's my issue on StackExchange, for anyone that wants to delve deeper into my issue: https://dba.stackexchange.com/questions/325104/error-could-not-access-status-of-transaction-could-not-open-file-pg-xact-0029 https://dba.stackexchange.com/questions/325104/error-could-n... Here's an thread talking about the issue, while OP's issue doesn't seem to match exactly what I was experiencing, one of the replies describes my exact issue: https://postgrespro.com/list/thread-id/2546853 https://postgrespro.com/list/thread-id/2546853

oofnik 3y ago

At my first DevOps job we had defined a function which deleted old partitions from time to time. If you invoked it manually because of some automation failure with the wrong arguments, it had a habit of nuking production data, which, needless to say, happened at least once. Naturally, the function was called footgun. We can pretend that the proliferation of managed databases, newfangled NoSQL datastores, and other abstractions preclude the need for accumulated empirical observation across a range of tech stacks and time, but sometimes there's really no substitute for greybeard wisdom.

ahachete 3y ago

The recommendation for work_mem doesn't account for all the possible cases. It is already noted elsewhere on this thread [1] that the use of memory per connection could be higher than work_mem, and this is true even if you don't use stored procedures, as the memory incurred can be on a per-query node. So it can be a multiple of work_mem per connection. But there's a factor that even worsens this: parallel query, which is typically enabled by default, and will add another multiple to work_mem. Tuning work_mem is a hard art, and requires a delicate balance between trying to optimize some query's performance (that could avoid touching disk or using some indexes) vs the risk of causing db-wide errors like OOMs (very dangerous) or running out of SHM (errors only on queries being run, but still not desirable). So I normally lean on being quite conservative (db stability first!) so I divide the available memory (server memory - OS memory - shared_buffers and other PG buffers memory) by the number of connections, also divided by the parallelism and by another multiple factor --and then leave some additional room. In any case I'd recommend reviewing the detailed information, suggestions and links on the topic on postgresqlCONF [2] (disclaimer: a free project built by my company) [1]: https://news.ycombinator.com/item?id=35697986 https://news.ycombinator.com/item?id=35697986 [2]: https://postgresqlco.nf/doc/en/param/work_mem/ https://postgresqlco.nf/doc/en/param/work_mem/ (edit: formatting)

slig 3y ago

Anyone using StackGres/PGO can confirm if they have better defaults?

peter_retief 3y ago

Handed down to me by the grey beards! I like that. Postgres default settings are not the best, I remember moving from mysql to postgres some time back and discovered this. I always wondered why the default would not work best for most deployments? Anyone know why this is so?

rwmj 3y ago

The main tip I learned from using PostgreSQL (or relational databases in general) is never use an ORM. They cause far more trouble than they are worth and it's far easier to see what is going on when you're writing SQL queries directly.

eitland 3y ago
Might be a good idea if you arr the only one on the team or if everyone is like you. If not, be prepared to check for sql injection vulnerabilities the first n PRs from any new team member. Also to explain how to do it and your reasoning for it.
- rwmj 3y ago
  Kind of goes without saying that any framework/library you use must not allow you to write SQL injection vulnerabilities, and if it does you should stop using it right now.
  - steve-chavez 3y ago
    SQL injection is always possible with an ORM, since they always allow executing raw SQL as an escape hatch.
    - eitland 3y ago
      True. But unlike with the alternative that many end on, raw sql, it doesn't funnel anyone into a place where writing "select * from sometable where id=" + id feels like a logical next step unless you actually have studied the subject or read the manual ;-)
sodapopcan 3y ago
ORMs can be great in many situations. Any ORM worth its salt has a way to spit out exactly what SQL it is generating. I have worked on apps, though, where we end with a disproportionate amount of raw SQL so obviously in those scenarios they become useless. It's almost always been writing some sort of reporting system in Postgres instead of using a column store, though.

eqefqe 3y ago

Things that Postgresql should address: - Start compiling queries and functions to native code and hash them just like other DBs - Improve connection scalability by using tasks instead of threads (each thread needs a lot of RAM!) - Automatically maintained clustered index (like in SQL Server) would be nice to have

reese_john 3y ago

> With that in place you could acquire events from the queue like so: UPDATE event_queue SET acquired_at = now() WHERE id IN ( SELECT id FROM event_queue WHERE acquired_at IS NULL ORDER BY occurred_at LIMIT 1000 -- Set this limit according to your usage ) RETURNING *; Would you need a FOR UPDATE in that subquery?

Mavvie 3y ago
No, because they're setting acquired_at which marks it as "handled". You only need FOR UPDATE (SKIP LOCKED) if you want to process the event inside a transaction; but the approach in the article is to just bulk "grab the events and mark them as done, atomically"

xnickb 3y ago

The "solution" for the number 4 is a pretty bad footgun in itself, as it is obviously prone to race conditions. Didn't read further.

dpedu 3y ago
Even within a transaction?
philbo 3y ago
Yep, a few people have made the same comment. I'll make a correction to the post soon, sorry that it upset you. As obvious as it seemed, I had no idea. Thanks for pointing it out though, feedback from experts is the big payoff to posting from my p.o.v. and now I know a thing which I didn't know before.

Topgamer7 3y ago

I found one yesterday. Names can only be like 62 characters long. Then it silently truncates then. At least when creating triggers that is.

ttfkam 3y ago

I'm surprised to see a discussion of queueing in Postgres without mentioning SKIP LOCKED.

fbn79 3y ago

I'm using last stable postgres but I can confirm you that point 7 still exists. here my case https://dba.stackexchange.com/questions/323960/query-using-view-very-slow-when-the-same-query-is-fast-using-with https://dba.stackexchange.com/questions/323960/query-using-v...

Calamitous 3y ago

Most common one I’ve seen in the last 5-10 years: using a JSON column instead of putting in a lookup table, or instead of properly analyzing and normalizing your data. That’s a mistake that you’ll be paying for for a while.

lopatin 3y ago
How much slower is it in your experience?
- pramsey 3y ago
  If you let the JSON blobs grow past the page size the cost of recovering TOAST tuples can be 10x reading main storage. Also JSON is fundamentally de-normalized so you can incur scan and read costs just from hauling out duplicate values where a nice normalized lookup would be snappy. And finally JSON recovery is going to pull the whole object every time, even though you are probably only interested in one element of the object, so again, higher recovery times compared to an equivalent normalized model.
- Calamitous 3y ago
  It’s not even the speed, it’s writing queries. The syntax is clunkier, and since you don’t have a nice clean schema, you end up doing a lot of jumping through hoops to check for the presence of keys/proper data types, etc. Or doing your basic data validation in your app instead of letting the database do its job.

keitmo 3y ago

Here's one that bit us a few years ago: SEQUENCEs, used to implement SERIAL and BIGSERIAL primary keys, are not transacted. "BEGIN; {insert 1,000,000 rows}; ROLLBACK" always adds 1,000,000 to the table's primary key SEQUENCE, despite the ROLLBACK. Likewise for upsert (via INSERT ON CONFLICT). The end result: A table's SERIAL (32-bit signed integer) primary key can overflow even when it contains far fewer than 2^31 rows.

andorov 3y ago
this also matters if you do a lot of upserts on a table that are predominantly updates. postgres requests an id from the sequence for each row of the incoming data ahead of time since it doesn't know which rows are updates and which are inserts. the sequence doesn't reset down for the unused so this can eat through it unexpectedly quickly. if you hit the max integer for the sequence and need space to implement a fundamental fix you can quickly change the sequence to start at -1 and go down. there's no issue with negative ids since they're also integers.
skeletal88 3y ago
But that is the point of serials, that they ignore transactions and are monotonically increasing.
hot_gril 3y ago
Yes. Another reason I blindly use `bigserial` as the PK for everything.

Mavvie 3y ago

> Setting acquired_at on read guarantees that each event is handled only once. After they've been handled, you can then delete the acquired events in batches too (there are better options than Postgres for permanently storing historical event data of unbounded size). This bothers me. It's technically true, but ignores a lot of nuance/complexity around real-world event processing needs. This approach means you will never be able to retry event processing in case it fails (or your server is shut down/crashes). So you either have to update the logic to also process events where "acquired_at is older than some timeout", which breaks your "handled only once" guarantee, or you can change to a SELECT FOR UPDATE SKIP LOCKED approach which has its own problems like higher database resource usage (but at least it won't process a slow job twice at the same time).

philbo 3y ago
Yep, a few people have mentioned this to me here and on Reddit. I didn't know about the issues with the approach I proposed, so was pleased to read the comments. Will add a correction to the post as soon as I have a sec, thanks.
- Mavvie 3y ago
  Thank you! It was a great article, and definitely pointed out a few things I'm doing wrong in my Postgres setups.

claytongulick 3y ago

My rule of thumb is to use DB functions for writes that require good data consistency, and for reads that are join heavy. For things like formatting and transforming data, I prefer to have that work done on the client (web browser) if it's for a presentation concern, or middle tier if it's for an integration. My theory there is "don't make the database work hard". Use it for what it's great at: fast reads and data consistency. DB functions and sprocs are a great way to reduce latency and load, and boost performance if used for the right things. An easy rule of thumb is that if my function isn't using SQL as the language, I need to really think about whether it belongs in the DB. That all being said, I do use udfs to construct JSON results for some reports also, when it makes sense based on the params and number of round-trips to the server it would cause in the middle or UI tiers. Even though it violates some of the things above, it's really damn convenient and usually limited to low-traffic queries.

philsnow 3y ago

A writing tip: even in lists of "don't"s like this, find a way to write directives/imperatives in a positive sense. Asking readers to keep mentally flipping the sense of the thing you're telling them to do just adds cognitive load and makes it harder for them to pay attention to what you want them to pay attention to. Write "do"s, not "don't"s.

blocko 3y ago
I mostly agree, but there is one slight benefit: whenever I read one of these articles, each topic acts as a little quiz where I get to test whether I think it's a do or a don't before seeing the explanation
hot_gril 3y ago
Glad I wasn't the only one confused by this.
philbo 3y ago
Yeah, a lot of people have said this. I was wary of asserting "do's" because I'm not a Postgres expert. It felt more honest to phrase stuff in terms of my own mistakes and "don't do what I did", but of course that's confusing. And then I ruined it by still asserting "do's" in the bodies of some sections.
- hot_gril 3y ago
  I think the "don't"s are fine, but each bullet should say "don't" in that case.
  - philsnow 3y ago
    That would also work well, yeah

lbriner 3y ago

One thing that caught me out is that if you are doing an operation on a string like "lower(email)" then the query planner will not use an index on the email column, instead you would need an index on "lower(email)", which is fine if you always access it in the same way but otherwise requires multiple indexes to get coverage in all your scenarios. There are also plenty of other weird planner choices which I can't work out but which were much easier to understand in SQL Server. Sometimes, the smallest change will stop using an index e.g. using LIMIT on a query can completely bypass an index.

deleted 3y ago

[deleted]

hot_gril 3y ago

The two biggest ones imo were not mentioned: 1. Contrary to popular belief, Postgres isn't fully ACID (specifically the "I") with the default isolation mode. For example, selecting the sum of a column then inserting conditionally on that creates a race condition. Serializable mode is fully isolated, but it has many caveats and shouldn't be used often, so you should instead become familiar with what's isolated and what isn't. See https://www.postgresql.org/docs/current/transaction-iso.html https://www.postgresql.org/docs/current/transaction-iso.html 2. timestamp (without time zone) is bad; always use timestamptz, no exceptions. Unintuitively, timestamp (without time zone) is the one that makes your DB's time zone affect your selected data. Neither one actually stores a time zone, it's just a difference in output formatting. This is a moot point if your DB's locale is set to UTC, but that's not the default.

klysm 3y ago
> it has many caveats and shouldn't be used often I’d argue it isn’t used enough given its isolation advantages
- hot_gril 3y ago
  Serializable mode is only useful in low throughput cases. You take a big performance hit and can easily have a bunch of queries waiting one each other until the queue fills up. I haven't used it in forever, I just make sure my stuff works with the default isolation mode. You also can't slap it on like a band-aid for unsure users. The DB client has to implement retry logic, which most Postgres libs don't seem to have, and the code has to careful not to cause side effects inside the retriable area. Spanner and some other DBMSs are fully serializable always. They do a better job of that than Postgres, I guess cause they focus on it, but they're still a lot slower in the end. What would be cool is some extension to watch your non-serializable transactions for concurrency anomalies like 1% of the time and use that tiny sample to alert you of race conditions. Like a thread sanitizer does for code. Does that exist??
  - klysm 3y ago
    Yes you take the performance hit, but many workloads are low enough throughput for it to be completely fine. I’m not sure how you could detect such anomalies though without running the same serialization checking logic within postgres

jwsteigerwalt 3y ago

This list conceptually applies to just about any RDBMS. NOTIFY is incredibly powerful and when used prudently, can be a fantastic solution to some business problems.

anthonyfloyd 3y ago

[dead]

TheSniffer55 3y ago

[dead]

DoodieButt33 3y ago

[dead]

McPoopie24 3y ago

[dead]

TheBrickStall 3y ago

[dead]

StinkyPete56 3y ago

[dead]