Hi Brian,
Thank you for the confirmation !
I agree, dropping and recreating index in production, and especially the primary key, isn’t something I would like to do.
It isn’t too long to recreate all the indexes?
I think we’ll be careful to minimize the update and delete from the tables to limit the fragmentation risk.
Sebastien
From: users-list [mailto:users-list-bounces+sebastien.raillard=passman.fr@monetdb.org] On Behalf Of Brian Hood
Sent: mardi 24 novembre 2015 17:58
To: Communication channel for MonetDB users
Subject: Re: Vacuum limitations
Hi Sebastien,
The way i have done this is DROP MY PRIMARY KEY constraint and all my indices and then vacuum and recreate them.
Don't like the idea of the dropping the constraint in especially a production environment.
sql>\d wifi_ippacket
CREATE TABLE "threatmonitor"."wifi_ippacket" (
"guid" CHAR(36) NOT NULL,
"recv_date" CHARACTER LARGE OBJECT,
"ip_df" VARCHAR(5),
"ip_dst" VARCHAR(15),
"ip_hlen" INTEGER NOT NULL,
"ip_id" INTEGER NOT NULL,
"ip_len" INTEGER NOT NULL,
"ip_mf" VARCHAR(5),
"ip_off" INTEGER NOT NULL,
"ip_proto" INTEGER NOT NULL,
"ip_src" VARCHAR(15),
"ip_sum" CHAR(10),
"ip_tos" INTEGER NOT NULL,
"ip_ttl" INTEGER NOT NULL,
"ip_ver" INTEGER NOT NULL,
CONSTRAINT "wifi_ippacket_guid_pkey" PRIMARY KEY ("guid")
);
CREATE INDEX "index_guid_wifiip" ON "threatmonitor"."wifi_ippacket" ("guid");
CREATE INDEX "index_ip_dst_wifiip" ON "threatmonitor"."wifi_ippacket" ("ip_dst");
CREATE INDEX "index_ip_src_wifiip" ON "threatmonitor"."wifi_ippacket" ("ip_src");
---
DROP INDEX "index_guid_wifiip";
DROP INDEX "index_ip_dst_wifiip";
DROP INDEX "index_ip_src_wifiip";
ALTER TABLE wifi_ippacket DROP CONSTRAINT wifi_ippacket_guid_pkey;
call sys.vacuum('threatmonitor', 'wifi_ippacket');
ALTER TABLE wifi_ippacket ADD CONSTRAINT wifi_ippacket_guid_pkey PRIMARY KEY (guid);
CREATE INDEX "index_guid_wifiip" ON "threatmonitor"."wifi_ippacket" ("guid");
CREATE INDEX "index_ip_dst_wifiip" ON "threatmonitor"."wifi_ippacket" ("ip_dst");
CREATE INDEX "index_ip_src_wifiip" ON "threatmonitor"."wifi_ippacket" ("ip_src");
sql>\d wifi_ippacket
CREATE TABLE "threatmonitor"."wifi_ippacket" (
"guid" CHAR(36) NOT NULL,
"recv_date" CHARACTER LARGE OBJECT,
"ip_df" VARCHAR(5),
"ip_dst" VARCHAR(15),
"ip_hlen" INTEGER NOT NULL,
"ip_id" INTEGER NOT NULL,
"ip_len" INTEGER NOT NULL,
"ip_mf" VARCHAR(5),
"ip_off" INTEGER NOT NULL,
"ip_proto" INTEGER NOT NULL,
"ip_src" VARCHAR(15),
"ip_sum" CHAR(10),
"ip_tos" INTEGER NOT NULL,
"ip_ttl" INTEGER NOT NULL,
"ip_ver" INTEGER NOT NULL,
CONSTRAINT "wifi_ippacket_guid_pkey" PRIMARY KEY ("guid")
);
CREATE INDEX "index_guid_wifiip" ON "threatmonitor"."wifi_ippacket" ("guid");
CREATE INDEX "index_ip_dst_wifiip" ON "threatmonitor"."wifi_ippacket" ("ip_dst");
CREATE INDEX "index_ip_src_wifiip" ON "threatmonitor"."wifi_ippacket" ("ip_src");
On Tue, Nov 24, 2015 at 3:35 PM, Sébastien RAILLARD (PASSMAN)