database column order

[f.holop]

hello there,

an interesting topic popped up on HN:

Column order in PostgreSQL does matter
https://news.ycombinator.com/item?id=32067473

I was wondering how django does this, so I looked at some of the sql
generated by migrations in my apps (particularly initial ones) and if I
read the SQL correctly django mostly matches column order with the model
fields order with the notable exception of foreign key(s). Those being
(by default and quite often) fixed width, and it seems that they might
benefit from the order described in the articles, I was wondering what
was the reason for putting them at the end.


-f
--

[charettes]

Foreign keys are added at the end in cases where there are reference cycles between models that must be broken. Instead of creating the column first and then creating the foreign key constraint the migration framework opted to combine both operations as it simplifies a few things.

If you add fields to one your existing models the migration framework will add them at the end of your table as that's the most straightforward way of doing so. Of the top of my head only MySQL supports a syntax to add a column to an existing table at a specific position and doing so basically rebuilt the table (prior to 8.0.29[0]) which is what the Postgres wiki suggests doing if you really want a particular physical layout[1].

As pointed out in the comments on HN there is some ongoing work to lift this limitation on Postgres[2] but even if this lands in the next version of Postgres we still have to figure out what should be done with historical models that have a different field layout than their table. The migration framework also doesn't have any notion of field ordering right now so that would also be work that needs to be done.

I'm of opinion that if you are experiencing performance issues due to the physical layout of a particular table managed by Django you're likely better off not relying on the automatically generated migrations that Django provides and use a mix of `RunSQL` and `SeparateDatabaseAndState` to achieve what you're after.

Cheers,

Simon

[0] https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html#online-ddl-column-syntax-notes

[1] https://wiki.postgresql.org/wiki/Alter_column_position

[2] https://www.postgresql.org/message-id/flat/20220628083230.c4qhp4fdp3dp22ig%40jrouhaud

[Adam Johnson]

I agree with Simon here. I don’t think Django should account for postgres column order, especially when there’s no way to change column positions other than by rebuilding the table. By the time you have enough data for the space savings to be valuable, you probably need to use a careful strategy to rebuild the table, perhaps using a view as the Postgres wiki page says.

--
You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-develop...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/b4fd0636-f5d7-44bb-a3cb-130e21e18b57n%40googlegroups.com.

[f.holop]

charettes - Tue, 12 July 2022 at 13:11:14

> If you add fields to one your existing models the migration framework will
> add them at the end of your table as that's the most straightforward way of
> doing so. Of the top of my head only MySQL supports a syntax to add a

is it a correct takeaway, that while building an app and playing with
model fields left and right, before going live it's a good practice to
remove all migration files and let django recreate them and the DB?

in my understanding that will recreate the tables with a column order
closer to model fields order (plus reduce the number of migration
files), while flattening the migrations probably keeps the previous order?

> models that have a different field layout than their table. The migration
> framework also doesn't have any notion of field ordering right now so that
> would also be work that needs to be done.

i think the current situation is a kind of sweet spot where there is
room for a bit of manual "optimisation" by ordering the fields.
going web-scale will need more consideration by those lucky ones :}

> I'm of opinion that if you are experiencing performance issues due to the
> physical layout of a particular table managed by Django you're likely
> better off not relying on the automatically generated migrations that
> Django provides and use a mix of `RunSQL` and `SeparateDatabaseAndState` to
> achieve what you're after.

i think it's more about wasted disk space than raw performance (although
one is related to the other).

thank you for the indepth answer.

--
-f