A Missing Link in Postgres 11: Fast Column Creation with Defaults

by grzm • 8 years ago

219 44 8 years ago

haney2• 8 years ago

This is such a helpful addition that will help avoid so much downtime. Thank you to the Postgres contributors who made it possible.

pilif3• 8 years ago

You could certainly get the same effect without downtime prior to this. It just needed a few extra steps and some cooperation by the application: adding a null able column without default has always been instantaneous, so you would do that, then update all the rows that are missing the value.

Finally, you add the default to the schema which will now be in the millisecond range because all/most rows have a default.

And then you add the not null constraint which will also be in the ms range.

You can do all of this in a transaction too because postgres' Schema modifications are transactional.

This is how I have added countless columns with default values without downtime over the last few years.

philliphaydon2• 8 years ago

This is incredibly slow updating a billion rows to give a value to a new column as a blanket update will still be run as an implicit transaction. As does the constraint check before the default is applied. It can also use a LOT of disk space to do this as well.

haney2• 8 years ago

Sorry, I should have clarified “accidentally downtime”. This doesn’t make anything new possibly, but will definitely eliminate a common way to shoot one’s self in the foot.

mgkimsal• 8 years ago

whoo doggy...

https://github.com/postgres/postgres/commit/16828d5c0273b4fe...

that was a lot of places to have to patch to make this happen!

nerdponx2• 8 years ago

I think the most impressive part of a well-maintained software project, to me, is the ability of the maintainers to keep patches organized and maintain coherent and comprehensive change logs. I can barely manage this in my own four-man team.

hvidgaard• 8 years ago

That is for better or worse, mostly down to someone actually spending the time to ensure that it happens.

LoSboccacc• 8 years ago

in a 4 man team the cost of maintaining program structure is enough to grind everything else to an halt. larger teams on larger projects have an incentive to keep the structure organized because without a clear codebase the project will grind to an halt and have the manpower to delegate maintenance tasks without killing the project speed.

philliphaydon2• 8 years ago

Oh god this is the best feature of pg11 IMO.

alangpierce3• 8 years ago

Hmm, something I'm not understanding from the description of the implementation is how it handles this sequence of operations:

1.) Start with a nullable column with some existing rows with null values.

2.) Make the column required with default=100. Postgres now marks atthasmissing as true and attmissingval as 100. Rows will be lazy-populated to 100, but aren't immediately modified.

3.) Make the column nullable again.

4.) Add some new null values.

5.) Make the column required with default=200.

At this point, the right behavior is that all originally-null rows from step #1 have value 100 and all rows added in step #4 have value 200, but that's not representable with the atthasmissing/attmissingval system. I guess maybe in steps 3 or 5 it needs to just bail out and do a full table rewrite?

I was thinking through this problem a while ago and one idea I had was that you could version your schema and have each row remember what the schema version was when the row was created. Then you could lazy-populate any null value by looking up the next default value for the column chronologically in schema history. But I guess maybe that's complex enough or would hurt common-case perf enough that Postgres realistically isn't ever going to implement it.

kdmytro• 8 years ago

The patch is about ALTER TABLE ADD COLUMN with non-null DEFAULT. Your step 1 does not have a default value so this patch will not affect the database behavior. Postgres will not do anything with atthasmissing or attmissingval on steps 2 and 5 because these are ALTER TABLE ALTER COLUMN DROP NOT NULL, which is out of scope of the discussed patch.

alangpierce3• 8 years ago

I see, thanks! I guess I was interpreting it as "this is the new way that default values work in Postgres", but really it's only specific schema changes that are affected.

colanderman• 8 years ago

Setting a column NOT NULL with a default does not replace NULLs with the default value, it throws an error.

da_chicken2• 8 years ago

As others have said, this is about adding columns entirely to tables, not modifying their schema. However....

This should fail at step 2 (and again at step 5). Modifying the default value of a column never modifies existing rows of a column. DDL (e.g., ALTER TABLE) should never modify data. You need DML (e.g., UPDATE) to do that. Example:

  CREATE TABLE product (
    id SERIAL PRIMARY KEY NOT NULL,
    quantity int NULL
  );

  INSERT INTO product (quantity) VALUES (NULL);

  ALTER TABLE product ALTER COLUMN quantity SET DEFAULT 0;
  ALTER TABLE product ALTER COLUMN quantity SET NOT NULL;

The second ALTER TABLE will fail with "Column 'quantity' contains null values."

  SELECT * FROM product WHERE quantity IS NULL;

And that will still return values.

You'd need to run this before modifying the NULL constraint to NOT NULL:

  UPDATE product SET quantity = 0 WHERE quantity IS NULL;

When you add or modify a constraint, the existing data must already conform to the new constraint or it will fail. When you modify a default value, you only affect future rows inserted into the table. A good RDBMS doesn't silently modify your data and errors when it tries to do something that you didn't explicitly tell it to do.

Remember that a column that doesn't have a default value specified actually has an implicit DEFAULT NULL default value (unless it's something special like SERIAL). That's how RDBMS know to throw an error when you don't specify a NOT NULL column. It tries to default the column to NULL and can't.

alangpierce3• 8 years ago

Thanks! I guess I had some gaps in my mental model of how postgres handles these things, this clears it up quite a bit!

Just to confirm/repeat back:

