S3 links in RDBMS

Choosing the right way to declare the paths of S3 images in our database

The question of the datatype for the path link arose with the default choice: VARCHAR(255), which is generally not enough to safely store S3 links.

Especially with presigned URLs, which include the authentication tokens, expiration timestamps, and signature parameters. Typically exceeding 400-600 characters. On the other hand, if we go by the object text and have deep folder structures, then the S3 keys can be up to 1024 bytes long.

If we wrap our S3 bucket in a CDN (CloudFront), the URLs might be shorter, but adding any custom query parameters or versioning strings can still exceed the limit. So, VARCHAR(512).

The choice of the type of key:

We’re storing the full S3 URI (Identifier) (s3://bucket-name/path/to/object) in DB, not just the key.
This keeps it self-contained and makes it easier to resolve the bucket/region later if needed.
Access is always via presigned URLs, so nothing is exposed directly.

Datatype in RDBMS

Now, since we are in Postgres, VARCHAR(512) or TEXT are our options.

1. Use TEXT
In PostgreSQL, there is no performance difference between VARCHAR(n) and TEXT
Why: PostgreSQL stores them the same way under the hood.
Benefit: Using TEXT means we never have to worry about a deep folder structure or a very long filename breaking our database schema in the future.

2. Use VARCHAR(512)
If this is MySQL, VARCHAR is generally preferred over TEXT for "shorter" strings because it is easier to index and stays stored "inline" with the table row.
Why 512? While S3 keys can technically be 1,024 characters, most real-world paths (uploads/2024/05/news/headline-image.png) stay well under 500.
Safety: 512 gives you a massive buffer compared to 255 without the overhead of a full TEXT blob.

Comparison

• Both TEXT and VARCHAR(n) use the same internal storage format in Postgres (called varlena). If we store the string "news/image.jpg", it will take up exactly 15 bytes (14 for the text + 1 for the header) regardless of whether the column is TEXT or VARCHAR
  
• Unlike some older databases, Postgres does not "reserve" the maximum length. If you define a column as VARCHAR(255) but only store 10 characters, you are only billed for those 10 characters.
  
• Both types benefit from TOAST (The Oversized-Attribute Storage Technique).
• Technically, VARCHAR(255) is a tiny bit slower than TEXT because Postgres has to check the string length against the limit (255) every time you insert or update data. TEXT skips this check.
  
• Indexes on TEXT and VARCHAR columns perform identically. S3 path lookups will be just as fast with either type.
  
• If we realize later that our S3 keys are getting longer (e.g., adding unique prefixes or versioning), changing a VARCHAR(255) to VARCHAR(500) in a live AWS database can sometimes require an "Access Exclusive" lock, which could briefly freeze our table.

Conclusion

We are going with TEXT for any path stored in Postgres. It is the standard "best practice" for PostgreSQL on AWS. It gives us the flexibility to handle any S3 path length without any extra cost or performance hit.