I'm using pg_repack to order one table by a timestamp column, however I'm finding that after running pg_repack command it's not ordered at all.

I'm using this command

pg_repack --jobs 4 -t table_with_dates -e -o "start_time asc"

After about 10 minutes it finishes but when I query the first page of the table there is no order over "start_time" column.

page 0 rows

I'm using postgresql 9.6.3 and pg_repack 1.4.0

Is there something i'm missing here so I can get the correct data ordering?

Edit 1: Table definition

CREATE TABLE customer_service (
    customer_service_id character varying(32) NOT NULL,
    store_id character varying(32) NOT NULL,
    start_time timestamp without time zone NOT NULL,
    end_time timestamp without time zone NOT NULL,
    customer_id character varying(32) NOT NULL,
    service_id character varying(32) NOT NULL,
    CONSTRAINT customer_service_key PRIMARY KEY (customer_service_id),
CREATE INDEX customer_service_start_idx
  ON public.customer_service
  USING btree

 CREATE INDEX customer_service_customer_idx
  ON public.customer_service
  USING btree(customer_id);

CREATE INDEX customer_service_service_idx
  ON public.customer_service
  USING btree

SELECT performed by pg_repack:

LOG: (query) CREATE TABLE repack.table_6527203 WITH (oids = false) TABLESPACE pg_default AS SELECT customer_service_id, store_id, start_time, end_time, customer_id, service_id
FROM ONLY customer_service

LOG: (query) INSERT INTO repack.table_6527203 SELECT customer_service_id, store_id, start_time, end_time, customer_id, service_id
FROM ONLY customer_service

Seems like the insert into is not ordering the data?, as the order by is only present on the create table command

Edit 2: If I run

WITH dates_per_page AS (
  SELECT (ctid::text::point)[0]::bigint AS page_number, COUNT(DISTINCT date(start_time))
  FROM customer_service
) SELECT avg(count) FROM dates_per_page;

I get 15.340 as the result, I would expect a number closer to 1 here...

  • 1
    Can you show us what's the SELECT you performed to retrieve the data? The data might be in order (at least in terms of pages), even if it doesn't look like it. – joanolo Aug 1 '17 at 7:11
  • @joanolo the ctid shows these rows are in this physical order, doesn't it? – dezso Aug 1 '17 at 8:39
  • 1
    Could you please show the table definition (with indexes)? No screenshot, normal text, please. – dezso Aug 1 '17 at 8:43
  • I can confirm the missing ORDER BY clause is the one to blame here. – acmzero Aug 1 '17 at 16:58

This problem is fixed on the version 1.4.1 of pg_repack.

github issue for reference

  • Please accept your answer so that your question is marked as resolved – a_horse_with_no_name Nov 14 '20 at 11:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.