StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>The thing you are trying to do is table partitioning. Most databases support this feature without the need for manual book keeping.</p> <p>Partitioning will also yield much better results than manually moving parts of the data to a different table. By using partitioning you avoid: - Data inconsistency. Which is easy to introduce because you will move records in bulk and then remove a lot of them from the source table. It's easy to make a mistake and copy only a portion of the data. - Performance drop - moving the data around and the associated overhead from transactions will generally neglect any benefit you got from reducing the size of the ChatCurrent table.</p> <p>For a really quick rundown. Table partitioning allows you to tell the database that parts of the data are stored and retrieved together, this significantly speeds up queries as the database knows that it only has to look into a specific part of the data set. Example: chat's from the current day, last hour, last month etc. You can additionally store each partition on a different drive, that way you can keep your current chatter on a fast SSD drive and your history on regular slower disks.</p> <p>Please refer to your database manual to know the details about how it handles partitioning.</p> <p>Example for PostgreSQL: <a href="http://www.postgresql.org/docs/current/static/ddl-partitioning.html" rel="nofollow">http://www.postgresql.org/docs/current/static/ddl-partitioning.html</a></p> <blockquote> <p>Partitioning refers to splitting what is logically one large table into smaller physical pieces. Partitioning can provide several benefits:</p> <ul> <li><p>Query performance can be improved dramatically in certain situations, particularly when most of the heavily accessed rows of the table are in a single partition or a small number of partitions. The partitioning substitutes for leading columns of indexes, reducing index size and making it more likely that the heavily-used parts of the indexes fit in memory.</p></li> <li><p>When queries or updates access a large percentage of a single partition, performance can be improved by taking advantage of sequential scan of that partition instead of using an index and random access reads scattered across the whole table.</p></li> <li><p>Bulk loads and deletes can be accomplished by adding or removing partitions, if that requirement is planned into the partitioning design. ALTER TABLE NO INHERIT and DROP TABLE are both far faster than a bulk operation. These commands also entirely avoid the VACUUM overhead caused by a bulk DELETE.</p></li> <li><p>Seldom-used data can be migrated to cheaper and slower storage media.</p></li> </ul> </blockquote>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload