Removing A Lot of Old Data (But Keeping Some Recent)

  1. Create a new table exactly like the first one
  2. Set ownership/privileges on the new table to match the old table
  3. Have the old table INHERIT the new table
  4. Swap old and new table names
  5. Move the data you want to keep from the old table to the new one
  6. Drop the old table
CREATE TABLE public.notifications_new (LIKE public.notifications INCLUDING ALL);

ALTER TABLE public.notifications_new OWNER TO sysadmin;

GRANT select ON public.notifications_new TO read_only
GRANT select, insert, update, delete ON public.notifications TO app_user;
GRANT all ON public.notifications TO admin;

ALTER TABLE public.notifications INHERIT public.notifications_new;

BEGIN;
LOCK TABLE public.notifications IN ACCESS EXCLUSIVE MODE;
LOCK TABLE public.notifications_new IN ACCESS EXCLUSIVE MODE;
ALTER TABLE public.notifications RENAME TO notifications_old;
ALTER TABLE public.notifications_new RENAME TO notifications;

COMMIT; (or ROLLBACK; if there's a problem)
WITH row_batch AS (
SELECT id FROM public.notifications_old WHERE updated_at >= '2016-10-18 00:00:00'::timestamp LIMIT 20000 ),
delete_rows AS (
DELETE FROM public.notifications_old o USING row_batch b WHERE b.id = o.id RETURNING o.id, account_id, created_at, updated_at, resource_id, notifier_id, notifier_type)
INSERT INTO public.notifications SELECT * FROM delete_rows;

--

--

--

Database Admin w/ OmniTI & Player of Way Too Many Video Games

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Git Tutorial

Querying Multidimensional Data in Swift

How a manager can use OBS or Loom.

Why Everyone Should Learn How To To Program

why everyone should learn to code

PEGA Robotic Studio RPA

ROS2 support for Zethus

Java Exception Hierarchy

Basic CSS Terms

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Keith Fiske

Keith Fiske

Database Admin w/ OmniTI & Player of Way Too Many Video Games

More from Medium

One Curse and One Blessing of Being a Pet’s Guardian

Will #MU break $10?

Eat that frog with a Pomodoro

3 lessons from my 3 hour dinner with Chris Do