How do you guys move old data from production to an archival location? Say data older than 1 year. Is this done in batches, or in a streaming fashion? Are there tools to do this?

When I used mysql/percona in production, we used partitioned tables for the tables that grew without bound and removed old partitions automatically to a data warehouse.

Given a connection or db-spec from clojure.java.jdbc, how can I validate that connection with a timeout? For example, I want to attempt to make a connection but if a connection cannot be established within 30 seconds then I want to return an error. I see that prepare-statement has a :timeout but get-connection does not. Clearly if the connection string is invalid or a connection cannot be made (because for example, the system is not on VPN) then the prepare-statement function is unreachable

@mark.davidson I do this by using a connection pool library, like https://github.com/brettwooldridge/HikariCP , which has a setting like connectionTimeout that sets this for you

There might be other ways to do this, but I believe they're all JDBC driver specific

Thank you @tanzoniteblack. I’ll take a look at HikariCP or other connection pools.

+1 for connection pooling. We use c3p0 in production at work and we've been very happy with that.

c3p0 periodically checks the DB is available by running a small query on a test table, so it can rotate bad connections out and stand up new ones.

we used to use c3p0 at Yummly, but switched to hikari a few years ago. Does everything we were using c3p0 for, except that it required less configuration to perform well and was simpler for me to understand (coincidentally, I was the one that migrated us)

nothing wrong with c3p0 in general, I just found hikaricp easier to use

If anyone using HikariCP wants to put an example into http://clojure-doc.org/articles/ecosystem/java_jdbc/reusing_connections.html that would be fantastic

There's a clojure helper library for hikari that has documentation on how to use it with jdbc

https://github.com/tomekw/hikari-cp