Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Ways to shoot yourself in the foot with Postgres

philbooth.me

Post reply on HN
Print view View original

211–220 of 329 posts

Re: Ways to shoot yourself in the foot with Postgres

#211

Post by jaggederest »

hans_castorp wrote: post #97
jaggederest wrote: post #92

Earlier quoted context omitted.

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. 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.

> TRUNCATE is not MVCC-safe. After truncation, the table will appear empty to concurrent transactions, if they are using a snapshot taken before the truncation occurred.

Sorry, that's what I mean. It's safe in the sense that you can roll it back, but it's not safe in the sense that other concurrent transactions will see the table as empty if they are long-running.

Re: Ways to shoot yourself in the foot with Postgres

#212

Post by skeletal88 »

keitmo wrote: post #177

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 ro…

But that is the point of serials, that they ignore transactions and are monotonically increasing.

Re: Ways to shoot yourself in the foot with Postgres

#213

Post by jaggederest »

thomasjudge wrote: post #152
jaggederest wrote: post #57

Earlier quoted context omitted.

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.

Sybase, actually

The first Sybase grew out of the Ingres project at UCB, so transitively, SQL Server is also a descendant.

Re: Ways to shoot yourself in the foot with Postgres

#214

Post by jaggederest »

Whitespace wrote: post #124
jaggederest wrote: post #19

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…

The article and the comments here don't make it clear why running it in production shouldn't be done. If slow_query is already running in production, why would running EXPLAIN ANALYZE slow_query be bad? Is the overhead of running EXPLAIN ANALYZE so much worse than running slow_query itself?

No, it's really not, and that's why I say it must sometimes be done. Certainly if you're running tens or hundreds of copies of the query per minute, one more won't hurt (much).

The real problem you run into is when the query in question is doing something pathologically bad - locking a bunch of tables and then grinding away for an hour, which effectively is a denial of service attack.

Re: Ways to shoot yourself in the foot with Postgres

#215

Post by chasd00 »

Whitespace wrote: post #124
jaggederest wrote: post #19

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…

The article and the comments here don't make it clear why running it in production shouldn't be done. If slow_query is already running in production, why would running EXPLAIN ANALYZE slow_query be bad? Is the overhead of running EXPLAIN ANALYZE so much worse than running slow_query itself?

one thing to consider is the person who needs to run explain analyze may not have any access whatsoever to the production database. Also, there may be no process in place to get someone to run it on prod on their behalf. Finally, if there is a DBA on production they may just say no.

Re: Ways to shoot yourself in the foot with Postgres

#216

Post by pier25 »

h1fra wrote: post #89

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…

> Postgres as a queue is definitely working and scales pretty far

You mean with triggers and listen/notify ?

Re: Ways to shoot yourself in the foot with Postgres

#217

Post by lbriner »

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.

Re: Ways to shoot yourself in the foot with Postgres

#218

Post by ellisv »

masklinn wrote: post #205
ellisv wrote: post #187

Earlier quoted context omitted.

Also good to remember that booleans can have 3 values: true, false, or null. Creating a partial index on `WHERE NOT NULL` can be helpful too.

Postgres uses distinct nulls. I've not checked, but I'd assume postgres simply does not index nulls, as it can't find them again anyway (unless you use the new "NULLS NOT DISTINCT" anyway). I think you need a separate index on the boolean IS NULL (which should probably be a partial index on whichever of IS NULL and IS NOT NULL is better).

You're correct, thanks for noting this – I had it backwards.

Re: Ways to shoot yourself in the foot with Postgres

#219

Post by ellisv »

pier25 wrote: post #216
h1fra wrote: post #89

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…

> Postgres as a queue is definitely working and scales pretty far You mean with triggers and listen/notify ?

Probably meant as something like this: https://www.crunchydata.com/blog/message-queuing-using-nativ...

But I find that listen/notify seem to be drastically underused.

Re: Ways to shoot yourself in the foot with Postgres

#220

Post by icedchai »

ak39 wrote: post #26

"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.)

I'd say because it is a much more specialized skill than programming in python / ruby / JS / or whatever your app language. Ideally, I would say "use the best tool for the job", which may very well be a stored procedure for data locality reasons, but practically speaking, with a larger team, you may be asking for trouble.
Post reply on HN