What about `pg_notify`? I just want to use it to replace my kafka server which is lite overload but costs much.
Ways to shoot yourself in the foot with Postgres
91–100 of 329 posts
Re: Ways to shoot yourself in the foot with Postgres
#92Using `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…
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.
Re: Ways to shoot yourself in the foot with Postgres
#93"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.)
In theory it's attractive to perform business logic in the DB with functions and procedures but in practice the "devops experience" is painful.
Functions are restricted in their available languages, ability to A/B test and scale. There's also complexity entailed by having two sources of business logic because people can forget which one does what, needing to constantly switch back and forth between procedures and app code when debugging.
Additionally the networking, resiliency and update patterns of databases are often not well suited to functions. You may want your functions to have public internet access but not you DB or rollbacks of your data but not function versions.
All of these issues can be overcome by people who're confident DBAs and sysadmin types in addition to being application developers but that's a small group of people.
I wish there were more startups in this area working to improve the developer experience of DB functions because you're correct about the superior performance and I'm pretty sure most of the issues I raised could be solved with well thought out tooling. However, at the moment such tools don't exist so these functions are painful to use.
Re: Ways to shoot yourself in the foot with Postgres
#94First 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.
I am of the firm opinion that Postgres + Redis are basically the only DBs you ever need.
[dead]
Re: Ways to shoot yourself in the foot with Postgres
#95First 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.
I am of the firm opinion that Postgres + Redis are basically the only DBs you ever need.
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);`
Re: Ways to shoot yourself in the foot with Postgres
#96Few 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…
> 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).
Re: Ways to shoot yourself in the foot with Postgres
#97Using `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…
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…
TRUNCATE is absolutely transactional. You can rollback a TRUNCATE statement if you run it in a transaction.
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.
Re: Ways to shoot yourself in the foot with Postgres
#98Earlier quoted context omitted.
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.
Can you point to some good resources on how to write better postgres queries? Or give examples of common pitfalls?
[deleted]
Re: Ways to shoot yourself in the foot with Postgres
#99Here'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
The defaults do suck but common storage options like SSDs or Elastic Block Storage still do sequential IO substantially faster than random.
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.
Re: Ways to shoot yourself in the foot with Postgres
#100Earlier quoted context omitted.
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 ne…
You do not need to copy the DB, see pg_upgrade
That depends on the circumstances. To quote the manpage:
> If you use link mode, the upgrade will be much faster (no file copying) and use less disk space, but you will not be able to access your old cluster once you start the new cluster after the upgrade. Link mode also requires that the old and new cluster data directories be in the same file system. (Tablespaces and pg_wal can be on different file systems.) Clone mode provides the same speed and disk space advantages but does not cause the old cluster to be unusable once the new cluster is started. Clone mode also requires that the old and new data directories be in the same file system. This mode is only available on certain operating systems and file systems.