CODEWITHSUNDEEP
postgresqlamazon-aurora
Aramil Rey
Aramil Rey

Reputation: 3475

Can I configure Aurora DB max identifier length? How?

I have column names that are very long, regardless of the reason for this, I need to store very large names for columns (At least 128), and very large rows overall because of this

mysql has a strong limit of 64 for columns and 65,535 for rows

postgresql has a "configurable" limit of 63 for columns and couldn't find one for rows

My question is this: AWS Aurora offers both DBs, I've been researching and couldn't find whether this limit is configurable in their postgresql product, is it? How can I change this configuration?

Upvotes: 1

Views: 2250

Answers (2)

Jay
Jay

Reputation: 791

Stack: Nestjs Typeorm Postgres Aurora (AWS Service)

Hopefully, this helps others. In my case, I cannot reconfigure my Postgres instance because I use AWS. My solution was to shorten the Alias names for my eager-loaded relationships which were over 63 characters. I use the SnakeNamingStrategy for Typeorm which might be creating more issues as well. Instead of removing it, I extended it and wrote an overwriting function for eager-loaded aliases.

Here is my solution:

// short-snake-naming.strategy.ts

import { SnakeNamingStrategy } from "typeorm-naming-strategies";
import { NamingStrategyInterface } from "typeorm";

export class ShortSnakeNamingStrategy
  extends SnakeNamingStrategy
  implements NamingStrategyInterface
{
  eagerJoinRelationAlias(alias: string, propertyPath: string): string {
    return `${alias.replace(
      /[a-zA-Z]+(_[a-zA-Z]+)*/g,
      (w) => `${w[0]}_`
    )}_${propertyPath}`;
  }
}

// read-database.configuration.ts

import { TypeOrmModuleOptions, TypeOrmOptionsFactory } from "@nestjs/typeorm";
import { SnakeNamingStrategy } from "typeorm-naming-strategies";
import { ShortSnakeNamingStrategy } from "./short-snake-naming.strategy";

export class ReadDatabaseConfiguration implements TypeOrmOptionsFactory {
  createTypeOrmOptions(): TypeOrmModuleOptions | Promise<TypeOrmModuleOptions> {
    return {
      name: "read",
      type: "postgres",
      ...

      namingStrategy: new ShortSnakeNamingStrategy(),
    };
  }
}

The ShortSnakeNamingStrategy Class takes each eager-loaded relationship and shortens its name from Product__change_log___auth_user___roles__permissions to P_____c____a___r__permissions

So far this has generated no collisions and kept it below the 63 character max index length.

Upvotes: 1

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656804

The default maximum length for all PostgreSQL identifiers (not just columns names) is 63 bytes (not characters). This can be changed, like the manual informs where you linked:

can be increased by recompiling PostgreSQL

Or in more detail here:

The system uses no more than NAMEDATALEN-1 bytes of an identifier; longer names can be written in commands, but they will be truncated. By default, NAMEDATALEN is 64 so the maximum identifier length is 63 bytes. If this limit is problematic, it can be raised by changing the NAMEDATALEN constant in src/include/pg_config_manual.h.

Related:

So while regular PostgreSQL can be adjusted by editing the source and compiling, this is obviously not an option for a hosted service like Amazon Aurora Postgres. So, no, you cannot change this limit.

There is no maximum length for the sum of all column names of a table in Postgres. There is a maximum number of columns though. 1600 or less, depending on data types. Details:

Upvotes: 3

Related Questions