New answers tagged

0

Put auto_explain into shared_preload_libraries in postgresql.conf and restart PostgreSQL. Set auto_explain.log_min_duration = 0 auto_explain.log_nested_statements = on auto_explain.log_analyze = on auto_explain.log_buffers = on in postgresql.conf (and reload). Then run the slow query again. This will log all SQL statements and their execution plan in ...


1

Please try this: DELETE FROM journal WHERE id NOT IN ( SELECT j.id FROM journal j WHERE j.created_at >= 636742944000000000 ORDER BY j.created_at DESC ) Please make sure to create the index on created_at and id. In additional, I think id in a table should be unique/primary key.


1

Your query eliminates user_id from the passed array. Typically, you'd want to show those with a count of 0. LEFT JOIN LATERAL .. ON true, followed by COALESCE takes care of that. If you actually want those eliminated switch to CROSS JOIN and drop COALESCE, same performance: SELECT t.user_id, COALESCE(unread_count, 0) AS unread_count FROM unnest('{200 user ...


2

Your explain plan is a bit confusing, as it looks like the index scan is getting the data for all 200 user_ids at once, but then doing that 200 times. But doing the experiment, that is not what it is doing, each iteration of the nested loop is getting the data for one user_id from that list, not the whole list. So it is just a presentation issue in the ...


5

All tables will use bigint as primary key, and all (indirectly) inherit from the same base table I am not sure I like this "all tables inherit from the base table", but given that, it sounds doable with Postgres: To generate the primary key for all tables, create one sequence: create sequence one_for_all as bigint; Create the base table using that ...


1

The database generates WAL for the entire database instance. It is the logical sender's job to sort them out to restrict and construct what gets sent. I don't think there is a way to get the logical sender to work ahead. You should look at why it is falling behind, not why there is a lot of WAL.


1

This is essentially for free, and you should enable it in all databases unless the workload is designed to have long lock waits regularly (which would be a design bug). These error messages are written by the deadlock detector, which is expensive to run, which is why PostgreSQL doesn't run it unless someone has to wait deadlock_timeout seconds for a lock. 1 ...


0

There are many possibities! PITR vs STANDBY PITR & Standby are complementary solutions: Standby is interesting to recover from: physical problem: server crash, datacenter unavailabity and offer quick switchover procedures PITR will help you in case of data damage: If someone performs an operation that damages your data (DROP TABLE , UPDATE with a ...


0

You will probably be faster with NOT EXISTS: DELETE FROM journal WHERE NOT EXISTS (SELECT 1 FROM journal AS j WHERE j.id = journal.id AND j.created_at >= 636742944000000000);


0

I think you already are doing it the best way. DELETE FROM journal WHERE id NOT in (SELECT id FROM journal GROUP BY (id) HAVING MAX(created_at) >= 636742944000000000);


Top 50 recent answers are included