Postgresql – How does Postgres handle integer data of different sizes in the same column

If you have queries generating temporary files, it could indicate that work_mem is not sufficiently sized or that the query is not optimised.

Try setting the parameter log_temp_files = 0 in your postgresql.conf. This will log the creation of temporary files and the statement being executed.

Additionally an explain analyze of your query may show disk sort happening.

Taking the log output, you will see the size of temp files created by your query. You can use this to either fine tune the query or fine tune the work_mem parameter.

UPDATE: see this answer for a working example of the below

How would I store it? What you've got is

• User data (formatted_address)
• Normalized address breakdown (address_components)

I would store only the formatted_address or whatever the user provided. Why?

• I don't trust Django or whatever you're integrating with
• I want the ability to re-normalize addresses when new data comes out.

PostGIS is a PostgreSQL extension that provides address_standardizer. You can install standardize_address like this,

CREATE EXTENSION address_standardizer;
CREATE EXTENSION address_standardizer_data_us;

From there you can provide the formatted address to standardize_address

SELECT standardize_address( formatted_address )

This will return a composite type stdaddr. I may cache this against the version of the extension and the timestamp of the call to standardize_address.

The stdaddr composite type will also be much easier to geocode which you always seem to need at some point if you're going down this path.

All of that said, Django appears to be using Google Maps to normalize their addresses. If that works for you, you can run with it. I'm sure that's not a bad source of address normalization. How you store that, will depend entirely on how you want to query it. If you're even querying it at all and not just presenting a profile. If you're not JOINing based on any of that I think a new column django_google_maps jsonb is a great starting point.