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?
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 VIEWwhich create a new table and populate it can use a parallel plan.
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.
If the table is empty before you begin, the result will be the same.