Blog
add tag
Jack Douglas
Postgres 9.3 was first released on September 9, 2013, and is now available on db<>fiddle.

9.4 has been 'unavailable' for several years, and that's coming back too (not sure about 8.4 yet). What's the point though, these versions are very long in the tooth?

The main reason is to test out a gradual move to micro-VMs and [Firecracker](https://firecracker-microvm.github.io) for the long tail of older RDBMS vertsions. I think the two most commen positive comments about db<>fiddle are that [it's got a lot of versions](https://news.ycombinator.com/item?id=30631962) available, and that it's kept up to date with new releases. However keeping an ever-growing list of full-fat VMs running 24/7 doesn't scale so well (without making the site expensive to run).

There are pros and cons though. I think the pros outweight the cons, but it's worth listing them out and I'm very happy to get feedback, especially if I've missed any. Exactly how many recent versions of (say) Postgres should be on full VMs is something I'm not sure about yet - it's nice to get a fast response on the versions most commonly used:

```sql
select version_code, sum(fiddle_daily_count) 
from fiddle_daily
where fiddle_daily_on>='2022-01-01' and engine_code='postgres'
group by version_code
order by 2 desc;

┌──────────────┬────────┐
│ version_code │  sum   │
├──────────────┼────────┤
│ 14           │ 103715 │
│ 12           │  73187 │
│ 13           │  44419 │
│ 10           │  16478 │
│ 11           │  14416 │
│ 9.6          │  10460 │
│ 9.5          │   7691 │
└──────────────┴────────┘
```

* disadvantages: 

    1. Firecracker VMs have a cold start that adds about 5s to every run.
    2. The way I'm choosing to implement them means that (for Postgres) only one result is returned per batch, even if multiple statements are in the batch. That's a change from how Postgres engines currently work on db<>fiddle. My reason is that [libpq works like that](https://www.postgresql.org/docs/9.3/libpq-exec.html#LIBPQ-EXEC-SELECT-INFO):

        > The command string can include multiple SQL commands (separated by semicolons). Multiple queries sent in a single PQexec call are processed in a single transaction, unless there are explicit BEGIN/COMMIT commands included in the query string to divide it into multiple transactions. ***Note however that the returned PGresult structure describes only the result of the last command executed from the string.***
        
        I think this means that any library that returns multiple results (eg the [PHP postgres library](https://www.php.net/manual/en/function.pg-send-query.php) or [node-postgres](https://node-postgres.com)) must be doing some kind of parsing to split the statements within the batch. This is something I've always been keen to avoid for db<>fiddle itself, and I have a strong hunch that there will be edge cases where that parsing will break - it's not as simple as just splitting on semi-colons.

* advantages:

    1. With Firecracker, there is no resource limit on the number of RDBMS versions we can provide - because no resources are used at all except while a fiddle is actually running. This makes an even bigger difference for older, less-used versions. I know plenty of old versions of Oracle are still in production, and I'm sure the same is true for some of the other RDBMSs, so having them available is sure to help someone.
    
    1. For Postgres, you get a completely dedicated cluster, not just a dedicated database in a cluster. This means that cluster-level objects like users can be brought into scope.
    
    1. The Firecracker Postgres engines give you superuser access, so you can do (and demonstrate) all sorts of things you can't with a regular fiddle, like [adding extensions on the fly](https://test.dbfiddle.uk/?rdbms=postgres_9.3&fiddle=551898a638716281f5dbaa86b8e87410). This does feel a little risky, but there is some mitigation from the extra isolation of the micro-VMs—they don't even have a network interface—and the extra risk probably isn't going to be avoidable for other programming language fiddle backends that are in the works. I'm considering further isolating the Firecracker VMs from the full VMs on a separate host to add another layer of security.
    
    1. The cold start time can probably be brought down with more modern hardware at some point. The host is currently running a [E5-1650](https://en.wikipedia.org/wiki/List_of_Intel_Xeon_processors_(Sandy_Bridge-based)#Xeon_E5-1650) CPU released in 2013. If faster hardware and later Firecracker versions (or other optimisations) can bring the cold start time down to ~1s I think it becomes an option even for the latest VMs, which would simplify the overall architecture.
    
There are a couple of other things going on in parallel as I gear up for a switch to the new UI that is in the works. Both should be mostly transparent to users of db<>fiddle but I'm mentioning them in case you are interested, and in case issues come up:

1. Everything is moving to Node.js. The new UI is basically a bunch of Lambda functions, replacing the old jumble of PHP that I threw together when I built the site. The engines also used PHP to create the result of each 'run' and new engines are having that code rewritten in Node.

2. The existing engines build up a JSON response. Each engine does this slightly differently because (for example) some return multiple tables of data per batch, and some can display an error with each result whereas others have errors at the batch level. This has always been a bit complex to maintain, and will get much worse if I do go ahead and add all sorts of other engines that aren't RDBMS's.

	New engines are just returning markdown and life is much simpler—not least because we currently need to translate that JSON to markdown on the fly anyway, for cut'n'paste into Stack Overflow.
    
    I added PHP Markdown Extra to the existing site to render the markdown server-side, but this is just a stop-gap: the new site uses [markdown-it](https://github.com/markdown-it/markdown-it) for rendering (just like on TA, but server-side instead of client-side). 

I'm aiming to finish the switch to the new site next week, but it might slip, possibly even back to the end of August. The main remaining job is to move all the existing VMs onto a new host, and there are one or two that could probably use a rebuild along the way. After that adding new (old) engines and languages will be a gradual process.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.