* When you say DDL operations (ALTER TABLE) will "never modify data", they're still allowed to add a column with a non-null default since that's not "modifying data" by your definition, it's picking the value to use when introducing new data. I shouldn't think of it as "set implicit null to 3", I should think of it as "set nothingness to 3", just like how it would "set nothingness to null" in normal column creation. But it would never be willing to "set null to 3" in a DDL operation.

* Postgres doesn't have an O(1) way (meaning: never does a table scan while holding a significant lock) to transition a nullable column to non-null, since it'll need to check the contents of the entire table. (Although maybe it could use an index?) I guess I was hoping that the default would help here, but really it only affects creation of new values.

* More generally speaking, Postgres's design direction is to put data integrity as a higher priority than scale. It'll do both if it can (like in the article), but there are still cases where schema changes might cause user disruption if done on a gigantic table. It could have been designed so that schema changes are always fast (like they tend to be in NoSQL systems), but that would come at the expense of other nice properties.

sudhirj• 8 years ago

This might have been the last case (for me at least) of having to choose a sub-standard database structure because of a limitation of the RDBMS. Now all migrations can add NOT NULL DEFAULT columns with impunity! Even on 100GB tables! Not sure exactly how much faster this will be, though.

wiz21c• 8 years ago

FTA :

>> and a gaping hole in Postgres’ operational story is filled

Are there any other holes one should be aware of ? I'm looking forward to migrate a database with ~100 tables many of which having ~100 millions rows and lot's of FK to Postgresql... The operational side of things is what I know the least. Any pointers ?

minxomat• 8 years ago

Lots of "Big Data" optimizations, like partitions becoming a first class feature, parallel queries and LLVM JIT on CPU-bound queries.

HPE did an excellent technical, no marketing BS writeup on PG11[0]. I highly recommend everyone read this.

[0] https://h50146.www5.hpe.com/products/software/oe/linux/mains...

brightball• 8 years ago

I'm about halfway through this now, but great read. Thank you for the link.

snaky• 8 years ago

While 100 millions rows is not exactly big number for PostgreSQL, so you might be well without partitioning, but still.

Despite the flexibility, this approach forces the planner to perform an exhaustive search and to check constraints on each partition to determine whether it should be present in the plan or not. Large amount of partitions may result in significant planning overhead.
The pg_pathman module features partition managing functions and optimized planning mechanism which utilizes knowledge of the partitions' structure.

https://github.com/postgrespro/pg_pathman

andruby2• 8 years ago

We have tables with 5B rows and don't use partitioning (yet). I do recommend testing and optimising the vacuum settings for large or often updated tables.

sandGorgon• 8 years ago

could you share some info about the vacuum settings ? especially for tables that have bulk updates

lozenge• 8 years ago

Is it OLTP (frequent writes) or analytics?

If OLTP the best advice is create a reasonable load tester and use it for all future schema migrations. This will find all unexpected table locks due to DDL commands like CREATE INDEX, ALTER TABLE etc.

garyclarke27• 8 years ago

Another example of how impressive Postgres is, well done. I’m always amazed by how much faster Postgres gets with each release - unlike most other softwares that often get slower. Many parallel processing improvements in 11 should keep this up. Array and Composite Domains, Stored Procedures, Awesome Window functions even better framing / exlusion. Very few weakness vs Oracle now, apart from one glaring weakness - Materialized views - lack of auto incremental refresh, severely limits their usefulness. (SQL Server Mat views also fairly useless beacuse they have so many limitations). Would love to see - NOT IN Foreign key constraint - I would imagine this would be quite easy to implement on the back of existing IS In Foreign Keys, would be incredibly useful. Also why not increase the default identifier length from 63 to 127? I know can be done on first install(only then) so is a pain to do, I find long constraint names useful for understanding errors.

iKevinShah2• 8 years ago

What I feel lacking in PG(though it can be easily managed by adding respective plugins) is the lack of unsigned int alike (from MySQL) AND UNIQUE columns be case-insensitive or an option of having case-insensitive unique columns while default be case-sensitive (As it is currently)

bmdavi3• 8 years ago

There's no 'CASE INSENSITIVE' option for unique indexes, but you can unique on lower(my_column) to achieve the safe effect.

Deimorz• 8 years ago

You can do case-insensitive columns by enabling the "citext" module, which is included but not enabled by default: https://www.postgresql.org/docs/current/static/citext.html

daigoba662• 8 years ago

This is cool.

And another example of a feature that’s only available in Microsoft SQL Server Enterprise Edition ($$$), but effctively free in Postgres.

grouseway2• 8 years ago

No, another example of a feature that SQL server has had for a decade or more (in standard edition). Maybe you're thinking of online index creation?

There are lots of reasons to criticize MSSQL (ridiculous cost, poor syntax/language extensions, awful text search, painful process for adhoc (non ETL) imports, etc...) but my decade old 2008 edition is still superior to PG in many ways. Why can't we have honest comparisons?

daigoba662• 8 years ago

Adding a NOT NULL column with a default value as an online DDL operation is in fact an Enterprise Edition feature.

rossdavidh• 8 years ago

I have personally witnessed bogus schemas percolating up into the Django models.py, because of problems related to this. Good to know that Postgres11 fixes that. The fact that PG continues to improve in substantial ways even still, is pretty impressive for such a venerable piece of software. Well done.

A Missing Link in Postgres 11: Fast Column Creation with Defaults

Related Stories