3 ms·

Love Postgres, but here's what I wish was different: RDS Proxy/PG Bouncer should be default connection behavior. Ideally no persistent connection at all, more

Love Postgres, but here's what I wish was different: RDS Proxy/PG Bouncer should be default connection behavior. Ideally no persistent connection at all, more akin to https would be great. Vacuuming is ridiculous. It doesn't make sense to me what could possibly take so long. It also doesn't make sense to me that it needs to be blocking (I understand that it's now parallelizable-ish). Using a comparatively slow interpreted language, I can iterate through millions of items, on disk, and do any number of things, within a few seconds at most. I have had databases with like, a few thousand items, somehow take hours upon hours to vacuum/analyze. Nested transactions would be great. I know there are savepoints but it doesn't work well when dealing with anything in parallel. And finally, my #1 complaint: Please let ME decide when to roll back/invalidate a transaction. If I want to write something like an upsert, maybe my code says "insert this record, and if I catch an unique constraint error, update the record." In Postgres, at the initial insert, because there's an error, it will just invalidate my transaction! I could have done 100 other things in this transaction so far, all invalidated because of a DB error. An error that I was expecting to catch and handle myself at the application level, and now the entire transaction needs to be rolled back. WHY?

koolba 3y ago
> RDS Proxy/PG Bouncer should be default connection behavior. Ideally no persistent connection at all, more akin to https would be great. That doesn't make sense. A database connection is inherently stateful as you run multiple commands in a transaction. > Vacuuming is ridiculous. It doesn't make sense to me what could possibly take so long. It also doesn't make sense to me that it needs to be blocking (I understand that it's now parallelizable-ish). Using a comparatively slow interpreted language, I can iterate through millions of items, on disk, and do any number of things, within a few seconds at most. I have had databases with like, a few thousand items, somehow take hours upon hours to vacuum/analyze. Routine vacuuming is not blocking (on VACUUM FULL to reclaim space is blocking). The entire storage approach has its warts, but works well for 99.99% of use cases. I'd argue that write amplification is a much larger problem. > Nested transactions would be great. I know there are savepoints but it doesn't work well when dealing with anything in parallel. What does it mean to work with a transaction in parallel? The A and I in ACID are for "Atomic" and "Isolation". > And finally, my #1 complaint: Please let ME decide when to roll back/invalidate a transaction. If I want to write something like an upsert, maybe my code says "insert this record, and if I catch an unique constraint error, update the record." In Postgres, at the initial insert, because there's an error, it will just invalidate my transaction! I could have done 100 other things in this transaction so far, all invalidated because of a DB error. An error that I was expecting to catch and handle myself at the application level, and now the entire transaction needs to be rolled back. WHY? That's exactly what using a SAVEPOINT does. The default of failing and trashing the connection state (until a ROLLBACK) is a sensible default. It also allows for command pipelining as you can send multiple commands and not worry about partial execution due to intermediate failure. If your application code is repeatedly failing then you should be fixing your application. There are many ways to perform consistent INSERT-or-UPDATE in PostgreSQL: https://www.postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT https://www.postgresql.org/docs/current/sql-insert.html#SQL-...
kstrauser 3y ago
I agree about PGBouncer. You absolutely want persistent connections, though: establishing a TLS connection is comparatively costly and you don't want to pay it more than you need to. It's been maybe 15 years since I've waited for a vacuum to finish outside of me doing a `VACUUM FULL` on an offline copy as an experiment. It's had subtransactions for years. It has an exception clause so you can catch errors and roll back. In the absence of explicit exception handling, it must roll back a transaction instead of committing who-knows-what to disk. That's the whole point of transactions.
mdavidn 3y ago
You should read the documentation for INSERT ... ON CONFLICT. https://www.postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT https://www.postgresql.org/docs/current/sql-insert.html#SQL-... I'm not sure what's happening with your VACUUM. It does not lock the table without the FULL parameter. Or perhaps your tables have too many indexes?

koolba 3y ago

> RDS Proxy/PG Bouncer should be default connection behavior. Ideally no persistent connection at all, more akin to https would be great. That doesn't make sense. A database connection is inherently stateful as you run multiple commands in a transaction. > Vacuuming is ridiculous. It doesn't make sense to me what could possibly take so long. It also doesn't make sense to me that it needs to be blocking (I understand that it's now parallelizable-ish). Using a comparatively slow interpreted language, I can iterate through millions of items, on disk, and do any number of things, within a few seconds at most. I have had databases with like, a few thousand items, somehow take hours upon hours to vacuum/analyze. Routine vacuuming is not blocking (on VACUUM FULL to reclaim space is blocking). The entire storage approach has its warts, but works well for 99.99% of use cases. I'd argue that write amplification is a much larger problem. > Nested transactions would be great. I know there are savepoints but it doesn't work well when dealing with anything in parallel. What does it mean to work with a transaction in parallel? The A and I in ACID are for "Atomic" and "Isolation". > And finally, my #1 complaint: Please let ME decide when to roll back/invalidate a transaction. If I want to write something like an upsert, maybe my code says "insert this record, and if I catch an unique constraint error, update the record." In Postgres, at the initial insert, because there's an error, it will just invalidate my transaction! I could have done 100 other things in this transaction so far, all invalidated because of a DB error. An error that I was expecting to catch and handle myself at the application level, and now the entire transaction needs to be rolled back. WHY? That's exactly what using a SAVEPOINT does. The default of failing and trashing the connection state (until a ROLLBACK) is a sensible default. It also allows for command pipelining as you can send multiple commands and not worry about partial execution due to intermediate failure. If your application code is repeatedly failing then you should be fixing your application. There are many ways to perform consistent INSERT-or-UPDATE in PostgreSQL: https://www.postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT https://www.postgresql.org/docs/current/sql-insert.html#SQL-...

kstrauser 3y ago

I agree about PGBouncer. You absolutely want persistent connections, though: establishing a TLS connection is comparatively costly and you don't want to pay it more than you need to. It's been maybe 15 years since I've waited for a vacuum to finish outside of me doing a `VACUUM FULL` on an offline copy as an experiment. It's had subtransactions for years. It has an exception clause so you can catch errors and roll back. In the absence of explicit exception handling, it must roll back a transaction instead of committing who-knows-what to disk. That's the whole point of transactions.

mdavidn 3y ago

You should read the documentation for INSERT ... ON CONFLICT. https://www.postgresql.org/docs/current/sql-insert.html#SQL-ON-CONFLICT https://www.postgresql.org/docs/current/sql-insert.html#SQL-... I'm not sure what's happening with your VACUUM. It does not lock the table without the FULL parameter. Or perhaps your tables have too many indexes?