1

Does pre-sorting a csv by a designated column and then inserting the data into postgres have the same effect as not pre-sorting, importing the data, creating the index and then CLUSTERing the table using that index?

2

Aside from the index you mentioned, and it being the default CLUSTER sort order now (Postgres remembers), both tables will turn out identical. Same order, no bloat.

Parallelism could potentially change this. But for now, writing queries are not parallelized. The manual:

Even when it is in general possible for parallel query plans to be generated, the planner will not generate them for a given query if any of the following are true:

  • The query writes any data or locks any database rows. If a query contains a data-modifying operation either at the top level or within a CTE, no parallel plans for that query will be generated. As an exception, the commands CREATE TABLE ... AS, SELECT INTO, and CREATE MATERIALIZED VIEW which create a new table and populate it can use a parallel plan.

So neither INSERT nor COPY use parallel queries and a single process writes rows as received, i.e. in the order of the CSV file - even if there are no formal guarantees. Tables are heaps in theory and Postgres (like most RDBMS) generally treats the physical order of rows as implementation detail. But in the current implementation order is preserved, and that won't change without good reason. Parallelism would be a good reason, but that's hard to implement for writing queries.

Related:

  • What do you mean with "aside from the index you mentioned" in the first sentence? – Laurenz Albe Oct 29 at 5:36
  • I mean the index he mentioned. It's there (and needed) in the 2nd scenario, but not in the 1st. – Erwin Brandstetter Oct 29 at 7:06
0

If the table is empty before you begin, the result will be the same.

  • Hmm, I wonder if this true for a partitioned table? – Colin 't Hart Oct 29 at 9:24
  • If you could CLUSTER a partitioned table, it would also be true. As it is, you get ERROR: cannot cluster a partitioned table. As it is, you have to cluster the partitions (which makes much more sense anyway). – Laurenz Albe Oct 29 at 11:08
  • 2
    A couple more caveats. His system sort utility needs to understand the .csv files the same way his loader does, and it must be using the same locale/collation. – jjanes Oct 29 at 11:59

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.