Tag Info

Hot answers tagged

4

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 ...


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 employer wants to keep the data, but not in a database, and wants to be able to restore it to that database if the need arises. If you want the data to be restored later, then best is to Script out schema of the tables that you wish to drop and save it as a sql script. BCP out the data (without using -n switch as -n is for native format which is ...


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

Not sure why your boss doesn't want to keep a backup of the DB on hand in the case of need of future restore, but I guess he has his reasons. The nice thing about exporting this data out to text files is that you could always import them back into databases that aren't necessarily SQL Server in the future - as well as read them with text editors in a pinch. ...


1

The problem is that these deletes are now taking over 10 hours to run. if you are not on Enterprise edition - as Partitioning - switching in and out is only supported in Enterprise edition, I would suggest you to create and ordered view on the top of table that you are deleting data from. This will incur less I/O and generate minimal T-Log. Founded the ...


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