Menu
I am storing sent emails in a RDBMS including the to address, from address, and email body.
The body can really be any arbitrary amount of text, and I won't ever care to search on it.
Are there any performance issues I should worry about when having a potentially large column that isn't used too often in one of my most frequently accessed tables (Emails) ?
(This project is written in Rails)
453
Option One: use LOB storage. In relational databases, a particular data type exists to store big amounts of data: LOB (Large OBject). Once we need to store large text in the database, we can start with defining a LOB column. All we need to do is mark the docText attribute with the @Lob annotation.
Yes, You Can Keep Full-Text Search in Postgres You can get even deeper and make your Postgres full-text search even more robust, by implementing features such as highlighting results, or writing your own custom dictionaries or functions.
What is PostgreSQL Text datatype? In PostgreSQL, the text data type is used to keep the character of infinite length. And the text data type can hold a string with a maximum length of 65,535 bytes.
You should have no problem using Postgres for reporting and analytics. Postgres generally provides fast and stable query performance and can quickly access up to a terabyte on a single instance. On the flipside, if you have a smaller (or growing) team in need of big data insights, Postgres isn't a cure-all.
postgresql stores large objects in a secondary area. You can read about it here: TOAST. The main concern will be keeping the large object out of the select list of queries that return many rows, so that you avoid visiting the secondary storage area.
If and when you do decide to add search functionality to the body text, you will need to use a full text strategy, which is well supported in Postgres, but is somewhat non intuitive. The topic receives a full chapter of treatment in the manual.
87
No, you don't need to worry about that.
Technically there is no difference in storage between a e.g. varchar(5) and a text column.
Quote from the manual
there is no performance difference among these three types, apart from increased storage space when using the blank-padded type
The three types mentioned there are char, varchar and text. Where char is the "blank-padded type".
23
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
