A colleague gave me the following query to run: DELETE FROM data_log_20msec_table WHERE (log_id IN (SELECT log_id FROM data_log_20msec_table ORDER BY log_id DESC OFFSET 10000000))
log_id is the primary key (big serial) data_log is the table described below This query keeps the most recent 10 million rows and deletes the remaining ones. If I call this once a minute, it would be deleting 3000 rows each time. Is there a way to optimize this statement? Postgres was setup with default configuration. Is there anything we can change in the configuration to make this run more efficiently? The table is defined as below: CREATE TABLE data_log_20msec_table ( log_id bigserial NOT NULL, timestamp_dbl double precision, data bytea, CONSTRAINT data_log_20msec_table_pkey PRIMARY KEY (log_id) ) WITH (OIDS=FALSE); ALTER TABLE data_log_20msec_table OWNER TO postgres; -- Index: data_log_20msec_table_timestamp_index -- DROP INDEX data_log_20msec_table_timestamp_index; CREATE INDEX data_log_20msec_table_timestamp_index ON data_log_20msec_table USING btree (timestamp_dbl); Is there anything we can do here that can optimize the deletion of rows? Much thanks to anyone who can help us out. Regards, Dave Original Post: I am inserting 250 rows of data (~2kbytes/row) every 5 seconds into a table (the primary key is a big serial). I need to be able to limit the size of the table to prevent filling up the disk. Is there a way to setup the table to do this automatically or do I have to periodically figure out how many rows are in the table and delete the oldest rows manually? ________________________________ This electronic mail message is intended exclusively for the individual(s) or entity to which it is addressed. This message, together with any attachment, is confidential and may contain privileged information. Any unauthorized review, use, printing, retaining, copying, disclosure or distribution is strictly prohibited. If you have received this message in error, please immediately advise the sender by reply email message to the sender and delete all copies of this message. THIS E-MAIL IS NOT AN OFFER OR ACCEPTANCE: Notwithstanding the Uniform Electronic Transactions Act or any other law of similar import, absent an express statement to the contrary contained in this e-mail, neither this e-mail nor any attachments are an offer or acceptance to enter into a contract, and are not intended to bind the sender, LeTourneau Technologies, Inc., or any of its subsidiaries, affiliates, or any other person or entity. WARNING: Although the company has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments.
