Tag Info

Hot answers tagged

4

What is the problem with the table becoming large? Generally, any sort of OLTP query will access the table using an appropriate index in which case the size of the table is more or less irrelevant. The cost of using an index will grow at an O(log(n)) rate-- practically, a b*-tree index will only add one or two levels for any realistically sized table. And ...


3

My suggestion about archiving: Create archive_tablespace (if you want you can separate hardware on archive) Create tables. For example we want to archive table posts. create table posts_all ( LIKE public.posts) ; create table posts_archive () inherits ( public.posts_all) ; alter table public.posts inherits ( public.posts_all ) ; After that ...


2

I recently answered similar question here: database-archive-solutions You should read about postgresql table partitioning, if you have good way to split data in parts.


2

By all means use an unsigned int surrogate key as your primary, clustered index. However, instead of using sequential values, build some padding into the sequence. This means that you'll have to assign the id manually instead of using auto_increment. If you use unsigned int in MySQL, the max value is 4,294,967,295. If you expect to have at most 100,000 ...


2

I would use an auto-number col as the primary key. Add another col for the rank. When adding a new item to the table set If the rank is last set the rank to be MAX(RANK) +1. If the rank is in the middle increment the rank of all other items , by the following. UPDATE Words SET Rank = Rank +1 where Rank >= CurrentRankReplacedId I agree, this is ...


1

I changed my account, so it's already Max. Here, a example : pt-archiver --source h=<server_source>,D=<database_source>,t=<your_table> --dest h=<server_target> --where "date_field < DATE_SUB(NOW(), INTERVAL 3 MONTH)" --limit 1000 --txn-size 1000 --statistics Add the option --dry-run if you want to test. Add the option ...


1

Try pt-archiver from Percona Toolkit, it permits to transfert data on the fly between two MySQL instances. You can for instance use the --where option to filter result set by date. It's particulary well designed for your needs, because you can transfer data and delete it from source in the same command... Official page is here : pt-archiver If you want ...


1

You need to define your requirements more specifically before even thinking about a solution: Why is archiving necessary? It sounds like the system already handles old data, so what is the business need to separate out this data? Performance? Is archive data a read-only snapshot, or are historical data changes possible? If changes are possible, which types ...



Only top voted, non community-wiki answers of a minimum length are eligible