1

I have the following DDL for a Postgres table that was generated by JPA:

postgres=# \d+ mytable;
                                                     Table "public.mytable"
       Column       |            Type             | Collation | Nullable | Default | Storage  | Compression | Stats target | Description 
--------------------+-----------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
 group_name         | character varying(255)      |           | not null |         | extended |             |              | 
 name               | character varying(255)      |           | not null |         | extended |             |              | 
 base               | numeric(18,3)               |           | not null |         | main     |             |              | 
 ts                 | timestamp(6) with time zone |           | not null |         | plain    |             |              | 
 main_group_name    | character varying(255)      |           | not null |         | extended |             |              | 
 plu                | character varying(255)      |           | not null |         | extended |             |              | 
 id                 | uuid                        |           | not null |         | plain    |             |              | 
 rate               | numeric(18,3)               |           | not null |         | main     |             |              | 
 unit               | numeric(18,3)               |           | not null |         | main     |             |              | 
 totals             | numeric(18,3)               |           |          |         | main     |             |              | 
 quantity           | numeric(18,3)               |           |          |         | main     |             |              | 
Indexes:
    "mytable_pkey" PRIMARY KEY, btree (group_name, name, base, ts, main_group_name, plu, id, rate, unit)
    "idxkil334988tyckig28rd87vdxk" btree (id, hour)

Access method: heap

The access pattern of this table consists of UPSERT-ing data and then reading from it. DELETEs will be infrequent and done in batches on data older than X days based on ts.

In the absolute worst case, I am expecting this table to contain upwards of 100's of billions of entries per year.

A more realistic usage pattern would put it around a few billion rows, per year. Currently, the last 2 years of data would be kept but this may change.

In addition to potentially (read: eventually) partitioning this table, I want to try and squeeze more performance and storage out of it by aligning the column types in a more optimal way.

From my understanding of the Postgres docs and verification using the max allowed values and pg_column_size(), the above layout would result in the following worst case alignment:

       Column       |            Type             | max_size | variable_length      
--------------------+-----------------------------+----------|------------------    
 group_name         | character varying(255)      | 255 + 4  | true                 
 name               | character varying(255)      | 255 + 4  | true                 
 base               | numeric(18,3)               | 16       | true                 
 ts                 | timestamp(6) with time zone | 8        | false                
 main_group_name    | character varying(255)      | 255 + 4  | true                 
 plu                | character varying(255)      | 255 + 4  | true                 
 id                 | uuid                        | 16       | false                
 rate               | numeric(18,3)               | 16       | true                 
 unit               | numeric(18,3)               | 16       | true                 
 totals             | numeric(18,3)               | 16       | true                 
 quantity           | numeric(18,3)               | 16       | true                 

leading to a worst case of

259 + 5 padding
 + 259 + 5 padding
 + 16
 + 8
 + 259 + 5 padding
 + 259 + 5 padding
 + 16
 + 16
 + 16
 + 16
 + 16
-------
1160

more than 1kB per row not including headers, pointers etc. This is purely for the row data and does not include the PK index which unfortunately almost duplicates this data.

My questions are the following:

  1. Are my worst-case estimations above correct?
  2. Will placing the non-variable-length columns (ts, id) first yield any significant benefits?
  3. Barring a change in business requirements (e.g. char(255) => char(30)) or table partitioning, are there other optimizations that I can undertake on this table?
6
  • 1
    FWIW, Partitioning is a data management tool, it's not meant for performance optimization regarding DQL statements.
    – J.D.
    Commented Jan 5 at 19:31
  • I'm not sure I see a clean distinction in this case; though performance optimization may only be a side-effect of the partitioning, it is an effect nonetheless, no? But yes, the partitioning would serve more to manage and delete obsolete data rather than improve performance directly.
    – user991710
    Commented Jan 5 at 19:37
  • 1
    Note that varchar(N) talks about characters, not bytes. So a single varchar(255) field can be up to a kilobyte in size in UTF8 when using 4-byte characters. Do you actually have different group names in each tuple? Maybe it’s possible to deduplicate them into a separate table, leaving an int reference here?
    – Melkij
    Commented Jan 5 at 19:48
  • "though performance optimization may only be a side-effect of the partitioning, it is an effect nonetheless, no?" - And putting less gas in your car will result in better gas mileage per gallon because the car will weigh less overall, doesn't mean it's the correct way to go about things. The reason partitioning is redundant and not a tool meant for performance optimization of DQL queries is because indexing does an exponentially more efficient job at "partitioning" the data (O(log(n)) search time complexity) than the linear algorithm (O(n)) of Partitioning.
    – J.D.
    Commented Jan 5 at 19:51
  • 1
    If you want to insert billions of rows, then the varchar(255) columns are suspicious. If they have low cardinality, they should be in a separate table with a foreign key reference. In fact several columns could probably be moved to another much smaller table. The primary key is also suspicious, why so many columns?
    – bobflux
    Commented Jan 5 at 21:08

1 Answer 1

1

The "worst case" size estimation is almost, but not quite correct.

You are missing that "varlena" data types (including varchar and numeric) have a "packed" storage format on disk, that reduces the header from 4 bytes (or more) to a single byte for a storage size below 128 bytes.

You are missing that integer alignment only kicks in above that limit (when the header of a "varlena" column is expanded to an actual integer).

You are missing that UNICODE characters occupy 1-4 bytes per character. Typically, ASCII characters dominate. Then the avg. space required per character is slightly above 1.0. But the worst case is 1020 bytes + overhead for a single varchar(255) column, changing the name of the game ...

2 of such worst-case varchar columns (you have 4) bring the row size above the default limit to trigger the TOAST management code.

But varchar(255) hardly ever makes any sense to begin with. I have yet to see my first real-life application where a maximum of 255 characters makes sense.

And your PK is an abomination that would eventually raise an error in the worst case. Use a much smaller PK (or any index for that matter). See:

All of this is academic. I doubt you need varchar(255) for "group names". Nor should you store lengthy group names in every single row. The table design is nonsense for "billions of rows" - key word normalization.

Get your relational design and data types straight before playing "column tetris". And think about possible partitioning only after that.

See:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.