Re: using a postgres table as a multi-writer multi-updater queue
От | Steve Petrie, P.Eng. |
---|---|
Тема | Re: using a postgres table as a multi-writer multi-updater queue |
Дата | |
Msg-id | 44E2F15E670946229EB4D3BD2B432D35@Dell обсуждение исходный текст |
Ответ на | using a postgres table as a multi-writer multi-updater queue (Chris Withers <chris@simplistix.co.uk>) |
Ответы |
Re: using a postgres table as a multi-writer multi-updater
queue
|
Список | pgsql-general |
Thanks to Jeff for the helpful response. My remarks are below. ----- Original Message ----- From: "Jeff Janes" <jeff.janes@gmail.com> To: "Steve Petrie, P.Eng." <apetrie@aspetrie.net> Cc: "Tim Uckun" <timuckun@gmail.com>; "Merlin Moncure" <mmoncure@gmail.com>; "John R Pierce" <pierce@hogranch.com>; "PostgreSQL General" <pgsql-general@postgresql.org> Sent: Thursday, November 26, 2015 2:07 AM Subject: Re: [GENERAL] using a postgres table as a multi-writer multi-updater queue > On Wed, Nov 25, 2015 at 8:39 PM, Steve Petrie, P.Eng. > <apetrie@aspetrie.net> wrote: >> >>> You don't ever want to delete from such a table so you need to set up >>> something which allows you to truncate the tables when you no longer >>> need >>> them. ... > Truncation is far more efficient than deletion + vacuuming. If you > are running on the edge of your hardware's capabilities, this > efficiency is important. But if you are not on the edge, then it is > not worth worrying about. This is what I was hoping to learn -- because I doubt my app workload will ever approach the edge of hardware capability. > Just make sure your autovacuum settings are > at least as aggressive as the default settings. > I'll keep that in mind. And by a happy coincidence, in another recent forum thread (26 Nov 2015, subject "Re: [GENERAL] Re: [webmaster] How to commit/vacuum a batch of delete statements in a postgresql function") there is advice from Adrian Klaver to about the need to execute VACUUM outside of a transaction block. >> My plan was always, to avoid eventual exhaustion of the SERIAL sequence >> number integer value series, by swapping in during the periodic app >> shutdown, a freshly truncated postgres <eto_sql_tb_session_www> table. > > I'd make the serial column and bigserial, and then forget about it. I considered using bigint, but decided against doing so for three reasons. 1. int rep is already going to be way more precision than is needed, 2. avoid the extra resource consumption incurred by bigint as compared to int, and 3. keep strictly 32-bit compatible with my 32-bit Win XP / PHP development system. In fact the PHP app is programmed to terminate abnormally on detecting a postgres SERIAL sequence number that exceeds the maximum positive value of a strictly 32-bit signed integer. > > Cheers, > > Jeff Steve Petrie, P.Eng. ITS-ETO Consortium Oakville, Ontario, Canada apetrie@aspetrie.net
В списке pgsql-general по дате отправления: